mcs/class/ByteFX.Data/mysqlclient/command.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.Data;\r
  20 using System.ComponentModel;\r
  21 using System.Collections;\r
  22 \r
  23 namespace ByteFX.Data.MySQLClient\r
  24 {\r
  25 #if WINDOWS\r
  26         [System.Drawing.ToolboxBitmap( typeof(MySQLCommand), "Designers.command.bmp")]\r
  27 #endif\r
  28         public sealed class MySQLCommand : Component, IDbCommand, ICloneable\r
  29         {\r
  30                 MySQLConnection                         m_connection;\r
  31                 MySQLTransaction                        m_txn;\r
  32                 string                                          m_sCmdText;\r
  33                 int                                                     m_UpdateCount;\r
  34                 UpdateRowSource                         m_updatedRowSource = UpdateRowSource.Both;\r
  35                 MySQLParameterCollection        m_parameters = new MySQLParameterCollection();\r
  36 \r
  37                 // Implement the default constructor here.\r
  38                 public MySQLCommand()\r
  39                 {\r
  40                 }\r
  41 \r
  42                 // Implement other constructors here.\r
  43                 public MySQLCommand(string cmdText)\r
  44                 {\r
  45                         m_sCmdText = cmdText;\r
  46                 }\r
  47 \r
  48                 public MySQLCommand(System.ComponentModel.IContainer container)\r
  49                 {\r
  50                         /// <summary>\r
  51                         /// Required for Windows.Forms Class Composition Designer support\r
  52                         /// </summary>\r
  53                         container.Add(this);\r
  54                 }\r
  55 \r
  56                 public MySQLCommand(string cmdText, MySQLConnection connection)\r
  57                 {\r
  58                         m_sCmdText    = cmdText;\r
  59                         m_connection  = connection;\r
  60                 }\r
  61 \r
  62                 public new void Dispose() \r
  63                 {\r
  64                         base.Dispose();\r
  65                 }\r
  66 \r
  67                 public MySQLCommand(string cmdText, MySQLConnection connection, MySQLTransaction txn)\r
  68                 {\r
  69                         m_sCmdText              = cmdText;\r
  70                         m_connection    = connection;\r
  71                         m_txn                   = txn;\r
  72                 } \r
  73 \r
  74                 /****\r
  75                 * IMPLEMENT THE REQUIRED PROPERTIES.\r
  76                 ****/\r
  77                 public string CommandText\r
  78                 {\r
  79                         get { return m_sCmdText;  }\r
  80                         set  { m_sCmdText = value;  }\r
  81                 }\r
  82 \r
  83                 public int CommandTimeout\r
  84                 {\r
  85                         /*\r
  86                         * The sample does not support a command time-out. As a result,\r
  87                         * for the get, zero is returned because zero indicates an indefinite\r
  88                         * time-out period. For the set, throw an exception.\r
  89                         */\r
  90                         get  { return 0; }\r
  91                         set  { if (value != 0) throw new NotSupportedException(); }\r
  92                 }\r
  93 \r
  94                 public CommandType CommandType\r
  95                 {\r
  96                         /*\r
  97                         * The sample only supports CommandType.Text.\r
  98                         */\r
  99                         get { return CommandType.Text; }\r
 100                         set { if (value != CommandType.Text) throw new NotSupportedException(); }\r
 101                 }\r
 102 \r
 103                 public IDbConnection Connection\r
 104                 {\r
 105                         /*\r
 106                         * The user should be able to set or change the connection at \r
 107                         * any time.\r
 108                         */\r
 109                         get \r
 110                         { \r
 111                                 return m_connection;  \r
 112                         }\r
 113                         set\r
 114                         {\r
 115                                 /*\r
 116                                 * The connection is associated with the transaction\r
 117                                 * so set the transaction object to return a null reference if the connection \r
 118                                 * is reset.\r
 119                                 */\r
 120                                 if (m_connection != value)\r
 121                                 this.Transaction = null;\r
 122 \r
 123                                 m_connection = (MySQLConnection)value;\r
 124                         }\r
 125                 }\r
 126 \r
 127                 public MySQLParameterCollection Parameters\r
 128                 {\r
 129                         get  { return m_parameters; }\r
 130                 }\r
 131 \r
 132                 IDataParameterCollection IDbCommand.Parameters\r
 133                 {\r
 134                         get  { return m_parameters; }\r
 135                 }\r
 136 \r
 137                 public IDbTransaction Transaction\r
 138                 {\r
 139                         /*\r
 140                         * Set the transaction. Consider additional steps to ensure that the transaction\r
 141                         * is compatible with the connection, because the two are usually linked.\r
 142                         */\r
 143                         get \r
 144                         { \r
 145                                 return m_txn; \r
 146                         }\r
 147                         set \r
 148                         { \r
 149                                 m_txn = (MySQLTransaction)value; \r
 150                         }\r
 151                 }\r
 152 \r
 153                 public UpdateRowSource UpdatedRowSource\r
 154                 {\r
 155                         get \r
 156                         { \r
 157                                 return m_updatedRowSource;  \r
 158                         }\r
 159                         set \r
 160                         { \r
 161                                 m_updatedRowSource = value; \r
 162                         }\r
 163                 }\r
 164 \r
 165                 /****\r
 166                         * IMPLEMENT THE REQUIRED METHODS.\r
 167                         ****/\r
 168                 public void Cancel()\r
 169                 {\r
 170                         // The sample does not support canceling a command\r
 171                         // once it has been initiated.\r
 172                         throw new NotSupportedException();\r
 173                 }\r
 174 \r
 175                 public IDbDataParameter CreateParameter()\r
 176                 {\r
 177                         return new MySQLParameter();\r
 178                 }\r
 179 \r
 180                 /// <summary>\r
 181                 /// Convert the SQL command into a series of ASCII bytes streaming\r
 182                 /// each of the parameters into the proper place\r
 183                 /// </summary>\r
 184                 /// <param name="sql">Source SQL command with parameter markers</param>\r
 185                 /// <returns>Byte array with all parameters included</returns>\r
 186                 private ArrayList ConvertSQLToBytes(string sql)\r
 187                 {\r
 188                         ArrayList       byteArrays = new ArrayList();\r
 189                         System.IO.MemoryStream ms = new System.IO.MemoryStream();\r
 190                         \r
 191                         if (sql[ sql.Length-1 ] != ';')\r
 192                                 sql += ';';\r
 193                         byte[] bytes = System.Text.Encoding.ASCII.GetBytes(sql);\r
 194 \r
 195                         byte left_byte = 0;\r
 196                         int  parm_start=-1, parm_end = -1;\r
 197                         for (int x=0; x < bytes.Length; x++)\r
 198                         {\r
 199                                 byte b = bytes[x];\r
 200                                 // if we see a quote marker, then check to see if we are opening\r
 201                                 // or closing a quote\r
 202                                 if (b == '\'' || b == '\"')\r
 203                                 {\r
 204                                         if (b == left_byte)\r
 205                                         {\r
 206                                                 left_byte = 0;\r
 207                                         }\r
 208                                         else\r
 209                                         {\r
 210                                                 if (left_byte == 0)\r
 211                                                         left_byte = b;\r
 212                                         }\r
 213                                         ms.WriteByte(b);\r
 214                                 }\r
 215 \r
 216                                         // if we find a ; not part of a quoted string, then take the parsed portion\r
 217                                         // as a sql command and add it to the array\r
 218                                 else if (b == ';' && left_byte == 0)\r
 219                                 {\r
 220                                         byte[] sqlBytes = ms.ToArray();\r
 221                                         byteArrays.Add( sqlBytes );\r
 222                                         ms = new System.IO.MemoryStream();\r
 223                                 }\r
 224 \r
 225                                         // if we see the marker for a parameter, then save its position and\r
 226                                         // look for the end\r
 227                                 else if (b == '@' && left_byte == 0) \r
 228                                 {\r
 229                                         parm_start = x;\r
 230                                         left_byte = b;\r
 231                                 }\r
 232 \r
 233                                         // if we see a space and we are tracking a parameter, then end the parameter and have\r
 234                                         // that parameter serialize itself to the memory streams\r
 235                                 else if ((b == ' ' || b == ',' || b == ';' || b == ')') && left_byte == '@')\r
 236                                 {\r
 237                                         parm_end = x-1;\r
 238                                         string parm_name = sql.Substring(parm_start, parm_end-parm_start+1);\r
 239                                         MySQLParameter p = (m_parameters[parm_name] as MySQLParameter);\r
 240                                         p.SerializeToBytes(ms);\r
 241                                         ms.WriteByte(b);\r
 242 \r
 243                                         if (b == ';') \r
 244                                         {\r
 245                                                 byte[] sqlBytes = ms.ToArray();\r
 246                                                 byteArrays.Add( sqlBytes );\r
 247                                                 ms = new System.IO.MemoryStream();\r
 248                                         }\r
 249 \r
 250                                         left_byte = 0;\r
 251                                 }\r
 252 \r
 253                                         // we want to write out the bytes in all cases except when we are parsing out a parameter\r
 254                                 else if (left_byte != '@')\r
 255                                         ms.WriteByte( b );\r
 256                         }\r
 257 \r
 258                         // if we have any left, then add it at the end\r
 259                         if (ms.Length > 0) \r
 260                         {\r
 261                                 byte[] newbytes = ms.ToArray();\r
 262                                 byteArrays.Add( newbytes );\r
 263                         }\r
 264 \r
 265 /*                      string s = new string('c', 0);\r
 266                         byte[] bites = (byte[])byteArrays[0];\r
 267                         for (int zt=0; zt < bites.Length; zt++)\r
 268                                 s += Convert.ToChar(bites[zt]);\r
 269                         System.Windows.Forms.MessageBox.Show(s);\r
 270 */\r
 271                         return byteArrays;\r
 272                 }\r
 273 \r
 274                 /// <summary>\r
 275                 /// Executes a single non-select SQL statement.  Examples of this are update,\r
 276                 /// insert, etc.\r
 277                 /// </summary>\r
 278                 /// <returns>Number of rows affected</returns>\r
 279                 public int ExecuteNonQuery()\r
 280                 {\r
 281                         /*\r
 282                         * ExecuteNonQuery is intended for commands that do\r
 283                         * not return results, instead returning only the number\r
 284                         * of records affected.\r
 285                         */\r
 286 \r
 287                         // There must be a valid and open connection.\r
 288                         if (m_connection == null || m_connection.State != ConnectionState.Open)\r
 289                         throw new InvalidOperationException("Connection must valid and open");\r
 290 \r
 291                         ArrayList list = ConvertSQLToBytes( m_sCmdText );\r
 292                         m_UpdateCount = 0;\r
 293 \r
 294                         // Execute the command.\r
 295                         Driver d = m_connection.Driver;\r
 296                         try \r
 297                         {\r
 298                                 for (int x=0; x < list.Count; x++)\r
 299                                 {\r
 300                                         d.SendQuery( (byte[])list[x] ); \r
 301                                         if (d.LastResult == 0)\r
 302                                                 m_UpdateCount += d.ReadLength();\r
 303                                 }\r
 304                         }\r
 305                         catch (Exception ex)\r
 306                         {\r
 307                                 throw ex;\r
 308                         }\r
 309 \r
 310                         return m_UpdateCount;\r
 311                 }\r
 312 \r
 313                 IDataReader IDbCommand.ExecuteReader ()\r
 314                 {\r
 315                         return ExecuteReader ();\r
 316                 }\r
 317 \r
 318                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)\r
 319                 {\r
 320                         return ExecuteReader (behavior);\r
 321                 }\r
 322 \r
 323                 public MySQLDataReader ExecuteReader()\r
 324                 {\r
 325                         return ExecuteReader(CommandBehavior.Default);\r
 326                 }\r
 327 \r
 328                 public MySQLDataReader ExecuteReader(CommandBehavior behavior)\r
 329                 {\r
 330                         /*\r
 331                         * ExecuteReader should retrieve results from the data source\r
 332                         * and return a DataReader that allows the user to process \r
 333                         * the results.\r
 334                         */\r
 335 \r
 336                         // There must be a valid and open connection.\r
 337                         if (m_connection == null || m_connection.State != ConnectionState.Open)\r
 338                                 throw new InvalidOperationException("Connection must valid and open");\r
 339 \r
 340                         if (0 != (behavior & CommandBehavior.CloseConnection))\r
 341                         {\r
 342                         }\r
 343 \r
 344                         if (0 != (behavior & CommandBehavior.Default))\r
 345                         {\r
 346                         }\r
 347 \r
 348                         if (0 != (behavior & CommandBehavior.KeyInfo))\r
 349                         {\r
 350                         }\r
 351 \r
 352                         if (0 != (behavior & CommandBehavior.SchemaOnly))\r
 353                         {\r
 354                         }\r
 355 \r
 356                         if (0 != (behavior & CommandBehavior.SequentialAccess))\r
 357                         {\r
 358                         }\r
 359 \r
 360                         if (0 != (behavior & CommandBehavior.SingleResult))\r
 361                         {\r
 362                         }\r
 363 \r
 364                         if (0 != (behavior & CommandBehavior.SingleRow))\r
 365                         {\r
 366                         }\r
 367 \r
 368 \r
 369                         /*\r
 370                         * ExecuteReader should retrieve results from the data source\r
 371                         * and return a DataReader that allows the user to process \r
 372                         * the results.\r
 373                         */\r
 374                         ArrayList cmds = ConvertSQLToBytes( m_sCmdText );\r
 375                         m_UpdateCount = 0;\r
 376 \r
 377                         MySQLDataReader reader = new MySQLDataReader(m_connection, behavior == CommandBehavior.SequentialAccess);\r
 378 \r
 379                         // Execute the command.\r
 380                         Driver d = m_connection.Driver;\r
 381                         try \r
 382                         {\r
 383                                 for (int x=0; x < cmds.Count; x++)\r
 384                                 {\r
 385 /*                                      string st = new string('c',0);\r
 386                                         for (int z=0; z < ((byte[])cmds[x]).Length; z++)\r
 387                                         {\r
 388                                                 st += Convert.ToChar( ((byte[])cmds[x])[z] );\r
 389                                         }\r
 390                                         System.Windows.Forms.MessageBox.Show(st); */\r
 391 \r
 392 /*                                      System.IO.FileStream fs = new System.IO.FileStream("c:\\cmd.sql",  System.IO.FileMode.OpenOrCreate);\r
 393                                         byte[] bites = (byte[])(cmds[0]);\r
 394                                         fs.Write(bites, 0, bites.Length);\r
 395                                         fs.Close();\r
 396 */\r
 397                                         d.SendQuery( (byte[])cmds[x] );\r
 398                                         if (d.LastResult == 0)\r
 399                                                 m_UpdateCount += d.ReadLength();\r
 400                                         else\r
 401                                                 reader.LoadResults();\r
 402                                 }\r
 403                         }\r
 404                         catch (Exception ex) \r
 405                         {\r
 406                                 throw ex;\r
 407                         }\r
 408 \r
 409                         return reader;\r
 410 \r
 411                         //TODO implement rest of command behaviors on ExecuteReader\r
 412                         /*\r
 413                         * The only CommandBehavior option supported by this\r
 414                         * sample is the automatic closing of the connection\r
 415                         * when the user is done with the reader.\r
 416                         */\r
 417                 //      if (behavior == CommandBehavior.CloseConnection)\r
 418                 //        return new TemplateDataReader(resultset, m_connection);\r
 419                 //      else\r
 420                 //        return new TemplateDataReader(resultset);\r
 421                 }\r
 422 \r
 423                 /// <summary>\r
 424                 /// ExecuteScalar executes a single SQL command that will return\r
 425                 /// a single row with a single column, or if more rows/columns are\r
 426                 /// returned it will return the first column of the first row.\r
 427                 /// </summary>\r
 428                 /// <returns></returns>\r
 429                 public object ExecuteScalar()\r
 430                 {\r
 431                         // There must be a valid and open connection.\r
 432                         if (m_connection == null || m_connection.State != ConnectionState.Open)\r
 433                                 throw new InvalidOperationException("Connection must valid and open");\r
 434 \r
 435                         MySQLDataReader reader = new MySQLDataReader(m_connection, false);\r
 436                         ArrayList cmds = ConvertSQLToBytes( m_sCmdText );\r
 437                         m_UpdateCount = 0;\r
 438 \r
 439                         // Execute the command.\r
 440                         Driver d = m_connection.Driver;\r
 441 \r
 442                         try \r
 443                         {\r
 444                                 for (int x=0; x < cmds.Count; x++)\r
 445                                 {\r
 446                                         d.SendQuery( (byte[])cmds[x] );\r
 447                                         if (d.LastResult == 0)\r
 448                                                 m_UpdateCount += d.ReadLength();\r
 449                                         else\r
 450                                                 reader.LoadResults();\r
 451                                 }\r
 452                         }\r
 453                         catch (Exception ex)\r
 454                         {\r
 455                                 throw ex;\r
 456                         }\r
 457 \r
 458                         if (! reader.Read()) return null;\r
 459                         return reader.GetValue(0);\r
 460                 }\r
 461 \r
 462                 public void Prepare()\r
 463                 {\r
 464                 }\r
 465 \r
 466                 #region ICloneable\r
 467                 public object Clone() \r
 468                 {\r
 469                         MySQLCommand clone = new MySQLCommand(m_sCmdText, m_connection, m_txn);\r
 470                         foreach (MySQLParameter p in m_parameters) \r
 471                         {\r
 472                                 clone.Parameters.Add(p.Clone());\r
 473                         }\r
 474                         return clone;\r
 475                 }\r
 476                 #endregion\r
 477   }\r
 478 }\r