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)
9 // Umadevi S (sumadevi@novell.com)
11 // (C) Ximian, Inc. 2002
12 // Copyright (C) Tim Coleman, 2002
16 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
18 // Permission is hereby granted, free of charge, to any person obtaining
19 // a copy of this software and associated documentation files (the
20 // "Software"), to deal in the Software without restriction, including
21 // without limitation the rights to use, copy, modify, merge, publish,
22 // distribute, sublicense, and/or sell copies of the Software, and to
23 // permit persons to whom the Software is furnished to do so, subject to
24 // the following conditions:
26 // The above copyright notice and this permission notice shall be
27 // included in all copies or substantial portions of the Software.
29 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
30 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
31 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
32 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
33 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
34 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
35 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
39 using Mono
.Data
.Tds
.Protocol
;
41 using System
.ComponentModel
;
43 using System
.Data
.Common
;
44 using System
.Data
.SqlTypes
;
45 using System
.Runtime
.InteropServices
;
48 namespace System
.Data
.SqlClient
{
49 [TypeConverterAttribute (typeof (SqlParameterConverter
))]
51 public sealed class SqlParameter
: DbParameter
, IDbDataParameter
, IDataParameter
, ICloneable
53 public sealed class SqlParameter
: MarshalByRefObject
, IDbDataParameter
, IDataParameter
, ICloneable
58 TdsMetaParameter metaParameter
;
60 SqlParameterCollection container
= null;
62 ParameterDirection direction
= ParameterDirection
.Input
;
64 bool isVariableSizeType
= false;
65 bool isTypeSet
= false;
69 DataRowVersion sourceVersion
;
75 public SqlParameter ()
76 : this (String
.Empty
, SqlDbType
.NVarChar
, 0, ParameterDirection
.Input
, false, 0, 0, String
.Empty
, DataRowVersion
.Current
, null)
81 public SqlParameter (string parameterName
, object value)
83 metaParameter
= new TdsMetaParameter (parameterName
, SqlTypeToFrameworkType (value));
84 this.sourceVersion
= DataRowVersion
.Current
;
88 public SqlParameter (string parameterName
, SqlDbType dbType
)
89 : this (parameterName
, dbType
, 0, ParameterDirection
.Input
, false, 0, 0, String
.Empty
, DataRowVersion
.Current
, null)
93 public SqlParameter (string parameterName
, SqlDbType dbType
, int size
)
94 : this (parameterName
, dbType
, size
, ParameterDirection
.Input
, false, 0, 0, String
.Empty
, DataRowVersion
.Current
, null)
98 public SqlParameter (string parameterName
, SqlDbType dbType
, int size
, string sourceColumn
)
99 : this (parameterName
, dbType
, size
, ParameterDirection
.Input
, false, 0, 0, sourceColumn
, DataRowVersion
.Current
, null)
103 [EditorBrowsable (EditorBrowsableState
.Advanced
)]
104 public SqlParameter (string parameterName
, SqlDbType dbType
, int size
, ParameterDirection direction
, bool isNullable
, byte precision
, byte scale
, string sourceColumn
, DataRowVersion sourceVersion
, object value)
106 metaParameter
= new TdsMetaParameter (parameterName
, size
,
107 isNullable
, precision
,
109 SqlTypeToFrameworkType (value));
111 Direction
= direction
;
112 SourceColumn
= sourceColumn
;
113 SourceVersion
= sourceVersion
;
116 // This constructor is used internally to construct a
117 // SqlParameter. The value array comes from sp_procedure_params_rowset.
118 // This is in SqlCommand.DeriveParameters.
119 internal SqlParameter (object[] dbValues
)
120 : this (dbValues
[3].ToString (), String
.Empty
)
124 Direction
= ParameterDirection
.Input
;
126 ParameterName
= (string) dbValues
[3];
128 switch ((short) dbValues
[5]) {
130 Direction
= ParameterDirection
.Input
;
133 Direction
= ParameterDirection
.Output
;
136 Direction
= ParameterDirection
.InputOutput
;
139 Direction
= ParameterDirection
.ReturnValue
;
142 Direction
= ParameterDirection
.Input
;
145 IsNullable
= (dbValues
[8] != null &&
146 dbValues
[8] != DBNull
.Value
) ? (bool) dbValues
[8] : false;
148 if (dbValues
[12] != null && dbValues
[12] != DBNull
.Value
)
149 Precision
= (byte) ((short) dbValues
[12]);
151 if (dbValues
[13] != null && dbValues
[13] != DBNull
.Value
)
152 Scale
= (byte) ( (short) dbValues
[13]);
154 SetDbTypeName ((string) dbValues
[16]);
157 #endregion // Constructors
161 // Used to ensure that only one collection can contain this
163 internal SqlParameterCollection Container
{
164 get { return container; }
165 set { container = value; }
168 internal void CheckIfInitialized ()
171 throw new Exception ("all parameters to have an explicity set type");
173 if (isVariableSizeType
) {
174 if (SqlDbType
== SqlDbType
.Decimal
&& Precision
== 0)
175 throw new Exception ("Parameter of type 'Decimal' have an explicitly set Precision and Scale");
177 throw new Exception ("all variable length parameters to have an explicitly set non-zero Size");
181 #if ONLY_1_0 || ONLY_1_1
183 [DataSysDescription ("The parameter generic type.")]
184 [DesignerSerializationVisibility (DesignerSerializationVisibility
.Hidden
)]
185 [RefreshProperties (RefreshProperties
.All
)]
187 [DataCategory ("Data")]
193 get { return dbType; }
200 [DataCategory ("Data")]
201 #if ONLY_1_0 || ONLY_1_1
202 [DataSysDescription ("Input, output, or bidirectional parameter.")]
203 [DefaultValue (ParameterDirection
.Input
)]
206 [RefreshProperties (RefreshProperties
.All
)]
212 ParameterDirection Direction
{
213 get { return direction; }
216 switch( direction
) {
217 case ParameterDirection
.Output
:
218 MetaParameter
.Direction
= TdsParameterDirection
.Output
;
220 case ParameterDirection
.InputOutput
:
221 MetaParameter
.Direction
= TdsParameterDirection
.InputOutput
;
223 case ParameterDirection
.ReturnValue
:
224 MetaParameter
.Direction
= TdsParameterDirection
.ReturnValue
;
230 internal TdsMetaParameter MetaParameter
{
231 get { return metaParameter; }
234 #if ONLY_1_0 || ONLY_1_1
236 [DataSysDescription ("a design-time property used for strongly typed code-generation.")]
237 [DefaultValue (false)]
239 [EditorBrowsable (EditorBrowsableState
.Advanced
)]
246 get { return metaParameter.IsNullable; }
247 set { metaParameter.IsNullable = value; }
251 [DataCategory ("Data")]
252 #if ONLY_1_0 || ONLY_1_1
253 [DataSysDescription ("Offset in variable length data types.")]
257 [EditorBrowsable (EditorBrowsableState
.Advanced
)]
260 get { return offset; }
261 set { offset = value; }
264 #if ONLY_1_0 || ONLY_1_1
265 [DataSysDescription ("Name of the parameter, like '@p1'")]
272 string ParameterName
{
273 get { return metaParameter.ParameterName; }
274 set { metaParameter.ParameterName = value; }
277 [DataCategory ("Data")]
278 #if ONLY_1_0 || ONLY_1_1
279 [DataSysDescription ("For decimal, numeric, varnumeric DBTypes.")]
284 [EditorBrowsable (EditorBrowsableState
.Never
)]
286 public byte Precision
{
287 get { return metaParameter.Precision; }
288 set { metaParameter.Precision = value; }
291 [DataCategory ("Data")]
292 #if ONLY_1_0 || ONLY_1_1
293 [DataSysDescription ("For decimal, numeric, varnumeric DBTypes.")]
298 [EditorBrowsable (EditorBrowsableState
.Never
)]
301 get { return metaParameter.Scale; }
302 set { metaParameter.Scale = value; }
305 [DataCategory ("Data")]
306 #if ONLY_1_0 || ONLY_1_1
307 [DataSysDescription ("Size of variable length data types (string & arrays).")]
315 get { return metaParameter.Size; }
316 set { metaParameter.Size = value; }
319 [DataCategory ("Data")]
320 #if ONLY_1_0 || ONLY_1_1
321 [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.")]
328 string SourceColumn
{
329 get { return sourceColumn; }
330 set { sourceColumn = value; }
333 [DataCategory ("Data")]
334 #if ONLY_1_0 || ONLY_1_1
335 [DataSysDescription ("When used by a DataAdapter.Update (UpdateCommand only), the version of the DataRow value that is used to update the data source.")]
336 [DefaultValue (DataRowVersion
.Current
)]
342 DataRowVersion SourceVersion
{
343 get { return sourceVersion; }
344 set { sourceVersion = value; }
347 [DataCategory ("Data")]
348 #if ONLY_1_0 || ONLY_1_1
349 [DataSysDescription ("The parameter native type.")]
350 [DefaultValue (SqlDbType
.NVarChar
)]
352 [RefreshProperties (RefreshProperties
.All
)]
354 [DbProviderSpecificTypeProperty(true)]
356 public SqlDbType SqlDbType
{
357 get { return sqlDbType; }
359 SetSqlDbType (value);
364 [DataCategory ("Data")]
365 #if ONLY_1_0 || ONLY_1_1
366 [DataSysDescription ("Value of the parameter.")]
367 [DefaultValue (null)]
369 [TypeConverterAttribute (typeof (StringConverter
))]
371 [RefreshProperties (RefreshProperties
.All
)]
378 get { return metaParameter.Value; }
381 InferSqlType (value);
382 metaParameter
.Value
= SqlTypeToFrameworkType (value);
387 // public SqlCompareOptions CompareInfo{
391 public override bool SourceColumnNullMapping
{
392 get { return false ; }
397 #endregion // Properties
401 object ICloneable
.Clone ()
403 return new SqlParameter (ParameterName
, SqlDbType
, Size
, Direction
, IsNullable
, Precision
, Scale
, SourceColumn
, SourceVersion
, Value
);
406 // If the value is set without the DbType/SqlDbType being set, then we
407 // infer type information.
408 private void InferSqlType (object value)
410 if (value == null || value == DBNull
.Value
)
413 Type type
= value.GetType ();
415 string exception
= String
.Format ("The parameter data type of {0} is invalid.", type
.Name
);
417 switch (type
.FullName
) {
419 case "System.Data.SqlTypes.SqlInt64":
420 SetSqlDbType (SqlDbType
.BigInt
);
422 case "System.Boolean":
423 case "System.Data.SqlTypes.SqlBoolean":
424 SetSqlDbType (SqlDbType
.Bit
);
426 case "System.String":
427 case "System.Data.SqlTypes.SqlString":
428 SetSqlDbType (SqlDbType
.NVarChar
);
430 case "System.DateTime":
431 case "System.Data.SqlTypes.SqlDateTime":
432 SetSqlDbType (SqlDbType
.DateTime
);
434 case "System.Decimal":
435 case "System.Data.SqlTypes.SqlDecimal":
436 SetSqlDbType (SqlDbType
.Decimal
);
438 case "System.Double":
439 case "System.Data.SqlTypes.SqlDouble":
440 SetSqlDbType (SqlDbType
.Float
);
442 case "System.Byte[]":
443 case "System.Data.SqlTypes.SqlBinary":
444 SetSqlDbType (SqlDbType
.VarBinary
);
447 case "System.Data.SqlTypes.SqlByte":
448 SetSqlDbType (SqlDbType
.TinyInt
);
451 case "System.Data.SqlTypes.SqlInt32":
452 SetSqlDbType (SqlDbType
.Int
);
454 case "System.Single":
455 case "System.Data.SqlTypes.Single":
456 SetSqlDbType (SqlDbType
.Real
);
459 case "System.Data.SqlTypes.SqlInt16":
460 SetSqlDbType (SqlDbType
.SmallInt
);
463 case "System.Data.SqlTypes.SqlGuid":
464 SetSqlDbType (SqlDbType
.UniqueIdentifier
);
467 case "System.SmallMoney":
468 case "System.Data.SqlTypes.SqlMoney":
469 SetSqlDbType (SqlDbType
.Money
);
471 case "System.Object":
472 SetSqlDbType (SqlDbType
.Variant
);
475 throw new ArgumentException (exception
);
479 // When the DbType is set, we also set the SqlDbType, as well as the SQL Server
480 // string representation of the type name. If the DbType is not convertible
481 // to an SqlDbType, throw an exception.
482 private void SetDbType (DbType type
)
484 string exception
= String
.Format ("No mapping exists from DbType {0} to a known SqlDbType.", type
);
487 case DbType
.AnsiString
:
488 MetaParameter
.TypeName
= "varchar";
489 sqlDbType
= SqlDbType
.VarChar
;
490 isVariableSizeType
= true;
492 case DbType
.AnsiStringFixedLength
:
493 MetaParameter
.TypeName
= "char";
494 sqlDbType
= SqlDbType
.Char
;
495 isVariableSizeType
= true;
498 MetaParameter
.TypeName
= "varbinary";
499 sqlDbType
= SqlDbType
.VarBinary
;
500 isVariableSizeType
= true;
503 MetaParameter
.TypeName
= "bit";
504 sqlDbType
= SqlDbType
.Bit
;
507 MetaParameter
.TypeName
= "tinyint";
508 sqlDbType
= SqlDbType
.TinyInt
;
510 case DbType
.Currency
:
511 sqlDbType
= SqlDbType
.Money
;
512 MetaParameter
.TypeName
= "money";
515 case DbType
.DateTime
:
516 MetaParameter
.TypeName
= "datetime";
517 sqlDbType
= SqlDbType
.DateTime
;
520 MetaParameter
.TypeName
= "decimal";
521 sqlDbType
= SqlDbType
.Decimal
;
524 MetaParameter
.TypeName
= "float";
525 sqlDbType
= SqlDbType
.Float
;
528 MetaParameter
.TypeName
= "uniqueidentifier";
529 sqlDbType
= SqlDbType
.UniqueIdentifier
;
532 MetaParameter
.TypeName
= "smallint";
533 sqlDbType
= SqlDbType
.SmallInt
;
536 MetaParameter
.TypeName
= "int";
537 sqlDbType
= SqlDbType
.Int
;
540 MetaParameter
.TypeName
= "bigint";
541 sqlDbType
= SqlDbType
.BigInt
;
544 MetaParameter
.TypeName
= "sql_variant";
545 sqlDbType
= SqlDbType
.Variant
;
548 MetaParameter
.TypeName
= "real";
549 sqlDbType
= SqlDbType
.Real
;
552 MetaParameter
.TypeName
= "nvarchar";
553 sqlDbType
= SqlDbType
.NVarChar
;
554 isVariableSizeType
= true;
556 case DbType
.StringFixedLength
:
557 MetaParameter
.TypeName
= "nchar";
558 sqlDbType
= SqlDbType
.NChar
;
559 isVariableSizeType
= true;
562 MetaParameter
.TypeName
= "datetime";
563 sqlDbType
= SqlDbType
.DateTime
;
566 throw new ArgumentException (exception
);
571 // Used by internal constructor which has a SQL Server typename
572 private void SetDbTypeName (string dbTypeName
)
574 switch (dbTypeName
.ToLower ()) {
576 SqlDbType
= SqlDbType
.BigInt
;
579 SqlDbType
= SqlDbType
.Binary
;
582 SqlDbType
= SqlDbType
.Bit
;
585 SqlDbType
= SqlDbType
.Char
;
588 SqlDbType
= SqlDbType
.DateTime
;
591 SqlDbType
= SqlDbType
.Decimal
;
594 SqlDbType
= SqlDbType
.Float
;
597 SqlDbType
= SqlDbType
.Image
;
600 SqlDbType
= SqlDbType
.Int
;
603 SqlDbType
= SqlDbType
.Money
;
606 SqlDbType
= SqlDbType
.NChar
;
609 SqlDbType
= SqlDbType
.NText
;
612 SqlDbType
= SqlDbType
.NVarChar
;
615 SqlDbType
= SqlDbType
.Real
;
617 case "smalldatetime":
618 SqlDbType
= SqlDbType
.SmallDateTime
;
621 SqlDbType
= SqlDbType
.SmallInt
;
624 SqlDbType
= SqlDbType
.SmallMoney
;
627 SqlDbType
= SqlDbType
.Text
;
630 SqlDbType
= SqlDbType
.Timestamp
;
633 SqlDbType
= SqlDbType
.TinyInt
;
635 case "uniqueidentifier":
636 SqlDbType
= SqlDbType
.UniqueIdentifier
;
639 SqlDbType
= SqlDbType
.VarBinary
;
642 SqlDbType
= SqlDbType
.VarChar
;
645 SqlDbType
= SqlDbType
.Variant
;
650 // When the SqlDbType is set, we also set the DbType, as well as the SQL Server
651 // string representation of the type name. If the SqlDbType is not convertible
652 // to a DbType, throw an exception.
653 private void SetSqlDbType (SqlDbType type
)
655 string exception
= String
.Format ("No mapping exists from SqlDbType {0} to a known DbType.", type
);
658 case SqlDbType
.BigInt
:
659 MetaParameter
.TypeName
= "bigint";
660 dbType
= DbType
.Int64
;
662 case SqlDbType
.Binary
:
663 MetaParameter
.TypeName
= "binary";
664 dbType
= DbType
.Binary
;
665 isVariableSizeType
= true;
667 case SqlDbType
.Timestamp
:
668 MetaParameter
.TypeName
= "timestamp";
669 dbType
= DbType
.Binary
;
671 case SqlDbType
.VarBinary
:
672 MetaParameter
.TypeName
= "varbinary";
673 dbType
= DbType
.Binary
;
674 isVariableSizeType
= true;
677 MetaParameter
.TypeName
= "bit";
678 dbType
= DbType
.Boolean
;
681 MetaParameter
.TypeName
= "char";
682 dbType
= DbType
.AnsiStringFixedLength
;
683 isVariableSizeType
= true;
685 case SqlDbType
.DateTime
:
686 MetaParameter
.TypeName
= "datetime";
687 dbType
= DbType
.DateTime
;
689 case SqlDbType
.SmallDateTime
:
690 MetaParameter
.TypeName
= "smalldatetime";
691 dbType
= DbType
.DateTime
;
693 case SqlDbType
.Decimal
:
694 MetaParameter
.TypeName
= "decimal";
695 dbType
= DbType
.Decimal
;
697 case SqlDbType
.Float
:
698 MetaParameter
.TypeName
= "float";
699 dbType
= DbType
.Double
;
701 case SqlDbType
.Image
:
702 MetaParameter
.TypeName
= "image";
703 dbType
= DbType
.Binary
;
704 isVariableSizeType
= true;
707 MetaParameter
.TypeName
= "int";
708 dbType
= DbType
.Int32
;
710 case SqlDbType
.Money
:
711 MetaParameter
.TypeName
= "money";
712 dbType
= DbType
.Currency
;
714 case SqlDbType
.SmallMoney
:
715 MetaParameter
.TypeName
= "smallmoney";
716 dbType
= DbType
.Currency
;
718 case SqlDbType
.NChar
:
719 MetaParameter
.TypeName
= "nchar";
720 dbType
= DbType
.StringFixedLength
;
721 isVariableSizeType
= true;
723 case SqlDbType
.NText
:
724 MetaParameter
.TypeName
= "ntext";
725 dbType
= DbType
.String
;
726 isVariableSizeType
= true;
728 case SqlDbType
.NVarChar
:
729 MetaParameter
.TypeName
= "nvarchar";
730 dbType
= DbType
.String
;
731 isVariableSizeType
= true;
734 MetaParameter
.TypeName
= "real";
735 dbType
= DbType
.Single
;
737 case SqlDbType
.SmallInt
:
738 MetaParameter
.TypeName
= "smallint";
739 dbType
= DbType
.Int16
;
742 MetaParameter
.TypeName
= "text";
743 dbType
= DbType
.AnsiString
;
744 isVariableSizeType
= true;
746 case SqlDbType
.VarChar
:
747 MetaParameter
.TypeName
= "varchar";
748 dbType
= DbType
.AnsiString
;
749 isVariableSizeType
= true;
751 case SqlDbType
.TinyInt
:
752 MetaParameter
.TypeName
= "tinyint";
753 dbType
= DbType
.Byte
;
755 case SqlDbType
.UniqueIdentifier
:
756 MetaParameter
.TypeName
= "uniqueidentifier";
757 dbType
= DbType
.Guid
;
759 case SqlDbType
.Variant
:
760 MetaParameter
.TypeName
= "sql_variant";
761 dbType
= DbType
.Object
;
764 throw new ArgumentException (exception
);
769 public override string ToString()
771 return ParameterName
;
774 private object SqlTypeToFrameworkType (object value)
776 if (! (value is INullable
)) // if the value is not SqlType
779 // Map to .net type, as Mono TDS respects only types from .net
780 switch (value.GetType ().FullName
) {
781 case "System.Data.SqlTypes.SqlBinary":
782 return ( (SqlBinary
) value).Value
;
783 case "System.Data.SqlTypes.SqlBoolean":
784 return ( (SqlBoolean
) value).Value
;
785 case "System.Data.SqlTypes.SqlByte":
786 return ( (SqlByte
) value).Value
;
787 case "System.Data.SqlTypes.SqlDateTime":
788 return ( (SqlDateTime
) value).Value
;
789 case "System.Data.SqlTypes.SqlDecimal":
790 return ( (SqlDecimal
) value).Value
;
791 case "System.Data.SqlTypes.SqlDouble":
792 return ( (SqlDouble
) value).Value
;
793 case "System.Data.SqlTypes.SqlGuid":
794 return ( (SqlGuid
) value).Value
;
795 case "System.Data.SqlTypes.SqlInt16":
796 return ( (SqlInt16
) value).Value
;
797 case "System.Data.SqlTypes.SqlInt32 ":
798 return ( (SqlInt32
) value).Value
;
799 case "System.Data.SqlTypes.SqlInt64":
800 return ( (SqlInt64
) value).Value
;
801 case "System.Data.SqlTypes.SqlMoney":
802 return ( (SqlMoney
) value).Value
;
803 case "System.Data.SqlTypes.SqlSingle":
804 return ( (SqlSingle
) value).Value
;
805 case "System.Data.SqlTypes.SqlString":
806 return ( (SqlString
) value).Value
;
812 [MonoTODO ("Not implemented")]
813 public override void ResetDbType ()
815 throw new NotImplementedException ();
819 #endregion // Methods