mcs/class/Mono.Data.SqliteClient/Mono.Data.SqliteClient/SqliteCommand.cs

   1 //
   2 // Mono.Data.SqliteClient.SqliteCommand.cs
   3 //
   4 // Represents a Transact-SQL statement or stored procedure to execute against
   5 // a Sqlite database file.
   6 //
   7 // Author(s):   Vladimir Vukicevic  <vladimir@pobox.com>
   8 //              Everaldo Canuto  <everaldo_canuto@yahoo.com.br>
   9 //              Chris Turchin <chris@turchin.net>
  10 //              Jeroen Zwartepoorte <jeroen@xs4all.nl>
  11 //              Thomas Zoechling <thomas.zoechling@gmx.at>
  12 //
  13 // Copyright (C) 2002  Vladimir Vukicevic
  14 //
  15 // Permission is hereby granted, free of charge, to any person obtaining
  16 // a copy of this software and associated documentation files (the
  17 // "Software"), to deal in the Software without restriction, including
  18 // without limitation the rights to use, copy, modify, merge, publish,
  19 // distribute, sublicense, and/or sell copies of the Software, and to
  20 // permit persons to whom the Software is furnished to do so, subject to
  21 // the following conditions:
  22 //
  23 // The above copyright notice and this permission notice shall be
  24 // included in all copies or substantial portions of the Software.
  25 //
  26 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  27 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  28 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  29 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  30 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  31 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  32 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  33 //
  34
  35 using System;
  36 using System.Text;
  37 using System.Runtime.InteropServices;
  38 using System.Text.RegularExpressions;
  39 using System.Data;
  40 using System.Diagnostics;
  41
  42 namespace Mono.Data.SqliteClient
  43 {
  44         public class SqliteCommand : IDbCommand
  45         {
  46                 #region Fields
  47
  48                 private SqliteConnection parent_conn;
  49                 //private SqliteTransaction transaction;
  50                 private IDbTransaction transaction;
  51                 private string sql;
  52                 private int timeout;
  53                 private CommandType type;
  54                 private UpdateRowSource upd_row_source;
  55                 private SqliteParameterCollection sql_params;
  56                 private bool prepared = false;
  57                 private IntPtr pStmt;
  58
  59                 #endregion
  60
  61                 #region Constructors and destructors
  62
  63                 public SqliteCommand ()
  64                 {
  65                         sql = "";
  66                         sql_params = new SqliteParameterCollection ();
  67                 }
  68
  69                 public SqliteCommand (string sqlText)
  70                 {
  71                         sql = sqlText;
  72                         sql_params = new SqliteParameterCollection ();
  73                 }
  74
  75                 public SqliteCommand (string sqlText, SqliteConnection dbConn)
  76                 {
  77                         sql = sqlText;
  78                         parent_conn = dbConn;
  79                         sql_params = new SqliteParameterCollection ();
  80                 }
  81
  82                 public SqliteCommand (string sqlText, SqliteConnection dbConn, IDbTransaction trans)
  83                 {
  84                         sql = sqlText;
  85                         parent_conn = dbConn;
  86                         transaction = trans;
  87                         sql_params = new SqliteParameterCollection ();
  88                 }
  89
  90                 public void Dispose ()
  91                 {
  92                 }
  93
  94                 #endregion
  95
  96                 #region Properties
  97
  98                 public string CommandText
  99                 {
 100                         get { return sql; }
 101                         set { sql = value; }
 102                 }
 103
 104                 public int CommandTimeout
 105                 {
 106                         get { return timeout; }
 107                         set { timeout = value; }
 108                 }
 109
 110                 public CommandType CommandType
 111                 {
 112                         get { return type; }
 113                         set { type = value; }
 114                 }
 115
 116                 IDbConnection IDbCommand.Connection
 117                 {
 118                         get
 119                         {
 120                                 return parent_conn;
 121                         }
 122                         set
 123                         {
 124                                 if (!(value is SqliteConnection))
 125                                 {
 126                                         throw new InvalidOperationException ("Can't set Connection to something other than a SqliteConnection");
 127                                 }
 128                                 parent_conn = (SqliteConnection) value;
 129                         }
 130                 }
 131
 132                 public SqliteConnection Connection
 133                 {
 134                         get { return parent_conn; }
 135                         set { parent_conn = value; }
 136                 }
 137
 138                 IDataParameterCollection IDbCommand.Parameters
 139                 {
 140                         get { return Parameters; }
 141                 }
 142
 143                 public SqliteParameterCollection Parameters
 144                 {
 145                         get { return sql_params; }
 146                 }
 147
 148                 public IDbTransaction Transaction
 149                 {
 150                         get { return transaction; }
 151                         set { transaction = value; }
 152                 }
 153
 154                 public UpdateRowSource UpdatedRowSource
 155                 {
 156                         get { return upd_row_source; }
 157                         set { upd_row_source = value; }
 158                 }
 159
 160                 #endregion
 161
 162                 #region Internal Methods
 163
 164                 internal int NumChanges ()
 165                 {
 166                         if (parent_conn.Version == 3)
 167                                 return Sqlite.sqlite3_changes(parent_conn.Handle);
 168                         else
 169                                 return Sqlite.sqlite_changes(parent_conn.Handle);
 170                 }
 171
 172                 private string ReplaceParams(Match m)
 173                 {
 174                         string input = m.Value;
 175                         if (m.Groups["param"].Success)
 176                         {
 177                                 Group g = m.Groups["param"];
 178                                 string find = g.Value;
 179                                 //FIXME: sqlite works internally only with strings, so this assumtion is mostly legit, but what about date formatting, etc?
 180                                 //Need to fix SqlLiteDataReader first to acurately describe the tables
 181                                 SqliteParameter sqlp = Parameters[find];
 182                                 string replace = Convert.ToString(sqlp.Value);
 183                                 if(sqlp.DbType == DbType.String)
 184                                 {
 185                                         replace =  "\"" + replace + "\"";
 186                                 }
 187
 188                                 input = Regex.Replace(input,find,replace);
 189                                 return input;
 190                         }
 191                         else
 192                         return m.Value;
 193                 }
 194
 195                 #endregion
 196
 197                 #region Public Methods
 198
 199                 public void Cancel ()
 200                 {
 201                 }
 202
 203                 public string ProcessParameters()
 204                 {
 205                         string processedText = sql;
 206
 207                         //Regex looks odd perhaps, but it works - same impl. as in the firebird db provider
 208                         //the named parameters are using the ADO.NET standard @-prefix but sqlite is considering ":" as a prefix for v.3...
 209                         //ref: http://www.mail-archive.com/sqlite-users@sqlite.org/msg01851.html
 210                         //Regex r = new Regex(@"(('[^']*?\@[^']*')*[^'@]*?)*(?<param>@\w+)+([^'@]*?('[^']*?\@[^']*'))*",RegexOptions.ExplicitCapture);
 211
 212                         //The above statement is true for the commented regEx, but I changed it to use the :-prefix, because now (12.05.2005 sqlite3)
 213                         //sqlite is using : as Standard Parameterprefix
 214
 215                         Regex r = new Regex(@"(('[^']*?\:[^']*')*[^':]*?)*(?<param>:\w+)+([^':]*?('[^']*?\:[^']*'))*",RegexOptions.ExplicitCapture);
 216                         MatchEvaluator me = new MatchEvaluator(ReplaceParams);
 217                         processedText = r.Replace(sql, me);
 218                         return processedText;
 219                 }
 220
 221                 public void Prepare ()
 222                 {
 223                         SqliteError err = SqliteError.OK;
 224                         IntPtr pzTail = IntPtr.Zero;
 225                         pStmt = IntPtr.Zero;
 226                         if (parent_conn.Version == 3)
 227                         {
 228                                 err = Sqlite.sqlite3_prepare (parent_conn.Handle, sql, sql.Length, out pStmt, out pzTail);
 229                                 if (err != SqliteError.OK)
 230                                         throw new ApplicationException ("Sqlite error in prepare " + err);
 231                                 int pcount = Sqlite.sqlite3_bind_parameter_count (pStmt);
 232
 233                                 for (int i = 1; i <= pcount; i++)
 234                                 {
 235                                         String name = Sqlite.sqlite3_bind_parameter_name (pStmt, i);
 236                                         SqliteParameter param = sql_params[name];
 237                                         Type ptype = param.Value.GetType ();
 238
 239                                         if (ptype.Equals (typeof (String)))
 240                                         {
 241                                                 String s = (String)param.Value;
 242                                                 err = Sqlite.sqlite3_bind_text (pStmt, i, s, s.Length, (IntPtr)(-1));
 243                                         }
 244                                         else if (ptype.Equals (typeof (DBNull)))
 245                                         {
 246                                                 err = Sqlite.sqlite3_bind_null (pStmt, i);
 247                                         }
 248                                         else if (ptype.Equals (typeof (Boolean)))
 249                                         {
 250                                                 bool b = (bool)param.Value;
 251                                                 err = Sqlite.sqlite3_bind_int (pStmt, i, b ? 1 : 0);
 252                                         } else if (ptype.Equals (typeof (Byte)))
 253                                         {
 254                                                 err = Sqlite.sqlite3_bind_int (pStmt, i, (Byte)param.Value);
 255                                         }
 256                                         else if (ptype.Equals (typeof (Char)))
 257                                         {
 258                                                 err = Sqlite.sqlite3_bind_int (pStmt, i, (Char)param.Value);
 259                                         }
 260                                         else if (ptype.Equals (typeof (Int16)))
 261                                         {
 262                                                 err = Sqlite.sqlite3_bind_int (pStmt, i, (Int16)param.Value);
 263                                         }
 264                                         else if (ptype.Equals (typeof (Int32)))
 265                                         {
 266                                                 err = Sqlite.sqlite3_bind_int (pStmt, i, (Int32)param.Value);
 267                                         }
 268                                         else if (ptype.Equals (typeof (SByte)))
 269                                         {
 270                                                 err = Sqlite.sqlite3_bind_int (pStmt, i, (SByte)param.Value);
 271                                         }
 272                                         else if (ptype.Equals (typeof (UInt16)))
 273                                         {
 274                                                 err = Sqlite.sqlite3_bind_int (pStmt, i, (UInt16)param.Value);
 275                                         }
 276                                         else if (ptype.Equals (typeof (DateTime)))
 277                                         {
 278                                                 DateTime dt = (DateTime)param.Value;
 279                                                 err = Sqlite.sqlite3_bind_int64 (pStmt, i, dt.ToFileTime ());
 280                                         }
 281                                         else if (ptype.Equals (typeof (Double)))
 282                                         {
 283                                                 err = Sqlite.sqlite3_bind_double (pStmt, i, (Double)param.Value);
 284                                         }
 285                                         else if (ptype.Equals (typeof (Single)))
 286                                         {
 287                                                 err = Sqlite.sqlite3_bind_double (pStmt, i, (Single)param.Value);
 288                                         }
 289                                         else if (ptype.Equals (typeof (UInt32)))
 290                                         {
 291                                                 err = Sqlite.sqlite3_bind_int64 (pStmt, i, (UInt32)param.Value);
 292                                         }
 293                                         else if (ptype.Equals (typeof (Int64)))
 294                                         {
 295                                                 err = Sqlite.sqlite3_bind_int64 (pStmt, i, (Int64)param.Value);
 296                                         }
 297                                         else
 298                                         {
 299                                                 throw new ApplicationException("Unkown Parameter Type");
 300                                         }
 301                                         if (err != SqliteError.OK)
 302                                         {
 303                                                 throw new ApplicationException ("Sqlite error in bind " + err);
 304                                         }
 305                                 }
 306                         }
 307                         else
 308                         {
 309                                 IntPtr errMsg = IntPtr.Zero;
 310                                 string msg = "";
 311                                 string sqlData = sql;
 312                                 if (Parameters.Count > 0)
 313                                 {
 314                                         sqlData = ProcessParameters();
 315                                 }
 316                                 err = Sqlite.sqlite_compile (parent_conn.Handle, sqlData, out pzTail, out pStmt, out errMsg);
 317
 318                                 if (err != SqliteError.OK)
 319                                 {
 320                                         if (errMsg != IntPtr.Zero)
 321                                         {
 322                                                 msg = Marshal.PtrToStringAnsi (errMsg);
 323                                                 Sqlite.sqliteFree (errMsg);
 324                                         }
 325                                         throw new ApplicationException ("Sqlite error " + msg);
 326                                 }
 327                         }
 328                         prepared=true;
 329                 }
 330
 331
 332                 IDbDataParameter IDbCommand.CreateParameter()
 333                 {
 334                         return CreateParameter ();
 335                 }
 336
 337                 public SqliteParameter CreateParameter ()
 338                 {
 339                         return new SqliteParameter ();
 340                 }
 341
 342                 public int ExecuteNonQuery ()
 343                 {
 344                         int rows_affected;
 345                         SqliteDataReader r = ExecuteReader (CommandBehavior.Default, false, out rows_affected);
 346                         return rows_affected;
 347                 }
 348
 349                 public object ExecuteScalar ()
 350                 {
 351                         SqliteDataReader r = ExecuteReader ();
 352                         if (r == null || !r.Read ()) {
 353                                 return null;
 354                         }
 355                         object o = r[0];
 356                         r.Close ();
 357                         return o;
 358                 }
 359
 360                 IDataReader IDbCommand.ExecuteReader ()
 361                 {
 362                         return ExecuteReader ();
 363                 }
 364
 365                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
 366                 {
 367                         return ExecuteReader (behavior);
 368                 }
 369
 370                 public SqliteDataReader ExecuteReader ()
 371                 {
 372                         return ExecuteReader (CommandBehavior.Default);
 373                 }
 374
 375                 public SqliteDataReader ExecuteReader (CommandBehavior behavior)
 376                 {
 377                         int r;
 378                         return ExecuteReader (behavior, true, out r);
 379                 }
 380
 381                 public SqliteDataReader ExecuteReader (CommandBehavior behavior, bool want_results, out int rows_affected)
 382                 {
 383                         SqliteDataReader reader = null;
 384                         SqliteError err = SqliteError.OK;
 385                         IntPtr errMsg = IntPtr.Zero;
 386                         parent_conn.StartExec ();
 387
 388                         string msg = "";
 389                         try
 390                         {
 391                                 if (!prepared)
 392                                 {
 393                                         Prepare ();
 394                                 }
 395                                 if (want_results)
 396                                 {
 397                                         reader = new SqliteDataReader (this, pStmt, parent_conn.Version);
 398                                 }
 399                                 else
 400                                 {
 401                                         if (parent_conn.Version == 3)
 402                                         {
 403                                                 err = Sqlite.sqlite3_step (pStmt);
 404                                         }
 405                                         else
 406                                         {
 407                                                 int cols;
 408                                                 IntPtr pazValue = IntPtr.Zero;
 409                                                 IntPtr pazColName = IntPtr.Zero;
 410                                                 err = Sqlite.sqlite_step (pStmt, out cols, out pazValue, out pazColName);
 411                                         }
 412                                 }
 413                         }
 414                         finally
 415                         {
 416                                 if (parent_conn.Version == 3)
 417                                 {}
 418                                 else
 419                                 {
 420                                         err = Sqlite.sqlite_finalize (pStmt, out errMsg);
 421                                 }
 422                                 parent_conn.EndExec ();
 423                                 prepared = false;
 424                         }
 425
 426                         if (err != SqliteError.OK &&
 427                             err != SqliteError.DONE &&
 428                             err != SqliteError.ROW)
 429                         {
 430                                 if (errMsg != IntPtr.Zero)
 431                                 {
 432                                         //msg = Marshal.PtrToStringAnsi (errMsg);
 433                                         if (parent_conn.Version == 3)
 434                                         {
 435                                                 err = Sqlite.sqlite3_finalize (pStmt, out errMsg);
 436                                         }
 437                                         else
 438                                         {
 439                                                 err = Sqlite.sqlite_finalize (pStmt, out errMsg);
 440                                                 Sqlite.sqliteFree (errMsg);
 441                                         }
 442                                 }
 443                                 throw new ApplicationException ("Sqlite error " + msg);
 444                         }
 445                         rows_affected = NumChanges ();
 446                         return reader;
 447                 }
 448
 449                 public int LastInsertRowID ()
 450                 {
 451                         if (parent_conn.Version == 3)
 452                                 return Sqlite.sqlite3_last_insert_rowid(parent_conn.Handle);
 453                         else
 454                                 return Sqlite.sqlite_last_insert_rowid(parent_conn.Handle);
 455                 }
 456         #endregion
 457         }
 458 }