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