2 // System.Data.SqlClient.SqlParameter.cs
5 // Rodrigo Moya (rodrigo@ximian.com)
6 // Daniel Morgan (danmorg@sc.rr.com)
7 // Tim Coleman (tim@timcoleman.com)
8 // Diego Caravana (diego@toth.it)
10 // (C) Ximian, Inc. 2002
11 // Copyright (C) Tim Coleman, 2002
15 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
17 // Permission is hereby granted, free of charge, to any person obtaining
18 // a copy of this software and associated documentation files (the
19 // "Software"), to deal in the Software without restriction, including
20 // without limitation the rights to use, copy, modify, merge, publish,
21 // distribute, sublicense, and/or sell copies of the Software, and to
22 // permit persons to whom the Software is furnished to do so, subject to
23 // the following conditions:
25 // The above copyright notice and this permission notice shall be
26 // included in all copies or substantial portions of the Software.
28 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
29 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
30 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
31 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
32 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
33 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
34 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
38 using Mono
.Data
.Tds
.Protocol
;
40 using System
.ComponentModel
;
42 using System
.Data
.Common
;
43 using System
.Runtime
.InteropServices
;
46 namespace System
.Data
.SqlClient
{
47 [TypeConverterAttribute (typeof (SqlParameterConverter
))]
48 public sealed class SqlParameter
: MarshalByRefObject
, IDbDataParameter
, IDataParameter
, ICloneable
52 TdsMetaParameter metaParameter
;
54 SqlParameterCollection container
= null;
56 ParameterDirection direction
= ParameterDirection
.Input
;
58 bool isSizeSet
= false;
59 bool isTypeSet
= false;
63 DataRowVersion sourceVersion
;
69 public SqlParameter ()
70 : this (String
.Empty
, SqlDbType
.NVarChar
, 0, ParameterDirection
.Input
, false, 0, 0, String
.Empty
, DataRowVersion
.Current
, null)
74 public SqlParameter (string parameterName
, object value)
76 metaParameter
= new TdsMetaParameter (parameterName
, value);
77 this.sourceVersion
= DataRowVersion
.Current
;
81 public SqlParameter (string parameterName
, SqlDbType dbType
)
82 : this (parameterName
, dbType
, 0, ParameterDirection
.Input
, false, 0, 0, String
.Empty
, DataRowVersion
.Current
, null)
86 public SqlParameter (string parameterName
, SqlDbType dbType
, int size
)
87 : this (parameterName
, dbType
, size
, ParameterDirection
.Input
, false, 0, 0, String
.Empty
, DataRowVersion
.Current
, null)
91 public SqlParameter (string parameterName
, SqlDbType dbType
, int size
, string sourceColumn
)
92 : this (parameterName
, dbType
, size
, ParameterDirection
.Input
, false, 0, 0, sourceColumn
, DataRowVersion
.Current
, null)
96 [EditorBrowsable (EditorBrowsableState
.Advanced
)]
97 public SqlParameter (string parameterName
, SqlDbType dbType
, int size
, ParameterDirection direction
, bool isNullable
, byte precision
, byte scale
, string sourceColumn
, DataRowVersion sourceVersion
, object value)
99 metaParameter
= new TdsMetaParameter (parameterName
, size
, isNullable
, precision
, scale
, value);
102 Direction
= direction
;
103 SourceColumn
= sourceColumn
;
104 SourceVersion
= sourceVersion
;
107 // This constructor is used internally to construct a
108 // SqlParameter. The value array comes from sp_procedure_params_rowset.
109 // This is in SqlCommand.DeriveParameters.
110 internal SqlParameter (object[] dbValues
)
114 Direction
= ParameterDirection
.Input
;
116 ParameterName
= (string) dbValues
[3];
118 switch ((short) dbValues
[5]) {
120 Direction
= ParameterDirection
.Input
;
123 Direction
= ParameterDirection
.Output
;
126 Direction
= ParameterDirection
.InputOutput
;
129 Direction
= ParameterDirection
.ReturnValue
;
133 IsNullable
= (bool) dbValues
[8];
135 if (dbValues
[12] != null)
136 Precision
= (byte) ((short) dbValues
[12]);
137 if (dbValues
[13] != null)
138 Scale
= (byte) ((short) dbValues
[13]);
140 SetDbTypeName ((string) dbValues
[16]);
143 #endregion // Constructors
147 // Used to ensure that only one collection can contain this
149 internal SqlParameterCollection Container
{
150 get { return container; }
151 set { container = value; }
155 [DataCategory ("Data")]
156 [DataSysDescription ("The parameter generic type.")]
157 [DesignerSerializationVisibility (DesignerSerializationVisibility
.Hidden
)]
158 [RefreshProperties (RefreshProperties
.All
)]
159 public DbType DbType
{
160 get { return dbType; }
167 [DataCategory ("Data")]
168 [DataSysDescription ("Input, output, or bidirectional parameter.")]
169 [DefaultValue (ParameterDirection
.Input
)]
170 public ParameterDirection Direction
{
171 get { return direction; }
174 switch( direction
) {
175 case ParameterDirection
.Output
:
176 MetaParameter
.Direction
= TdsParameterDirection
.Output
;
178 case ParameterDirection
.InputOutput
:
179 MetaParameter
.Direction
= TdsParameterDirection
.InputOutput
;
181 case ParameterDirection
.ReturnValue
:
182 MetaParameter
.Direction
= TdsParameterDirection
.ReturnValue
;
188 internal TdsMetaParameter MetaParameter
{
189 get { return metaParameter; }
192 string IDataParameter
.ParameterName
{
193 get { return metaParameter.ParameterName; }
194 set { metaParameter.ParameterName = value; }
198 [DataSysDescription ("a design-time property used for strongly typed code-generation.")]
199 [DefaultValue (false)]
201 [EditorBrowsable (EditorBrowsableState
.Advanced
)]
202 public bool IsNullable
{
203 get { return metaParameter.IsNullable; }
204 set { metaParameter.IsNullable = value; }
208 [DataCategory ("Data")]
209 [DataSysDescription ("Offset in variable length data types.")]
212 get { return offset; }
213 set { offset = value; }
216 [DataSysDescription ("Name of the parameter, like '@p1'")]
218 public string ParameterName
{
219 get { return metaParameter.ParameterName; }
220 set { metaParameter.ParameterName = value; }
223 [DataCategory ("Data")]
224 [DataSysDescription ("For decimal, numeric, varnumeric DBTypes.")]
226 public byte Precision
{
227 get { return metaParameter.Precision; }
228 set { metaParameter.Precision = value; }
231 [DataCategory ("Data")]
232 [DataSysDescription ("For decimal, numeric, varnumeric DBTypes.")]
235 get { return metaParameter.Scale; }
236 set { metaParameter.Scale = value; }
239 [DataCategory ("Data")]
240 [DataSysDescription ("Size of variable length datatypes (strings & arrays).")]
243 get { return metaParameter.Size; }
244 set { metaParameter.Size = value; }
247 [DataCategory ("Data")]
248 [DataSysDescription ("When used by a DataAdapter.Update, the source column name that is used to find the DataSetColumn name in the ColumnMappings. This is to copy a value between the parameter and a datarow.")]
250 public string SourceColumn
{
251 get { return sourceColumn; }
252 set { sourceColumn = value; }
255 [DataCategory ("Data")]
256 [DataSysDescription ("When used by a DataAdapter.Update (UpdateCommand only), the version of the DataRow value that is used to update the data source.")]
257 [DefaultValue (DataRowVersion
.Current
)]
258 public DataRowVersion SourceVersion
{
259 get { return sourceVersion; }
260 set { sourceVersion = value; }
263 [DataCategory ("Data")]
264 [DataSysDescription ("The parameter native type.")]
265 [DefaultValue (SqlDbType
.NVarChar
)]
266 [RefreshProperties (RefreshProperties
.All
)]
267 public SqlDbType SqlDbType
{
268 get { return sqlDbType; }
270 SetSqlDbType (value);
275 [DataCategory ("Data")]
276 [DataSysDescription ("Value of the parameter.")]
277 [DefaultValue (null)]
278 [TypeConverterAttribute (typeof (StringConverter
))]
279 public object Value
{
280 get { return metaParameter.Value; }
283 InferSqlType (value);
284 metaParameter
.Value
= value;
288 #endregion // Properties
292 object ICloneable
.Clone ()
294 return new SqlParameter (ParameterName
, SqlDbType
, Size
, Direction
, IsNullable
, Precision
, Scale
, SourceColumn
, SourceVersion
, Value
);
297 // If the value is set without the DbType/SqlDbType being set, then we
298 // infer type information.
299 private void InferSqlType (object value)
301 Type type
= value.GetType ();
303 string exception
= String
.Format ("The parameter data type of {0} is invalid.", type
.Name
);
305 switch (type
.FullName
) {
307 SetSqlDbType (SqlDbType
.BigInt
);
309 case "System.Boolean":
310 SetSqlDbType (SqlDbType
.Bit
);
312 case "System.String":
313 SetSqlDbType (SqlDbType
.NVarChar
);
315 case "System.DateTime":
316 SetSqlDbType (SqlDbType
.DateTime
);
318 case "System.Decimal":
319 SetSqlDbType (SqlDbType
.Decimal
);
321 case "System.Double":
322 SetSqlDbType (SqlDbType
.Float
);
324 case "System.Byte[]":
325 SetSqlDbType (SqlDbType
.VarBinary
);
328 SetSqlDbType (SqlDbType
.TinyInt
);
331 SetSqlDbType (SqlDbType
.Int
);
333 case "System.Single":
334 SetSqlDbType (SqlDbType
.Real
);
337 SetSqlDbType (SqlDbType
.SmallInt
);
340 SetSqlDbType (SqlDbType
.UniqueIdentifier
);
342 case "System.Object":
343 SetSqlDbType (SqlDbType
.Variant
);
346 throw new ArgumentException (exception
);
350 // When the DbType is set, we also set the SqlDbType, as well as the SQL Server
351 // string representation of the type name. If the DbType is not convertible
352 // to an SqlDbType, throw an exception.
353 private void SetDbType (DbType type
)
355 string exception
= String
.Format ("No mapping exists from DbType {0} to a known SqlDbType.", type
);
358 case DbType
.AnsiString
:
359 MetaParameter
.TypeName
= "varchar";
360 sqlDbType
= SqlDbType
.VarChar
;
362 case DbType
.AnsiStringFixedLength
:
363 MetaParameter
.TypeName
= "char";
364 sqlDbType
= SqlDbType
.Char
;
367 MetaParameter
.TypeName
= "varbinary";
368 sqlDbType
= SqlDbType
.VarBinary
;
371 MetaParameter
.TypeName
= "bit";
372 sqlDbType
= SqlDbType
.Bit
;
375 MetaParameter
.TypeName
= "tinyint";
376 sqlDbType
= SqlDbType
.TinyInt
;
378 case DbType
.Currency
:
379 sqlDbType
= SqlDbType
.Money
;
380 MetaParameter
.TypeName
= "money";
383 case DbType
.DateTime
:
384 MetaParameter
.TypeName
= "datetime";
385 sqlDbType
= SqlDbType
.DateTime
;
388 MetaParameter
.TypeName
= "decimal";
389 sqlDbType
= SqlDbType
.Decimal
;
392 MetaParameter
.TypeName
= "float";
393 sqlDbType
= SqlDbType
.Float
;
396 MetaParameter
.TypeName
= "uniqueidentifier";
397 sqlDbType
= SqlDbType
.UniqueIdentifier
;
400 MetaParameter
.TypeName
= "smallint";
401 sqlDbType
= SqlDbType
.SmallInt
;
404 MetaParameter
.TypeName
= "int";
405 sqlDbType
= SqlDbType
.Int
;
408 MetaParameter
.TypeName
= "bigint";
409 sqlDbType
= SqlDbType
.BigInt
;
412 MetaParameter
.TypeName
= "sql_variant";
413 sqlDbType
= SqlDbType
.Variant
;
416 MetaParameter
.TypeName
= "real";
417 sqlDbType
= SqlDbType
.Real
;
420 MetaParameter
.TypeName
= "nvarchar";
421 sqlDbType
= SqlDbType
.NVarChar
;
423 case DbType
.StringFixedLength
:
424 MetaParameter
.TypeName
= "nchar";
425 sqlDbType
= SqlDbType
.NChar
;
428 MetaParameter
.TypeName
= "datetime";
429 sqlDbType
= SqlDbType
.DateTime
;
432 throw new ArgumentException (exception
);
437 // Used by internal constructor which has a SQL Server typename
438 private void SetDbTypeName (string dbTypeName
)
440 switch (dbTypeName
.ToLower ()) {
442 SqlDbType
= SqlDbType
.BigInt
;
445 SqlDbType
= SqlDbType
.Binary
;
448 SqlDbType
= SqlDbType
.Bit
;
451 SqlDbType
= SqlDbType
.Char
;
454 SqlDbType
= SqlDbType
.DateTime
;
457 SqlDbType
= SqlDbType
.Decimal
;
460 SqlDbType
= SqlDbType
.Float
;
463 SqlDbType
= SqlDbType
.Image
;
466 SqlDbType
= SqlDbType
.Int
;
469 SqlDbType
= SqlDbType
.Money
;
472 SqlDbType
= SqlDbType
.NChar
;
475 SqlDbType
= SqlDbType
.NText
;
478 SqlDbType
= SqlDbType
.NVarChar
;
481 SqlDbType
= SqlDbType
.Real
;
483 case "smalldatetime":
484 SqlDbType
= SqlDbType
.SmallDateTime
;
487 SqlDbType
= SqlDbType
.SmallInt
;
490 SqlDbType
= SqlDbType
.SmallMoney
;
493 SqlDbType
= SqlDbType
.Text
;
496 SqlDbType
= SqlDbType
.Timestamp
;
499 SqlDbType
= SqlDbType
.TinyInt
;
501 case "uniqueidentifier":
502 SqlDbType
= SqlDbType
.UniqueIdentifier
;
505 SqlDbType
= SqlDbType
.VarBinary
;
508 SqlDbType
= SqlDbType
.VarChar
;
511 SqlDbType
= SqlDbType
.Variant
;
516 // When the SqlDbType is set, we also set the DbType, as well as the SQL Server
517 // string representation of the type name. If the SqlDbType is not convertible
518 // to a DbType, throw an exception.
519 private void SetSqlDbType (SqlDbType type
)
521 string exception
= String
.Format ("No mapping exists from SqlDbType {0} to a known DbType.", type
);
524 case SqlDbType
.BigInt
:
525 MetaParameter
.TypeName
= "bigint";
526 dbType
= DbType
.Int64
;
528 case SqlDbType
.Binary
:
529 MetaParameter
.TypeName
= "binary";
530 dbType
= DbType
.Binary
;
532 case SqlDbType
.Timestamp
:
533 MetaParameter
.TypeName
= "timestamp";
534 dbType
= DbType
.Binary
;
536 case SqlDbType
.VarBinary
:
537 MetaParameter
.TypeName
= "varbinary";
538 dbType
= DbType
.Binary
;
541 MetaParameter
.TypeName
= "bit";
542 dbType
= DbType
.Boolean
;
545 MetaParameter
.TypeName
= "char";
546 dbType
= DbType
.AnsiStringFixedLength
;
548 case SqlDbType
.DateTime
:
549 MetaParameter
.TypeName
= "datetime";
550 dbType
= DbType
.DateTime
;
552 case SqlDbType
.SmallDateTime
:
553 MetaParameter
.TypeName
= "smalldatetime";
554 dbType
= DbType
.DateTime
;
556 case SqlDbType
.Decimal
:
557 MetaParameter
.TypeName
= "decimal";
558 dbType
= DbType
.Decimal
;
560 case SqlDbType
.Float
:
561 MetaParameter
.TypeName
= "float";
562 dbType
= DbType
.Double
;
564 case SqlDbType
.Image
:
565 MetaParameter
.TypeName
= "image";
566 dbType
= DbType
.Binary
;
569 MetaParameter
.TypeName
= "int";
570 dbType
= DbType
.Int32
;
572 case SqlDbType
.Money
:
573 MetaParameter
.TypeName
= "money";
574 dbType
= DbType
.Currency
;
576 case SqlDbType
.SmallMoney
:
577 MetaParameter
.TypeName
= "smallmoney";
578 dbType
= DbType
.Currency
;
580 case SqlDbType
.NChar
:
581 MetaParameter
.TypeName
= "nchar";
582 dbType
= DbType
.StringFixedLength
;
584 case SqlDbType
.NText
:
585 MetaParameter
.TypeName
= "ntext";
586 dbType
= DbType
.String
;
588 case SqlDbType
.NVarChar
:
589 MetaParameter
.TypeName
= "nvarchar";
590 dbType
= DbType
.String
;
593 MetaParameter
.TypeName
= "real";
594 dbType
= DbType
.Single
;
596 case SqlDbType
.SmallInt
:
597 MetaParameter
.TypeName
= "smallint";
598 dbType
= DbType
.Int16
;
601 MetaParameter
.TypeName
= "text";
602 dbType
= DbType
.AnsiString
;
604 case SqlDbType
.VarChar
:
605 MetaParameter
.TypeName
= "varchar";
606 dbType
= DbType
.AnsiString
;
608 case SqlDbType
.TinyInt
:
609 MetaParameter
.TypeName
= "tinyint";
610 dbType
= DbType
.Byte
;
612 case SqlDbType
.UniqueIdentifier
:
613 MetaParameter
.TypeName
= "uniqueidentifier";
614 dbType
= DbType
.Guid
;
616 case SqlDbType
.Variant
:
617 MetaParameter
.TypeName
= "sql_variant";
618 dbType
= DbType
.Object
;
621 throw new ArgumentException (exception
);
626 public override string ToString()
628 return ParameterName
;
631 #endregion // Methods