SQLiteHelper

[雷纹]

using 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 Utills
{
    /// <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,para