class/System.Data/System.Data.SqlClient/SqlCommandBuilder.cs

   1 //
   2 // System.Data.SqlClient.SqlCommandBuilder.cs
   3 //
   4 // Author:
   5 //   Tim Coleman (tim@timcoleman.com)
   6 //   Veerapuram Varadhan (vvaradhan@novell.com)
   7 //
   8 // Copyright (C) Tim Coleman, 2002
   9 //
  10
  11 //
  12 // Copyright (C) 2004, 2009 Novell, Inc (http://www.novell.com)
  13 //
  14 // Permission is hereby granted, free of charge, to any person obtaining
  15 // a copy of this software and associated documentation files (the
  16 // "Software"), to deal in the Software without restriction, including
  17 // without limitation the rights to use, copy, modify, merge, publish,
  18 // distribute, sublicense, and/or sell copies of the Software, and to
  19 // permit persons to whom the Software is furnished to do so, subject to
  20 // the following conditions:
  21 //
  22 // The above copyright notice and this permission notice shall be
  23 // included in all copies or substantial portions of the Software.
  24 //
  25 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  26 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  27 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  28 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  29 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  30 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  31 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  32 //
  33
  34 using System;
  35 using System.Collections;
  36 using System.ComponentModel;
  37 using System.Data;
  38 using System.Data.Common;
  39 #if NET_2_0
  40 using System.Data.SqlTypes;
  41 #endif
  42 using System.Text;
  43
  44 namespace System.Data.SqlClient
  45 {
  46 #if NET_2_0
  47         public sealed class SqlCommandBuilder : DbCommandBuilder
  48 #else
  49         public sealed class SqlCommandBuilder : Component
  50 #endif // NET_2_0
  51         {
  52                 #region Fields
  53
  54 #if ONLY_1_1
  55                 bool disposed;
  56
  57                 DataTable dbSchemaTable;
  58                 string quotePrefix;
  59                 string quoteSuffix;
  60                 string tableName;
  61                 SqlDataAdapter adapter;
  62                 SqlCommand insertCommand;
  63                 SqlCommand deleteCommand;
  64                 SqlCommand updateCommand;
  65                 // Used to construct WHERE clauses
  66                 static readonly string clause1 = "({0} = 1 AND {1} IS NULL)";
  67                 static readonly string clause2 = "({0} = {1})";
  68
  69 #else
  70                 readonly string _catalogSeparator = ".";
  71                 readonly string _schemaSeparator = ".";
  72                 readonly CatalogLocation _catalogLocation = CatalogLocation.Start;
  73 #endif
  74
  75                 #endregion // Fields
  76
  77                 #region Constructors
  78
  79                 public SqlCommandBuilder ()
  80                 {
  81 #if NET_2_0
  82                         QuoteSuffix = "]";
  83                         QuotePrefix = "[";
  84 #endif
  85                 }
  86
  87                 public SqlCommandBuilder (SqlDataAdapter adapter)
  88                         : this ()
  89                 {
  90                         DataAdapter = adapter;
  91                 }
  92
  93                 #endregion // Constructors
  94
  95                 #region Properties
  96
  97 #if !NET_2_0
  98                 [DataSysDescription ("The DataAdapter for which to automatically generate SqlCommands")]
  99 #endif
 100                 [DefaultValue (null)]
 101                 public new SqlDataAdapter DataAdapter {
 102                         get {
 103 #if ONLY_1_1
 104                                 return adapter;
 105 #else
 106                                 return (SqlDataAdapter)base.DataAdapter;
 107 #endif
 108                         } set {
 109 #if ONLY_1_1
 110                if (adapter != null)
 111                        adapter.RowUpdating -= new SqlRowUpdatingEventHandler (RowUpdatingHandler);
 112
 113                adapter = value;
 114                if (adapter != null)
 115                        adapter.RowUpdating += new SqlRowUpdatingEventHandler (RowUpdatingHandler);
 116 #else
 117                                 base.DataAdapter = value;
 118 #endif
 119                         }
 120                 }
 121
 122                 [Browsable (false)]
 123                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 124 #if !NET_2_0
 125                 [DataSysDescription ("The character used in a text command as the opening quote for quoting identifiers that contain special characters.")]
 126 #else
 127                 [EditorBrowsable (EditorBrowsableState.Never)]
 128 #endif // NET_2_0
 129                 public
 130 #if NET_2_0
 131                 override
 132 #endif // NET_2_0
 133                 string QuotePrefix {
 134                         get {
 135 #if ONLY_1_1
 136                                 if (quotePrefix == null)
 137                                         return string.Empty;
 138                                 return quotePrefix;
 139 #else
 140                                 return base.QuotePrefix;
 141 #endif
 142                         }
 143                         set {
 144 #if ONLY_1_1
 145                                 if (dbSchemaTable != null)
 146                                         throw new InvalidOperationException (
 147                                                 "The QuotePrefix and QuoteSuffix " +
 148                                                 "properties cannot be changed once " +
 149                                                 "an Insert, Update, or Delete " +
 150                                                 "command has been generated.");
 151                                 quotePrefix = value;
 152 #else
 153                                 if (value != "[" && value != "\"")
 154                                         throw new ArgumentException ("Only '[' " +
 155                                                 "and '\"' are allowed as value " +
 156                                                 "for the 'QuoteSuffix' property.");
 157                                 base.QuotePrefix = value;
 158 #endif
 159                         }
 160                 }
 161
 162                 [Browsable (false)]
 163                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 164 #if !NET_2_0
 165                 [DataSysDescription ("The character used in a text command as the closing quote for quoting identifiers that contain special characters. ")]
 166 #else
 167                 [EditorBrowsable (EditorBrowsableState.Never)]
 168 #endif // NET_2_0
 169                 public
 170 #if NET_2_0
 171                 override
 172 #endif // NET_2_0
 173                 string QuoteSuffix {
 174                         get {
 175 #if ONLY_1_1
 176                                 if (quoteSuffix == null)
 177                                         return string.Empty;
 178                                 return quoteSuffix;
 179 #else
 180                                 return base.QuoteSuffix;
 181 #endif
 182                         }
 183                         set {
 184 #if ONLY_1_1
 185                                 if (dbSchemaTable != null)
 186                                         throw new InvalidOperationException (
 187                                                 "The QuotePrefix and QuoteSuffix " +
 188                                                 "properties cannot be changed once " +
 189                                                 "an Insert, Update, or Delete " +
 190                                                 "command has been generated.");
 191                                 quoteSuffix = value;
 192 #else
 193                                 if (value != "]" && value != "\"")
 194                                         throw new ArgumentException ("Only ']' " +
 195                                                 "and '\"' are allowed as value " +
 196                                                 "for the 'QuoteSuffix' property.");
 197                                 base.QuoteSuffix = value;
 198 #endif
 199                         }
 200                 }
 201
 202 #if NET_2_0
 203                 [EditorBrowsable (EditorBrowsableState.Never)]
 204                 [Browsable (false)]
 205                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 206 #if !NET_2_0
 207                 [DefaultValue (".")]
 208 #endif
 209                 public override string CatalogSeparator {
 210                         get { return _catalogSeparator; }
 211                         set {
 212                                 if (value != _catalogSeparator)
 213                                         throw new ArgumentException ("Only " +
 214                                                 "'.' is allowed as value " +
 215                                                 "for the 'CatalogSeparator' " +
 216                                                 "property.");
 217                         }
 218                 }
 219
 220                 [EditorBrowsable (EditorBrowsableState.Never)]
 221                 [Browsable (false)]
 222                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 223 #if !NET_2_0
 224                 [DefaultValue (".")]
 225 #endif
 226                 public override string SchemaSeparator {
 227                         get { return _schemaSeparator; }
 228                         set {
 229                                 if (value != _schemaSeparator)
 230                                         throw new ArgumentException ("Only " +
 231                                                 "'.' is allowed as value " +
 232                                                 "for the 'SchemaSeparator' " +
 233                                                 "property.");
 234                         }
 235                 }
 236
 237                 [EditorBrowsable (EditorBrowsableState.Never)]
 238                 [Browsable (false)]
 239                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 240 #if !NET_2_0
 241                 [DefaultValue (CatalogLocation.Start)]
 242 #endif
 243                 public override CatalogLocation CatalogLocation {
 244                         get { return _catalogLocation; }
 245                         set {
 246                                 if (value != CatalogLocation.Start)
 247                                         throw new ArgumentException ("Only " +
 248                                                 "'Start' is allowed as value " +
 249                                                 "for the 'CatalogLocation' " +
 250                                                 "property.");
 251                         }
 252                 }
 253
 254 #endif // NET_2_0
 255
 256 #if ONLY_1_1
 257                 private SqlCommand SourceCommand {
 258                         get {
 259                                 if (adapter != null)
 260                                         return adapter.SelectCommand;
 261                                 return null;
 262                         }
 263                 }
 264 #endif
 265
 266                 #endregion // Properties
 267
 268                 #region Methods
 269
 270 #if ONLY_1_1
 271                 private void BuildCache (bool closeConnection)
 272                 {
 273                         SqlCommand sourceCommand = SourceCommand;
 274                         if (sourceCommand == null)
 275                                 throw new InvalidOperationException ("The DataAdapter.SelectCommand property needs to be initialized.");
 276                         SqlConnection connection = sourceCommand.Connection;
 277                         if (connection == null)
 278                                 throw new InvalidOperationException ("The DataAdapter.SelectCommand.Connection property needs to be initialized.");
 279
 280                         if (dbSchemaTable == null) {
 281                                 if (connection.State == ConnectionState.Open)
 282                                         closeConnection = false;
 283                                 else
 284                                         connection.Open ();
 285
 286                                 SqlDataReader reader = sourceCommand.ExecuteReader (CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
 287                                 dbSchemaTable = reader.GetSchemaTable ();
 288                                 reader.Close ();
 289                                 if (closeConnection)
 290                                         connection.Close ();
 291                                 BuildInformation (dbSchemaTable);
 292                         }
 293                 }
 294
 295                 private void BuildInformation (DataTable schemaTable)
 296                 {
 297                         tableName = String.Empty;
 298                         foreach (DataRow schemaRow in schemaTable.Rows) {
 299                                 if (schemaRow.IsNull ("BaseTableName") ||
 300                                     (string) schemaRow ["BaseTableName"] == String.Empty)
 301                                         continue;
 302
 303                                 if (tableName == String.Empty)
 304                                         tableName = (string) schemaRow ["BaseTableName"];
 305                                 else if (tableName != (string) schemaRow["BaseTableName"])
 306                                         throw new InvalidOperationException ("Dynamic SQL generation is not supported against multiple base tables.");
 307                         }
 308                         if (tableName == String.Empty)
 309                                 throw new InvalidOperationException ("Dynamic SQL generation is not supported with no base table.");
 310                         dbSchemaTable = schemaTable;
 311                 }
 312
 313                 private SqlCommand CreateDeleteCommand (bool useColumnsForParameterNames)
 314                 {
 315                         // If no table was found, then we can't do an delete
 316                         if (QuotedTableName == String.Empty)
 317                                 return null;
 318
 319                         CreateNewCommand (ref deleteCommand);
 320
 321                         string command = String.Format ("DELETE FROM  {0}", QuotedTableName);
 322                         StringBuilder whereClause = new StringBuilder ();
 323                         bool keyFound = false;
 324                         int parmIndex = 1;
 325
 326                         foreach (DataRow schemaRow in dbSchemaTable.Rows) {
 327                                 if ((bool)schemaRow["IsExpression"] == true)
 328                                         continue;
 329                                 if (!IncludedInWhereClause (schemaRow))
 330                                         continue;
 331
 332                                 if (whereClause.Length > 0)
 333                                         whereClause.Append (" AND ");
 334
 335                                 bool isKey = (bool) schemaRow ["IsKey"];
 336                                 SqlParameter parameter = null;
 337
 338                                 if (isKey)
 339                                         keyFound = true;
 340
 341                                 bool allowNull = (bool) schemaRow ["AllowDBNull"];
 342                                 if (!isKey) {
 343                                         string sourceColumnName = (string) schemaRow ["BaseColumnName"];
 344                                         if (useColumnsForParameterNames) {
 345                                                 parameter = deleteCommand.Parameters.Add (
 346                                                         GetNullCheckParameterName (sourceColumnName),
 347                                                         SqlDbType.Int);
 348                                         } else {
 349                                                 parameter = deleteCommand.Parameters.Add (
 350                                                         GetParameterName (parmIndex++),
 351                                                         SqlDbType.Int);
 352                                         }
 353                                         parameter.IsNullable = allowNull;
 354                                         parameter.SourceVersion = DataRowVersion.Current;
 355                                         parameter.Value = 1;
 356
 357                                         whereClause.Append ("(");
 358                                         whereClause.Append (String.Format (clause1, parameter.ParameterName,
 359                                                                         GetQuotedString (sourceColumnName)));
 360                                         whereClause.Append (" OR ");
 361                                 }
 362
 363                                 if (useColumnsForParameterNames)
 364                                         parameter = CreateParameter (schemaRow, true);
 365                                 else
 366                                         parameter = CreateParameter (parmIndex++, schemaRow);
 367                                 deleteCommand.Parameters.Add (parameter);
 368                                 ApplyParameterInfo (parameter, schemaRow, StatementType.Delete, true);
 369                                 parameter.IsNullable = allowNull;
 370                                 parameter.SourceVersion = DataRowVersion.Original;
 371
 372                                 whereClause.Append (String.Format (clause2, GetQuotedString (parameter.SourceColumn), parameter.ParameterName));
 373
 374                                 if (!isKey)
 375                                         whereClause.Append (")");
 376                         }
 377                         if (!keyFound)
 378                                 throw new InvalidOperationException ("Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.");
 379
 380                         // We're all done, so bring it on home
 381                         string sql = String.Format ("{0} WHERE ( {1} )", command, whereClause.ToString ());
 382                         deleteCommand.CommandText = sql;
 383                         return deleteCommand;
 384                 }
 385
 386                 private SqlCommand CreateInsertCommand (bool useColumnsForParameterNames)
 387                 {
 388                         if (QuotedTableName == String.Empty)
 389                                 return null;
 390
 391                         CreateNewCommand (ref insertCommand);
 392
 393                         string command = String.Format ("INSERT INTO {0}", QuotedTableName);
 394                         string sql;
 395                         StringBuilder columns = new StringBuilder ();
 396                         StringBuilder values = new StringBuilder ();
 397
 398                         int parmIndex = 1;
 399                         foreach (DataRow schemaRow in dbSchemaTable.Rows) {
 400                                 if (!IncludedInInsert (schemaRow))
 401                                         continue;
 402
 403                                 if (parmIndex > 1) {
 404                                         columns.Append (" , ");
 405                                         values.Append (" , ");
 406                                 }
 407
 408                                 SqlParameter parameter = null;
 409                                 if (useColumnsForParameterNames) {
 410                                         parameter = CreateParameter (schemaRow, false);
 411                                 } else {
 412                                         parameter = CreateParameter (parmIndex, schemaRow);
 413                                 }
 414
 415                                 insertCommand.Parameters.Add (parameter);
 416                                 ApplyParameterInfo (parameter, schemaRow, StatementType.Insert, false);
 417                                 parameter.SourceVersion = DataRowVersion.Current;
 418                                 parameter.IsNullable = (bool) schemaRow ["AllowDBNull"];
 419
 420                                 columns.Append (GetQuotedString (parameter.SourceColumn));
 421                                 values.Append (parameter.ParameterName);
 422
 423                                 parmIndex++;
 424                         }
 425
 426                         sql = String.Format ("{0}( {1} ) VALUES ( {2} )", command, columns.ToString (), values.ToString ());
 427                         insertCommand.CommandText = sql;
 428                         return insertCommand;
 429                 }
 430
 431                 private void CreateNewCommand (ref SqlCommand command)
 432                 {
 433                         SqlCommand sourceCommand = SourceCommand;
 434                         if (command == null) {
 435                                 command = sourceCommand.Connection.CreateCommand ();
 436                                 command.CommandTimeout = sourceCommand.CommandTimeout;
 437                                 command.Transaction = sourceCommand.Transaction;
 438                         }
 439                         command.CommandType = CommandType.Text;
 440                         command.UpdatedRowSource = UpdateRowSource.None;
 441                         command.Parameters.Clear ();
 442                 }
 443
 444                 private SqlCommand CreateUpdateCommand (bool useColumnsForParameterNames)
 445                 {
 446                         // If no table was found, then we can't do an update
 447                         if (QuotedTableName == String.Empty)
 448                                 return null;
 449
 450                         CreateNewCommand (ref updateCommand);
 451
 452                         string command = String.Format ("UPDATE {0} SET ", QuotedTableName);
 453                         StringBuilder columns = new StringBuilder ();
 454                         StringBuilder whereClause = new StringBuilder ();
 455                         int parmIndex = 1;
 456                         bool keyFound = false;
 457
 458                         // First, create the X=Y list for UPDATE
 459                         foreach (DataRow schemaRow in dbSchemaTable.Rows) {
 460                                 if (!IncludedInUpdate (schemaRow))
 461                                         continue;
 462                                 if (columns.Length > 0)
 463                                         columns.Append (" , ");
 464
 465                                 SqlParameter parameter = null;
 466                                 if (useColumnsForParameterNames) {
 467                                         parameter = CreateParameter (schemaRow, false);
 468                                 } else {
 469                                         parameter = CreateParameter (parmIndex++, schemaRow);
 470                                 }
 471                                 updateCommand.Parameters.Add (parameter);
 472                                 ApplyParameterInfo (parameter, schemaRow, StatementType.Update, false);
 473                                 parameter.IsNullable = (bool) schemaRow ["AllowDBNull"];
 474                                 parameter.SourceVersion = DataRowVersion.Current;
 475
 476                                 columns.Append (String.Format ("{0} = {1}", GetQuotedString (parameter.SourceColumn), parameter.ParameterName));
 477                         }
 478
 479                         // Now, create the WHERE clause.  This may be optimizable, but it would be ugly to incorporate
 480                         // into the loop above.  "Premature optimization is the root of all evil." -- Knuth
 481                         foreach (DataRow schemaRow in dbSchemaTable.Rows) {
 482                                 if ((bool)schemaRow["IsExpression"] == true)
 483                                         continue;
 484
 485                                 if (!IncludedInWhereClause (schemaRow))
 486                                         continue;
 487
 488                                 if (whereClause.Length > 0)
 489                                         whereClause.Append (" AND ");
 490
 491                                 bool isKey = (bool) schemaRow ["IsKey"];
 492                                 SqlParameter parameter = null;
 493
 494                                 if (isKey)
 495                                         keyFound = true;
 496
 497                                 bool allowNull = (bool) schemaRow ["AllowDBNull"];
 498                                 if (!isKey) {
 499                                         string sourceColumnName = (string) schemaRow ["BaseColumnName"];
 500                                         if (useColumnsForParameterNames) {
 501                                                 parameter = updateCommand.Parameters.Add (
 502                                                         GetNullCheckParameterName (sourceColumnName),
 503                                                         SqlDbType.Int);
 504                                         } else {
 505                                                 parameter = updateCommand.Parameters.Add (
 506                                                         GetParameterName (parmIndex++),
 507                                                         SqlDbType.Int);
 508                                         }
 509                                         parameter.IsNullable = allowNull;
 510                                         parameter.SourceVersion = DataRowVersion.Current;
 511                                         parameter.Value = 1;
 512
 513                                         whereClause.Append ("(");
 514                                         whereClause.Append (String.Format (clause1, parameter.ParameterName,
 515                                                                         GetQuotedString (sourceColumnName)));
 516                                         whereClause.Append (" OR ");
 517
 518                                 }
 519
 520                                 if (useColumnsForParameterNames) {
 521                                         parameter = CreateParameter (schemaRow, true);
 522                                 } else {
 523                                         parameter = CreateParameter (parmIndex++, schemaRow);
 524                                 }
 525                                 updateCommand.Parameters.Add (parameter);
 526                                 ApplyParameterInfo (parameter, schemaRow, StatementType.Update, true);
 527                                 parameter.IsNullable = allowNull;
 528                                 parameter.SourceVersion = DataRowVersion.Original;
 529
 530                                 whereClause.Append (String.Format (clause2, GetQuotedString (parameter.SourceColumn), parameter.ParameterName));
 531
 532                                 if (!isKey)
 533                                         whereClause.Append (")");
 534                         }
 535                         if (!keyFound)
 536                                 throw new InvalidOperationException ("Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.");
 537
 538                         // We're all done, so bring it on home
 539                         string sql = String.Format ("{0}{1} WHERE ( {2} )", command, columns.ToString (), whereClause.ToString ());
 540                         updateCommand.CommandText = sql;
 541                         return updateCommand;
 542                 }
 543
 544                 private SqlParameter CreateParameter (DataRow schemaRow, bool whereClause)
 545                 {
 546                         string sourceColumn = (string) schemaRow ["BaseColumnName"];
 547                         string name;
 548                         if (whereClause)
 549                                 name = GetParameterName ("Original_" + sourceColumn);
 550                         else
 551                                 name = GetParameterName (sourceColumn);
 552
 553                         SqlParameter param = new SqlParameter ();
 554                         param.ParameterName = name;
 555                         param.SourceColumn = sourceColumn;
 556                         return param;
 557                 }
 558
 559                 private SqlParameter CreateParameter (int paramIndex, DataRow schemaRow)
 560                 {
 561                         string sourceColumn = (string) schemaRow ["BaseColumnName"];
 562                         string name = GetParameterName (paramIndex);
 563
 564                         SqlParameter param = new SqlParameter ();
 565                         param.ParameterName = name;
 566                         param.SourceColumn = sourceColumn;
 567                         return param;
 568                 }
 569 #endif // ONLY_1_1
 570
 571                 public static void DeriveParameters (SqlCommand command)
 572                 {
 573                         command.DeriveParameters ();
 574                 }
 575
 576 #if ONLY_1_1
 577                 protected override void Dispose (bool disposing)
 578                 {
 579                         if (!disposed) {
 580                                 if (disposing) {
 581                                         if (insertCommand != null)
 582                                                 insertCommand.Dispose ();
 583                                         if (deleteCommand != null)
 584                                                 deleteCommand.Dispose ();
 585                                         if (updateCommand != null)
 586                                                 updateCommand.Dispose ();
 587                                         if (dbSchemaTable != null)
 588                                                 dbSchemaTable.Dispose ();
 589                                 }
 590                                 disposed = true;
 591                         }
 592                 }
 593 #endif
 594
 595                 public
 596 #if NET_2_0
 597                 new
 598 #endif // NET_2_0
 599                 SqlCommand GetDeleteCommand ()
 600                 {
 601 #if NET_2_0
 602                         return (SqlCommand) base.GetDeleteCommand (false);
 603 #else
 604                         BuildCache (true);
 605                         if (deleteCommand == null)
 606                                 return CreateDeleteCommand (false);
 607                         return deleteCommand;
 608 #endif
 609                 }
 610
 611                 public
 612 #if NET_2_0
 613                 new
 614 #endif // NET_2_0
 615                 SqlCommand GetInsertCommand ()
 616                 {
 617 #if NET_2_0
 618                         return (SqlCommand) base.GetInsertCommand (false);
 619 #else
 620                         BuildCache (true);
 621                         if (insertCommand == null)
 622                                 return CreateInsertCommand (false);
 623                         return insertCommand;
 624 #endif
 625                 }
 626
 627                 public
 628 #if NET_2_0
 629                 new
 630 #endif // NET_2_0
 631                 SqlCommand GetUpdateCommand ()
 632                 {
 633 #if NET_2_0
 634                         return (SqlCommand) base.GetUpdateCommand (false);
 635 #else
 636                         BuildCache (true);
 637                         if (updateCommand == null)
 638                                 return CreateUpdateCommand (false);
 639                         return updateCommand;
 640 #endif
 641                 }
 642
 643 #if NET_2_0
 644                 public new SqlCommand GetUpdateCommand (bool useColumnsForParameterNames)
 645                 {
 646                         return (SqlCommand) base.GetUpdateCommand (useColumnsForParameterNames);
 647                 }
 648
 649                 public new SqlCommand GetDeleteCommand (bool useColumnsForParameterNames)
 650                 {
 651                         return (SqlCommand) base.GetDeleteCommand (useColumnsForParameterNames);
 652                 }
 653
 654                 public new SqlCommand GetInsertCommand (bool useColumnsForParameterNames)
 655                 {
 656                         return (SqlCommand) base.GetInsertCommand (useColumnsForParameterNames);
 657                 }
 658
 659                 public override string QuoteIdentifier (string unquotedIdentifier)
 660                 {
 661                         if (unquotedIdentifier == null)
 662                                 throw new ArgumentNullException ("unquotedIdentifier");
 663
 664                         string prefix = QuotePrefix;
 665                         string suffix = QuoteSuffix;
 666
 667                         if ((prefix == "[" && suffix != "]") || (prefix == "\"" && suffix != "\""))
 668                                 throw new ArgumentException ("The QuotePrefix " +
 669                                         "and QuoteSuffix properties do not match.");
 670
 671                         string escaped = unquotedIdentifier.Replace (suffix,
 672                                 suffix + suffix);
 673                         return string.Concat (prefix, escaped, suffix);
 674                 }
 675
 676                 public override string UnquoteIdentifier (string quotedIdentifier)
 677                 {
 678                         return base.UnquoteIdentifier (quotedIdentifier);
 679                 }
 680 #endif // NET_2_0
 681
 682                 private bool IncludedInInsert (DataRow schemaRow)
 683                 {
 684                         // If the parameter has one of these properties, then we don't include it in the insert:
 685                         // AutoIncrement, Hidden, Expression, RowVersion, ReadOnly
 686
 687                         if (!schemaRow.IsNull ("IsAutoIncrement") && (bool) schemaRow ["IsAutoIncrement"])
 688                                 return false;
 689                         if (!schemaRow.IsNull ("IsHidden") && (bool) schemaRow ["IsHidden"])
 690                                 return false;
 691                         if (!schemaRow.IsNull ("IsExpression") && (bool) schemaRow ["IsExpression"])
 692                                 return false;
 693                         if (!schemaRow.IsNull ("IsRowVersion") && (bool) schemaRow ["IsRowVersion"])
 694                                 return false;
 695                         if (!schemaRow.IsNull ("IsReadOnly") && (bool) schemaRow ["IsReadOnly"])
 696                                 return false;
 697                         return true;
 698                 }
 699
 700                 private bool IncludedInUpdate (DataRow schemaRow)
 701                 {
 702                         // If the parameter has one of these properties, then we don't include it in the insert:
 703                         // AutoIncrement, Hidden, RowVersion
 704
 705                         if (!schemaRow.IsNull ("IsAutoIncrement") && (bool) schemaRow ["IsAutoIncrement"])
 706                                 return false;
 707                         if (!schemaRow.IsNull ("IsHidden") && (bool) schemaRow ["IsHidden"])
 708                                 return false;
 709                         if (!schemaRow.IsNull ("IsRowVersion") && (bool) schemaRow ["IsRowVersion"])
 710                                 return false;
 711                         if (!schemaRow.IsNull ("IsExpression") && (bool) schemaRow ["IsExpression"])
 712                                 return false;
 713                         if (!schemaRow.IsNull ("IsReadOnly") && (bool) schemaRow ["IsReadOnly"])
 714                                 return false;
 715
 716                         return true;
 717                 }
 718
 719                 private bool IncludedInWhereClause (DataRow schemaRow)
 720                 {
 721                         if ((bool) schemaRow ["IsLong"])
 722                                 return false;
 723                         return true;
 724                 }
 725
 726 #if ONLY_1_1
 727                 private string GetQuotedString (string value)
 728                 {
 729                         if (value == null || value.Length == 0)
 730                                 return value;
 731
 732                         string prefix = QuotePrefix;
 733                         string suffix = QuoteSuffix;
 734
 735                         if (prefix.Length == 0 && suffix.Length == 0)
 736                                 return value;
 737                         return String.Format ("{0}{1}{2}", prefix, value, suffix);
 738                 }
 739
 740                 string GetNullCheckParameterName (string parameterName)
 741                 {
 742                         return GetParameterName ("IsNull_" + parameterName);
 743                 }
 744
 745
 746                 private string QuotedTableName {
 747                         get { return GetQuotedString (tableName); }
 748                 }
 749
 750                 public void RefreshSchema ()
 751                 {
 752                         // FIXME: "Figure out what else needs to be cleaned up when we refresh."
 753                         tableName = String.Empty;
 754                         dbSchemaTable = null;
 755                         deleteCommand = null;
 756                         insertCommand = null;
 757                         updateCommand = null;
 758                 }
 759 #endif
 760
 761 #if NET_2_0
 762                 protected override void ApplyParameterInfo (DbParameter parameter,
 763                                                             DataRow datarow,
 764                                                             StatementType statementType,
 765                                                             bool whereClause)
 766                 {
 767                         SqlParameter sqlParam = (SqlParameter) parameter;
 768 #else
 769                 void ApplyParameterInfo (SqlParameter sqlParam,
 770                                          DataRow datarow,
 771                                          StatementType statementType,
 772                                          bool whereClause)
 773                 {
 774 #endif
 775                         sqlParam.SqlDbType = (SqlDbType) datarow ["ProviderType"];
 776
 777                         object precision = datarow ["NumericPrecision"];
 778                         if (precision != DBNull.Value) {
 779                                 short val = (short) precision;
 780                                 if (val < byte.MaxValue && val >= byte.MinValue)
 781                                         sqlParam.Precision = (byte) val;
 782                         }
 783
 784                         object scale = datarow ["NumericScale"];
 785                         if (scale != DBNull.Value) {
 786                                 short val = ((short) scale);
 787                                 if (val < byte.MaxValue && val >= byte.MinValue)
 788                                         sqlParam.Scale = (byte) val;
 789                         }
 790                 }
 791
 792 #if NET_2_0
 793                 protected override
 794 #endif
 795                 string GetParameterName (int parameterOrdinal)
 796                 {
 797                         return String.Format ("@p{0}",  parameterOrdinal);
 798                 }
 799
 800 #if NET_2_0
 801                 protected override
 802 #endif
 803                 string GetParameterName (string parameterName)
 804                 {
 805                         return String.Format ("@{0}", parameterName);
 806                 }
 807
 808 #if NET_2_0
 809                 protected override string GetParameterPlaceholder (int parameterOrdinal)
 810                 {
 811                         return GetParameterName (parameterOrdinal);
 812                 }
 813 #endif
 814
 815                 #endregion // Methods
 816
 817                 #region Event Handlers
 818
 819                 void RowUpdatingHandler (object sender, SqlRowUpdatingEventArgs args)
 820                 {
 821 #if NET_2_0
 822                         base.RowUpdatingHandler (args);
 823 #else
 824                         if (args.Command != null)
 825                                 return;
 826                         try {
 827                                 switch (args.StatementType) {
 828                                 case StatementType.Insert:
 829                                         args.Command = GetInsertCommand ();
 830                                         break;
 831                                 case StatementType.Update:
 832                                         args.Command = GetUpdateCommand ();
 833                                         break;
 834                                 case StatementType.Delete:
 835                                         args.Command = GetDeleteCommand ();
 836                                         break;
 837                                 }
 838                         } catch (Exception e) {
 839                                 args.Errors = e;
 840                                 args.Status = UpdateStatus.ErrorsOccurred;
 841                         }
 842 #endif
 843                 }
 844
 845 #if NET_2_0
 846                 protected override void SetRowUpdatingHandler (DbDataAdapter adapter)
 847                 {
 848                                 SqlDataAdapter sda = adapter as SqlDataAdapter;
 849                                 if (sda == null) {
 850                                         throw new InvalidOperationException ("Adapter needs to be a SqlDataAdapter");
 851                                 }
 852
 853                                 if (sda != base.DataAdapter)
 854                                         sda.RowUpdating += new SqlRowUpdatingEventHandler (RowUpdatingHandler);
 855                                 else
 856                                         sda.RowUpdating -= new SqlRowUpdatingEventHandler (RowUpdatingHandler);;
 857                 }
 858
 859                 protected override DataTable GetSchemaTable (DbCommand srcCommand)
 860                 {
 861                         using (SqlDataReader rdr = (SqlDataReader) srcCommand.ExecuteReader (CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
 862                                 return rdr.GetSchemaTable ();
 863                 }
 864
 865                 protected override DbCommand InitializeCommand (DbCommand command)
 866                 {
 867                         if (command == null) {
 868                                 command = new SqlCommand ();
 869                         } else {
 870                                 command.CommandTimeout = 30;
 871                                 command.Transaction = null;
 872                                 command.CommandType = CommandType.Text;
 873                                 command.UpdatedRowSource = UpdateRowSource.None;
 874                         }
 875                         return command;
 876                 }
 877 #endif // NET_2_0
 878
 879                 #endregion // Event Handlers
 880         }
 881 }