mcs/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 //
   7 // Copyright (C) Tim Coleman, 2002
   8 //
   9
  10 //
  11 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
  12 //
  13 // Permission is hereby granted, free of charge, to any person obtaining
  14 // a copy of this software and associated documentation files (the
  15 // "Software"), to deal in the Software without restriction, including
  16 // without limitation the rights to use, copy, modify, merge, publish,
  17 // distribute, sublicense, and/or sell copies of the Software, and to
  18 // permit persons to whom the Software is furnished to do so, subject to
  19 // the following conditions:
  20 //
  21 // The above copyright notice and this permission notice shall be
  22 // included in all copies or substantial portions of the Software.
  23 //
  24 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  25 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  26 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  27 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  28 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  29 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  30 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  31 //
  32
  33 using System;
  34 using System.Collections;
  35 using System.ComponentModel;
  36 using System.Data;
  37 using System.Data.Common;
  38 using System.Text;
  39
  40 namespace System.Data.SqlClient {
  41 #if NET_2_0
  42         public sealed class SqlCommandBuilder : DbCommandBuilder
  43 #else
  44         public sealed class SqlCommandBuilder : Component
  45 #endif // NET_2_0
  46         {
  47                 #region Fields
  48
  49                 bool disposed = false;
  50
  51                 DataTable dbSchemaTable;
  52                 SqlDataAdapter adapter;
  53                 string quotePrefix;
  54                 string quoteSuffix;
  55                 string[] columnNames;
  56                 string tableName;
  57
  58                 SqlCommand deleteCommand;
  59                 SqlCommand insertCommand;
  60                 SqlCommand updateCommand;
  61
  62                 // Used to construct WHERE clauses
  63                 static readonly string clause1 = "({0} = 1 AND {1} IS NULL)";
  64                 static readonly string clause2 = "({0} = {1})";
  65
  66                 #endregion // Fields
  67
  68                 #region Constructors
  69
  70                 public SqlCommandBuilder ()
  71                 {
  72                         dbSchemaTable = null;
  73                         adapter = null;
  74 #if NET_2_0
  75                         quoteSuffix = "]";
  76                         quotePrefix = "[";
  77 #else
  78                         quoteSuffix = String.Empty;
  79                         quotePrefix = String.Empty;
  80 #endif
  81                 }
  82
  83                 public SqlCommandBuilder (SqlDataAdapter adapter)
  84                         : this ()
  85                 {
  86                         DataAdapter = adapter;
  87                 }
  88
  89                 #endregion // Constructors
  90
  91                 #region Properties
  92
  93 #if !NET_2_0
  94                 [DataSysDescription ("The DataAdapter for which to automatically generate SqlCommands")]
  95 #endif
  96                 [DefaultValue (null)]
  97                 public new SqlDataAdapter DataAdapter {
  98                         get { return adapter; }
  99                         set {
 100                                 if (adapter != null)
 101                                         adapter.RowUpdating -= new SqlRowUpdatingEventHandler (RowUpdatingHandler);
 102
 103                                 adapter = value;
 104
 105                                 if (adapter != null)
 106                                         adapter.RowUpdating += new SqlRowUpdatingEventHandler (RowUpdatingHandler);
 107                         }
 108                 }
 109
 110                 private string QuotedTableName {
 111                         get { return GetQuotedString (tableName); }
 112                 }
 113
 114                 [Browsable (false)]
 115 #if !NET_2_0
 116                 [DataSysDescription ("The character used in a text command as the opening quote for quoting identifiers that contain special characters.")]
 117 #endif
 118                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 119                 public
 120 #if NET_2_0
 121                 override
 122 #endif // NET_2_0
 123                 string QuotePrefix {
 124                         get { return quotePrefix; }
 125                         set {
 126                                 if (dbSchemaTable != null)
 127                                         throw new InvalidOperationException ("The QuotePrefix and QuoteSuffix properties cannot be changed once an Insert, Update, or Delete command has been generated.");
 128                                 quotePrefix = value;
 129                         }
 130                 }
 131
 132                 [Browsable (false)]
 133 #if !NET_2_0
 134                 [DataSysDescription ("The character used in a text command as the closing quote for quoting identifiers that contain special characters. ")]
 135 #endif
 136                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 137                 public
 138 #if NET_2_0
 139                 override
 140 #endif // NET_2_0
 141                 string QuoteSuffix {
 142                         get { return quoteSuffix; }
 143                         set {
 144                                 if (dbSchemaTable != null)
 145                                         throw new InvalidOperationException ("The QuotePrefix and QuoteSuffix properties cannot be changed once an Insert, Update, or Delete command has been generated.");
 146                                 quoteSuffix = value;
 147                         }
 148                 }
 149
 150                 private SqlCommand SourceCommand {
 151                         get {
 152                                 if (adapter != null)
 153                                         return adapter.SelectCommand;
 154                                 return null;
 155                         }
 156                 }
 157
 158                 #endregion // Properties
 159
 160                 #region Methods
 161
 162                 private void BuildCache (bool closeConnection)
 163                 {
 164                         SqlCommand sourceCommand = SourceCommand;
 165                         if (sourceCommand == null)
 166                                 throw new InvalidOperationException ("The DataAdapter.SelectCommand property needs to be initialized.");
 167                         SqlConnection connection = sourceCommand.Connection;
 168                         if (connection == null)
 169                                 throw new InvalidOperationException ("The DataAdapter.SelectCommand.Connection property needs to be initialized.");
 170
 171                         if (dbSchemaTable == null) {
 172                                 if (connection.State == ConnectionState.Open)
 173                                         closeConnection = false;
 174                                 else
 175                                         connection.Open ();
 176
 177                                 SqlDataReader reader = sourceCommand.ExecuteReader (CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
 178                                 dbSchemaTable = reader.GetSchemaTable ();
 179                                 reader.Close ();
 180                                 if (closeConnection)
 181                                         connection.Close ();
 182                                 BuildInformation (dbSchemaTable);
 183                         }
 184                 }
 185
 186                 private void BuildInformation (DataTable schemaTable)
 187                 {
 188                         tableName = String.Empty;
 189                         foreach (DataRow schemaRow in schemaTable.Rows) {
 190                                 if (schemaRow.IsNull ("BaseTableName") ||
 191                                     schemaRow ["BaseTableName"] == String.Empty)
 192                                         continue;
 193
 194                                 if (tableName == String.Empty)
 195                                         tableName = (string) schemaRow ["BaseTableName"];
 196                                 else if (tableName != (string) schemaRow["BaseTableName"])
 197                                         throw new InvalidOperationException ("Dynamic SQL generation is not supported against multiple base tables.");
 198                         }
 199                         if (tableName == String.Empty)
 200                                 throw new InvalidOperationException ("Dynamic SQL generation is not supported with no base table.");
 201                         dbSchemaTable = schemaTable;
 202                 }
 203
 204                 private SqlCommand CreateDeleteCommand ()
 205                 {
 206                         // If no table was found, then we can't do an delete
 207                         if (QuotedTableName == String.Empty)
 208                                 return null;
 209
 210                         CreateNewCommand (ref deleteCommand);
 211
 212                         string command = String.Format ("DELETE FROM {0}", QuotedTableName);
 213                         StringBuilder columns = new StringBuilder ();
 214                         StringBuilder whereClause = new StringBuilder ();
 215                         string dsColumnName = String.Empty;
 216                         bool keyFound = false;
 217                         int parmIndex = 1;
 218
 219                         foreach (DataRow schemaRow in dbSchemaTable.Rows) {
 220                                 if ((bool)schemaRow["IsExpression"] == true)
 221                                         continue;
 222                                 if (!IncludedInWhereClause (schemaRow))
 223                                         continue;
 224
 225                                 if (whereClause.Length > 0)
 226                                         whereClause.Append (" AND ");
 227
 228                                 bool isKey = (bool) schemaRow ["IsKey"];
 229                                 SqlParameter parameter = null;
 230
 231                                 if (isKey)
 232                                         keyFound = true;
 233
 234                                 //ms.net 1.1 generates the null check for columns even if AllowDBNull is false
 235                                 //while ms.net 2.0 does not. Anyways, since both forms are logically equivalent
 236                                 //following the 2.0 approach
 237                                 bool allowNull = (bool) schemaRow ["AllowDBNull"];
 238                                 if (!isKey && allowNull) {
 239                                         parameter = deleteCommand.Parameters.Add (String.Format ("@p{0}", parmIndex++),
 240                                                                                 SqlDbType.Int);
 241                                         String sourceColumnName = (string) schemaRow ["BaseColumnName"];
 242                                         parameter.Value = 1;
 243
 244                                         whereClause.Append ("(");
 245                                         whereClause.Append (String.Format (clause1, parameter.ParameterName,
 246                                                                         GetQuotedString (sourceColumnName)));
 247                                         whereClause.Append (" OR ");
 248                                 }
 249
 250                                 parameter = deleteCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow));
 251                                 parameter.SourceVersion = DataRowVersion.Original;
 252
 253                                 whereClause.Append (String.Format (clause2, GetQuotedString (parameter.SourceColumn), parameter.ParameterName));
 254
 255                                 if (!isKey && allowNull)
 256                                         whereClause.Append (")");
 257                         }
 258                         if (!keyFound)
 259                                 throw new InvalidOperationException ("Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.");
 260
 261                         // We're all done, so bring it on home
 262                         string sql = String.Format ("{0} WHERE ({1})", command, whereClause.ToString ());
 263                         deleteCommand.CommandText = sql;
 264                         return deleteCommand;
 265                 }
 266
 267                 private SqlCommand CreateInsertCommand ()
 268                 {
 269                         if (QuotedTableName == String.Empty)
 270                                 return null;
 271
 272                         CreateNewCommand (ref insertCommand);
 273
 274                         string command = String.Format ("INSERT INTO {0}", QuotedTableName);
 275                         string sql;
 276                         StringBuilder columns = new StringBuilder ();
 277                         StringBuilder values = new StringBuilder ();
 278                         string dsColumnName = String.Empty;
 279
 280                         int parmIndex = 1;
 281                         foreach (DataRow schemaRow in dbSchemaTable.Rows) {
 282                                 if (!IncludedInInsert (schemaRow))
 283                                         continue;
 284
 285                                 if (parmIndex > 1) {
 286                                         columns.Append (", ");
 287                                         values.Append (", ");
 288                                 }
 289
 290                                 SqlParameter parameter = insertCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow));
 291                                 parameter.SourceVersion = DataRowVersion.Current;
 292
 293                                 columns.Append (GetQuotedString (parameter.SourceColumn));
 294                                 values.Append (parameter.ParameterName);
 295                         }
 296
 297                         sql = String.Format ("{0} ({1}) VALUES ({2})", command, columns.ToString (), values.ToString ());
 298                         insertCommand.CommandText = sql;
 299                         return insertCommand;
 300                 }
 301
 302                 private void CreateNewCommand (ref SqlCommand command)
 303                 {
 304                         SqlCommand sourceCommand = SourceCommand;
 305                         if (command == null) {
 306                                 command = sourceCommand.Connection.CreateCommand ();
 307                                 command.CommandTimeout = sourceCommand.CommandTimeout;
 308                                 command.Transaction = sourceCommand.Transaction;
 309                         }
 310                         command.CommandType = CommandType.Text;
 311                         command.UpdatedRowSource = UpdateRowSource.None;
 312                         command.Parameters.Clear ();
 313                 }
 314
 315                 private SqlCommand CreateUpdateCommand ()
 316                 {
 317                         // If no table was found, then we can't do an update
 318                         if (QuotedTableName == String.Empty)
 319                                 return null;
 320
 321                         CreateNewCommand (ref updateCommand);
 322
 323                         string command = String.Format ("UPDATE {0} SET ", QuotedTableName);
 324                         StringBuilder columns = new StringBuilder ();
 325                         StringBuilder whereClause = new StringBuilder ();
 326                         int parmIndex = 1;
 327                         string dsColumnName = String.Empty;
 328                         bool keyFound = false;
 329
 330                         // First, create the X=Y list for UPDATE
 331                         foreach (DataRow schemaRow in dbSchemaTable.Rows) {
 332                                 if (!IncludedInUpdate (schemaRow))
 333                                         continue;
 334                                 if (columns.Length > 0)
 335                                         columns.Append (", ");
 336
 337                                 SqlParameter parameter = updateCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow));
 338                                 parameter.SourceVersion = DataRowVersion.Current;
 339
 340                                 columns.Append (String.Format ("{0} = {1}", GetQuotedString (parameter.SourceColumn), parameter.ParameterName));
 341                         }
 342
 343                         // Now, create the WHERE clause.  This may be optimizable, but it would be ugly to incorporate
 344                         // into the loop above.  "Premature optimization is the root of all evil." -- Knuth
 345                         foreach (DataRow schemaRow in dbSchemaTable.Rows) {
 346                                 if ((bool)schemaRow["IsExpression"] == true)
 347                                         continue;
 348
 349                                 if (!IncludedInWhereClause (schemaRow))
 350                                         continue;
 351
 352                                 if (whereClause.Length > 0)
 353                                         whereClause.Append (" AND ");
 354
 355                                 bool isKey = (bool) schemaRow ["IsKey"];
 356                                 SqlParameter parameter = null;
 357
 358
 359                                 if (isKey)
 360                                         keyFound = true;
 361
 362                                 //ms.net 1.1 generates the null check for columns even if AllowDBNull is false
 363                                 //while ms.net 2.0 does not. Anyways, since both forms are logically equivalent
 364                                 //following the 2.0 approach
 365                                 bool allowNull = (bool) schemaRow ["AllowDBNull"];
 366                                 if (!isKey && allowNull) {
 367                                         parameter = updateCommand.Parameters.Add (String.Format ("@p{0}", parmIndex++),
 368                                                                                 SqlDbType.Int);
 369                                         parameter.Value = 1;
 370                                         whereClause.Append ("(");
 371                                         whereClause.Append (String.Format (clause1, parameter.ParameterName,
 372                                                                         GetQuotedString ((string) schemaRow ["BaseColumnName"])));
 373                                         whereClause.Append (" OR ");
 374                                 }
 375
 376                                 parameter = updateCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow));
 377                                 parameter.SourceVersion = DataRowVersion.Original;
 378
 379                                 whereClause.Append (String.Format (clause2, GetQuotedString (parameter.SourceColumn), parameter.ParameterName));
 380
 381                                 if (!isKey && allowNull)
 382                                         whereClause.Append (")");
 383                         }
 384                         if (!keyFound)
 385                                 throw new InvalidOperationException ("Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.");
 386
 387                         // We're all done, so bring it on home
 388                         string sql = String.Format ("{0}{1} WHERE ({2})", command, columns.ToString (), whereClause.ToString ());
 389                         updateCommand.CommandText = sql;
 390                         return updateCommand;
 391                 }
 392
 393                 private SqlParameter CreateParameter (int parmIndex, DataRow schemaRow)
 394                 {
 395                         string name = String.Format ("@p{0}", parmIndex);
 396                         string sourceColumn = (string) schemaRow ["BaseColumnName"];
 397                         SqlDbType sqlDbType = (SqlDbType) schemaRow ["ProviderType"];
 398                         int size = (int) schemaRow ["ColumnSize"];
 399
 400                         return new SqlParameter (name, sqlDbType, size, sourceColumn);
 401                 }
 402
 403                 public static void DeriveParameters (SqlCommand command)
 404                 {
 405                         command.DeriveParameters ();
 406                 }
 407
 408                 protected override void Dispose (bool disposing)
 409                 {
 410                         if (!disposed) {
 411                                 if (disposing) {
 412                                         if (insertCommand != null)
 413                                                 insertCommand.Dispose ();
 414                                         if (deleteCommand != null)
 415                                                 deleteCommand.Dispose ();
 416                                         if (updateCommand != null)
 417                                                 updateCommand.Dispose ();
 418                                         if (dbSchemaTable != null)
 419                                                 dbSchemaTable.Dispose ();
 420                                 }
 421                                 disposed = true;
 422                         }
 423                 }
 424
 425                 public
 426 #if NET_2_0
 427                 new
 428 #endif // NET_2_0
 429                 SqlCommand GetDeleteCommand ()
 430                 {
 431                         BuildCache (true);
 432                         if (deleteCommand == null)
 433                                 return CreateDeleteCommand ();
 434                         return deleteCommand;
 435                 }
 436
 437                 public
 438 #if NET_2_0
 439                 new
 440 #endif // NET_2_0
 441                 SqlCommand GetInsertCommand ()
 442                 {
 443                         BuildCache (true);
 444                         if (insertCommand == null)
 445                                 return CreateInsertCommand ();
 446                         return insertCommand;
 447                 }
 448
 449                 private string GetQuotedString (string value)
 450                 {
 451                         if (value == String.Empty || value == null)
 452                                 return value;
 453                         if (quotePrefix == String.Empty && quoteSuffix == String.Empty)
 454                                 return value;
 455                         return String.Format ("{0}{1}{2}", quotePrefix, value, quoteSuffix);
 456                 }
 457
 458                 public
 459 #if NET_2_0
 460                 new
 461 #endif // NET_2_0
 462                 SqlCommand GetUpdateCommand ()
 463                 {
 464                         BuildCache (true);
 465                         if (updateCommand == null)
 466                                 return CreateUpdateCommand ();
 467                         return updateCommand;
 468                 }
 469
 470                 private bool IncludedInInsert (DataRow schemaRow)
 471                 {
 472                         // If the parameter has one of these properties, then we don't include it in the insert:
 473                         // AutoIncrement, Hidden, Expression, RowVersion, ReadOnly
 474
 475                         if (!schemaRow.IsNull ("IsAutoIncrement") && (bool) schemaRow ["IsAutoIncrement"])
 476                                 return false;
 477                         if (!schemaRow.IsNull ("IsHidden") && (bool) schemaRow ["IsHidden"])
 478                                 return false;
 479                         if (!schemaRow.IsNull ("IsExpression") && (bool) schemaRow ["IsExpression"])
 480                                 return false;
 481                         if (!schemaRow.IsNull ("IsRowVersion") && (bool) schemaRow ["IsRowVersion"])
 482                                 return false;
 483                         if (!schemaRow.IsNull ("IsReadOnly") && (bool) schemaRow ["IsReadOnly"])
 484                                 return false;
 485                         return true;
 486                 }
 487
 488                 private bool IncludedInUpdate (DataRow schemaRow)
 489                 {
 490                         // If the parameter has one of these properties, then we don't include it in the insert:
 491                         // AutoIncrement, Hidden, RowVersion
 492
 493                         if (!schemaRow.IsNull ("IsAutoIncrement") && (bool) schemaRow ["IsAutoIncrement"])
 494                                 return false;
 495                         if (!schemaRow.IsNull ("IsHidden") && (bool) schemaRow ["IsHidden"])
 496                                 return false;
 497                         if (!schemaRow.IsNull ("IsRowVersion") && (bool) schemaRow ["IsRowVersion"])
 498                                 return false;
 499                         if (!schemaRow.IsNull ("IsExpression") && (bool) schemaRow ["IsExpression"])
 500                                 return false;
 501                         if (!schemaRow.IsNull ("IsReadOnly") && (bool) schemaRow ["IsReadOnly"])
 502                                 return false;
 503
 504                         return true;
 505                 }
 506
 507                 private bool IncludedInWhereClause (DataRow schemaRow)
 508                 {
 509                         if ((bool) schemaRow ["IsLong"])
 510                                 return false;
 511                         return true;
 512                 }
 513
 514                 [MonoTODO ("Figure out what else needs to be cleaned up when we refresh.")]
 515                 public
 516 #if NET_2_0
 517                 override
 518 #endif // NET_2_0
 519                 void RefreshSchema ()
 520                 {
 521                         tableName = String.Empty;
 522                         dbSchemaTable = null;
 523                         CreateNewCommand (ref deleteCommand);
 524                         CreateNewCommand (ref updateCommand);
 525                         CreateNewCommand (ref insertCommand);
 526                 }
 527
 528 #if NET_2_0
 529                 [MonoTODO]
 530                 protected override void ApplyParameterInfo (DbParameter dbParameter,
 531                                                             DataRow row,
 532                                                             StatementType statementType,
 533                                                             bool whereClause)
 534                 {
 535                         throw new NotImplementedException ();
 536                 }
 537
 538                 [MonoTODO]
 539                 protected override string GetParameterName (int position)
 540                 {
 541                         throw new NotImplementedException ();
 542                 }
 543
 544                 [MonoTODO]
 545                 protected override string GetParameterName (string parameterName)
 546                 {
 547                         throw new NotImplementedException ();
 548                 }
 549
 550
 551                 [MonoTODO]
 552                 protected override string GetParameterPlaceholder (int position)
 553                 {
 554                         throw new NotImplementedException ();
 555                 }
 556
 557 #endif // NET_2_0
 558                 #endregion // Methods
 559
 560                 #region Event Handlers
 561
 562                 private void RowUpdatingHandler (object sender, SqlRowUpdatingEventArgs args)
 563                 {
 564                         if (args.Command != null)
 565                                 return;
 566                         try {
 567                                 switch (args.StatementType) {
 568                                 case StatementType.Insert:
 569                                         args.Command = GetInsertCommand ();
 570                                         break;
 571                                 case StatementType.Update:
 572                                         args.Command = GetUpdateCommand ();
 573                                         break;
 574                                 case StatementType.Delete:
 575                                         args.Command = GetDeleteCommand ();
 576                                         break;
 577                                 }
 578                         } catch (Exception e) {
 579                                 args.Errors = e;
 580                                 args.Status = UpdateStatus.ErrorsOccurred;
 581                         }               \r
 582                 }
 583
 584 #if NET_2_0
 585                 [MonoTODO]
 586                 protected override void SetRowUpdatingHandler (DbDataAdapter adapter)
 587                 {
 588                         throw new NotImplementedException ();
 589                 }
 590 #endif // NET_2_0
 591
 592                 #endregion // Event Handlers
 593         }
 594 }
 595