mcs/class/System.Data.OracleClient/System.Data.OracleClient/OracleCommandBuilder.cs

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