C#工具类（一）：使用.Net封装SqliteHelper

使用.Net封装Sqlite数据库操作工具类

需要应用第三方库System.Data.SQLite.dll

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace Kinglong.App.Tools
{
    public static class SqliteHelper
    {
        #region
        private static string connectionString = @"Data Source=C:\Kinglong\Data.db";
        private static SQLiteConnection connection;
        private static object SqliteLocker = new object(); //加锁防止处理多线程安全问题
        public static SQLiteConnection Connection
        {
            get
            {
                lock (SqliteLocker)
                {
                    if (connection == null)
                    {
                        connection = new SQLiteConnection(connectionString);
                        connection.Open();
                    }
                    else if (connection.State == System.Data.ConnectionState.Closed)
                    {
                        try
                        {
                            connection.Open();
                        }
                        catch
                        {
                            connection = new SQLiteConnection(connectionString);
                            connection.Open();
                        }
                    }
                    else if (connection.State == System.Data.ConnectionState.Broken)
                    {
                        connection.Close();
                        connection.Open();
                    }
                    else if (connection.State == System.Data.ConnectionState.Open)
                    {
                        connection.Close();
                        connection = new SQLiteConnection(connectionString);
                        connection.Open();

                    }
                    return connection;
                }
            }
            set 
            { 
                lock(SqliteLocker)
                {
                    SQLiteHelper.connection = value;
                }
            }
        }

        public static SQLiteConnection NonManageConn = new SQLiteConnection(connectionString);
        public static int ExecuteCmdNoClose(string safeSql)
        {
            lock (SqliteLocker)
            {
                SQLiteCommand cmd = new SQLiteCommand(safeSql, NonManageConn);
                int result = cmd.ExecuteNonQuery();
                return result;
            }
        }
        public static int ExecuteCmdNoClose(string sql, params SQLiteParameter[] values)
        {
            SQLiteCommand cmd = new SQLiteCommand(sql, NonManageConn);
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteNonQuery();
        }
        
        #endregion


        /// 

        /// 创建数据表
        /// 
        public static void CreateTable()
        {
            string sql = "";
            SQLiteCommand cmd;
            try
            {
                if (!IsExistTable(Connection, "TableTest"))
                {
                    Connection.Close();
                    Connection.Open();
                    sql = "create table TableTest(Id nvarchar(50),Name nvarchar(64),CreateTime datetime,IsDelete int)";
                    cmd = new SQLiteCommand(sql, Connection);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("CreateTable:" + ex.Message);
            }
            finally
            {
                Connection.Close();
            }
        }

        /// 

        /// 是否存在表
        /// 
        private static bool IsExistTable(SQLiteConnection conn, string tableName)
        {
            try
            {
                conn.Close();
                conn.Open();
                string sql = "SELECT count(1) FROM sqlite_master where type='table' and name='" + tableName + "'";
                SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                int tableCount = Convert.ToInt32(cmd.ExecuteScalar());
                if (tableCount > 0) return true;
                return false;
            }
            catch (Exception ex)
            {
                throw new Exception("IsExistTable:" + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        /// 

        /// 执行非查询命令,返回受影响函数
        /// 
        public static int ExecuteNonQuery(string safeSql)
        {
            int result;
            lock (SqliteLocker)
            {
                SQLiteCommand cmd = new SQLiteCommand(safeSql, Connection);
                result = cmd.ExecuteNonQuery();        
            }
            return result;
        }

        public static int ExecuteNonQuery(string sql, params SQLiteParameter[] values)
        {
            lock (SqliteLocker)
            {
                SQLiteCommand cmd = new SQLiteCommand(sql, Connection);
                cmd.Parameters.AddRange(values);
                return cmd.ExecuteNonQuery();
            }
        }

        public static int ExecuteScalar(string safeSql)
        {
            lock (SqliteLocker)
            {
                SQLiteCommand cmd = new SQLiteCommand(safeSql, Connection);
                int result = Convert.ToInt32(cmd.ExecuteScalar());
                return result;
            }
        }

        public static int ExecuteScalar(string sql, params SQLiteParameter[] values)
        {
            lock (SqliteLocker)
            {
                SQLiteCommand cmd = new SQLiteCommand(sql, Connection);
                cmd.Parameters.AddRange(values);
                int result = Convert.ToInt32(cmd.ExecuteScalar());
                return result;
            }
        }

        public static SQLiteDataReader ExecuteReader(string safeSql)
        {
            lock (SqliteLocker)
            {
                SQLiteCommand cmd = new SQLiteCommand(safeSql, Connection);
                //SQLiteDataReader reader = cmd.ExecuteReader();
                SQLiteDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
        }

        public static SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] values)
        {
            lock (SqliteLocker)
            {
                SQLiteCommand cmd = new SQLiteCommand(sql, Connection);
                cmd.Parameters.AddRange(values);
                SQLiteDataReader reader = cmd.ExecuteReader();
                return reader;
            }
        }

        public static DataTable ExecuteDataSet(string safeSql)
        {  
            DataSet ds = new DataSet();
            try
            {

                lock (SqliteLocker)
                {
                    SQLiteCommand cmd = new SQLiteCommand(safeSql, Connection);
                    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                    da.Fill(ds);
                }

                return ds.Tables[0];
            }
            catch (Exception e)
            {
                if (e.Message.ToString() == "Connection was closed, statement was terminated")
                {
                    string connectionString = "";
                    connection = new SQLiteConnection(connectionString);
                    connection.Open();
                    return ExecuteDataSet(safeSql);
                }

            } return new DataTable();


        }

        public static DataTable ExecuteDataSet(string sql, params SQLiteParameter[] values)
        {
            lock (SqliteLocker)
            {
                DataSet ds = new DataSet();
                try
                {
                    SQLiteCommand cmd = new SQLiteCommand(sql, Connection);
                    cmd.Parameters.AddRange(values);
                    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                    da.Fill(ds);
                    return ds.Tables[0];
                }
                catch (Exception)
                {

                }
                return new DataTable();
            }

        }
        
    }
}