mcs/class/System.Data/System.Data.Odbc/OdbcCommandBuilder.cs

   1 //
   2 // System.Data.Odbc.OdbcCommandBuilder
   3 //
   4 // Author:
   5 //   Umadevi S (sumadevi@novell.com)
   6 //   Sureshkumar T (tsureshkumar@novell.com)
   7 //
   8 // Copyright (C) Novell Inc, 2004
   9 //
  10
  11 //
  12 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
  13 //
  14 // Permission is hereby granted, free of charge, to any person obtaining
  15 // a copy of this software and associated documentation files (the
  16 // "Software"), to deal in the Software without restriction, including
  17 // without limitation the rights to use, copy, modify, merge, publish,
  18 // distribute, sublicense, and/or sell copies of the Software, and to
  19 // permit persons to whom the Software is furnished to do so, subject to
  20 // the following conditions:
  21 //
  22 // The above copyright notice and this permission notice shall be
  23 // included in all copies or substantial portions of the Software.
  24 //
  25 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  26 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  27 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  28 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  29 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  30 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  31 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  32 //
  33
  34 using System.Text;
  35 using System.Data;
  36 using System.Data.Common;
  37 using System.ComponentModel;
  38
  39 namespace System.Data.Odbc
  40 {
  41         /// <summary>
  42         /// Provides a means of automatically generating single-table commands used to reconcile changes made to a DataSet with the associated database. This class cannot be inherited.
  43         /// </summary>
  44
  45 #if NET_2_0
  46         public sealed class OdbcCommandBuilder : DbCommandBuilder
  47 #else // 1_1
  48         public sealed class OdbcCommandBuilder : Component
  49 #endif // NET_2_0
  50         {
  51                 #region Fields
  52
  53                 private OdbcDataAdapter _adapter;
  54                 private string                  _quotePrefix;
  55                 private string                  _quoteSuffix;
  56
  57                 private DataTable               _schema;
  58                 private string                  _tableName;
  59                 private OdbcCommand             _insertCommand;
  60                 private OdbcCommand             _updateCommand;
  61                 private OdbcCommand             _deleteCommand;
  62
  63                 bool _disposed;
  64
  65                 private OdbcRowUpdatingEventHandler rowUpdatingHandler;
  66
  67                 #endregion // Fields
  68
  69                 #region Constructors
  70
  71                 public OdbcCommandBuilder ()
  72                 {
  73                         _quotePrefix = string.Empty;
  74                         _quoteSuffix = string.Empty;
  75                 }
  76
  77                 public OdbcCommandBuilder (OdbcDataAdapter adapter)
  78                         : this ()
  79                 {
  80                         DataAdapter = adapter;
  81                 }
  82
  83                 #endregion // Constructors
  84
  85                 #region Properties
  86
  87                 [OdbcDescriptionAttribute ("The DataAdapter for which to automatically generate OdbcCommands")]
  88                 [DefaultValue (null)]
  89                 public
  90 #if NET_2_0
  91                 new
  92 #endif // NET_2_0
  93                 OdbcDataAdapter DataAdapter {
  94                         get {
  95                                 return _adapter;
  96                         }
  97                         set {
  98                                 if (_adapter == value)
  99                                         return;
 100
 101                                 if (rowUpdatingHandler != null)
 102                                         rowUpdatingHandler = new OdbcRowUpdatingEventHandler (OnRowUpdating);
 103
 104                                 if (_adapter != null)
 105                                         _adapter.RowUpdating -= rowUpdatingHandler;
 106                                 _adapter = value;
 107                                 if (_adapter != null)
 108                                         _adapter.RowUpdating += rowUpdatingHandler;
 109                         }
 110                 }
 111
 112                 private OdbcCommand SelectCommand {
 113                         get {
 114                                 if (DataAdapter == null)
 115                                         return null;
 116                                 return DataAdapter.SelectCommand;
 117                         }
 118                 }
 119
 120                 private DataTable Schema {
 121                         get {
 122                                 if (_schema == null)
 123                                         RefreshSchema ();
 124                                 return _schema;
 125                         }
 126                 }
 127
 128                 private string TableName {
 129                         get {
 130                                 if (_tableName != string.Empty)
 131                                         return _tableName;
 132
 133                                 DataRow [] schemaRows = Schema.Select ("BaseTableName is not null and BaseTableName <> ''");
 134                                 if (schemaRows.Length > 1) {
 135                                         string tableName = (string) schemaRows [0] ["BaseTableName"];
 136                                         foreach (DataRow schemaRow in schemaRows) {
 137                                                 if ( (string) schemaRow ["BaseTableName"] != tableName)
 138                                                         throw new InvalidOperationException ("Dynamic SQL generation is not supported against multiple base tables.");
 139                                         }
 140                                 }
 141                                 if (schemaRows.Length == 0)
 142                                         throw new InvalidOperationException ("Cannot determine the base table name. Cannot proceed");
 143                                 _tableName = schemaRows [0] ["BaseTableName"].ToString ();
 144                                 return _tableName;
 145                         }
 146                 }
 147
 148                 [BrowsableAttribute (false)]
 149                 [OdbcDescriptionAttribute ("The prefix string wrapped around sql objects")]
 150                 [DesignerSerializationVisibilityAttribute (DesignerSerializationVisibility.Hidden)]
 151 #if ONLY_1_1
 152                 public
 153 #else
 154                 new
 155 #endif
 156                 string QuotePrefix {
 157                         get {
 158                                 return _quotePrefix;
 159                         }
 160                         set {
 161                                 _quotePrefix = value;
 162                         }
 163                 }
 164
 165                 [BrowsableAttribute (false)]
 166                 [OdbcDescriptionAttribute ("The suffix string wrapped around sql objects")]
 167                 [DesignerSerializationVisibilityAttribute (DesignerSerializationVisibility.Hidden)]
 168 #if ONLY_1_1
 169                 public
 170 #else
 171                 new
 172 #endif // NET_2_0
 173                 string QuoteSuffix {
 174                         get {
 175                                 return _quoteSuffix;
 176                         }
 177                         set {
 178                                 _quoteSuffix = value;
 179                         }
 180                 }
 181
 182                 #endregion // Properties
 183
 184                 #region Methods
 185
 186                 [MonoTODO]
 187                 public static void DeriveParameters (OdbcCommand command)
 188                 {
 189                         throw new NotImplementedException ();
 190                 }
 191
 192 #if ONLY_1_1
 193                 protected override
 194 #else
 195                 new
 196 #endif
 197                 void Dispose (bool disposing)
 198                 {
 199                         if (_disposed)
 200                                 return;
 201
 202                         if (disposing) {
 203                                 // dispose managed resource
 204                                 if (_insertCommand != null)
 205                                         _insertCommand.Dispose ();
 206                                 if (_updateCommand != null)
 207                                         _updateCommand.Dispose ();
 208                                 if (_deleteCommand != null)
 209                                         _deleteCommand.Dispose ();
 210                                 if (_schema != null)
 211                                         _insertCommand.Dispose ();
 212
 213                                 _insertCommand = null;
 214                                 _updateCommand = null;
 215                                 _deleteCommand = null;
 216                                 _schema = null;
 217                         }
 218                         _disposed = true;
 219                 }
 220
 221                 private bool IsUpdatable (DataRow schemaRow)
 222                 {
 223                         if ( (! schemaRow.IsNull ("IsAutoIncrement") && (bool) schemaRow ["IsAutoIncrement"])
 224                              || (! schemaRow.IsNull ("IsHidden") && (bool) schemaRow ["IsHidden"])
 225                              || (! schemaRow.IsNull ("IsExpression") && (bool) schemaRow ["IsExpression"])
 226                              || (! schemaRow.IsNull ("IsRowVersion") && (bool) schemaRow ["IsRowVersion"])
 227                              || (! schemaRow.IsNull ("IsReadOnly") && (bool) schemaRow ["IsReadOnly"])
 228                              )
 229                                 return false;
 230                         return true;
 231                 }
 232
 233                 private string GetColumnName (DataRow schemaRow)
 234                 {
 235                         string columnName = schemaRow.IsNull ("BaseColumnName") ? String.Empty : (string) schemaRow ["BaseColumnName"];
 236                         if (columnName == String.Empty)
 237                                 columnName = schemaRow.IsNull ("ColumnName") ? String.Empty : (string) schemaRow ["ColumnName"];
 238                         return columnName;
 239                 }
 240
 241                 private OdbcParameter AddParameter (OdbcCommand cmd, string paramName, OdbcType odbcType,
 242                                                     int length, string sourceColumnName, DataRowVersion rowVersion)
 243                 {
 244                         OdbcParameter param;
 245                         if (length >= 0 && sourceColumnName != String.Empty)
 246                                 param = cmd.Parameters.Add (paramName, odbcType, length, sourceColumnName);
 247                         else
 248                                 param = cmd.Parameters.Add (paramName, odbcType);
 249                         param.SourceVersion = rowVersion;
 250                         return param;
 251                 }
 252
 253                 /*
 254                  * creates where clause for optimistic concurrency
 255                  */
 256                 private string CreateOptWhereClause (OdbcCommand command, bool option)
 257                 {
 258                         string [] whereClause = new string [Schema.Rows.Count];
 259
 260                         int count = 0;
 261
 262                         foreach (DataRow schemaRow in Schema.Rows) {
 263                                 // exclude non updatable columns
 264                                 if (! IsUpdatable (schemaRow))
 265                                         continue;
 266
 267                                 string columnName = null;
 268                                 if (option)
 269                                         columnName = GetColumnName (schemaRow);
 270                                 else
 271                                         columnName = String.Format ("@p{0}", count);
 272
 273                                 if (columnName == String.Empty)
 274                                         throw new InvalidOperationException ("Cannot form delete command. Column name is missing!");
 275
 276                                 bool    allowNull  = schemaRow.IsNull ("AllowDBNull") || (bool) schemaRow ["AllowDBNull"];
 277                                 OdbcType sqlDbType = schemaRow.IsNull ("ProviderType") ? OdbcType.VarChar : (OdbcType) schemaRow ["ProviderType"];
 278                                 int     length     = schemaRow.IsNull ("ColumnSize") ? -1 : (int) schemaRow ["ColumnSize"];
 279
 280                                 if (allowNull) {
 281                                         whereClause [count] = String.Format ("((? = 1 AND {0} IS NULL) OR ({0} = ?))",
 282                                                                               columnName);
 283                                         AddParameter (command, columnName, sqlDbType, length, columnName, DataRowVersion.Original);
 284                                         AddParameter (command, columnName, sqlDbType, length, columnName, DataRowVersion.Original);
 285                                 } else {
 286                                         whereClause [count] = String.Format ( "({0} = ?)", columnName);
 287                                         AddParameter (command, columnName, sqlDbType, length, columnName, DataRowVersion.Original);
 288                                 }
 289
 290                                 count++;
 291                         }
 292
 293                         return String.Join (" AND ", whereClause, 0, count);
 294                 }
 295
 296                 private void CreateNewCommand (ref OdbcCommand command)
 297                 {
 298                         OdbcCommand sourceCommand = SelectCommand;
 299                         if (command == null) {
 300                                 command = new OdbcCommand ();
 301                                 command.Connection = sourceCommand.Connection;
 302                                 command.CommandTimeout = sourceCommand.CommandTimeout;
 303                                 command.Transaction = sourceCommand.Transaction;
 304                         }
 305                         command.CommandType = CommandType.Text;
 306                         command.UpdatedRowSource = UpdateRowSource.None;
 307                         command.Parameters.Clear ();
 308                 }
 309
 310                 private OdbcCommand CreateInsertCommand (bool option)
 311                 {
 312                         CreateNewCommand (ref _insertCommand);
 313
 314                         string query = String.Format ("INSERT INTO {0}", QuoteIdentifier (TableName));
 315                         string [] columns = new string [Schema.Rows.Count];
 316                         string [] values  = new string [Schema.Rows.Count];
 317
 318                         int count = 0;
 319
 320                         foreach (DataRow schemaRow in Schema.Rows) {
 321                                 // exclude non updatable columns
 322                                 if (! IsUpdatable (schemaRow))
 323                                         continue;
 324
 325                                 string columnName = null;
 326
 327                                 if (option)
 328                                         columnName = GetColumnName (schemaRow);
 329                                 else
 330                                         columnName = String.Format ("@p{0}", count);
 331
 332                                 if (columnName == String.Empty)
 333                                         throw new InvalidOperationException ("Cannot form insert command. Column name is missing!");
 334
 335                                 // create column string & value string
 336                                 columns [count] = QuoteIdentifier(columnName);
 337                                 values [count++] = "?";
 338
 339                                 // create parameter and add
 340                                 OdbcType sqlDbType = schemaRow.IsNull ("ProviderType") ? OdbcType.VarChar : (OdbcType) schemaRow ["ProviderType"];
 341                                 int length = schemaRow.IsNull ("ColumnSize") ? -1 : (int) schemaRow ["ColumnSize"];
 342
 343                                 AddParameter (_insertCommand, columnName, sqlDbType, length, columnName, DataRowVersion.Current);
 344                         }
 345
 346                         query = String.Format ("{0} ({1}) VALUES ({2})",
 347                                                query,
 348                                                String.Join (", ", columns, 0, count),
 349                                                String.Join (", ", values, 0, count) );
 350                         _insertCommand.CommandText = query;
 351                         return _insertCommand;
 352                 }
 353
 354                 public
 355 #if NET_2_0
 356                 new
 357 #endif // NET_2_0
 358                 OdbcCommand GetInsertCommand ()
 359                 {
 360                         // FIXME: check validity of adapter
 361                         if (_insertCommand != null)
 362                                 return _insertCommand;
 363
 364                         if (_schema == null)
 365                                 RefreshSchema ();
 366
 367                         return CreateInsertCommand (false);
 368                 }
 369
 370 #if NET_2_0
 371                 public new OdbcCommand GetInsertCommand (bool useColumnsForParameterNames)
 372                 {
 373                         // FIXME: check validity of adapter
 374                         if (_insertCommand != null)
 375                                 return _insertCommand;
 376
 377                         if (_schema == null)
 378                                 RefreshSchema ();
 379
 380                         return CreateInsertCommand (useColumnsForParameterNames);
 381                 }
 382 #endif // NET_2_0
 383
 384                 private OdbcCommand CreateUpdateCommand (bool option)
 385                 {
 386                         CreateNewCommand (ref _updateCommand);
 387
 388                         string query = String.Format ("UPDATE {0} SET", QuoteIdentifier (TableName));
 389                         string [] setClause = new string [Schema.Rows.Count];
 390
 391                         int count = 0;
 392
 393                         foreach (DataRow schemaRow in Schema.Rows) {
 394                                 // exclude non updatable columns
 395                                 if (! IsUpdatable (schemaRow))
 396                                         continue;
 397
 398                                 string columnName = null;
 399                                 if (option)
 400                                         columnName = GetColumnName (schemaRow);
 401                                 else
 402                                         columnName = String.Format ("@p{0}", count);
 403
 404                                 if (columnName == String.Empty)
 405                                         throw new InvalidOperationException ("Cannot form update command. Column name is missing!");
 406
 407                                 OdbcType sqlDbType = schemaRow.IsNull ("ProviderType") ? OdbcType.VarChar : (OdbcType) schemaRow ["ProviderType"];
 408                                 int length = schemaRow.IsNull ("ColumnSize") ? -1 : (int) schemaRow ["ColumnSize"];
 409
 410                                 // create column = value string
 411                                 setClause [count] = String.Format ("{0} = ?", QuoteIdentifier(columnName));
 412                                 AddParameter (_updateCommand, columnName, sqlDbType, length, columnName, DataRowVersion.Current);
 413                                 count++;
 414                         }
 415
 416                         // create where clause. odbc uses positional parameters. so where class
 417                         // is created seperate from the above loop.
 418                         string whereClause = CreateOptWhereClause (_updateCommand, option);
 419
 420                         query = String.Format ("{0} {1} WHERE ({2})",
 421                                                query,
 422                                                String.Join (", ", setClause, 0, count),
 423                                                whereClause);
 424                         _updateCommand.CommandText = query;
 425                         return _updateCommand;
 426                 }
 427
 428                 public
 429 #if NET_2_0
 430                 new
 431 #endif // NET_2_0
 432                 OdbcCommand GetUpdateCommand ()
 433                 {
 434                         // FIXME: check validity of adapter
 435                         if (_updateCommand != null)
 436                                 return _updateCommand;
 437
 438                         if (_schema == null)
 439                                 RefreshSchema ();
 440
 441                         return CreateUpdateCommand (false);
 442                 }
 443
 444 #if NET_2_0
 445                 public new OdbcCommand GetUpdateCommand (bool useColumnsForParameterNames)
 446                 {
 447                         // FIXME: check validity of adapter
 448                         if (_updateCommand != null)
 449                                 return _updateCommand;
 450
 451                         if (_schema == null)
 452                                 RefreshSchema ();
 453
 454                         return CreateUpdateCommand (useColumnsForParameterNames);
 455                 }
 456 #endif // NET_2_0
 457
 458                 private OdbcCommand CreateDeleteCommand (bool option)
 459                 {
 460                         CreateNewCommand (ref _deleteCommand);
 461
 462                         string query = String.Format ("DELETE FROM {0}", QuoteIdentifier (TableName));
 463                         string whereClause = CreateOptWhereClause (_deleteCommand, option);
 464
 465                         query = String.Format ("{0} WHERE ({1})", query, whereClause);
 466                         _deleteCommand.CommandText = query;
 467                         return _deleteCommand;
 468                 }
 469
 470                 public
 471 #if NET_2_0
 472                 new
 473 #endif // NET_2_0
 474                 OdbcCommand GetDeleteCommand ()
 475                 {
 476                         // FIXME: check validity of adapter
 477                         if (_deleteCommand != null)
 478                                 return _deleteCommand;
 479
 480                         if (_schema == null)
 481                                 RefreshSchema ();
 482
 483                         return CreateDeleteCommand (false);
 484                 }
 485
 486 #if NET_2_0
 487                 public new OdbcCommand GetDeleteCommand (bool useColumnsForParameterNames)
 488                 {
 489                         // FIXME: check validity of adapter
 490                         if (_deleteCommand != null)
 491                                 return _deleteCommand;
 492
 493                         if (_schema == null)
 494                                 RefreshSchema ();
 495
 496                         return CreateDeleteCommand (useColumnsForParameterNames);
 497                 }
 498 #endif // NET_2_0
 499
 500 #if ONLY_1_1
 501                 public
 502 #else
 503                 new
 504 #endif // NET_2_0
 505                 void RefreshSchema ()
 506                 {
 507                         // creates metadata
 508                         if (SelectCommand == null)
 509                                 throw new InvalidOperationException ("SelectCommand should be valid");
 510                         if (SelectCommand.Connection == null)
 511                                 throw new InvalidOperationException ("SelectCommand's Connection should be valid");
 512
 513                         CommandBehavior behavior = CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo;
 514                         if (SelectCommand.Connection.State != ConnectionState.Open) {
 515                                 SelectCommand.Connection.Open ();
 516                                 behavior |= CommandBehavior.CloseConnection;
 517                         }
 518
 519                         OdbcDataReader reader = SelectCommand.ExecuteReader (behavior);
 520                         _schema = reader.GetSchemaTable ();
 521                         reader.Close ();
 522
 523                         // force creation of commands
 524                         _insertCommand  = null;
 525                         _updateCommand  = null;
 526                         _deleteCommand  = null;
 527                         _tableName      = String.Empty;
 528                 }
 529
 530 #if NET_2_0
 531                 protected override void ApplyParameterInfo (DbParameter parameter,
 532                                                             DataRow row,
 533                                                             StatementType statementType,
 534                                                             bool whereClause)
 535                 {
 536                         OdbcParameter odbcParam = (OdbcParameter) parameter;
 537                         odbcParam.Size = int.Parse (row ["ColumnSize"].ToString ());
 538                         if (row ["NumericPrecision"] != DBNull.Value)
 539                                 odbcParam.Precision = byte.Parse (row ["NumericPrecision"].ToString ());
 540                         if (row ["NumericScale"] != DBNull.Value)
 541                                 odbcParam.Scale = byte.Parse (row ["NumericScale"].ToString ());
 542                         odbcParam.DbType = (DbType) row ["ProviderType"];
 543                 }
 544
 545                 protected override string GetParameterName (int parameterOrdinal)
 546                 {
 547                         return String.Format("@p{0}", parameterOrdinal);
 548                 }
 549
 550                 protected override string GetParameterName (string parameterName)
 551                 {
 552                         return String.Format("@{0}", parameterName);
 553                 }
 554
 555                 protected override string GetParameterPlaceholder (int parameterOrdinal)
 556                 {
 557                         return GetParameterName (parameterOrdinal);
 558                 }
 559
 560                 // FIXME: According to MSDN - "if this method is called again with
 561                 // the same DbDataAdapter, the DbCommandBuilder is unregistered for
 562                 // that DbDataAdapter's RowUpdating event" - this behaviour is yet
 563                 // to be verified
 564                 protected override void SetRowUpdatingHandler (DbDataAdapter adapter)
 565                 {
 566                         if (!(adapter is OdbcDataAdapter))
 567                                 throw new InvalidOperationException ("Adapter needs to be a SqlDataAdapter");
 568                         if (rowUpdatingHandler == null)
 569                                 rowUpdatingHandler = new OdbcRowUpdatingEventHandler (OnRowUpdating);
 570
 571                         ((OdbcDataAdapter) adapter).RowUpdating += rowUpdatingHandler;
 572                 }
 573 #endif // NET_2_0
 574
 575 #if NET_2_0
 576                 public override
 577 #else
 578                 private
 579 #endif
 580                 string QuoteIdentifier (string unquotedIdentifier)
 581                 {
 582                         if (unquotedIdentifier == null || unquotedIdentifier.Length == 0)
 583                                 return unquotedIdentifier;
 584                         return String.Format ("{0}{1}{2}", QuotePrefix,
 585                                 unquotedIdentifier, QuoteSuffix);
 586                 }
 587
 588 #if NET_2_0
 589                 // FIXME:  Not sure what the extra "connection" param does!
 590                 public string QuoteIdentifier (string unquotedIdentifier, OdbcConnection connection)
 591                 {
 592                         return QuoteIdentifier (unquotedIdentifier);
 593                 }
 594
 595                 public string UnquoteIdentifier (string quotedIdentifier, OdbcConnection connection)
 596                 {
 597                         return UnquoteIdentifier (quotedIdentifier);
 598                 }
 599
 600                 public override string UnquoteIdentifier (string quotedIdentifier)
 601                 {
 602                         if (quotedIdentifier == null || quotedIdentifier.Length == 0)
 603                                 return quotedIdentifier;
 604
 605                         StringBuilder sb = new StringBuilder (quotedIdentifier.Length);
 606                         sb.Append (quotedIdentifier);
 607                         if (quotedIdentifier.StartsWith (QuotePrefix))
 608                                 sb.Remove (0,QuotePrefix.Length);
 609                         if (quotedIdentifier.EndsWith (QuoteSuffix))
 610                                 sb.Remove (sb.Length - QuoteSuffix.Length, QuoteSuffix.Length );
 611                         return sb.ToString ();
 612                 }
 613 #endif
 614
 615                 private void OnRowUpdating (object sender, OdbcRowUpdatingEventArgs args)
 616                 {
 617                         if (args.Command != null)
 618                                 return;
 619                         try {
 620                                 switch (args.StatementType) {
 621                                 case StatementType.Insert:
 622                                         args.Command = GetInsertCommand ();
 623                                         break;
 624                                 case StatementType.Update:
 625                                         args.Command = GetUpdateCommand ();
 626                                         break;
 627                                 case StatementType.Delete:
 628                                         args.Command = GetDeleteCommand ();
 629                                         break;
 630                                 }
 631                         } catch (Exception e) {
 632                                 args.Errors = e;
 633                                 args.Status = UpdateStatus.ErrorsOccurred;
 634                         }
 635                 }
 636
 637                 #endregion // Methods
 638         }
 639 }