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

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