mcs/class/ByteFX.Data/mysqlclient/CommandBuilder.cs

   1 // ByteFX.Data data access components for .Net
   2 // Copyright (C) 2002-2003  ByteFX, Inc.
   3 //
   4 // This library is free software; you can redistribute it and/or
   5 // modify it under the terms of the GNU Lesser General Public
   6 // License as published by the Free Software Foundation; either
   7 // version 2.1 of the License, or (at your option) any later version.
   8 //
   9 // This library is distributed in the hope that it will be useful,
  10 // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  12 // Lesser General Public License for more details.
  13 //
  14 // You should have received a copy of the GNU Lesser General Public
  15 // License along with this library; if not, write to the Free Software
  16 // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
  17
  18 using System;
  19 using System.ComponentModel;
  20 using System.Data;
  21 using System.Text;
  22
  23 namespace ByteFX.Data.MySqlClient
  24 {
  25         /// <summary>
  26         /// Automatically generates single-table commands used to reconcile changes made to a DataSet with the associated MySQL database. This class cannot be inherited.
  27         /// </summary>
  28         /// <include file='docs/MySqlCommandBuilder.xml' path='MyDocs/MyMembers[@name="Class"]/*'/>
  29         [ToolboxItem(false)]
  30         [System.ComponentModel.DesignerCategory("Code")]
  31         public sealed class MySqlCommandBuilder : Component
  32         {
  33                 private MySqlDataAdapter        _adapter;
  34                 private string                          _QuotePrefix;
  35                 private string                          _QuoteSuffix;
  36                 private DataTable                       _schema;
  37                 private string                          _tableName;
  38
  39                 private MySqlCommand            _updateCmd;
  40                 private MySqlCommand            _insertCmd;
  41                 private MySqlCommand            _deleteCmd;
  42
  43                 #region Constructors
  44                 /// <summary>
  45                 /// Overloaded. Initializes a new instance of the SqlCommandBuilder class.
  46                 /// </summary>
  47                 public MySqlCommandBuilder()
  48                 {
  49                 }
  50
  51                 /// <summary>
  52                 /// Overloaded. Initializes a new instance of the SqlCommandBuilder class.
  53                 /// </summary>
  54                 public MySqlCommandBuilder( MySqlDataAdapter adapter )
  55                 {
  56                         _adapter = adapter;
  57                         _adapter.RowUpdating += new MySqlRowUpdatingEventHandler( OnRowUpdating );
  58                 }
  59                 #endregion
  60
  61                 #region Properties
  62
  63                 /// <summary>
  64                 /// Gets or sets a MySqlDataAdapter object for which SQL statements are automatically generated.
  65                 /// </summary>
  66                 public MySqlDataAdapter DataAdapter
  67                 {
  68                         get { return _adapter; }
  69                         set
  70                         {
  71                                 if (_adapter != null)
  72                                 {
  73                                         _adapter.RowUpdating -= new MySqlRowUpdatingEventHandler( OnRowUpdating );
  74                                 }
  75                                 _adapter = value;
  76                         }
  77                 }
  78
  79                 /// <summary>
  80                 /// Gets or sets the beginning character or characters to use when specifying MySql database objects (for example, tables or columns) whose names contain characters such as spaces or reserved tokens.
  81                 /// </summary>
  82                 public string QuotePrefix
  83                 {
  84                         get { return _QuotePrefix; }
  85                         set { _QuotePrefix = value; }
  86                 }
  87
  88                 /// <summary>
  89                 /// Gets or sets the ending character or characters to use when specifying MySql database objects (for example, tables or columns) whose names contain characters such as spaces or reserved tokens.
  90                 /// </summary>
  91                 public string QuoteSuffix
  92                 {
  93                         get { return _QuoteSuffix; }
  94                         set { _QuoteSuffix = value; }
  95                 }
  96
  97                 #endregion
  98
  99                 #region Public Methods
 100                 /// <summary>
 101                 /// Retrieves parameter information from the stored procedure specified in the MySqlCommand and populates the Parameters collection of the specified MySqlCommand object.
 102                 /// This method is not currently supported since stored procedures are not available in MySql.
 103                 /// </summary>
 104                 /// <param name="command">The MySqlCommand referencing the stored procedure from which the parameter information is to be derived. The derived parameters are added to the Parameters collection of the MySqlCommand.</param>
 105                 /// <exception cref="InvalidOperationException">The command text is not a valid stored procedure name.</exception>
 106                 public static void DeriveParameters(MySqlCommand command)
 107                 {
 108                         throw new MySqlException("DeriveParameters is not supported (due to MySql not supporting SP)");
 109                 }
 110
 111                 /// <summary>
 112                 /// Gets the automatically generated MySqlCommand object required to perform deletions on the database.
 113                 /// </summary>
 114                 /// <returns></returns>
 115                 public MySqlCommand GetDeleteCommand()
 116                 {
 117                         if (_schema == null)
 118                                 GenerateSchema();
 119                         return CreateDeleteCommand();
 120                 }
 121
 122                 /// <summary>
 123                 /// Gets the automatically generated MySqlCommand object required to perform insertions on the database.
 124                 /// </summary>
 125                 /// <returns></returns>
 126                 public MySqlCommand GetInsertCommand()
 127                 {
 128                         if (_schema == null)
 129                                 GenerateSchema();
 130                         return CreateInsertCommand();
 131                 }
 132
 133                 /// <summary>
 134                 /// Gets the automatically generated MySqlCommand object required to perform updates on the database.
 135                 /// </summary>
 136                 /// <returns></returns>
 137                 public MySqlCommand GetUpdateCommand()
 138                 {
 139                         if (_schema == null)
 140                                 GenerateSchema();
 141                         return CreateUpdateCommand();
 142                 }
 143
 144                 /// <summary>
 145                 /// Refreshes the database schema information used to generate INSERT, UPDATE, or DELETE statements.
 146                 /// </summary>
 147                 public void RefreshSchema()
 148                 {
 149                         _schema = null;
 150                         _insertCmd = null;
 151                         _deleteCmd = null;
 152                         _updateCmd = null;
 153                 }
 154                 #endregion
 155
 156                 #region Private Methods
 157
 158                 private void GenerateSchema()
 159                 {
 160                         if (_adapter == null)
 161                                 throw new MySqlException("Improper MySqlCommandBuilder state: adapter is null");
 162                         if (_adapter.SelectCommand == null)
 163                                 throw new MySqlException("Improper MySqlCommandBuilder state: adapter's SelectCommand is null");
 164
 165                         MySqlDataReader dr = _adapter.SelectCommand.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
 166                         _schema = dr.GetSchemaTable();
 167                         dr.Close();
 168
 169                         // make sure we got at least one unique or key field and count base table names
 170                         bool   hasKeyOrUnique=false;
 171
 172                         foreach (DataRow row in _schema.Rows)
 173                         {
 174                                 if (true == (bool)row["IsKey"] || true == (bool)row["IsUnique"])
 175                                         hasKeyOrUnique=true;
 176                                 if (_tableName == null)
 177                                         _tableName = (string)row["BaseTableName"];
 178                                 else if (_tableName != (string)row["BaseTableName"])
 179                                         throw new InvalidOperationException("MySqlCommandBuilder does not support multi-table statements");
 180                         }
 181                         if (! hasKeyOrUnique)
 182                                 throw new InvalidOperationException("MySqlCommandBuilder cannot operate on tables with no unique or key columns");
 183                 }
 184
 185                 private string Quote(string table_or_column)
 186                 {
 187                         if (_QuotePrefix == null || _QuoteSuffix == null)
 188                                 return table_or_column;
 189                         return _QuotePrefix + table_or_column + _QuoteSuffix;
 190                 }
 191
 192                 private MySqlParameter CreateParameter(DataRow row, bool Original)
 193                 {
 194                         MySqlParameter p;
 195                         if (Original)
 196                                 p = new MySqlParameter( "@Original_" + (string)row["ColumnName"], (MySqlDbType)row["ProviderType"],
 197                                         ParameterDirection.Input, (string)row["ColumnName"], DataRowVersion.Original, DBNull.Value );
 198                         else
 199                                 p = new MySqlParameter( "@" + (string)row["ColumnName"], (MySqlDbType)row["ProviderType"],
 200                                         ParameterDirection.Input, (string)row["ColumnName"], DataRowVersion.Current, DBNull.Value );
 201                         return p;
 202                 }
 203
 204                 private MySqlCommand CreateBaseCommand()
 205                 {
 206                         MySqlCommand cmd = new MySqlCommand();
 207                         cmd.Connection = _adapter.SelectCommand.Connection;
 208                         cmd.CommandTimeout = _adapter.SelectCommand.CommandTimeout;
 209                         cmd.Transaction = _adapter.SelectCommand.Transaction;
 210                         return cmd;
 211                 }
 212
 213                 private MySqlCommand CreateDeleteCommand()
 214                 {
 215                         if (_deleteCmd != null) return _deleteCmd;
 216
 217                         MySqlCommand cmd = CreateBaseCommand();
 218
 219                         cmd.CommandText = "DELETE FROM " + Quote(_tableName) +
 220                                 " WHERE " + CreateOriginalWhere(cmd);
 221
 222                         _deleteCmd = cmd;
 223                         return cmd;
 224                 }
 225
 226                 private string CreateFinalSelect(bool forinsert)
 227                 {
 228                         StringBuilder sel = new StringBuilder();
 229                         StringBuilder where = new StringBuilder();
 230
 231                         foreach (DataRow row in _schema.Rows)
 232                         {
 233                                 string colname = (string)row["ColumnName"];
 234                                 if (sel.Length > 0)
 235                                         sel.Append(", ");
 236                                 sel.Append( colname );
 237                                 if ((bool)row["IsKey"] == false) continue;
 238                                 if (where.Length > 0)
 239                                         where.Append(" AND ");
 240                                 where.Append( "(" + colname + "=" );
 241                                 if (forinsert)
 242                                 {
 243                                         if ((bool)row["IsAutoIncrement"])
 244                                                 where.Append("last_insert_id()");
 245                                         else if ((bool)row["IsKey"])
 246                                                 where.Append("@" + colname);
 247                                 }
 248                                 else
 249                                 {
 250                                         where.Append("@Original_" + colname);
 251                                 }
 252                                 where.Append(")");
 253                         }
 254                         return "SELECT " + sel.ToString() + " FROM " + Quote(_tableName) +
 255                                    " WHERE " + where.ToString();
 256                 }
 257
 258                 private string CreateOriginalWhere(MySqlCommand cmd)
 259                 {
 260                         StringBuilder wherestr = new StringBuilder();
 261
 262                         foreach (DataRow row in _schema.Rows)
 263                         {
 264                                 if (! IncludedInWhereClause(row)) continue;
 265
 266                                 // first update the where clause since it will contain all parameters
 267                                 if (wherestr.Length > 0)
 268                                         wherestr.Append(" AND ");
 269                                 string colname = Quote((string)row["ColumnName"]);
 270
 271                                 MySqlParameter op = CreateParameter(row, true);
 272                                 cmd.Parameters.Add(op);
 273
 274                                 wherestr.Append( "(" + colname + "=@" + op.ParameterName);
 275                                 if ((bool)row["AllowDBNull"] == true)
 276                                         wherestr.Append( " or (" + colname + " IS NULL and @" + op.ParameterName + " IS NULL)");
 277                                 wherestr.Append(")");
 278                         }
 279                         return wherestr.ToString();
 280                 }
 281
 282                 private MySqlCommand CreateUpdateCommand()
 283                 {
 284                         if (_updateCmd != null) return _updateCmd;
 285
 286                         MySqlCommand cmd = CreateBaseCommand();
 287
 288                         StringBuilder setstr = new StringBuilder();
 289
 290                         foreach (DataRow schemaRow in _schema.Rows)
 291                         {
 292                                 string colname = Quote((string)schemaRow["ColumnName"]);
 293
 294                                 if (! IncludedInUpdate(schemaRow)) continue;
 295
 296                                 if (setstr.Length > 0)
 297                                         setstr.Append(", ");
 298
 299                                 MySqlParameter p = CreateParameter(schemaRow, false);
 300                                 cmd.Parameters.Add(p);
 301
 302                                 setstr.Append( colname + "=@" + p.ParameterName );
 303                         }
 304
 305                         cmd.CommandText = "UPDATE " + Quote(_tableName) + " SET " + setstr.ToString() +
 306                                                           " WHERE " + CreateOriginalWhere(cmd);
 307                         cmd.CommandText += "; " + CreateFinalSelect(false);
 308
 309                         _updateCmd = cmd;
 310                         return cmd;
 311                 }
 312
 313                 private MySqlCommand CreateInsertCommand()
 314                 {
 315                         if (_insertCmd != null) return _insertCmd;
 316
 317                         MySqlCommand cmd = CreateBaseCommand();
 318
 319                         StringBuilder setstr = new StringBuilder();
 320                         StringBuilder valstr = new StringBuilder();
 321                         foreach (DataRow schemaRow in _schema.Rows)
 322                         {
 323                                 string colname = Quote((string)schemaRow["ColumnName"]);
 324
 325                                 if (!IncludedInInsert(schemaRow)) continue;
 326
 327                                 if (setstr.Length > 0)
 328                                 {
 329                                         setstr.Append(", ");
 330                                         valstr.Append(", ");
 331                                 }
 332
 333                                 MySqlParameter p = CreateParameter(schemaRow, false);
 334                                 cmd.Parameters.Add(p);
 335
 336                                 setstr.Append( colname );
 337                                 valstr.Append( "@" + p.ParameterName );
 338                         }
 339
 340                         cmd.CommandText = "INSERT INTO " + Quote(_tableName) + " (" + setstr.ToString() + ") " +
 341                                 " VALUES (" + valstr.ToString() + ")";
 342                         cmd.CommandText += "; " + CreateFinalSelect(true);
 343
 344                         _insertCmd = cmd;
 345                         return cmd;
 346                 }
 347
 348                 private bool IncludedInInsert (DataRow schemaRow)
 349                 {
 350                         // If the parameter has one of these properties, then we don't include it in the insert:
 351                         // AutoIncrement, Hidden, Expression, RowVersion, ReadOnly
 352
 353                         if ((bool) schemaRow ["IsAutoIncrement"])
 354                                 return false;
 355 /*                      if ((bool) schemaRow ["IsHidden"])
 356                                 return false;
 357                         if ((bool) schemaRow ["IsExpression"])
 358                                 return false;*/
 359                         if ((bool) schemaRow ["IsRowVersion"])
 360                                 return false;
 361                         if ((bool) schemaRow ["IsReadOnly"])
 362                                 return false;
 363                         return true;
 364                 }
 365
 366                 private bool IncludedInUpdate (DataRow schemaRow)
 367                 {
 368                         // If the parameter has one of these properties, then we don't include it in the insert:
 369                         // AutoIncrement, Hidden, RowVersion
 370
 371                         if ((bool) schemaRow ["IsAutoIncrement"])
 372                                 return false;
 373 //                      if ((bool) schemaRow ["IsHidden"])
 374 //                              return false;
 375                         if ((bool) schemaRow ["IsRowVersion"])
 376                                 return false;
 377                         return true;
 378                 }
 379
 380                 private bool IncludedInWhereClause (DataRow schemaRow)
 381                 {
 382 //                      if ((bool) schemaRow ["IsLong"])
 383 //                              return false;
 384                         return true;
 385                 }
 386
 387                 private void SetParameterValues(MySqlCommand cmd, DataRow dataRow)
 388                 {
 389                         foreach (MySqlParameter p in cmd.Parameters)
 390                         {
 391                                 if (p.ParameterName.Length >= 8 && p.ParameterName.Substring(0, 8).Equals("Original"))
 392                                         p.Value = dataRow[ p.SourceColumn, DataRowVersion.Original ];
 393                                 else
 394                                         p.Value = dataRow[ p.SourceColumn, DataRowVersion.Current ];
 395                         }
 396                 }
 397
 398                 private void OnRowUpdating(object sender, MySqlRowUpdatingEventArgs args)
 399                 {
 400                         // make sure we are still to proceed
 401                         if (args.Status != UpdateStatus.Continue) return;
 402
 403                         if (_schema == null)
 404                                 GenerateSchema();
 405
 406                         if (StatementType.Delete == args.StatementType)
 407                                 args.Command = CreateDeleteCommand();
 408                         else if (StatementType.Update == args.StatementType)
 409                                 args.Command = CreateUpdateCommand();
 410                         else if (StatementType.Insert == args.StatementType)
 411                                 args.Command = CreateInsertCommand();
 412                         else if (StatementType.Select == args.StatementType)
 413                                 return;
 414
 415                         SetParameterValues(args.Command, args.Row);
 416                 }
 417                 #endregion
 418
 419         }
 420 }