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                 #endregion // Fields
  66
  67                 #region Constructors
  68
  69                 public OdbcCommandBuilder ()
  70                 {
  71                         _adapter = null;
  72                         _quotePrefix = String.Empty;
  73                         _quoteSuffix = String.Empty;
  74                 }
  75
  76                 public OdbcCommandBuilder (OdbcDataAdapter adapter)
  77                         : this ()
  78                 {
  79                         DataAdapter = adapter;
  80                 }
  81
  82                 #endregion // Constructors
  83
  84                 #region Properties
  85
  86                 [OdbcDescriptionAttribute ("The DataAdapter for which to automatically generate OdbcCommands")]
  87                 [DefaultValue (null)]
  88                 public
  89 #if NET_2_0
  90                 new
  91 #endif // NET_2_0
  92                 OdbcDataAdapter DataAdapter {
  93                         get {
  94                                 return _adapter;
  95                         }
  96                         set {
  97                                 if (_adapter == value)
  98                                         return;
  99
 100                                 if (_adapter != null)
 101                                         _adapter.RowUpdating -= new OdbcRowUpdatingEventHandler (OnRowUpdating);
 102                                 _adapter = value;
 103                                 if (_adapter != null)
 104                                         _adapter.RowUpdating += new OdbcRowUpdatingEventHandler (OnRowUpdating);
 105
 106                         }
 107                 }
 108
 109                 private OdbcCommand SelectCommand
 110                 {
 111                         get {
 112                                 if (DataAdapter == null)
 113                                         return null;
 114                                 return DataAdapter.SelectCommand;
 115                         }
 116                 }
 117
 118                 private DataTable Schema
 119                 {
 120                         get {
 121                                 if (_schema == null)
 122                                         RefreshSchema ();
 123                                 return _schema;
 124                         }
 125                 }
 126
 127                 private string TableName
 128                 {
 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                 public
 152 #if NET_2_0
 153                 override
 154 #endif // NET_2_0
 155                 string QuotePrefix {
 156                         get {
 157                                 return _quotePrefix;
 158                         }
 159                         set {
 160                                 _quotePrefix = value;
 161                         }
 162                 }
 163
 164                 [BrowsableAttribute (false)]
 165                 [OdbcDescriptionAttribute ("The suffix string wrapped around sql objects")]
 166                 [DesignerSerializationVisibilityAttribute (DesignerSerializationVisibility.Hidden)]
 167                 public
 168 #if NET_2_0
 169                 override
 170 #endif // NET_2_0
 171                 string QuoteSuffix {
 172                         get {
 173                                 return _quoteSuffix;
 174                         }
 175                         set {
 176                                 _quoteSuffix = value;
 177                         }
 178                 }
 179
 180                 #endregion // Properties
 181
 182                 #region Methods
 183
 184                 [MonoTODO]
 185                 public static void DeriveParameters (OdbcCommand command)
 186                 {
 187                         throw new NotImplementedException ();
 188                 }
 189
 190                 protected override void Dispose (bool disposing)
 191                 {
 192                         if (_disposed)
 193                                 return;
 194
 195                         if (disposing) {
 196                                 // dispose managed resource
 197                                 if (_insertCommand != null) _insertCommand.Dispose ();
 198                                 if (_updateCommand != null) _updateCommand.Dispose ();
 199                                 if (_deleteCommand != null) _deleteCommand.Dispose ();
 200                                 if (_schema != null) _insertCommand.Dispose ();
 201
 202                                 _insertCommand = null;
 203                                 _updateCommand = null;
 204                                 _deleteCommand = null;
 205                                 _schema = null;
 206                         }
 207                         _disposed = true;
 208                 }
 209
 210                 private bool IsUpdatable (DataRow schemaRow)
 211                 {
 212                         if ( (! schemaRow.IsNull ("IsAutoIncrement") && (bool) schemaRow ["IsAutoIncrement"])
 213                              || (! schemaRow.IsNull ("IsHidden") && (bool) schemaRow ["IsHidden"])
 214                              || (! schemaRow.IsNull ("IsExpression") && (bool) schemaRow ["IsExpression"])
 215                              || (! schemaRow.IsNull ("IsRowVersion") && (bool) schemaRow ["IsRowVersion"])
 216                              || (! schemaRow.IsNull ("IsReadOnly") && (bool) schemaRow ["IsReadOnly"])
 217                              )
 218                                 return false;
 219                         return true;
 220                 }
 221
 222                 private string GetColumnName (DataRow schemaRow)
 223                 {
 224                         string columnName = schemaRow.IsNull ("BaseColumnName") ? String.Empty : (string) schemaRow ["BaseColumnName"];
 225                         if (columnName == String.Empty)
 226                                 columnName = schemaRow.IsNull ("ColumnName") ? String.Empty : (string) schemaRow ["ColumnName"];
 227                         return columnName;
 228                 }
 229
 230                 private OdbcParameter AddParameter (OdbcCommand cmd, string paramName, OdbcType odbcType,
 231                                                     int length, string sourceColumnName, DataRowVersion rowVersion)
 232                 {
 233                         OdbcParameter param;
 234                         if (length >= 0 && sourceColumnName != String.Empty)
 235                                 param = cmd.Parameters.Add (paramName, odbcType, length, sourceColumnName);
 236                         else
 237                                 param = cmd.Parameters.Add (paramName, odbcType);
 238                         param.SourceVersion = rowVersion;
 239                         return param;
 240                 }
 241
 242                 /*
 243                  * creates where clause for optimistic concurrency
 244                  */
 245                 private string CreateOptWhereClause (OdbcCommand command)
 246                 {
 247                         string [] whereClause = new string [Schema.Rows.Count];
 248
 249                         int count = 0;
 250
 251                         foreach (DataRow schemaRow in Schema.Rows) {
 252
 253                                 // exclude non updatable columns
 254                                 if (! IsUpdatable (schemaRow))
 255                                         continue;
 256
 257                                 string columnName = GetColumnName (schemaRow);
 258                                 if (columnName == String.Empty)
 259                                         throw new InvalidOperationException ("Cannot form delete command. Column name is missing!");
 260
 261                                 bool    allowNull       = schemaRow.IsNull ("AllowDBNull") || (bool) schemaRow ["AllowDBNull"];
 262                                 OdbcType sqlDbType      = schemaRow.IsNull ("ProviderType") ? OdbcType.VarChar : (OdbcType) schemaRow ["ProviderType"];
 263                                 int     length          = schemaRow.IsNull ("ColumnSize") ? -1 : (int) schemaRow ["ColumnSize"];
 264
 265                                 if (allowNull) {
 266                                         whereClause [count] = String.Format ("((? = 1 AND {0} IS NULL) OR ({0} = ?))",
 267                                                                               columnName);
 268                                         AddParameter (command, columnName, sqlDbType, length, columnName, DataRowVersion.Original);
 269                                         AddParameter (command, columnName, sqlDbType, length, columnName, DataRowVersion.Original);
 270                                 } else {
 271                                         whereClause [count] = String.Format ( "({0} = ?)", columnName);
 272                                         AddParameter (command, columnName, sqlDbType, length, columnName, DataRowVersion.Original);
 273                                 }
 274
 275                                 count++;
 276                         }
 277
 278                         return String.Join (" AND ", whereClause, 0, count);
 279                 }
 280
 281                 public
 282 #if NET_2_0
 283                 new
 284 #endif // NET_2_0
 285                 OdbcCommand GetInsertCommand ()
 286                 {
 287                         // FIXME: check validity of adapter
 288                         if (_insertCommand != null)
 289                                 return _insertCommand;
 290
 291                         if (_schema == null)
 292                                 RefreshSchema ();
 293
 294                         _insertCommand = new OdbcCommand ();
 295                         _insertCommand.Connection = DataAdapter.SelectCommand.Connection;
 296                         _insertCommand.Transaction = DataAdapter.SelectCommand.Transaction;
 297                         _insertCommand.CommandType = CommandType.Text;
 298                         _insertCommand.UpdatedRowSource = UpdateRowSource.None;
 299
 300                         string query = String.Format ("INSERT INTO {0}", QuoteIdentifier (TableName));
 301                         string [] columns = new string [Schema.Rows.Count];
 302                         string [] values  = new string [Schema.Rows.Count];
 303
 304                         int count = 0;
 305
 306                         foreach (DataRow schemaRow in Schema.Rows) {
 307
 308                                 // exclude non updatable columns
 309                                 if (! IsUpdatable (schemaRow))
 310                                         continue;
 311
 312                                 string columnName = GetColumnName (schemaRow);
 313                                 if (columnName == String.Empty)
 314                                         throw new InvalidOperationException ("Cannot form insert command. Column name is missing!");
 315
 316                                 // create column string & value string
 317                                 columns [count] = QuoteIdentifier(columnName);
 318                                 values [count++] = "?";
 319
 320                                 // create parameter and add
 321                                 OdbcType sqlDbType = schemaRow.IsNull ("ProviderType") ? OdbcType.VarChar : (OdbcType) schemaRow ["ProviderType"];
 322                                 int length = schemaRow.IsNull ("ColumnSize") ? -1 : (int) schemaRow ["ColumnSize"];
 323
 324                                 AddParameter (_insertCommand, columnName, sqlDbType, length, columnName, DataRowVersion.Current);
 325                         }
 326
 327                         query = String.Format ("{0} ({1}) VALUES ({2})",
 328                                                query,
 329                                                String.Join (", ", columns, 0, count),
 330                                                String.Join (", ", values, 0, count) );
 331                         _insertCommand.CommandText = query;
 332                         return _insertCommand;
 333                 }
 334
 335 #if NET_2_0
 336                 [MonoTODO]
 337                 public new OdbcCommand GetInsertCommand (bool option)
 338                 {
 339                         // FIXME: check validity of adapter
 340                         if (_insertCommand != null)
 341                                 return _insertCommand;
 342
 343                         if (_schema == null)
 344                                 RefreshSchema ();
 345
 346                         if (option == false) {
 347                                 return GetInsertCommand ();
 348                         } else {
 349                                 throw new NotImplementedException ();
 350                         }
 351                 }
 352 #endif // NET_2_0
 353
 354                 public
 355 #if NET_2_0
 356                 new
 357 #endif // NET_2_0
 358                 OdbcCommand GetUpdateCommand ()
 359                 {
 360                         // FIXME: check validity of adapter
 361                         if (_updateCommand != null)
 362                                 return _updateCommand;
 363
 364                         if (_schema == null)
 365                                 RefreshSchema ();
 366
 367                         _updateCommand = new OdbcCommand ();
 368                         _updateCommand.Connection = DataAdapter.SelectCommand.Connection;
 369                         _updateCommand.Transaction = DataAdapter.SelectCommand.Transaction;
 370                         _updateCommand.CommandType = CommandType.Text;
 371                         _updateCommand.UpdatedRowSource = UpdateRowSource.None;
 372
 373                         string query = String.Format ("UPDATE {0} SET", QuoteIdentifier (TableName));
 374                         string [] setClause = new string [Schema.Rows.Count];
 375
 376                         int count = 0;
 377
 378                         foreach (DataRow schemaRow in Schema.Rows) {
 379
 380                                 // exclude non updatable columns
 381                                 if (! IsUpdatable (schemaRow))
 382                                         continue;
 383
 384                                 string columnName = GetColumnName (schemaRow);
 385                                 if (columnName == String.Empty)
 386                                         throw new InvalidOperationException ("Cannot form update command. Column name is missing!");
 387
 388                                 OdbcType sqlDbType = schemaRow.IsNull ("ProviderType") ? OdbcType.VarChar : (OdbcType) schemaRow ["ProviderType"];
 389                                 int length = schemaRow.IsNull ("ColumnSize") ? -1 : (int) schemaRow ["ColumnSize"];
 390
 391                                 // create column = value string
 392                                 setClause [count] = String.Format ("{0} = ?", QuoteIdentifier(columnName));
 393                                 AddParameter (_updateCommand, columnName, sqlDbType, length, columnName, DataRowVersion.Current);
 394                                 count++;
 395                         }
 396
 397                         // create where clause. odbc uses positional parameters. so where class
 398                         // is created seperate from the above loop.
 399                         string whereClause = CreateOptWhereClause (_updateCommand);
 400
 401                         query = String.Format ("{0} {1} WHERE ({2})",
 402                                                query,
 403                                                String.Join (", ", setClause, 0, count),
 404                                                whereClause);
 405                         _updateCommand.CommandText = query;
 406                         return _updateCommand;
 407                 }
 408
 409 #if NET_2_0
 410                 [MonoTODO]
 411                 public new OdbcCommand GetUpdateCommand (bool option)
 412                 {
 413                         // FIXME: check validity of adapter
 414                         if (_updateCommand != null)
 415                                 return _updateCommand;
 416
 417                         if (_schema == null)
 418                                 RefreshSchema ();
 419
 420                         if (option == false) {
 421                                 return GetUpdateCommand ();
 422                         } else {
 423                                 throw new NotImplementedException ();
 424                         }
 425                 }
 426 #endif // NET_2_0
 427
 428                 public
 429 #if NET_2_0
 430                 new
 431 #endif // NET_2_0
 432                 OdbcCommand GetDeleteCommand ()
 433                 {
 434                         // FIXME: check validity of adapter
 435                         if (_deleteCommand != null)
 436                                 return _deleteCommand;
 437
 438                         if (_schema == null)
 439                                 RefreshSchema ();
 440
 441                         _deleteCommand = new OdbcCommand ();
 442                         _deleteCommand.Connection = DataAdapter.SelectCommand.Connection;
 443                         _deleteCommand.Transaction = DataAdapter.SelectCommand.Transaction;
 444                         _deleteCommand.CommandType = CommandType.Text;
 445                         _deleteCommand.UpdatedRowSource = UpdateRowSource.None;
 446
 447                         string query = String.Format ("DELETE FROM {0}", QuoteIdentifier (TableName));
 448                         string whereClause = CreateOptWhereClause (_deleteCommand);
 449
 450                         query = String.Format ("{0} WHERE ({1})", query, whereClause);
 451                         _deleteCommand.CommandText = query;
 452                         return _deleteCommand;
 453                 }
 454
 455 #if NET_2_0
 456                 [MonoTODO]
 457                 public new OdbcCommand GetDeleteCommand (bool option)
 458                 {
 459                         // FIXME: check validity of adapter
 460                         if (_deleteCommand != null)
 461                                 return _deleteCommand;
 462
 463                         if (_schema == null)
 464                                 RefreshSchema ();
 465
 466                         if (option == false) {
 467                                 return GetDeleteCommand ();
 468                         } else {
 469                                 throw new NotImplementedException ();
 470                         }
 471                 }
 472 #endif // NET_2_0
 473
 474                 public
 475 #if NET_2_0
 476                 override
 477 #endif // NET_2_0
 478                 void RefreshSchema ()
 479                 {
 480                         // creates metadata
 481                         if (SelectCommand == null)
 482                                 throw new InvalidOperationException ("SelectCommand should be valid");
 483                         if (SelectCommand.Connection == null)
 484                                 throw new InvalidOperationException ("SelectCommand's Connection should be valid");
 485
 486                         CommandBehavior behavior = CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo;
 487                         if (SelectCommand.Connection.State != ConnectionState.Open) {
 488                                 SelectCommand.Connection.Open ();
 489                                 behavior |= CommandBehavior.CloseConnection;
 490                         }
 491
 492                         OdbcDataReader reader = SelectCommand.ExecuteReader (behavior);
 493                         _schema = reader.GetSchemaTable ();
 494                         reader.Close ();
 495
 496                         // force creation of commands
 497                         _insertCommand  = null;
 498                         _updateCommand  = null;
 499                         _deleteCommand  = null;
 500                         _tableName      = String.Empty;
 501                 }
 502
 503 #if NET_2_0
 504                 [MonoTODO]
 505                 protected override void ApplyParameterInfo (DbParameter dbParameter,
 506                                                             DataRow row,
 507                                                             StatementType statementType,
 508                                                             bool whereClause)
 509                 {
 510                         throw new NotImplementedException ();
 511                 }
 512
 513                 [MonoTODO]
 514                 protected override string GetParameterName (int position)
 515                 {
 516                         throw new NotImplementedException ();
 517                 }
 518
 519                 [MonoTODO]
 520                 protected override string GetParameterName (string parameterName)
 521                 {
 522                         throw new NotImplementedException ();
 523                 }
 524
 525                 [MonoTODO]
 526                 protected override string GetParameterPlaceholder (int position)
 527                 {
 528                         throw new NotImplementedException ();
 529                 }
 530
 531                 [MonoTODO]
 532                 protected override void SetRowUpdatingHandler (DbDataAdapter adapter)
 533                 {
 534                         throw new NotImplementedException ();
 535                 }
 536
 537 #endif // NET_2_0
 538
 539 #if NET_2_0
 540                 [MonoTODO]
 541                 public override
 542 #else
 543                 private
 544 #endif
 545                 string QuoteIdentifier (string unquotedIdentifier)
 546                 {
 547                   /*
 548 #if NET_2_0
 549                         throw new NotImplementedException ();
 550 #else
 551                   */
 552                         if (unquotedIdentifier == null || unquotedIdentifier == String.Empty)
 553                                 return unquotedIdentifier;
 554                         return String.Format ("{0}{1}{2}", QuotePrefix,
 555                                               unquotedIdentifier, QuoteSuffix);
 556                         /*
 557 #endif
 558                         */
 559                 }
 560
 561 #if NET_2_0
 562                 [MonoTODO]
 563                 public override
 564 #else
 565                 private
 566 #endif
 567                 string UnquoteIdentifier (string quotedIdentifier)
 568                 {
 569 #if NET_2_0
 570                         throw new NotImplementedException ();
 571 #else
 572                         if (quotedIdentifier == null || quotedIdentifier == String.Empty)
 573                                 return quotedIdentifier;
 574
 575                         StringBuilder sb = new StringBuilder (quotedIdentifier.Length);
 576                         sb.Append (quotedIdentifier);
 577                         if (quotedIdentifier.StartsWith (QuotePrefix))
 578                                 sb.Remove (0,QuotePrefix.Length);
 579                         if (quotedIdentifier.EndsWith (QuoteSuffix))
 580                                 sb.Remove (sb.Length - QuoteSuffix.Length, QuoteSuffix.Length );
 581                         return sb.ToString ();
 582 #endif
 583                 }
 584
 585                 private void OnRowUpdating (object sender, OdbcRowUpdatingEventArgs args)
 586                 {
 587                         if (args.Command != null)
 588                                 return;
 589                         try {
 590                                 switch (args.StatementType) {
 591                                 case StatementType.Insert:
 592                                         args.Command = GetInsertCommand ();
 593                                         break;
 594                                 case StatementType.Update:
 595                                         args.Command = GetUpdateCommand ();
 596                                         break;
 597                                 case StatementType.Delete:
 598                                         args.Command = GetDeleteCommand ();
 599                                         break;
 600                                 }
 601                         } catch (Exception e) {
 602                                 args.Errors = e;
 603                                 args.Status = UpdateStatus.ErrorsOccurred;
 604                         }
 605                 }
 606
 607
 608                 #endregion // Methods
 609         }
 610 }