/// <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)
{
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>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SQLiteParameter[]）</param>
public static bool ExecuteSqlTran(string connectionString, Hashtable SQLStringList)
{
SQLiteConnection conn = new SQLiteConnection(connectionString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
using (SQLiteTransaction trans = conn.BeginTransaction())
{
SQLiteCommand cmd = new SQLiteCommand();
try
{ //循环
foreach (DictionaryEntry myDE in SQLStringList)
{
//string cmdText = myDE.Key.ToString();
string cmdText = (string)myDE.Value;
// SQLiteParameter[] cmdParms = (SQLiteParameter[])myDE.Value;
// object[] cmdParms = (object[])myDE.Value;
object[] cmdParms = (object[])myDE.Key;
SQLiteParameterCollectionSub parms = null;
if (null != cmdParms)
{
parms = DeriveParameters(cmdText, cmdParms);
}
PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, parms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
trans.Commit();
return(true);
}
catch (System.Data.SQLite.SQLiteException e)
{
trans.Rollback();
conn.Close();
return(false);
//throw new Exception(e.Message);
}
}
}
/// <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;
}
/// <summary>
/// 为执行命令做参数准备
/// </summary>
/// <param >SqlCommand object</param>
/// <param >SqlConnection object</param>
/// <param >SqlTransaction object</param>
/// <param >一个存储过程,或者sql语句类型</param>
/// <param >一个命令sql语句</param>
/// <param >参数集合</param>
public static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, CommandType cmdType, string cmdText, SQLiteParameterCollectionSub cmdParms)
{
try
{
//判断连接的状态。如果是关闭状态，则打开
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
//cmd属性赋值
cmd.Connection = conn;
cmd.CommandText = cmdText;
//是否需要用到事务处理
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.CommandType = cmdType;
//添加cmd需要的存储过程参数
if (cmdParms != null)
{
foreach (SQLiteParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
}
catch (Exception e) { throw new Exception(e.Message); }
}