SQLite Helper

System; using System.Data; using System.Text.RegularExpressions; using System.Xml; using System.IO; using System.Collections; using System.Data.SQLite; using System.Configuration;//添加.net引用 namespace Tools.Common { /// <summary> /// 对SQLite操作的类 /// 引用：System.Data.SQLite.dll【版本：3.6.23.1（原始文件名：SQlite3.DLL）】 /// </summary> public class SQLiteHelper { /// <summary> /// 所有成员函数都是静态的，构造函数定义为私有 /// </summary> private SQLiteHelper() { } /// <summary> /// 连接字符串 /// </summary> public static string ConnectionString {//"Data Source=Test.db3;Pooling=true;FailIfMissing=false"; get { ////(AppSettings节点下的"SQLiteConnectionString") string text = ConfigurationManager.AppSettings["SQLiteConnectionString"]; string str2 = ConfigurationManager.AppSettings["IsEncrypt"]; if (str2 == "true") { text = DesEncrypt.Decrypt(text); } return text; } } private static SQLiteConnection _Conn = null; /// <summary> /// 连接对象 /// </summary> public static SQLiteConnection Conn { get { if (_Conn == null) _Conn = new SQLiteConnection(ConnectionString); return SQLiteHelper._Conn; } set { SQLiteHelper._Conn = value; } } #region CreateCommand(commandText,SQLiteParameter[]) /// <summary> /// 创建命令 /// </summary> /// <param name="connection">连接</param> /// <param name="commandText">语句</param> /// <param name="commandParameters">语句参数.</param> /// <returns>SQLite Command</returns> public static SQLiteCommand CreateCommand(string commandText, params SQLiteParameter[] commandParameters) { SQLiteCommand cmd = new SQLiteCommand(commandText, Conn); if (commandParameters.Length > 0) { foreach (SQLiteParameter parm in commandParameters) cmd.Parameters.Add(parm); } return cmd; } #endregion #region CreateParameter(parameterName,parameterType,parameterValue) /// <summary> /// 创建参数 /// </summary> /// <param name="parameterName">参数名</param> /// <param name="parameterType">参数类型</param> /// <param name="parameterValue">参数值</param> /// <returns>返回创建的参数</returns> public static SQLiteParameter CreateParameter(string parameterName, System.Data.DbType parameterType, object parameterValue) { SQLiteParameter parameter = new SQLiteParameter(); parameter.DbType = parameterType; parameter.ParameterName = parameterName; parameter.Value = parameterValue; return parameter; } #endregion #region ExecuteDataSet(commandText,paramList[]) /// <summary> /// 查询数据集 /// </summary> /// <param name="cn">连接.</param> /// <param name="commandText">查询语句.</param> /// <param name="paramList">object参数列表.</param> /// <returns></returns> public static DataSet ExecuteDataSet( string commandText,params object[] paramList) { SQLiteCommand cmd = Conn.CreateCommand(); cmd.CommandText = commandText; if (paramList != null) { AttachParameters(cmd, commandText, paramList); } DataSet ds = new DataSet(); if (Conn.State == ConnectionState.Closed) Conn.Open(); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); da.Fill(ds); da.Dispose(); cmd.Dispose(); Conn.Close(); return ds; } #endregion #region ExecuteDataSet(SQLiteCommand) /// <summary> /// 查询数据集 /// </summary> /// <param name="cmd">SQLiteCommand对象</param> /// <returns>返回数据集</returns> public static DataSet ExecuteDataSet(SQLiteCommand cmd) { if (cmd.Connection.State == ConnectionState.Closed) cmd.Connection.Open(); DataSet ds = new DataSet(); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); da.Fill(ds); da.Dispose(); cmd.Connection.Close(); cmd.Dispose(); return ds; } #endregion #region ExecuteDataSet(SQLiteTransaction,commandText,params SQLiteParameter[]) /// <summary> /// 查询数据集 /// </summary> /// <param name="transaction">SQLiteTransaction对象. </param> /// <param name="commandText">查询语句.</param> /// <param name="commandParameters">命令的参数列表.</param> /// <returns>DataSet</returns> /// <remarks>必须手动执行关闭连接transaction.connection.Close</remarks> public static DataSet ExecuteDataSet(SQLiteTransaction transaction, string commandText, params SQLiteParameter[] 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; foreach (SQLiteParameter parm in commandParameters) { cmd.Parameters.Add(parm); } if (transaction.Connection.State == ConnectionState.Closed) transaction.Connection.Open(); DataSet ds = ExecuteDataSet((SQLiteCommand)cmd); return ds; } #endregion #region ExecuteDataSet(SQLiteTransaction,commandText,object[] commandParameters) /// <summary> /// 查询数据集 /// </summary> /// <param name="transaction">SQLiteTransaction对象 </param> /// <param name="commandText">查询语句.</param> /// <param name="commandParameters">命令参数列表</param> /// <returns>返回数据集</returns> /// <remarks>必须手动执行关闭连接transaction.connection.Close</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; AttachParameters((SQLiteCommand)cmd, cmd.CommandText, commandParameters); if (transaction.Connection.State == ConnectionState.Closed) transaction.Connection.Open(); DataSet ds = ExecuteDataSet((SQLiteCommand)cmd); return ds; } #endregion #region UpdateDataset(insertCommand,deleteCommand,updateCommand,dataSet,tableName) /// <summary> /// 更新数据集中数据到数据库 /// </summary> /// <param name="insertCommand">insert语句</param> /// <param name="deleteCommand">delete语句</param> /// <param name="updateCommand">update语句</param> /// <param name="dataSet">要更新的DataSet</param> /// <param name="tableName">数据集中要更新的table名</param> public static void UpdateDataset(SQLiteCommand insertCommand, SQLiteCommand deleteCommand, SQLiteCommand updateCommand, DataSet dataSet, string tableName) { if (insertCommand == null) throw new ArgumentNullException("insertCommand"); if (deleteCommand == null) throw new ArgumentNullException("deleteCommand"); if (updateCommand == null) throw new ArgumentNullException("updateCommand"); if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName"); // Create a SQLiteDataAdapter, and dispose of it after we are done using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter()) { // Set the data adapter commands dataAdapter.UpdateCommand = updateCommand; dataAdapter.InsertCommand = insertCommand; dataAdapter.DeleteCommand = deleteCommand; // Update the dataset changes in the data source dataAdapter.Update(dataSet, tableName); // Commit all the changes made to the DataSet dataSet.AcceptChanges(); } } #endregion #region ExecuteReader(SQLiteCommand,commandText, object[] paramList) /// <summary> /// ExecuteReader方法 /// </summary> /// <param name="cmd">查询命令</param> /// <param name="commandText">含有类似@colume参数的sql语句</param> /// <param name="paramList">语句参数列表</param> /// <returns>IDataReader</returns> public static IDataReader ExecuteReader(SQLiteCommand cmd, string commandText, object[] paramList) { if (cmd.Connection == null) throw new ArgumentException("没有为命令指定活动的连接.", "cmd"); cmd.CommandText = commandText; AttachParameters(cmd, commandText, paramList); if (cmd.Connection.State == ConnectionState.Closed) cmd.Connection.Open(); IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return rdr; } #endregion #region ExecuteNonQuery(commandText,paramList) /// <summary> /// 执行ExecuteNonQuery方法 /// </summary> /// <param name="cn">连接</param> /// <param name="commandText">语句</param> /// <param name="paramList">参数</param> /// <returns></returns> public static int ExecuteNonQuery(string commandText, params object[] paramList) { SQLiteCommand cmd = Conn.CreateCommand(); cmd.CommandText = commandText; AttachParameters(cmd, commandText, paramList); if (Conn.State == ConnectionState.Closed) Conn.Open(); int result = cmd.ExecuteNonQuery(); cmd.Dispose(); Conn.Close(); return result; } #endregion #region ExecuteNonQuery(SQLiteTransaction,commandText,paramList) /// <summary> /// 执行ExecuteNonQuery方法,带事务 /// </summary> /// <param name="transaction">之前创建好的SQLiteTransaction对象</param> /// <param name="commandText">语句.</param> /// <param name="paramList">参数.</param> /// <returns>返回影响的行数</returns> /// <remarks> /// 定义事务 DbTransaction trans = conn.BeginTransaction(); /// 或者：SQLiteTransaction trans = Conn.BeginTransaction(); /// 操作代码示例： /// try ///{ /// // 连续操作记录 /// for (int i = 0; i < 1000; i++) /// { /// ExecuteNonQuery(trans,commandText,[] paramList); /// } /// trans.Commit(); ///} ///catch ///{ /// trans.Rollback(); /// throw; ///} ///trans.Connection.Close();//关闭事务连接 ///transaction.Dispose();//释放事务对象 /// </remarks> public static int ExecuteNonQuery(SQLiteTransaction transaction, string commandText, params object[] paramList) { if (transaction == null) throw new ArgumentNullException("transaction is null"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed,please provide an open transaction.", "transaction"); using (IDbCommand cmd = transaction.Connection.CreateCommand()) { cmd.CommandText = commandText; AttachParameters((SQLiteCommand)cmd, cmd.CommandText, paramList); if (transaction.Connection.State == ConnectionState.Closed) transaction.Connection.Open(); int result = cmd.ExecuteNonQuery(); return result; } } #endregion #region ExecuteNonQuery(IDbCommand) /// <summary> /// 执行ExecuteNonQuery方法 /// </summary> /// <param name="cmd">创建好的命令.</param> /// <returns></returns> public static int ExecuteNonQuery(IDbCommand cmd) { if (cmd.Connection.State == ConnectionState.Closed) cmd.Connection.Open(); int result = cmd.ExecuteNonQuery(); cmd.Connection.Close(); cmd.Dispose(); return result; } #endregion #region ExecuteScalar(commandText,paramList) /// <summary> /// 执行ExecuteScalar /// </summary> /// <param name="commandText">语句s</param> /// <param name="paramList">参数</param> /// <returns></returns> public static object ExecuteScalar(string commandText, params object[] paramList) { SQLiteConnection cn = new SQLiteConnection(ConnectionString); SQLiteCommand cmd = cn.CreateCommand(); cmd.CommandText = commandText; AttachParameters(cmd, commandText, paramList); if (cn.State == ConnectionState.Closed) cn.Open(); object result = cmd.ExecuteScalar(); cmd.Dispose(); cn.Close(); return result; } #endregion #region ExecuteXmlReader(IDbCommand) /// <summary> /// ExecuteXmlReader返回xml格式 /// </summary> /// <param name="command">语句</param> /// <returns>返回XmlTextReader对象</returns> public static XmlReader ExecuteXmlReader(IDbCommand command) { // open the connection if necessary, but make sure we // know to close it when we?re done. if (command.Connection.State != ConnectionState.Open) { command.Connection.Open(); } // get a data adapter SQLiteDataAdapter da = new SQLiteDataAdapter((SQLiteCommand)command); DataSet ds = new DataSet(); // fill the data set, and return the schema information da.MissingSchemaAction = MissingSchemaAction.AddWithKey; da.Fill(ds); // convert our dataset to XML StringReader stream = new StringReader(ds.GetXml()); command.Connection.Close(); // convert our stream of text to an XmlReader return new XmlTextReader(stream); } #endregion #region AttachParameters(SQLiteCommand,commandText,object[] paramList) /// <summary> /// 增加参数到命令（自动判断类型） /// </summary> /// <param name="commandText">命令语句</param> /// <param name="paramList">object参数列表</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> private static SQLiteParameterCollection AttachParameters(SQLiteCommand cmd, string commandText, params object[] paramList) { if (paramList == null || paramList.Length == 0) return null; SQLiteParameterCollection coll = cmd.Parameters; 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; } #endregion #region ExecuteNonQueryTypedParams(IDbCommand, DataRow) /// <summary> /// Executes non query typed params from a DataRow /// </summary> /// <param name="command">Command.</param> /// <param name="dataRow">Data row.</param> /// <returns>Integer result code</returns> public static int ExecuteNonQueryTypedParams(IDbCommand command, DataRow dataRow) { int retVal = 0; // If the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0) { // Set the parameters values AssignParameterValues(command.Parameters, dataRow); retVal = ExecuteNonQuery(command); } else { retVal = ExecuteNonQuery(command); } return retVal; } #endregion #region AssignParameterValues /// <summary> /// This method assigns dataRow column values to an IDataParameterCollection /// </summary> /// <param name="commandParameters">The IDataParameterCollection to be assigned values</param> /// <param name="dataRow">The dataRow used to hold the command‘s parameter values</param> /// <exception cref="System.InvalidOperationException">Thrown if any of the parameter names are invalid.</exception> protected internal static void AssignParameterValues(IDataParameterCollection commandParameters, DataRow dataRow) { if (commandParameters == null || dataRow == null) { // Do nothing if we get no data return; } DataColumnCollection columns = dataRow.Table.Columns; int i = 0; // Set the parameters values foreach (IDataParameter commandParameter in commandParameters) { // Check the parameter name if (commandParameter.ParameterName == null || commandParameter.ParameterName.Length <= 1) throw new InvalidOperationException(string.Format( "Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: ‘{1}‘.", i, commandParameter.ParameterName)); if (columns.Contains(commandParameter.ParameterName)) commandParameter.Value = dataRow[commandParameter.ParameterName]; else if (columns.Contains(commandParameter.ParameterName.Substring(1))) commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)]; i++; } } #endregion #region AssignParameterValues /// <summary> /// This method assigns dataRow column values to an array of IDataParameters /// </summary> /// <param name="commandParameters">Array of IDataParameters to be assigned values</param> /// <param name="dataRow">The dataRow used to hold the stored procedure‘s parameter values</param> /// <exception cref="System.InvalidOperationException">Thrown if