mcs/class/System.Data/System.Data.SqlClient/SqlParameter.cs

   1 //
   2 // System.Data.SqlClient.SqlParameter.cs
   3 //
   4 // Author:
   5 //   Rodrigo Moya (rodrigo@ximian.com)
   6 //   Daniel Morgan (danmorg@sc.rr.com)
   7 //   Tim Coleman (tim@timcoleman.com)
   8 //
   9 // (C) Ximian, Inc. 2002
  10 // Copyright (C) Tim Coleman, 2002
  11 //
  12
  13 using Mono.Data.Tds.Protocol;
  14 using System;
  15 using System.ComponentModel;
  16 using System.Data;
  17 using System.Data.Common;
  18 using System.Runtime.InteropServices;
  19 using System.Text;
  20
  21 namespace System.Data.SqlClient {
  22         public sealed class SqlParameter : MarshalByRefObject, IDbDataParameter, IDataParameter, ICloneable
  23         {
  24                 #region Fields
  25
  26                 SqlParameterCollection container = null;
  27                 DbType dbType;
  28                 ParameterDirection direction = ParameterDirection.Input;
  29                 bool isNullable;
  30                 bool isSizeSet = false;
  31                 bool isTypeSet = false;
  32                 object objValue;
  33                 int offset;
  34                 string parameterName;
  35                 byte precision;
  36                 byte scale;
  37                 int size;
  38                 SqlDbType sqlDbType;
  39                 string sourceColumn;
  40                 DataRowVersion sourceVersion;
  41                 string typeName;
  42
  43                 #endregion // Fields
  44
  45                 #region Constructors
  46
  47                 public SqlParameter ()
  48                         : this (String.Empty, SqlDbType.NVarChar, 0, ParameterDirection.Input, false, 0, 0, String.Empty, DataRowVersion.Current, null)
  49                 {
  50                 }
  51
  52                 public SqlParameter (string parameterName, object value)
  53                 {
  54                         this.parameterName = parameterName;
  55                         this.objValue = value;
  56                         this.sourceVersion = DataRowVersion.Current;
  57                         InferSqlType (value);
  58                 }
  59
  60                 public SqlParameter (string parameterName, SqlDbType dbType)
  61                         : this (parameterName, dbType, 0, ParameterDirection.Input, false, 0, 0, String.Empty, DataRowVersion.Current, null)
  62                 {
  63                 }
  64
  65                 public SqlParameter (string parameterName, SqlDbType dbType, int size)
  66                         : this (parameterName, dbType, size, ParameterDirection.Input, false, 0, 0, String.Empty, DataRowVersion.Current, null)
  67                 {
  68                 }
  69
  70                 public SqlParameter (string parameterName, SqlDbType dbType, int size, string sourceColumn)
  71                         : this (parameterName, dbType, size, ParameterDirection.Input, false, 0, 0, sourceColumn, DataRowVersion.Current, null)
  72                 {
  73                 }
  74
  75                 [EditorBrowsable (EditorBrowsableState.Advanced)]
  76                 public SqlParameter (string parameterName, SqlDbType dbType, int size, ParameterDirection direction, bool isNullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
  77                 {
  78                         SqlDbType = dbType;
  79                         Size = size;
  80                         Value = value;
  81
  82                         ParameterName = parameterName;
  83                         Direction = direction;
  84                         IsNullable = isNullable;
  85                         Precision = precision;
  86                         Scale = scale;
  87                         SourceColumn = sourceColumn;
  88                         SourceVersion = sourceVersion;
  89                 }
  90
  91                 // This constructor is used internally to construct a
  92                 // SqlParameter.  The value array comes from sp_procedure_params_rowset.
  93                 // This is in SqlCommand.DeriveParameters.
  94                 internal SqlParameter (object[] dbValues)
  95                 {
  96                         precision = 0;
  97                         scale = 0;
  98                         direction = ParameterDirection.Input;
  99
 100                         parameterName = (string) dbValues[3];
 101
 102                         switch ((short) dbValues[5]) {
 103                         case 1:
 104                                 direction = ParameterDirection.Input;
 105                                 break;
 106                         case 2:
 107                                 direction = ParameterDirection.Output;
 108                                 break;
 109                         case 3:
 110                                 direction = ParameterDirection.InputOutput;
 111                                 break;
 112                         case 4:
 113                                 direction = ParameterDirection.ReturnValue;
 114                                 break;
 115                         }
 116
 117                         isNullable = (bool) dbValues[8];
 118
 119                         if (dbValues[12] != null)
 120                                 precision = (byte) ((short) dbValues[12]);
 121                         if (dbValues[13] != null)
 122                                 scale = (byte) ((short) dbValues[13]);
 123
 124                         SetDbTypeName ((string) dbValues[16]);
 125                 }
 126
 127                 #endregion // Constructors
 128
 129                 #region Properties
 130
 131                 // Used to ensure that only one collection can contain this
 132                 // parameter
 133                 internal SqlParameterCollection Container {
 134                         get { return container; }
 135                         set { container = value; }
 136                 }
 137
 138                 [Browsable (false)]
 139                 [DataCategory ("Data")]
 140                 [DataSysDescription ("The parameter generic type.")]
 141                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 142                 [RefreshProperties (RefreshProperties.All)]
 143                 public DbType DbType {
 144                         get { return dbType; }
 145                         set {
 146                                 SetDbType (value);
 147                                 isTypeSet = true;
 148                         }
 149                 }
 150
 151                 [DataCategory ("Data")]
 152                 [DataSysDescription ("Input, output, or bidirectional parameter.")]
 153                 [DefaultValue (ParameterDirection.Input)]
 154                 public ParameterDirection Direction {
 155                         get { return direction; }
 156                         set { direction = value; }
 157                 }
 158
 159                 string IDataParameter.ParameterName {
 160                         get { return parameterName; }
 161                         set { parameterName = value; }
 162                 }
 163
 164                 [Browsable (false)]
 165                 [DataSysDescription ("a design-time property used for strongly typed code-generation.")]
 166                 [DefaultValue (false)]
 167                 [DesignOnly (true)]
 168                 [EditorBrowsable (EditorBrowsableState.Advanced)]
 169                 public bool IsNullable  {
 170                         get { return isNullable; }
 171                         set { isNullable = value; }
 172                 }
 173
 174                 [Browsable (false)]
 175                 [DataCategory ("Data")]
 176                 [DataSysDescription ("Offset in variable length data types.")]
 177                 [DefaultValue (0)]
 178                 public int Offset {
 179                         get { return offset; }
 180                         set { offset = value; }
 181                 }
 182
 183                 [DataSysDescription ("Name of the parameter, like '@p1'")]
 184                 [DefaultValue ("")]
 185                 public string ParameterName {
 186                         get { return parameterName; }
 187                         set { parameterName = value; }
 188                 }
 189
 190                 [DataCategory ("Data")]
 191                 [DataSysDescription ("For decimal, numeric, varnumeric DBTypes.")]
 192                 [DefaultValue (0)]
 193                 public byte Precision {
 194                         get { return precision; }
 195                         set { precision = value; }
 196                 }
 197
 198                 [DataCategory ("Data")]
 199                 [DataSysDescription ("For decimal, numeric, varnumeric DBTypes.")]
 200                 [DefaultValue (0)]
 201                 public byte Scale {
 202                         get { return scale; }
 203                         set { scale = value; }
 204                 }
 205
 206                 [DataCategory ("Data")]
 207                 [DataSysDescription ("Size of variable length datatypes (strings & arrays).")]
 208                 [DefaultValue (0)]
 209                 public int Size {
 210                         get { return size; }
 211                         set {
 212                                 size = value;
 213                                 isSizeSet = true;
 214                         }
 215                 }
 216
 217                 [DataCategory ("Data")]
 218                 [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.")]
 219                 [DefaultValue ("")]
 220                 public string SourceColumn {
 221                         get { return sourceColumn; }
 222                         set { sourceColumn = value; }
 223                 }
 224
 225                 [DataCategory ("Data")]
 226                 [DataSysDescription ("When used by a DataAdapter.Update (UpdateCommand only), the version of the DataRow value that is used to update the data source.")]
 227                 [DefaultValue (DataRowVersion.Current)]
 228                 public DataRowVersion SourceVersion {
 229                         get { return sourceVersion; }
 230                         set { sourceVersion = value; }
 231                 }
 232
 233                 [DataCategory ("Data")]
 234                 [DataSysDescription ("The parameter native type.")]
 235                 [DefaultValue (SqlDbType.NVarChar)]
 236                 [RefreshProperties (RefreshProperties.All)]
 237                 public SqlDbType SqlDbType {
 238                         get { return sqlDbType; }
 239                         set {
 240                                 SetSqlDbType (value);
 241                                 isTypeSet = true;
 242                         }
 243                 }
 244
 245                 [DataCategory ("Data")]
 246                 [DataSysDescription ("Value of the parameter.")]
 247                 [DefaultValue (null)]
 248                 public object Value {
 249                         get { return objValue; }
 250                         set {
 251                                 if (!isTypeSet)
 252                                         InferSqlType (value);
 253                                 objValue = value;
 254                         }
 255                 }
 256
 257                 #endregion // Properties
 258
 259                 #region Methods
 260
 261                 object ICloneable.Clone ()
 262                 {
 263                         return new SqlParameter (ParameterName, SqlDbType, Size, Direction, IsNullable, Precision, Scale, SourceColumn, SourceVersion, Value);
 264                 }
 265
 266                 // If the value is set without the DbType/SqlDbType being set, then we
 267                 // infer type information.
 268                 private void InferSqlType (object value)
 269                 {
 270                         Type type = value.GetType ();
 271
 272                         string exception = String.Format ("The parameter data type of {0} is invalid.", type.Name);
 273
 274                         switch (type.FullName) {
 275                         case "System.Int64":
 276                                 SetSqlDbType (SqlDbType.BigInt);
 277                                 break;
 278                         case "System.Boolean":
 279                                 SetSqlDbType (SqlDbType.Bit);
 280                                 break;
 281                         case "System.String":
 282                                 SetSqlDbType (SqlDbType.NVarChar);
 283                                 break;
 284                         case "System.DateTime":
 285                                 SetSqlDbType (SqlDbType.DateTime);
 286                                 break;
 287                         case "System.Decimal":
 288                                 SetSqlDbType (SqlDbType.Decimal);
 289                                 break;
 290                         case "System.Double":
 291                                 SetSqlDbType (SqlDbType.Float);
 292                                 break;
 293                         case "System.Byte[]":
 294                                 SetSqlDbType (SqlDbType.VarBinary);
 295                                 break;
 296                         case "System.Byte":
 297                                 SetSqlDbType (SqlDbType.TinyInt);
 298                                 break;
 299                         case "System.Int32":
 300                                 SetSqlDbType (SqlDbType.Int);
 301                                 break;
 302                         case "System.Single":
 303                                 SetSqlDbType (SqlDbType.Real);
 304                                 break;
 305                         case "System.Int16":
 306                                 SetSqlDbType (SqlDbType.SmallInt);
 307                                 break;
 308                         case "System.Guid":
 309                                 SetSqlDbType (SqlDbType.UniqueIdentifier);
 310                                 break;
 311                         case "System.Object":
 312                                 SetSqlDbType (SqlDbType.Variant);
 313                                 break;
 314                         default:
 315                                 throw new ArgumentException (exception);
 316                         }
 317                 }
 318
 319                 internal string Prepare (string name)
 320                 {
 321                         StringBuilder result = new StringBuilder ();
 322                         result.Append (name);
 323                         result.Append (" ");
 324                         result.Append (typeName);
 325
 326                         switch (sqlDbType) {
 327                         case SqlDbType.VarBinary :
 328                         case SqlDbType.NVarChar :
 329                         case SqlDbType.VarChar :
 330                                 if (!isSizeSet || size == 0)
 331                                         throw new InvalidOperationException ("All variable length parameters must have an explicitly set non-zero size.");
 332                                 result.Append (String.Format ("({0})", size));
 333                                 break;
 334                         case SqlDbType.NChar :
 335                         case SqlDbType.Char :
 336                         case SqlDbType.Binary :
 337                                 if (size > 0)
 338                                         result.Append (String.Format ("({0})", size));
 339                                 break;
 340                         case SqlDbType.Decimal :
 341                                 result.Append (String.Format ("({0},{1})", precision, scale));
 342                                 break;
 343                         default:
 344                                 break;
 345                         }
 346
 347                         return result.ToString ();
 348                 }
 349
 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)
 354                 {
 355                         string exception = String.Format ("No mapping exists from DbType {0} to a known SqlDbType.", type);
 356
 357                         switch (type) {
 358                         case DbType.AnsiString:
 359                                 typeName = "varchar";
 360                                 sqlDbType = SqlDbType.VarChar;
 361                                 break;
 362                         case DbType.AnsiStringFixedLength:
 363                                 typeName = "char";
 364                                 sqlDbType = SqlDbType.Char;
 365                                 break;
 366                         case DbType.Binary:
 367                                 typeName = "varbinary";
 368                                 sqlDbType = SqlDbType.VarBinary;
 369                                 break;
 370                         case DbType.Boolean:
 371                                 typeName = "bit";
 372                                 sqlDbType = SqlDbType.Bit;
 373                                 break;
 374                         case DbType.Byte:
 375                                 typeName = "tinyint";
 376                                 sqlDbType = SqlDbType.TinyInt;
 377                                 break;
 378                         case DbType.Currency:
 379                                 sqlDbType = SqlDbType.Money;
 380                                 typeName = "money";
 381                                 break;
 382                         case DbType.Date:
 383                         case DbType.DateTime:
 384                                 typeName = "datetime";
 385                                 sqlDbType = SqlDbType.DateTime;
 386                                 break;
 387                         case DbType.Decimal:
 388                                 typeName = "decimal";
 389                                 sqlDbType = SqlDbType.Decimal;
 390                                 break;
 391                         case DbType.Double:
 392                                 typeName = "float";
 393                                 sqlDbType = SqlDbType.Float;
 394                                 break;
 395                         case DbType.Guid:
 396                                 typeName = "uniqueidentifier";
 397                                 sqlDbType = SqlDbType.UniqueIdentifier;
 398                                 break;
 399                         case DbType.Int16:
 400                                 typeName = "smallint";
 401                                 sqlDbType = SqlDbType.SmallInt;
 402                                 break;
 403                         case DbType.Int32:
 404                                 typeName = "int";
 405                                 sqlDbType = SqlDbType.Int;
 406                                 break;
 407                         case DbType.Int64:
 408                                 typeName = "bigint";
 409                                 sqlDbType = SqlDbType.BigInt;
 410                                 break;
 411                         case DbType.Object:
 412                                 typeName = "sql_variant";
 413                                 sqlDbType = SqlDbType.Variant;
 414                                 break;
 415                         case DbType.Single:
 416                                 typeName = "real";
 417                                 sqlDbType = SqlDbType.Real;
 418                                 break;
 419                         case DbType.String:
 420                                 typeName = "nvarchar";
 421                                 sqlDbType = SqlDbType.NVarChar;
 422                                 break;
 423                         case DbType.StringFixedLength:
 424                                 typeName = "nchar";
 425                                 sqlDbType = SqlDbType.NChar;
 426                                 break;
 427                         case DbType.Time:
 428                                 typeName = "datetime";
 429                                 sqlDbType = SqlDbType.DateTime;
 430                                 break;
 431                         default:
 432                                 throw new ArgumentException (exception);
 433                         }
 434                         dbType = type;
 435                 }
 436
 437                 // Used by internal constructor which has a SQL Server typename
 438                 private void SetDbTypeName (string dbTypeName)
 439                 {
 440                         switch (dbTypeName.ToLower ()) {
 441                         case "bigint":
 442                                 SqlDbType = SqlDbType.BigInt;
 443                                 break;
 444                         case "binary":
 445                                 SqlDbType = SqlDbType.Binary;
 446                                 break;
 447                         case "bit":
 448                                 SqlDbType = SqlDbType.Bit;
 449                                 break;
 450                         case "char":
 451                                 SqlDbType = SqlDbType.Char;
 452                                 break;
 453                         case "datetime":
 454                                 SqlDbType = SqlDbType.DateTime;
 455                                 break;
 456                         case "decimal":
 457                                 SqlDbType = SqlDbType.Decimal;
 458                                 break;
 459                         case "float":
 460                                 SqlDbType = SqlDbType.Float;
 461                                 break;
 462                         case "image":
 463                                 SqlDbType = SqlDbType.Image;
 464                                 break;
 465                         case "int":
 466                                 SqlDbType = SqlDbType.Int;
 467                                 break;
 468                         case "money":
 469                                 SqlDbType = SqlDbType.Money;
 470                                 break;
 471                         case "nchar":
 472                                 SqlDbType = SqlDbType.NChar;
 473                                 break;
 474                         case "ntext":
 475                                 SqlDbType = SqlDbType.NText;
 476                                 break;
 477                         case "nvarchar":
 478                                 SqlDbType = SqlDbType.NVarChar;
 479                                 break;
 480                         case "real":
 481                                 SqlDbType = SqlDbType.Real;
 482                                 break;
 483                         case "smalldatetime":
 484                                 SqlDbType = SqlDbType.SmallDateTime;
 485                                 break;
 486                         case "smallint":
 487                                 SqlDbType = SqlDbType.SmallInt;
 488                                 break;
 489                         case "smallmoney":
 490                                 SqlDbType = SqlDbType.SmallMoney;
 491                                 break;
 492                         case "text":
 493                                 SqlDbType = SqlDbType.Text;
 494                                 break;
 495                         case "timestamp":
 496                                 SqlDbType = SqlDbType.Timestamp;
 497                                 break;
 498                         case "tinyint":
 499                                 SqlDbType = SqlDbType.TinyInt;
 500                                 break;
 501                         case "uniqueidentifier":
 502                                 SqlDbType = SqlDbType.UniqueIdentifier;
 503                                 break;
 504                         case "varbinary":
 505                                 SqlDbType = SqlDbType.VarBinary;
 506                                 break;
 507                         case "varchar":
 508                                 SqlDbType = SqlDbType.VarChar;
 509                                 break;
 510                         default:
 511                                 SqlDbType = SqlDbType.Variant;
 512                                 break;
 513                         }
 514                 }
 515
 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)
 520                 {
 521                         string exception = String.Format ("No mapping exists from SqlDbType {0} to a known DbType.", type);
 522
 523                         switch (type) {
 524                         case SqlDbType.BigInt:
 525                                 typeName = "bigint";
 526                                 dbType = DbType.Int64;
 527                                 break;
 528                         case SqlDbType.Binary:
 529                                 typeName = "binary";
 530                                 dbType = DbType.Binary;
 531                                 break;
 532                         case SqlDbType.Timestamp:
 533                                 typeName = "timestamp";
 534                                 dbType = DbType.Binary;
 535                                 break;
 536                         case SqlDbType.VarBinary:
 537                                 typeName = "varbinary";
 538                                 dbType = DbType.Binary;
 539                                 break;
 540                         case SqlDbType.Bit:
 541                                 typeName = "bit";
 542                                 dbType = DbType.Boolean;
 543                                 break;
 544                         case SqlDbType.Char:
 545                                 typeName = "char";
 546                                 dbType = DbType.AnsiStringFixedLength;
 547                                 break;
 548                         case SqlDbType.DateTime:
 549                                 typeName = "datetime";
 550                                 dbType = DbType.DateTime;
 551                                 break;
 552                         case SqlDbType.SmallDateTime:
 553                                 typeName = "smalldatetime";
 554                                 dbType = DbType.DateTime;
 555                                 break;
 556                         case SqlDbType.Decimal:
 557                                 typeName = "decimal";
 558                                 dbType = DbType.Decimal;
 559                                 break;
 560                         case SqlDbType.Float:
 561                                 typeName = "float";
 562                                 dbType = DbType.Double;
 563                                 break;
 564                         case SqlDbType.Image:
 565                                 typeName = "image";
 566                                 dbType = DbType.Binary;
 567                                 break;
 568                         case SqlDbType.Int:
 569                                 typeName = "int";
 570                                 dbType = DbType.Int32;
 571                                 break;
 572                         case SqlDbType.Money:
 573                                 typeName = "money";
 574                                 dbType = DbType.Currency;
 575                                 break;
 576                         case SqlDbType.SmallMoney:
 577                                 typeName = "smallmoney";
 578                                 dbType = DbType.Currency;
 579                                 break;
 580                         case SqlDbType.NChar:
 581                                 typeName = "nchar";
 582                                 dbType = DbType.StringFixedLength;
 583                                 break;
 584                         case SqlDbType.NText:
 585                                 typeName = "ntext";
 586                                 dbType = DbType.String;
 587                                 break;
 588                         case SqlDbType.NVarChar:
 589                                 typeName = "nvarchar";
 590                                 dbType = DbType.String;
 591                                 break;
 592                         case SqlDbType.Real:
 593                                 typeName = "real";
 594                                 dbType = DbType.Single;
 595                                 break;
 596                         case SqlDbType.SmallInt:
 597                                 typeName = "smallint";
 598                                 dbType = DbType.Int16;
 599                                 break;
 600                         case SqlDbType.Text:
 601                                 typeName = "text";
 602                                 dbType = DbType.AnsiString;
 603                                 break;
 604                         case SqlDbType.VarChar:
 605                                 typeName = "varchar";
 606                                 dbType = DbType.AnsiString;
 607                                 break;
 608                         case SqlDbType.TinyInt:
 609                                 typeName = "tinyint";
 610                                 dbType = DbType.Byte;
 611                                 break;
 612                         case SqlDbType.UniqueIdentifier:
 613                                 typeName = "uniqueidentifier";
 614                                 dbType = DbType.Guid;
 615                                 break;
 616                         case SqlDbType.Variant:
 617                                 typeName = "sql_variant";
 618                                 dbType = DbType.Object;
 619                                 break;
 620                         default:
 621                                 throw new ArgumentException (exception);
 622                         }
 623                         sqlDbType = type;
 624                 }
 625
 626                 public override string ToString()
 627                 {
 628                         return parameterName;
 629                 }
 630
 631                 #endregion // Methods
 632         }
 633 }