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