SQLite工具包：SQLiteHelper的全面解析

工具类前期准备：

1.System.Data.SQLite 库下载，用于C#操作SQLite的dll文件。下载地址：
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

2.SQLite可视化工具下载，用于查看SQLite库数据表数据。下载地址：
http://www.sqliteexpert.com/download.html

using Microsoft.Extensions.Configuration;using Microsoft.Extensions.Configuration.Json;using System;using System.Data;using System.Data.SQLite;using System.IO;namespace MySolution.MyProject.Utility{    public class SQLiteHelper    {        private static readonly IConfiguration _configuration;        /// <summary>        /// 构造函数        /// </summary>        static SQLiteHelper()        {            //在当前目录或者根目录中寻找appsettings.json文件            var fileName = "appsettings.json";            var directory = AppContext.BaseDirectory;            directory = directory.Replace("\", "/");            var filePath = $"{directory}/{fileName}";            if (!File.Exists(filePath))            {                var length = directory.IndexOf("/bin");                filePath = $"{directory.Substring(0, length)}/{fileName}";            }            var builder = new ConfigurationBuilder().Add(new JsonConfigurationSource { Path = fileName, ReloadOnChange = true });            _configuration = builder.Build();        }        /// <summary>        /// 设置连接字符串        /// </summary>        public static string Connectionstring        {            get { return _configuration.GetConnectionString("SqliteConnStr"); }        }        public static int ExecuteQuery(string cmdText, CommandType cmdType, params SQLiteParameter[] parameters)        {            using (SQLiteConnection conn = new SQLiteConnection(Connectionstring))            {                using (SQLiteCommand comm = conn.CreateCommand())                {                    try                    {                        conn.Open();                        comm.CommandType = cmdType;                        comm.CommandText = cmdText;                        comm.Parameters.AddRange(parameters);                        return comm.ExecuteNonQuery();                    }                    catch (Exception ex)                    {                        throw new Exception(ex.Message);                    }                    finally                    {                        if (conn != null && conn.State != ConnectionState.Closed)                            conn.Close();                    }                }            }        }        public static Object ExecuteScalar(string cmdText, CommandType cmdType, params SQLiteParameter[] parameters)        {            using (SQLiteConnection conn = new SQLiteConnection(Connectionstring))            {                using (SQLiteCommand comm = conn.CreateCommand())                {                    try                    {                        object result = comm.ExecuteScalar();                        return result;                    }                    catch (Exception ex)                    {                        throw new Exception(ex.Message);                    }                    finally                    {                        if (conn != null && conn.State != ConnectionState.Closed)                            conn.Close();                    }                }            }        }        public static DataTable ExecuteDatatable(string cmdtxt, CommandType cmdtype, params SQLiteParameter[] parameters)        {            SQLiteCommand cmd = GetCommand(cmdtxt, cmdtype, parameters);            SQLiteDataAdapter adap = new SQLiteDataAdapter(cmd);            DataTable dt = new DataTable();            adap.Fill(dt);            cmd.Parameters.Clear();            cmd.Connection.Close();            return dt;        }        public static DataSet ExecuteDataset(string cmdText, CommandType cmdType, params SQLiteParameter[] parameters)        {            SQLiteCommand command = GetCommand(cmdText, cmdType, parameters);            SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);            DataSet dataSet = new DataSet();            adapter.Fill(dataSet);            command.Parameters.Clear();            command.Connection.Close();            return dataSet;        }        public static SQLiteDataReader ExecuteDataReader(string cmdText, CommandType cmdType, params SQLiteParameter[] parameters)        {            SQLiteCommand cmd = GetCommand(cmdText, cmdType, parameters);            SQLiteDataReader result = cmd.ExecuteReader(CommandBehavior.CloseConnection);            cmd.Parameters.Clear();            cmd.Connection.Close();            return result;        }        public static DataSet Query(string SQLString)        {            using (SQLiteConnection connection = new SQLiteConnection(Connectionstring))            {                DataSet ds = new DataSet();                try                {                    connection.Open();                    SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);                    command.Fill(ds, "ds");                }                catch (System.Data.SqlClient.SqlException ex)                {                    throw new Exception(ex.Message);                }                return ds;            }        }        private static SQLiteCommand GetCommand(string cmdText, CommandType cmdType, params SQLiteParameter[] parameters)        {            SQLiteCommand cmd = new SQLiteCommand();            cmd.Connection = new SQLiteConnection(Connectionstring);            cmd.CommandText = cmdText;            cmd.CommandType = cmdType;            if (parameters != null)                foreach (SQLiteParameter p in parameters)                    cmd.Parameters.Add(p);            cmd.Connection.Open();            return cmd;        }    }}