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                 public
 336 #if NET_2_0
 337                 new
 338 #endif // NET_2_0
 339                 OdbcCommand GetUpdateCommand ()
 340                 {
 341                         // FIXME: check validity of adapter
 342                         if (_updateCommand != null)
 343                                 return _updateCommand;
 344
 345                         if (_schema == null)
 346                                 RefreshSchema ();
 347
 348                         _updateCommand = new OdbcCommand ();
 349                         _updateCommand.Connection = DataAdapter.SelectCommand.Connection;
 350                         _updateCommand.Transaction = DataAdapter.SelectCommand.Transaction;
 351                         _updateCommand.CommandType = CommandType.Text;
 352                         _updateCommand.UpdatedRowSource = UpdateRowSource.None;
 353
 354                         string query = String.Format ("UPDATE {0} SET", QuoteIdentifier (TableName));
 355                         string [] setClause = new string [Schema.Rows.Count];
 356
 357                         int count = 0;
 358
 359                         foreach (DataRow schemaRow in Schema.Rows) {
 360
 361                                 // exclude non updatable columns
 362                                 if (! IsUpdatable (schemaRow))
 363                                         continue;
 364
 365                                 string columnName = GetColumnName (schemaRow);
 366                                 if (columnName == String.Empty)
 367                                         throw new InvalidOperationException ("Cannot form update command. Column name is missing!");
 368
 369                                 OdbcType sqlDbType = schemaRow.IsNull ("ProviderType") ? OdbcType.VarChar : (OdbcType) schemaRow ["ProviderType"];
 370                                 int length = schemaRow.IsNull ("ColumnSize") ? -1 : (int) schemaRow ["ColumnSize"];
 371
 372                                 // create column = value string
 373                                 setClause [count] = String.Format ("{0} = ?", QuoteIdentifier(columnName));
 374                                 AddParameter (_updateCommand, columnName, sqlDbType, length, columnName, DataRowVersion.Current);
 375                                 count++;
 376                         }
 377
 378                         // create where clause. odbc uses positional parameters. so where class
 379                         // is created seperate from the above loop.
 380                         string whereClause = CreateOptWhereClause (_updateCommand);
 381
 382                         query = String.Format ("{0} {1} WHERE ({2})",
 383                                                query,
 384                                                String.Join (", ", setClause, 0, count),
 385                                                whereClause);
 386                         _updateCommand.CommandText = query;
 387                         return _updateCommand;
 388                 }
 389
 390                 public
 391 #if NET_2_0
 392                 new
 393 #endif // NET_2_0
 394                 OdbcCommand GetDeleteCommand ()
 395                 {
 396                         // FIXME: check validity of adapter
 397                         if (_deleteCommand != null)
 398                                 return _deleteCommand;
 399
 400                         if (_schema == null)
 401                                 RefreshSchema ();
 402
 403                         _deleteCommand = new OdbcCommand ();
 404                         _deleteCommand.Connection = DataAdapter.SelectCommand.Connection;
 405                         _deleteCommand.Transaction = DataAdapter.SelectCommand.Transaction;
 406                         _deleteCommand.CommandType = CommandType.Text;
 407                         _deleteCommand.UpdatedRowSource = UpdateRowSource.None;
 408
 409                         string query = String.Format ("DELETE FROM {0}", QuoteIdentifier (TableName));
 410                         string whereClause = CreateOptWhereClause (_deleteCommand);
 411
 412                         query = String.Format ("{0} WHERE ({1})", query, whereClause);
 413                         _deleteCommand.CommandText = query;
 414                         return _deleteCommand;
 415                 }
 416
 417                 public
 418 #if NET_2_0
 419                 override
 420 #endif // NET_2_0
 421                 void RefreshSchema ()
 422                 {
 423                         // creates metadata
 424                         if (SelectCommand == null)
 425                                 throw new InvalidOperationException ("SelectCommand should be valid");
 426                         if (SelectCommand.Connection == null)
 427                                 throw new InvalidOperationException ("SelectCommand's Connection should be valid");
 428
 429                         CommandBehavior behavior = CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo;
 430                         if (SelectCommand.Connection.State != ConnectionState.Open) {
 431                                 SelectCommand.Connection.Open ();
 432                                 behavior |= CommandBehavior.CloseConnection;
 433                         }
 434
 435                         OdbcDataReader reader = SelectCommand.ExecuteReader (behavior);
 436                         _schema = reader.GetSchemaTable ();
 437                         reader.Close ();
 438
 439                         // force creation of commands
 440                         _insertCommand  = null;
 441                         _updateCommand  = null;
 442                         _deleteCommand  = null;
 443                         _tableName      = String.Empty;
 444                 }
 445
 446 #if NET_2_0
 447                 [MonoTODO]
 448                 protected override void ApplyParameterInfo (DbParameter dbParameter,
 449                                                             DataRow row,
 450                                                             StatementType statementType,
 451                                                             bool whereClause)
 452                 {
 453                         throw new NotImplementedException ();
 454                 }
 455
 456                 [MonoTODO]
 457                 protected override string GetParameterName (int position)
 458                 {
 459                         throw new NotImplementedException ();
 460                 }
 461
 462                 [MonoTODO]
 463                 protected override string GetParameterName (string parameterName)
 464                 {
 465                         throw new NotImplementedException ();
 466                 }
 467
 468                 [MonoTODO]
 469                 protected override string GetParameterPlaceholder (int position)
 470                 {
 471                         throw new NotImplementedException ();
 472                 }
 473
 474                 [MonoTODO]
 475                 protected override void SetRowUpdatingHandler (DbDataAdapter adapter)
 476                 {
 477                         throw new NotImplementedException ();
 478                 }
 479
 480 #endif // NET_2_0
 481
 482 #if NET_2_0
 483                 [MonoTODO]
 484                 public override
 485 #else
 486                 private
 487 #endif
 488                 string QuoteIdentifier (string unquotedIdentifier)
 489                 {
 490 #if NET_2_0
 491                         throw new NotImplementedException ();
 492 #else
 493                         if (unquotedIdentifier == null || unquotedIdentifier == String.Empty)
 494                                 return unquotedIdentifier;
 495                         return String.Format ("{0}{1}{2}", QuotePrefix,
 496                                               unquotedIdentifier, QuoteSuffix);
 497 #endif
 498                 }
 499
 500 #if NET_2_0
 501                 [MonoTODO]
 502                 public override
 503 #else
 504                 private
 505 #endif
 506                 string UnquoteIdentifier (string quotedIdentifier)
 507                 {
 508 #if NET_2_0
 509                         throw new NotImplementedException ();
 510 #else
 511                         if (quotedIdentifier == null || quotedIdentifier == String.Empty)
 512                                 return quotedIdentifier;
 513
 514                         StringBuilder sb = new StringBuilder (quotedIdentifier.Length);
 515                         sb.Append (quotedIdentifier);
 516                         if (quotedIdentifier.StartsWith (QuotePrefix))
 517                                 sb.Remove (0,QuotePrefix.Length);
 518                         if (quotedIdentifier.EndsWith (QuoteSuffix))
 519                                 sb.Remove (sb.Length - QuoteSuffix.Length, QuoteSuffix.Length );
 520                         return sb.ToString ();
 521 #endif
 522                 }
 523
 524                 private void OnRowUpdating (object sender, OdbcRowUpdatingEventArgs args)
 525                 {
 526                         if (args.Command != null)
 527                                 return;
 528                         try {
 529                                 switch (args.StatementType) {
 530                                 case StatementType.Insert:
 531                                         args.Command = GetInsertCommand ();
 532                                         break;
 533                                 case StatementType.Update:
 534                                         args.Command = GetUpdateCommand ();
 535                                         break;
 536                                 case StatementType.Delete:
 537                                         args.Command = GetDeleteCommand ();
 538                                         break;
 539                                 }
 540                         } catch (Exception e) {
 541                                 args.Errors = e;
 542                                 args.Status = UpdateStatus.ErrorsOccurred;
 543                         }
 544                 }
 545
 546
 547                 #endregion // Methods
 548         }
 549 }