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         /// Summary description for CommandBuilder.
  27         /// </summary>
  28         [ToolboxItem(false)]
  29         [System.ComponentModel.DesignerCategory("Code")]
  30         public sealed class MySqlCommandBuilder : Component
  31         {
  32                 private MySqlDataAdapter        _adapter;
  33                 private string                          _QuotePrefix;
  34                 private string                          _QuoteSuffix;
  35                 private DataTable                       _schema;
  36                 private string                          _tableName;
  37
  38                 private MySqlCommand            _updateCmd;
  39                 private MySqlCommand            _insertCmd;
  40                 private MySqlCommand            _deleteCmd;
  41
  42                 #region Constructors
  43                 public MySqlCommandBuilder()
  44                 {
  45                 }
  46
  47                 public MySqlCommandBuilder( MySqlDataAdapter adapter )
  48                 {
  49                         _adapter = adapter;
  50                         _adapter.RowUpdating += new MySqlRowUpdatingEventHandler( OnRowUpdating );
  51                 }
  52                 #endregion
  53
  54                 #region Properties
  55
  56                 /// <summary>
  57                 /// Gets or sets a MySqlDataAdapter object for which SQL statements are automatically generated.
  58                 /// </summary>
  59                 public MySqlDataAdapter DataAdapter
  60                 {
  61                         get { return _adapter; }
  62                         set
  63                         {
  64                                 if (_adapter != null)
  65                                 {
  66                                         _adapter.RowUpdating -= new MySqlRowUpdatingEventHandler( OnRowUpdating );
  67                                 }
  68                                 _adapter = value;
  69                         }
  70                 }
  71
  72                 /// <summary>
  73                 /// 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.
  74                 /// </summary>
  75                 public string QuotePrefix
  76                 {
  77                         get { return _QuotePrefix; }
  78                         set { _QuotePrefix = value; }
  79                 }
  80
  81                 /// <summary>
  82                 /// 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.
  83                 /// </summary>
  84                 public string QuoteSuffix
  85                 {
  86                         get { return _QuoteSuffix; }
  87                         set { _QuoteSuffix = value; }
  88                 }
  89
  90                 #endregion
  91
  92                 #region Public Methods
  93                 public static void DeriveParameters(MySqlCommand command)
  94                 {
  95                         throw new MySqlException("DeriveParameters is not supported (due to MySql not supporting SP)");
  96                 }
  97
  98                 /// <summary>
  99                 /// Gets the automatically generated MySqlCommand object required to perform deletions on the database.
 100                 /// </summary>
 101                 /// <returns></returns>
 102                 public MySqlCommand GetDeleteCommand()
 103                 {
 104                         if (_schema == null)
 105                                 GenerateSchema();
 106                         return CreateDeleteCommand();
 107                 }
 108
 109                 /// <summary>
 110                 /// Gets the automatically generated MySqlCommand object required to perform insertions on the database.
 111                 /// </summary>
 112                 /// <returns></returns>
 113                 public MySqlCommand GetInsertCommand()
 114                 {
 115                         if (_schema == null)
 116                                 GenerateSchema();
 117                         return CreateInsertCommand();
 118                 }
 119
 120                 /// <summary>
 121                 /// Gets the automatically generated MySqlCommand object required to perform updates on the database.
 122                 /// </summary>
 123                 /// <returns></returns>
 124                 public MySqlCommand GetUpdateCommand()
 125                 {
 126                         if (_schema == null)
 127                                 GenerateSchema();
 128                         return CreateUpdateCommand();
 129                 }
 130
 131                 /// <summary>
 132                 /// Refreshes the database schema information used to generate INSERT, UPDATE, or DELETE statements.
 133                 /// </summary>
 134                 public void RefreshSchema()
 135                 {
 136                         _schema = null;
 137                         _insertCmd = null;
 138                         _deleteCmd = null;
 139                         _updateCmd = null;
 140                 }
 141                 #endregion
 142
 143                 #region Private Methods
 144
 145                 private void GenerateSchema()
 146                 {
 147                         if (_adapter == null)
 148                                 throw new MySqlException("Improper MySqlCommandBuilder state: adapter is null");
 149                         if (_adapter.SelectCommand == null)
 150                                 throw new MySqlException("Improper MySqlCommandBuilder state: adapter's SelectCommand is null");
 151
 152                         MySqlDataReader dr = _adapter.SelectCommand.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
 153                         _schema = dr.GetSchemaTable();
 154                         dr.Close();
 155
 156                         // make sure we got at least one unique or key field and count base table names
 157                         bool   hasKeyOrUnique=false;
 158
 159                         foreach (DataRow row in _schema.Rows)
 160                         {
 161                                 if (true == (bool)row["IsKey"] || true == (bool)row["IsUnique"])
 162                                         hasKeyOrUnique=true;
 163                                 if (_tableName == null)
 164                                         _tableName = (string)row["BaseTableName"];
 165                                 else if (_tableName != (string)row["BaseTableName"])
 166                                         throw new InvalidOperationException("MySqlCommandBuilder does not support multi-table statements");
 167                         }
 168                         if (! hasKeyOrUnique)
 169                                 throw new InvalidOperationException("MySqlCommandBuilder cannot operate on tables with no unique or key columns");
 170                 }
 171
 172                 private string Quote(string table_or_column)
 173                 {
 174                         if (_QuotePrefix == null || _QuoteSuffix == null)
 175                                 return table_or_column;
 176                         return _QuotePrefix + table_or_column + _QuoteSuffix;
 177                 }
 178
 179                 private MySqlParameter CreateParameter(DataRow row, bool Original)
 180                 {
 181                         MySqlParameter p;
 182                         if (Original)
 183                                 p = new MySqlParameter( "@Original_" + (string)row["ColumnName"], (MySqlDbType)row["ProviderType"],
 184                                         ParameterDirection.Input, (string)row["ColumnName"], DataRowVersion.Original, DBNull.Value );
 185                         else
 186                                 p = new MySqlParameter( "@" + (string)row["ColumnName"], (MySqlDbType)row["ProviderType"],
 187                                         ParameterDirection.Input, (string)row["ColumnName"], DataRowVersion.Current, DBNull.Value );
 188                         return p;
 189                 }
 190
 191                 private MySqlCommand CreateBaseCommand()
 192                 {
 193                         MySqlCommand cmd = new MySqlCommand();
 194                         cmd.Connection = _adapter.SelectCommand.Connection;
 195                         cmd.CommandTimeout = _adapter.SelectCommand.CommandTimeout;
 196                         cmd.Transaction = _adapter.SelectCommand.Transaction;
 197                         return cmd;
 198                 }
 199
 200                 private MySqlCommand CreateDeleteCommand()
 201                 {
 202                         if (_deleteCmd != null) return _deleteCmd;
 203
 204                         MySqlCommand cmd = CreateBaseCommand();
 205
 206                         cmd.CommandText = "DELETE FROM " + Quote(_tableName) +
 207                                 " WHERE " + CreateOriginalWhere(cmd);
 208
 209                         _deleteCmd = cmd;
 210                         return cmd;
 211                 }
 212
 213                 private string CreateFinalSelect(bool forinsert)
 214                 {
 215                         StringBuilder sel = new StringBuilder();
 216                         StringBuilder where = new StringBuilder();
 217
 218                         foreach (DataRow row in _schema.Rows)
 219                         {
 220                                 string colname = (string)row["ColumnName"];
 221                                 if (sel.Length > 0)
 222                                         sel.Append(", ");
 223                                 sel.Append( colname );
 224                                 if ((bool)row["IsKey"] == false) continue;
 225                                 if (where.Length > 0)
 226                                         where.Append(" AND ");
 227                                 where.Append( "(" + colname + "=" );
 228                                 if (forinsert)
 229                                 {
 230                                         if ((bool)row["IsAutoIncrement"])
 231                                                 where.Append("last_insert_id()");
 232                                         else if ((bool)row["IsKey"])
 233                                                 where.Append("@" + colname);
 234                                 }
 235                                 else
 236                                 {
 237                                         where.Append("@Original_" + colname);
 238                                 }
 239                                 where.Append(")");
 240                         }
 241                         return "SELECT " + sel.ToString() + " FROM " + Quote(_tableName) +
 242                                    " WHERE " + where.ToString();
 243                 }
 244
 245                 private string CreateOriginalWhere(MySqlCommand cmd)
 246                 {
 247                         StringBuilder wherestr = new StringBuilder();
 248
 249                         foreach (DataRow row in _schema.Rows)
 250                         {
 251                                 if (! IncludedInWhereClause(row)) continue;
 252
 253                                 // first update the where clause since it will contain all parameters
 254                                 if (wherestr.Length > 0)
 255                                         wherestr.Append(" AND ");
 256                                 string colname = Quote((string)row["ColumnName"]);
 257
 258                                 MySqlParameter op = CreateParameter(row, true);
 259                                 cmd.Parameters.Add(op);
 260
 261                                 wherestr.Append( "(" + colname + "=@" + op.ParameterName);
 262                                 if ((bool)row["AllowDBNull"] == true)
 263                                         wherestr.Append( " or (" + colname + " IS NULL and @" + op.ParameterName + " IS NULL)");
 264                                 wherestr.Append(")");
 265                         }
 266                         return wherestr.ToString();
 267                 }
 268
 269                 private MySqlCommand CreateUpdateCommand()
 270                 {
 271                         if (_updateCmd != null) return _updateCmd;
 272
 273                         MySqlCommand cmd = CreateBaseCommand();
 274
 275                         StringBuilder setstr = new StringBuilder();
 276
 277                         foreach (DataRow schemaRow in _schema.Rows)
 278                         {
 279                                 string colname = Quote((string)schemaRow["ColumnName"]);
 280
 281                                 if (! IncludedInUpdate(schemaRow)) continue;
 282
 283                                 if (setstr.Length > 0)
 284                                         setstr.Append(", ");
 285
 286                                 MySqlParameter p = CreateParameter(schemaRow, false);
 287                                 cmd.Parameters.Add(p);
 288
 289                                 setstr.Append( colname + "=@" + p.ParameterName );
 290                         }
 291
 292                         cmd.CommandText = "UPDATE " + Quote(_tableName) + " SET " + setstr.ToString() +
 293                                                           " WHERE " + CreateOriginalWhere(cmd);
 294                         cmd.CommandText += "; " + CreateFinalSelect(false);
 295
 296                         _updateCmd = cmd;
 297                         return cmd;
 298                 }
 299
 300                 private MySqlCommand CreateInsertCommand()
 301                 {
 302                         if (_insertCmd != null) return _insertCmd;
 303
 304                         MySqlCommand cmd = CreateBaseCommand();
 305
 306                         StringBuilder setstr = new StringBuilder();
 307                         StringBuilder valstr = new StringBuilder();
 308                         foreach (DataRow schemaRow in _schema.Rows)
 309                         {
 310                                 string colname = Quote((string)schemaRow["ColumnName"]);
 311
 312                                 if (!IncludedInInsert(schemaRow)) continue;
 313
 314                                 if (setstr.Length > 0)
 315                                 {
 316                                         setstr.Append(", ");
 317                                         valstr.Append(", ");
 318                                 }
 319
 320                                 MySqlParameter p = CreateParameter(schemaRow, false);
 321                                 cmd.Parameters.Add(p);
 322
 323                                 setstr.Append( colname );
 324                                 valstr.Append( "@" + p.ParameterName );
 325                         }
 326
 327                         cmd.CommandText = "INSERT INTO " + Quote(_tableName) + " (" + setstr.ToString() + ") " +
 328                                 " VALUES (" + valstr.ToString() + ")";
 329                         cmd.CommandText += "; " + CreateFinalSelect(true);
 330
 331                         _insertCmd = cmd;
 332                         return cmd;
 333                 }
 334
 335                 private bool IncludedInInsert (DataRow schemaRow)
 336                 {
 337                         // If the parameter has one of these properties, then we don't include it in the insert:
 338                         // AutoIncrement, Hidden, Expression, RowVersion, ReadOnly
 339
 340                         if ((bool) schemaRow ["IsAutoIncrement"])
 341                                 return false;
 342 /*                      if ((bool) schemaRow ["IsHidden"])
 343                                 return false;
 344                         if ((bool) schemaRow ["IsExpression"])
 345                                 return false;*/
 346                         if ((bool) schemaRow ["IsRowVersion"])
 347                                 return false;
 348                         if ((bool) schemaRow ["IsReadOnly"])
 349                                 return false;
 350                         return true;
 351                 }
 352
 353                 private bool IncludedInUpdate (DataRow schemaRow)
 354                 {
 355                         // If the parameter has one of these properties, then we don't include it in the insert:
 356                         // AutoIncrement, Hidden, RowVersion
 357
 358                         if ((bool) schemaRow ["IsAutoIncrement"])
 359                                 return false;
 360 //                      if ((bool) schemaRow ["IsHidden"])
 361 //                              return false;
 362                         if ((bool) schemaRow ["IsRowVersion"])
 363                                 return false;
 364                         return true;
 365                 }
 366
 367                 private bool IncludedInWhereClause (DataRow schemaRow)
 368                 {
 369 //                      if ((bool) schemaRow ["IsLong"])
 370 //                              return false;
 371                         return true;
 372                 }
 373
 374                 private void SetParameterValues(MySqlCommand cmd, DataRow dataRow)
 375                 {
 376                         foreach (MySqlParameter p in cmd.Parameters)
 377                         {
 378                                 if (p.ParameterName.Length >= 8 && p.ParameterName.Substring(0, 8).Equals("Original"))
 379                                         p.Value = dataRow[ p.SourceColumn, DataRowVersion.Original ];
 380                                 else
 381                                         p.Value = dataRow[ p.SourceColumn, DataRowVersion.Current ];
 382                         }
 383                 }
 384
 385                 private void OnRowUpdating(object sender, MySqlRowUpdatingEventArgs args)
 386                 {
 387                         // make sure we are still to proceed
 388                         if (args.Status != UpdateStatus.Continue) return;
 389
 390                         if (_schema == null)
 391                                 GenerateSchema();
 392
 393                         if (StatementType.Delete == args.StatementType)
 394                                 args.Command = CreateDeleteCommand();
 395                         else if (StatementType.Update == args.StatementType)
 396                                 args.Command = CreateUpdateCommand();
 397                         else if (StatementType.Insert == args.StatementType)
 398                                 args.Command = CreateInsertCommand();
 399                         else if (StatementType.Select == args.StatementType)
 400                                 return;
 401
 402                         SetParameterValues(args.Command, args.Row);
 403                 }
 404                 #endregion
 405
 406         }
 407 }