/// <summary>
/// Shortcut to ExecuteScalar with Sql Statement embedded params and object[] param values
/// </summary>
/// <param name="connectionString">SQLite Connection String</param>
/// <param name="commandText">SQL statment with embedded "@param" style parameters</param>
/// <param name="paramList">object[] array of param values</param>
/// <returns></returns>
public static object ExecuteScalar(string connectionString, string commandText, object[] paramList)
{
SQLiteConnection cn = new SQLiteConnection(connectionString);
SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText = commandText;
SQLiteParameterCollectionSub parms = DeriveParameters(commandText, paramList);
if (null != parms)
{
foreach (SQLiteParameter p in parms)
{
cmd.Parameters.Add(p);
}
}
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
object result = cmd.ExecuteScalar();
cmd.Dispose();
cn.Close();
return(result);
}
/// <summary>
/// Executes non-query sql Statment with Transaction
/// </summary>
/// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction, and Command, all of which must be created prior to making this method call. </param>
/// <param name="commandText">Command text.</param>
/// <param name="paramList">Param list.</param>
/// <returns>Integer</returns>
/// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>
public static int ExecuteNonQuery(SQLiteTransaction transaction, string commandText, object[] paramList)
{
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if (transaction != null && transaction.Connection == null)
{
throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
}
IDbCommand cmd = transaction.Connection.CreateCommand();
cmd.CommandText = commandText;
if (null != paramList)
{
SQLiteParameterCollectionSub coll = DeriveParameters(cmd.CommandText, paramList);
foreach (SQLiteParameter parm in coll)
{
cmd.Parameters.Add(parm);
}
}
if (transaction.Connection.State == ConnectionState.Closed)
{
transaction.Connection.Open();
}
int result = cmd.ExecuteNonQuery();
cmd.Dispose();
return(result);
}
/// <summary>
/// Executes the dataset with Transaction and object array of parameter values.
/// </summary>
/// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction, and Command, all of which must be created prior to making this method call. </param>
/// <param name="commandText">Command text.</param>
/// <param name="commandParameters">object[] array of parameter values.</param>
/// <returns>DataSet</returns>
/// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>
public static DataSet ExecuteDataSet(SQLiteTransaction transaction, string commandText, object[] commandParameters)
{
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if (transaction != null && transaction.Connection == null)
{
throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
}
IDbCommand cmd = transaction.Connection.CreateCommand();
cmd.CommandText = commandText;
SQLiteParameterCollectionSub coll = DeriveParameters(cmd.CommandText, commandParameters);
foreach (SQLiteParameter parm in coll)
{
cmd.Parameters.Add(parm);
}
if (transaction.Connection.State == ConnectionState.Closed)
{
transaction.Connection.Open();
}
DataSet ds = ExecuteDataSet((SQLiteCommand)cmd);
return(ds);
}
///<overloads></overloads>
/// <summary>
/// Shortcut method to execute dataset from SQL Statement and object[] arrray of /// parameter values
/// </summary>
/// <param name="cn">Connection.</param>
/// <param name="commandText">Command text.</param>
/// <param name="paramList">Param list.</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(SQLiteConnection cn, string commandText, object[] paramList)
{
SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText = commandText;
if (paramList != null && paramList.Length > 0)
{
SQLiteParameterCollectionSub parms = DeriveParameters(commandText, paramList);
foreach (SQLiteParameter p in parms)
{
cmd.Parameters.Add(p);
}
}
DataSet ds = new DataSet();
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
cmd.Dispose();
cn.Close();
return(ds);
}
/// <summary>
/// ShortCut method to return IDataReader
/// NOTE: You should explicitly close the Command.connection you passed in as
/// well as call Dispose on the Command after reader is closed.
/// We do this because IDataReader has no underlying Connection Property.
/// </summary>
/// <param name="cmd">SQLiteCommand Object</param>
/// <param name="commandText">SQL Statement with optional embedded "@param" style parameters</param>
/// <param name="paramList">object[] array of parameter values</param>
/// <returns>IDataReader</returns>
public static IDataReader ExecuteReader(SQLiteCommand cmd, string commandText, object[] paramList)
{
if (cmd.Connection == null)
{
throw new ArgumentException("Command must have live connection attached.", "cmd");
}
cmd.CommandText = commandText;
if (null != paramList)
{
SQLiteParameterCollectionSub parms = DeriveParameters(commandText, paramList);
foreach (SQLiteParameter p in parms)
{
cmd.Parameters.Add(p);
}
}
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return(rdr);
}
public static int ExecuteNonQuery(SQLiteConnection cn, string commandText, object[] paramList)
{
SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText = commandText;
if (null != paramList)
{
SQLiteParameterCollectionSub parms = DeriveParameters(commandText, paramList);
foreach (SQLiteParameter p in parms)
{
cmd.Parameters.Add(p);
}
}
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
int result = cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
return(result);
}
/// <summary>
/// Parses parameter names from SQL Statement, assigns values from object array , and returns fully populated ParameterCollection
/// </summary>
/// <param name="commandText">Sql Statement with SQL Server style "@param" style embedded parameters only [no OleDb style "?" placeholders]</param>
/// <param name="paramList">object[] array of parameter values</param>
/// <returns>SQLiteParameterCollection</returns>
/// <remarks>Status experimental. Regex appears to be handling most issues. Note that parameter object array must be in same order as parameter names appear in SQL statement.</remarks>
public static SQLiteParameterCollectionSub DeriveParameters(string commandText, object[] paramList)
{
if (paramList == null)
{
return(null);
}
//SQLiteParameterCollection coll = new SQLiteParameterCollection();
SQLiteParameterCollectionSub coll = new SQLiteParameterCollectionSub();
string parmString = commandText.Substring(commandText.IndexOf("@"));
// pre-process the string so always at least 1 space after a comma.
parmString = parmString.Replace(",", " ,");
// get the named parameters into a match collection
string pattern = @"(@)\S*(.*?)\b";
Regex ex = new Regex(pattern, RegexOptions.IgnoreCase);
MatchCollection mc = ex.Matches(parmString);
string[] paramNames = new string[mc.Count];
int i = 0;
foreach (Match m in mc)
{
paramNames[i] = m.Value;
i++;
}
// now let's type the parameters
int j = 0;
Type t = null;
foreach (object o in paramList)
{
t = o.GetType();
SQLiteParameter parm = new SQLiteParameter();
switch (t.ToString())
{
case ("DBNull"):
case ("Char"):
case ("SByte"):
case ("UInt16"):
case ("UInt32"):
case ("UInt64"):
throw new SystemException("Invalid data type");
case ("System.String"):
parm.DbType = DbType.String;
parm.ParameterName = paramNames[j];
parm.Value = (string)paramList[j];
coll.Add(parm);
break;
case ("System.Byte[]"):
parm.DbType = DbType.Binary;
parm.ParameterName = paramNames[j];
parm.Value = (byte[])paramList[j];
coll.Add(parm);
break;
case ("System.Int32"):
parm.DbType = DbType.Int32;
parm.ParameterName = paramNames[j];
parm.Value = (int)paramList[j];
coll.Add(parm);
break;
case ("System.Boolean"):
parm.DbType = DbType.Boolean;
parm.ParameterName = paramNames[j];
parm.Value = (bool)paramList[j];
coll.Add(parm);
break;
case ("System.DateTime"):
parm.DbType = DbType.DateTime;
parm.ParameterName = paramNames[j];
parm.Value = Convert.ToDateTime(paramList[j]);
coll.Add(parm);
break;
case ("System.Double"):
parm.DbType = DbType.Double;
parm.ParameterName = paramNames[j];
parm.Value = Convert.ToDouble(paramList[j]);
coll.Add(parm);
break;
case ("System.Decimal"):
parm.DbType = DbType.Decimal;
parm.ParameterName = paramNames[j];
parm.Value = Convert.ToDecimal(paramList[j]);
break;
case ("System.Guid"):
parm.DbType = DbType.Guid;
parm.ParameterName = paramNames[j];
parm.Value = (System.Guid)(paramList[j]);
break;
case ("System.Object"):
parm.DbType = DbType.Object;
parm.ParameterName = paramNames[j];
parm.Value = paramList[j];
coll.Add(parm);
break;
default:
throw new SystemException("Value is of unknown data type");
} // end switch
j++;
}
return(coll);
//return null;
}
