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

Sqlite 前端之家
前端之家收集整理的这篇文章主要介绍了一个功能齐全的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));
        }

    }
}

猜你在找的Sqlite相关文章

SQLite学习笔记
安装 在Windows上安装SQLite。 访问官网下载下Precompliled Binaries for Windows的两个压...
windows上如何安装Sqlite
一、安装 下载地址：http://www.sqlite.org/download.html 将Precompiled Binaries for Wi...
【Android数据存储】SQLite使用实例(附源码)
实例: 会员信息管理 功能：1.查看数据库 2.清空数据库 3.增加会员 4.删除会员 5.更新会员 ...
SQLite数据库管理
SQLite3常用命令
加密你的SQLite
关于SQLite SQLite是一个轻量的、跨平台的、开源的数据库引擎，它的在读写效率、消耗总量、...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite 这么娇小可爱，不多了解点都不行啊
（点击上方蓝字，快速关注我们) 作者：空之境界 网址：http://supermao.cn/sqlitezhe-yao-...
SQLITE3 使用总结
SQLITE3 使用总结 投稿：hebedich 字体：[ 增加  减小] 类型：转载 时间：2014-08-19 Sql...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问