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         public sealed class SqlCommandBuilder : Component
  42         {
  43                 #region Fields
  44
  45                 bool disposed = false;
  46
  47                 DataTable dbSchemaTable;
  48                 SqlDataAdapter adapter;
  49                 string quotePrefix;
  50                 string quoteSuffix;
  51                 string[] columnNames;
  52                 string tableName;
  53
  54                 SqlCommand deleteCommand;
  55                 SqlCommand insertCommand;
  56                 SqlCommand updateCommand;
  57
  58                 // Used to construct WHERE clauses
  59                 static readonly string clause1 = "({0} IS NULL AND {1} IS NULL)";
  60                 static readonly string clause2 = "({0} = {1})";
  61
  62                 #endregion // Fields
  63
  64                 #region Constructors
  65
  66                 public SqlCommandBuilder ()
  67                 {
  68                         dbSchemaTable = null;
  69                         adapter = null;
  70                         quoteSuffix = String.Empty;
  71                         quotePrefix = String.Empty;
  72                 }
  73
  74                 public SqlCommandBuilder (SqlDataAdapter adapter)
  75                         : this ()
  76                 {
  77                         DataAdapter = adapter;
  78                 }
  79
  80                 #endregion // Constructors
  81
  82                 #region Properties
  83
  84                 [DataSysDescription ("The DataAdapter for which to automatically generate SqlCommands")]
  85                 [DefaultValue (null)]
  86                 public SqlDataAdapter DataAdapter {
  87                         get { return adapter; }
  88                         set {
  89                                 adapter = value;
  90                                 if (adapter != null)
  91                                         adapter.RowUpdating += new SqlRowUpdatingEventHandler (RowUpdatingHandler);
  92                         }
  93                 }
  94
  95                 private string QuotedTableName {
  96                         get { return GetQuotedString (tableName); }
  97                 }
  98
  99                 [Browsable (false)]
 100                 [DataSysDescription ("The character used in a text command as the opening quote for quoting identifiers that contain special characters.")]
 101                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 102                 public string QuotePrefix {
 103                         get { return quotePrefix; }
 104                         set {
 105                                 if (dbSchemaTable != null)
 106                                         throw new InvalidOperationException ("The QuotePrefix and QuoteSuffix properties cannot be changed once an Insert, Update, or Delete command has been generated.");
 107                                 quotePrefix = value;
 108                         }
 109                 }
 110
 111                 [Browsable (false)]
 112                 [DataSysDescription ("The character used in a text command as the closing quote for quoting identifiers that contain special characters.")]
 113                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 114                 public string QuoteSuffix {
 115                         get { return quoteSuffix; }
 116                         set {
 117                                 if (dbSchemaTable != null)
 118                                         throw new InvalidOperationException ("The QuotePrefix and QuoteSuffix properties cannot be changed once an Insert, Update, or Delete command has been generated.");
 119                                 quoteSuffix = value;
 120                         }
 121                 }
 122
 123                 private SqlCommand SourceCommand {
 124                         get {
 125                                 if (adapter != null)
 126                                         return adapter.SelectCommand;
 127                                 return null;
 128                         }
 129                 }
 130
 131                 #endregion // Properties
 132
 133                 #region Methods
 134
 135                 private void BuildCache (bool closeConnection)
 136                 {
 137                         SqlCommand sourceCommand = SourceCommand;
 138                         if (sourceCommand == null)
 139                                 throw new InvalidOperationException ("The DataAdapter.SelectCommand property needs to be initialized.");
 140                         SqlConnection connection = sourceCommand.Connection;
 141                         if (connection == null)
 142                                 throw new InvalidOperationException ("The DataAdapter.SelectCommand.Connection property needs to be initialized.");
 143
 144                         if (dbSchemaTable == null) {
 145                                 if (connection.State == ConnectionState.Open)
 146                                         closeConnection = false;
 147                                 else
 148                                         connection.Open ();
 149
 150                                 SqlDataReader reader = sourceCommand.ExecuteReader (CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
 151                                 dbSchemaTable = reader.GetSchemaTable ();
 152                                 reader.Close ();
 153                                 if (closeConnection)
 154                                         connection.Close ();
 155                                 BuildInformation (dbSchemaTable);
 156                         }
 157                 }
 158
 159                 private void BuildInformation (DataTable schemaTable)
 160                 {
 161                         tableName = String.Empty;
 162                         foreach (DataRow schemaRow in schemaTable.Rows) {
 163                                 if (tableName == String.Empty)
 164                                         tableName = schemaRow.IsNull ("BaseTableName") ? null : (string) schemaRow ["BaseTableName"];
 165                                 else if (schemaRow.IsNull ("BaseTableName")) {
 166                                         if (tableName != null)
 167                                                 throw new InvalidOperationException ("Dynamic SQL generation is not supported against multiple base tables.");
 168                                 } else if (tableName != (string) schemaRow["BaseTableName"])
 169                                         throw new InvalidOperationException ("Dynamic SQL generation is not supported against multiple base tables.");
 170                         }
 171                         dbSchemaTable = schemaTable;
 172                 }
 173
 174                 private SqlCommand CreateDeleteCommand (DataRow row, DataTableMapping tableMapping)
 175                 {
 176                         // If no table was found, then we can't do an delete
 177                         if (QuotedTableName == String.Empty)
 178                                 return null;
 179
 180
 181                         CreateNewCommand (ref deleteCommand);
 182
 183                         string command = String.Format ("DELETE FROM {0} ", QuotedTableName);
 184                         StringBuilder columns = new StringBuilder ();
 185                         StringBuilder whereClause = new StringBuilder ();
 186                         string dsColumnName = String.Empty;
 187                         bool keyFound = false;
 188                         int parmIndex = 1;
 189
 190                         foreach (DataRow schemaRow in dbSchemaTable.Rows) {
 191                                 if (!IncludedInWhereClause (schemaRow))
 192                                         continue;
 193
 194                                 if (whereClause.Length > 0)
 195                                         whereClause.Append (" AND ");
 196
 197                                 bool isKey = (bool) schemaRow ["IsKey"];
 198                                 SqlParameter parameter = null;
 199
 200                                 if (!isKey) {
 201                                         parameter = deleteCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow));
 202
 203                                         dsColumnName = tableMapping.ColumnMappings [parameter.SourceColumn].DataSetColumn;
 204                                         if (row != null)
 205                                                 parameter.Value = row [dsColumnName, DataRowVersion.Current];
 206                                         whereClause.Append ("(");
 207                                         whereClause.Append (String.Format (clause1, GetQuotedString (parameter.SourceColumn), parameter.ParameterName));
 208                                         whereClause.Append (" OR ");
 209                                 }
 210                                 else
 211                                         keyFound = true;
 212
 213                                 parameter = deleteCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow));
 214
 215                                 dsColumnName = tableMapping.ColumnMappings [parameter.SourceColumn].DataSetColumn;
 216                                 if (row != null)
 217                                         parameter.Value = row [dsColumnName, DataRowVersion.Current];
 218
 219                                 whereClause.Append (String.Format (clause2, GetQuotedString (parameter.SourceColumn), parameter.ParameterName));
 220
 221                                 if (!isKey)
 222                                         whereClause.Append (")");
 223                         }
 224                         if (!keyFound)
 225                                 throw new InvalidOperationException ("Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.");
 226
 227                         // We're all done, so bring it on home
 228                         string sql = String.Format ("{0} WHERE ( {1} )", command, whereClause.ToString ());
 229                         deleteCommand.CommandText = sql;
 230                         return deleteCommand;
 231                 }
 232
 233                 private SqlCommand CreateInsertCommand (DataRow row, DataTableMapping tableMapping)
 234                 {
 235                         if (QuotedTableName == String.Empty)
 236                                 return null;
 237
 238                         CreateNewCommand (ref insertCommand);
 239
 240                         string command = String.Format ("INSERT INTO {0}", QuotedTableName);
 241                         string sql;
 242                         StringBuilder columns = new StringBuilder ();
 243                         StringBuilder values = new StringBuilder ();
 244                         string dsColumnName = String.Empty;
 245
 246                         int parmIndex = 1;
 247                         foreach (DataRow schemaRow in dbSchemaTable.Rows) {
 248                                 if (!IncludedInInsert (schemaRow))
 249                                         continue;
 250
 251                                 if (parmIndex > 1) {
 252                                         columns.Append (" , ");
 253                                         values.Append (" , ");
 254                                 }
 255
 256                                 SqlParameter parameter = insertCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow));
 257
 258                                 dsColumnName = tableMapping.ColumnMappings [parameter.SourceColumn].DataSetColumn;
 259                                 if (row != null)
 260                                         parameter.Value = row [dsColumnName];
 261
 262                                 columns.Append (GetQuotedString (parameter.SourceColumn));
 263                                 values.Append (parameter.ParameterName);
 264                         }
 265
 266                         sql = String.Format ("{0}( {1} ) VALUES ( {2} )", command, columns.ToString (), values.ToString ());
 267                         insertCommand.CommandText = sql;
 268                         return insertCommand;
 269                 }
 270
 271                 private void CreateNewCommand (ref SqlCommand command)
 272                 {
 273                         SqlCommand sourceCommand = SourceCommand;
 274                         if (command == null) {
 275                                 command = sourceCommand.Connection.CreateCommand ();
 276                                 command.CommandTimeout = sourceCommand.CommandTimeout;
 277                                 command.Transaction = sourceCommand.Transaction;
 278                         }
 279                         command.CommandType = CommandType.Text;
 280                         command.UpdatedRowSource = UpdateRowSource.None;
 281                 }
 282
 283                 private SqlCommand CreateUpdateCommand (DataRow row, DataTableMapping tableMapping)
 284                 {
 285                         // If no table was found, then we can't do an update
 286                         if (QuotedTableName == String.Empty)
 287                                 return null;
 288
 289                         CreateNewCommand (ref updateCommand);
 290
 291                         string command = String.Format ("UPDATE {0} SET ", QuotedTableName);
 292                         StringBuilder columns = new StringBuilder ();
 293                         StringBuilder whereClause = new StringBuilder ();
 294                         int parmIndex = 1;
 295                         string dsColumnName = String.Empty;
 296                         bool keyFound = false;
 297
 298                         // First, create the X=Y list for UPDATE
 299                         foreach (DataRow schemaRow in dbSchemaTable.Rows) {
 300                                 if (columns.Length > 0)
 301                                         columns.Append (" , ");
 302
 303                                 SqlParameter parameter = updateCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow));
 304
 305                                 dsColumnName = tableMapping.ColumnMappings [parameter.SourceColumn].DataSetColumn;
 306                                 if (row != null)
 307                                         parameter.Value = row [dsColumnName, DataRowVersion.Proposed];
 308
 309                                 columns.Append (String.Format ("{0} = {1}", GetQuotedString (parameter.SourceColumn), parameter.ParameterName));
 310                         }
 311
 312                         // Now, create the WHERE clause.  This may be optimizable, but it would be ugly to incorporate
 313                         // into the loop above.  "Premature optimization is the root of all evil." -- Knuth
 314                         foreach (DataRow schemaRow in dbSchemaTable.Rows) {
 315                                 if (!IncludedInWhereClause (schemaRow))
 316                                         continue;
 317
 318                                 if (whereClause.Length > 0)
 319                                         whereClause.Append (" AND ");
 320
 321                                 bool isKey = (bool) schemaRow ["IsKey"];
 322                                 SqlParameter parameter = null;
 323
 324
 325                                 if (!isKey) {
 326                                         parameter = updateCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow));
 327
 328                                         dsColumnName = tableMapping.ColumnMappings [parameter.SourceColumn].DataSetColumn;
 329                                         if (row != null)
 330                                                 parameter.Value = row [dsColumnName];
 331
 332                                         whereClause.Append ("(");
 333                                         whereClause.Append (String.Format (clause1, GetQuotedString (parameter.SourceColumn), parameter.ParameterName));
 334                                         whereClause.Append (" OR ");
 335                                 }
 336                                 else
 337                                         keyFound = true;
 338
 339                                 parameter = updateCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow));
 340
 341                                 dsColumnName = tableMapping.ColumnMappings [parameter.SourceColumn].DataSetColumn;
 342                                 if (row != null)
 343                                         parameter.Value = row [dsColumnName];
 344
 345                                 whereClause.Append (String.Format (clause2, GetQuotedString (parameter.SourceColumn), parameter.ParameterName));
 346
 347                                 if (!isKey)
 348                                         whereClause.Append (")");
 349                         }
 350                         if (!keyFound)
 351                                 throw new InvalidOperationException ("Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.");
 352
 353                         // We're all done, so bring it on home
 354                         string sql = String.Format ("{0}{1} WHERE ( {2} )", command, columns.ToString (), whereClause.ToString ());
 355                         updateCommand.CommandText = sql;
 356                         return updateCommand;
 357                 }
 358
 359                 private SqlParameter CreateParameter (int parmIndex, DataRow schemaRow)
 360                 {
 361                         string name = String.Format ("@p{0}", parmIndex);
 362                         string sourceColumn = (string) schemaRow ["BaseColumnName"];
 363                         SqlDbType sqlDbType = (SqlDbType) schemaRow ["ProviderType"];
 364                         int size = (int) schemaRow ["ColumnSize"];
 365
 366                         return new SqlParameter (name, sqlDbType, size, sourceColumn);
 367                 }
 368
 369                 public static void DeriveParameters (SqlCommand command)
 370                 {
 371                         command.DeriveParameters ();
 372                 }
 373
 374                 protected override void Dispose (bool disposing)
 375                 {
 376                         if (!disposed) {
 377                                 if (disposing) {
 378                                         if (insertCommand != null)
 379                                                 insertCommand.Dispose ();
 380                                         if (deleteCommand != null)
 381                                                 deleteCommand.Dispose ();
 382                                         if (updateCommand != null)
 383                                                 updateCommand.Dispose ();
 384                                         if (dbSchemaTable != null)
 385                                                 dbSchemaTable.Dispose ();
 386                                 }
 387                                 disposed = true;
 388                         }
 389                 }
 390
 391                 public SqlCommand GetDeleteCommand ()
 392                 {
 393                         BuildCache (true);
 394                         return CreateDeleteCommand (null, null);
 395                 }
 396
 397                 public SqlCommand GetInsertCommand ()
 398                 {
 399                         BuildCache (true);
 400                         return CreateInsertCommand (null, null);
 401                 }
 402
 403                 private string GetQuotedString (string value)
 404                 {
 405                         if (value == String.Empty || value == null)
 406                                 return value;
 407                         if (quotePrefix == String.Empty && quoteSuffix == String.Empty)
 408                                 return value;
 409                         return String.Format ("{0}{1}{2}", quotePrefix, value, quoteSuffix);
 410                 }
 411
 412                 public SqlCommand GetUpdateCommand ()
 413                 {
 414                         BuildCache (true);
 415                         return CreateUpdateCommand (null, null);
 416                 }
 417
 418                 private bool IncludedInInsert (DataRow schemaRow)
 419                 {
 420                         // If the parameter has one of these properties, then we don't include it in the insert:
 421                         // AutoIncrement, Hidden, Expression, RowVersion, ReadOnly
 422
 423                         if (!schemaRow.IsNull ("IsAutoIncrement") && (bool) schemaRow ["IsAutoIncrement"])
 424                                 return false;
 425                         if (!schemaRow.IsNull ("IsHidden") && (bool) schemaRow ["IsHidden"])
 426                                 return false;
 427                         if (!schemaRow.IsNull ("IsExpression") && (bool) schemaRow ["IsExpression"])
 428                                 return false;
 429                         if (!schemaRow.IsNull ("IsRowVersion") && (bool) schemaRow ["IsRowVersion"])
 430                                 return false;
 431                         if (!schemaRow.IsNull ("IsReadOnly") && (bool) schemaRow ["IsReadOnly"])
 432                                 return false;
 433                         return true;
 434                 }
 435
 436                 private bool IncludedInUpdate (DataRow schemaRow)
 437                 {
 438                         // If the parameter has one of these properties, then we don't include it in the insert:
 439                         // AutoIncrement, Hidden, RowVersion
 440
 441                         if ((bool) schemaRow ["IsAutoIncrement"])
 442                                 return false;
 443                         if ((bool) schemaRow ["IsHidden"])
 444                                 return false;
 445                         if ((bool) schemaRow ["IsRowVersion"])
 446                                 return false;
 447                         return true;
 448                 }
 449
 450                 private bool IncludedInWhereClause (DataRow schemaRow)
 451                 {
 452                         if ((bool) schemaRow ["IsLong"])
 453                                 return false;
 454                         return true;
 455                 }
 456
 457                 [MonoTODO ("Figure out what else needs to be cleaned up when we refresh.")]
 458                 public void RefreshSchema ()
 459                 {
 460                         tableName = String.Empty;
 461                         dbSchemaTable = null;
 462                 }
 463
 464                 #endregion // Methods
 465
 466                 #region Event Handlers
 467
 468                 private void RowUpdatingHandler (object sender, SqlRowUpdatingEventArgs e)
 469                 {
 470                         if (e.Status != UpdateStatus.Continue)
 471                                 return;
 472
 473                         switch (e.StatementType) {
 474                         case StatementType.Delete:
 475                                 deleteCommand = e.Command;
 476                                 break;
 477                         case StatementType.Insert:
 478                                 insertCommand = e.Command;
 479                                 break;
 480                         case StatementType.Update:
 481                                 updateCommand = e.Command;
 482                                 break;
 483                         default:
 484                                 return;
 485                         }
 486
 487                         try {
 488                                 BuildCache (false);
 489
 490                                 switch (e.StatementType) {
 491                                 case StatementType.Delete:
 492                                         e.Command = CreateDeleteCommand (e.Row, e.TableMapping);
 493                                         e.Status = UpdateStatus.Continue;
 494                                         break;
 495                                 case StatementType.Insert:
 496                                         e.Command = CreateInsertCommand (e.Row, e.TableMapping);
 497                                         e.Status = UpdateStatus.Continue;
 498                                         break;
 499                                 case StatementType.Update:
 500                                         e.Command = CreateUpdateCommand (e.Row, e.TableMapping);
 501                                         e.Status = UpdateStatus.Continue;
 502                                         break;
 503                                 }
 504
 505                                 if (e.Command != null && e.Row != null) {
 506                                         e.Row.AcceptChanges ();
 507                                         e.Status = UpdateStatus.SkipCurrentRow;
 508                                 }
 509                         }
 510                         catch (Exception exception) {
 511                                 e.Errors = exception;
 512                                 e.Status = UpdateStatus.ErrorsOccurred;
 513                         }
 514                 }
 515
 516                 #endregion // Event Handlers
 517         }
 518 }
 519