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

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