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