一个功能齐全的SQLite数据库的帮助类

using System;
using System.Text;
using System.Data.Common;
using System.Collections;
using System.Collections.Generic;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SQLite;

namespace xhan2000.Common40.Data
{
    public static class SQLiteHelper
    {
        public static readonly object WriteLock = new object();
        static readonly object writeLock = new object();
        public static char[] SplitChars = new char[] { ',',' ',')','(','+','-','*','/' };

        static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[0].ToString();
        public static string ConnectionString
        {
            get
            {
                return connectionString;
            }
            set
            {
                connectionString = value;
            }
        }

        public static bool SureOpen(DbConnection conn)
        {
            return Open(conn);
        }
        public static bool Open(DbConnection conn)
        {
            if (conn != null && conn.State != ConnectionState.Open)
            {
                conn.Open();
                return true;
            }
            return false;
        }
        public static bool SureClose(DbConnection conn)
        {
            return Close(conn);
        }
        public static bool Close(DbConnection conn)
        {
            if (conn != null && conn.State != ConnectionState.Closed)
            {
                conn.Close();
                return true;
            }
            return false;
        }

        public static object GetDbValue(object o)
        {
            if (o == null)
                return DBNull.Value;
            return o;
        }

        public static string GetSortString(Dictionary<string,bool> dic,bool isReverse)
        {
            StringBuilder sb = new StringBuilder();
            System.Collections.IEnumerator ienum = dic.GetEnumerator();
            while (ienum.MoveNext())
            {
                KeyValuePair<string,bool> kvp = (KeyValuePair<string,bool>)ienum.Current;
                sb.Append(kvp.Key + " " + (isReverse ? (kvp.Value ? "desc" : "asc") : (kvp.Value ? "asc" : "desc")) + ",");
            }
            string temp = sb.ToString();
            return temp.Substring(0,temp.Length - 1);
        }

        //static readonly SQLiteTransaction singleton = GetSQLiteTransaction();
        //public static SQLiteTransaction Singleton
        //{
        //    get
        //    {
        //        return singleton;
        //    }
        //}

        /// <summary>
        /// 获得连接对象
        /// </summary>
        /// <returns></returns>
        public static SQLiteConnection GetSQLiteConnection(string connectionString)
        {
            return new SQLiteConnection(connectionString);
        }
        public static SQLiteConnection GetSQLiteConnection()
        {
            return GetSQLiteConnection(ConnectionString);
        }
        public static SQLiteConnection CreateConnection(string connectionString)
        {
            return new SQLiteConnection(connectionString);
        }
        public static SQLiteConnection CreateConnection()
        {
            return CreateConnection(ConnectionString);
        }
        public static SQLiteTransaction GetSQLiteTransaction(string connectionString)
        {
            SQLiteConnection conn = GetSQLiteConnection(connectionString);
            conn.Open();
            return conn.BeginTransaction();
        }
        public static SQLiteTransaction GetSQLiteTransaction()
        {
            return GetSQLiteTransaction(ConnectionString);
        }
        public static SQLiteTransaction CreateTransaction(string connectionString)
        {
            SQLiteConnection conn = GetSQLiteConnection(connectionString);
            conn.Open();
            return conn.BeginTransaction(true);
        }
        public static SQLiteTransaction CreateTransaction()
        {
            return CreateTransaction(ConnectionString);
        }

        public static SQLiteCommand PrepareCommand(SQLiteCommand cmd,SQLiteConnection conn,string cmdText,params object[] p)
        {
            //if (conn.State != ConnectionState.Open)
            //    conn.Open();
            cmd.Parameters.Clear();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            //cmd.CommandType = CommandType.Text;
            //cmd.CommandTimeout = 30;

            if (cmd.CommandType == CommandType.Text && !cmdText.Contains(" "))
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }

            if (p != null && p.Length > 0)
            {
                List<object> list = new List<object>();
                for (int i = 0; i < p.Length; i++)
                {
                    if (p[i] is IList)
                    {
                        IList l = p[i] as IList;
                        for (int j = 0; j < l.Count; j++)
                        {
                            list.Add(l[j]);
                        }
                    }
                    else
                    {
                        list.Add(p[i]);
                    }
                }

                if (cmd.CommandText.Contains("?"))
                {
                    string[] temp = cmd.CommandText.Split('?');
                    for (int i = 0; i < temp.Length - 1; i++)
                    {
                        temp[i] = temp[i] + "@p" + (i + 1).ToString();
                    }
                    StringBuilder sb = new StringBuilder();
                    for (int i = 0; i < temp.Length; i++)
                    {
                        sb.Append(temp[i]);
                    }
                    cmd.CommandText = sb.ToString();
                }

                string[] tempStr = cmd.CommandText.Split('@');
                for (int i = 0; i < list.Count; i++)
                {
                    DbParameter parameter = cmd.CreateParameter();
                    if (tempStr[i + 1].IndexOfAny(SplitChars) > -1)
                    {
                        if (cmd.Parameters.Contains("@" + tempStr[i + 1].Substring(0,tempStr[i + 1].IndexOfAny(SplitChars))))
                            continue;
                        parameter.ParameterName = "@" + tempStr[i + 1].Substring(0,tempStr[i + 1].IndexOfAny(SplitChars));
                    }
                    else
                    {
                        if (cmd.Parameters.Contains("@" + tempStr[i + 1]))
                            continue;
                        parameter.ParameterName = "@" + tempStr[i + 1];
                    }
                    parameter.Value = GetDbValue(list[i]);
                    cmd.Parameters.Add(parameter);
                }
            }
            return cmd;
        }
        public static SQLiteCommand PrepareCommand(this SQLiteTransaction trans,params object[] p)
        {
            return PrepareCommand(PrepareCommand(trans),cmdText,p);
        }
        public static SQLiteCommand PrepareCommand(this SQLiteCommand cmd,CommandType commandType,params object[] p)
        {
            cmd.CommandType = commandType;
            return PrepareCommand(cmd,cmd.Connection,params object[] p)
        {
            return PrepareCommand(cmd,p);
        }
        public static SQLiteCommand PrepareCommand(this SQLiteConnection conn,params object[] p)
        {
            return PrepareCommand(conn.CreateCommand(),cmd.CommandText,p);
        }
        public static SQLiteCommand PrepareCommand(this SQLiteTransaction trans)
        {
            if (trans == null)
            {
                return GetSQLiteConnection().CreateCommand();
            }
            else
            {
                SQLiteCommand cmd = trans.Connection.CreateCommand();
                cmd.Transaction = trans;
                return cmd;
            }
        }

        public static DataSet ExecuteDataSet(this SQLiteTransaction trans,params object[] p)
        {
            return ExecuteDataSet(PrepareCommand(trans),p);
        }
        public static DataSet ExecuteDataSet(SQLiteCommand cmd,params object[] p)
        {
            DataSet ds = new DataSet();
            PrepareCommand(cmd,commandType,p);
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            da.Fill(ds);
            return ds;
        }
        public static DataSet ExecuteDataSet(SQLiteCommand cmd,params object[] p)
        {
            return ExecuteDataSet(cmd,CommandType.Text,p);
        }
        public static DataSet ExecuteDataSet(CommandType commandType,params object[] p)
        {
            DataSet ds = new DataSet();
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandType = commandType;
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                PrepareCommand(cmd,connection,p);
                SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                da.Fill(ds);
            }
            return ds;
        }
        public static DataSet ExecuteDataSet(string cmdText,params object[] p)
        {
            return ExecuteDataSet(CommandType.Text,p);
        }

        public static DataTable ExecuteDataTable(this SQLiteTransaction trans,params object[] p)
        {
            return ExecuteDataTable(PrepareCommand(trans),p);
        }
        public static DataTable ExecuteDataTable(SQLiteCommand cmd,p).Tables[0];
        }
        public static DataTable ExecuteDataTable(SQLiteCommand cmd,params object[] p)
        {
            return ExecuteDataTable(cmd,p);
        }
        public static DataTable ExecuteDataTable(CommandType commandType,params object[] p)
        {
            return ExecuteDataSet(commandType,p).Tables[0];
        }
        public static DataTable ExecuteDataTable(string cmdText,params object[] p)
        {
            return ExecuteDataSet(cmdText,p).Tables[0];
        }

        public static DataRow ExecuteDataRow(this SQLiteTransaction trans,params object[] p)
        {
            return ExecuteDataRow(PrepareCommand(trans),p);
        }
        public static DataRow ExecuteDataRow(SQLiteCommand cmd,params object[] p)
        {
            DataSet ds = ExecuteDataSet(cmd,p);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0].Rows[0];
            return null;
        }
        public static DataRow ExecuteDataRow(SQLiteCommand cmd,params object[] p)
        {
            return ExecuteDataRow(cmd,p);
        }
        public static DataRow ExecuteDataRow(CommandType commandType,params object[] p)
        {
            DataSet ds = ExecuteDataSet(commandType,p);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0].Rows[0];
            return null;
        }
        public static DataRow ExecuteDataRow(string cmdText,params object[] p)
        {
            return ExecuteDataRow(CommandType.Text,p);
        }

        public static int ExecuteNonQuery(this SQLiteTransaction trans,params object[] p)
        {
            return ExecuteNonQuery(PrepareCommand(trans),p);
        }
        public static int ExecuteNonQuery(SQLiteCommand cmd,params object[] p)
        {
            PrepareCommand(cmd,p);
            if (cmd.Transaction == null)
            {
                int i = 0;
                if (cmd.SureOpen())
                {
                    lock (writeLock)
                        i = cmd.ExecuteNonQuery();
                    cmd.SureClose();
                }
                else
                {
                    lock (writeLock)
                        i = cmd.ExecuteNonQuery();
                }
                return i;
            }
            else
            {
                return cmd.ExecuteNonQuery();
            }
        }
        public static int ExecuteNonQuery(SQLiteCommand cmd,params object[] p)
        {
            return ExecuteNonQuery(cmd,p);
        }
        public static int ExecuteNonQuery(CommandType commandType,params object[] p)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandType = commandType;
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                PrepareCommand(cmd,p);
                connection.SureOpen();
                lock (writeLock)
                    return cmd.ExecuteNonQuery();
            }
        }
        public static int ExecuteNonQuery(string cmdText,params object[] p)
        {
            return ExecuteNonQuery(CommandType.Text,p);
        }

        public static SQLiteDataReader ExecuteReader(this SQLiteTransaction trans,params object[] p)
        {
            return ExecuteReader(PrepareCommand(trans),p);
        }
        public static SQLiteDataReader ExecuteReader(SQLiteCommand cmd,p);
            if (cmd.SureOpen())
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            else
                return cmd.ExecuteReader();
        }
        public static SQLiteDataReader ExecuteReader(SQLiteCommand cmd,params object[] p)
        {
            return ExecuteReader(cmd,p);
        }
        public static SQLiteDataReader ExecuteReader(CommandType commandType,params object[] p)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandType = commandType;
            SQLiteConnection connection = GetSQLiteConnection();
            try
            {
                PrepareCommand(cmd,p);
                if (cmd.SureOpen())
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                else
                    return cmd.ExecuteReader();
            }
            catch
            {
                connection.Close();
                throw;
            }
        }
        public static SQLiteDataReader ExecuteReader(string cmdText,params object[] p)
        {
            return ExecuteReader(CommandType.Text,p);
        }

        public static object ExecuteScalar(this SQLiteTransaction trans,params object[] p)
        {
            return ExecuteScalar(PrepareCommand(trans),p);
        }
        public static object ExecuteScalar(SQLiteCommand cmd,p);
            object o = null;
            if (cmd.SureOpen())
            {
                o = cmd.ExecuteScalar();
                cmd.SureClose();
            }
            else
            {
                o = cmd.ExecuteScalar();
            }
            return o;
        }
        public static object ExecuteScalar(SQLiteCommand cmd,params object[] p)
        {
            return ExecuteScalar(cmd,p);
        }
        public static object ExecuteScalar(CommandType commandType,p);
                connection.SureOpen();
                return cmd.ExecuteScalar();
            }
        }
        public static object ExecuteScalar(string cmdText,params object[] p)
        {
            return ExecuteScalar(CommandType.Text,p);
        }


        public static int ExecuteInsert(this SQLiteTransaction trans,string tableName,params Dictionary<string,object>[] dic)
        {
            return ExecuteInsert(PrepareCommand(trans),tableName,dic);
        }
        public static int ExecuteInsert(this SQLiteCommand cmd,object>[] dic)
        {
            cmd.Parameters.Clear();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from " + tableName + " where 1=2";
            SQLiteDataAdapter a = new SQLiteDataAdapter(cmd);
            SQLiteCommandBuilder b = new SQLiteCommandBuilder(a);
            DataSet ds = new DataSet();
            a.Fill(ds);
            for (int i = 0; i < dic.Length; i++)
            {
                DataRow dr = ds.Tables[0].NewRow();
                IDictionaryEnumerator Mycollection = dic[i].GetEnumerator();
                while (Mycollection.MoveNext())
                {
                    dr[Mycollection.Key.ToString()] = Mycollection.Value != null ? (Mycollection.Value.ToString() != "" ? Mycollection.Value : DBNull.Value) : DBNull.Value;
                }
                ds.Tables[0].Rows.Add(dr);
            }
            if (cmd.Transaction == null)
            {
                lock (writeLock)
                    a.Update(ds);
            }
            else
            {
                a.Update(ds);
            }
            return dic.Length;
        }

        public static int ExecuteInsert(string tableName,object>[] dic)
        {
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                SQLiteDataAdapter a = new SQLiteDataAdapter("select * from " + tableName + " where 1=2",connection);
                SQLiteCommandBuilder b = new SQLiteCommandBuilder(a);
                DataSet ds = new DataSet();
                a.Fill(ds);
                for (int i = 0; i < dic.Length; i++)
                {
                    DataRow dr = ds.Tables[0].NewRow();
                    IDictionaryEnumerator Mycollection = dic[i].GetEnumerator();
                    while (Mycollection.MoveNext())
                    {
                        dr[Mycollection.Key.ToString()] = Mycollection.Value != null ? (Mycollection.Value.ToString() != "" ? Mycollection.Value : DBNull.Value) : DBNull.Value;
                    }
                    ds.Tables[0].Rows.Add(dr);
                }
                lock (writeLock)
                    a.Update(ds);
                return dic.Length;
            }
        }

        public static void ExecuteUpdate(this SQLiteTransaction trans,Dictionary<string,object> dic,string pkName)
        {
            ExecuteUpdate(PrepareCommand(trans),dic,pkName);
        }
        public static void ExecuteUpdate(this SQLiteCommand cmd,string pkName)
        {
            cmd.Parameters.Clear();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from " + tableName + " where " + pkName + "=@p";
            cmd.Parameters.AddWithValue("@p",dic[pkName]);
            SQLiteDataAdapter a = new SQLiteDataAdapter(cmd);
            SQLiteCommandBuilder b = new SQLiteCommandBuilder(a);
            DataSet ds = new DataSet();
            a.Fill(ds);
            DataRow dr = ds.Tables[0].Rows[0];
            IDictionaryEnumerator Mycollection = dic.GetEnumerator();
            while (Mycollection.MoveNext())
            {
                dr[Mycollection.Key.ToString()] = Mycollection.Value != null ? (Mycollection.Value.ToString() != "" ? Mycollection.Value : DBNull.Value) : DBNull.Value;
            }
            if (cmd.Transaction == null)
            {
                lock (writeLock)
                    a.Update(ds);
            }
            else
            {
                a.Update(ds);
            }
        }

        public static void ExecuteUpdate(this Dictionary<string,string pkName)
        {
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                SQLiteCommand command = new SQLiteCommand();
                PrepareCommand(command,"select * from " + tableName + " where " + pkName + "=@p",dic[pkName]);
                SQLiteDataAdapter a = new SQLiteDataAdapter(command);
                SQLiteCommandBuilder b = new SQLiteCommandBuilder(a);
                DataSet ds = new DataSet();
                a.Fill(ds);
                DataRow dr = ds.Tables[0].Rows[0];
                IDictionaryEnumerator Mycollection = dic.GetEnumerator();
                while (Mycollection.MoveNext())
                {
                    dr[Mycollection.Key.ToString()] = Mycollection.Value != null ? (Mycollection.Value.ToString() != "" ? Mycollection.Value : DBNull.Value) : DBNull.Value;
                }
                lock (writeLock)
                    a.Update(ds);
            }
        }




        /// <summary>  
        /// 查询数据库中的所有数据类型信息  
        /// </summary>  
        /// <returns></returns>  
        public static DataTable GetSchema()
        {
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                DataTable data = connection.GetSchema("TABLES");
                connection.Close();
                //foreach (DataColumn column in data.Columns)  
                //{  
                //    Console.WriteLine(column.ColumnName);  
                //}  
                return data;
            }
        }

        public static string GetPageSql(string tableName,string columns,int pageSize,int currentPageIndex,string condition,bool> dic)
        {
            return "select " + columns + " from " + tableName + " where " + condition + " order by " + GetSortString(dic,false) + " LIMIT " + (currentPageIndex * pageSize).ToString() + "," + ((currentPageIndex + 1) * pageSize).ToString();
        }
        public static string GetPageSql(string tableName,string sort)
        {
            return "select " + columns + " from " + tableName + " where " + condition + " order by " + sort + " LIMIT " + (currentPageIndex * pageSize).ToString() + "," + ((currentPageIndex + 1) * pageSize).ToString();
        }

        public static string GetPageSql(string tableName,int currentSize," + ((currentPageIndex * pageSize) + currentSize).ToString();
        }
        public static string GetPageSql(string tableName," + ((currentPageIndex * pageSize) + currentSize).ToString();
        }


        public static bool CreateDB(string dbPath)
        {
            if (System.IO.File.Exists(dbPath))
            {
                return false;
            }
            else
            {
                System.Data.SQLite.SQLiteConnection.CreateFile(dbPath);
                return true;
            }
            //using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath + ";"))
            //{
            //    connection.Open();
            //    using (SQLiteCommand command = new SQLiteCommand(connection))
            //    {
            //        try
            //        {
            //            command.CommandText = "CREATE TABLE Demo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE)";
            //            command.ExecuteNonQuery();
            //            command.CommandText = "DROP TABLE Demo";
            //            command.ExecuteNonQuery();
            //            return true;
            //        }
            //        catch
            //        {
            //            return false;
            //        }
            //    }
            //}
        }


        public static bool CreateLogInfo(string dbPath)
        {
            SQLiteHelper.CreateDB(dbPath);
            using (SQLiteConnection conn = new SQLiteConnection("Data Source=" + dbPath + ";"))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandText = "select count(*) from sqlite_master where type='table' and tbl_name='LogInfo'";
                    lock (writeLock)
                    {
                        conn.Open();
                        int i = int.Parse(cmd.ExecuteScalar().ToString());
                        if (i == 1) return false;
                        cmd.CommandText = "CREATE TABLE LogInfo (LogID integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,UserName nvarchar(60),LogGroup TINYINT,LogType nvarchar(100),LogLevel TINYINT,LogTarget nvarchar(100),LogContent nvarchar(4000),Comment nvarchar(2000),TheTime datetime DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime')),Status TINYINT);";
                        cmd.ExecuteNonQuery();
                    }
                    return true;
                }
            }
        }

        public static SQLiteDataReader GetLogInfo(int pageSize,string sort)
        {
            return ExecuteReader(GetPageSql("LogInfo","*",pageSize,currentPageIndex,condition,sort));
        }

    }
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!