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;
        }

    }
}