【C#】使用ADO.NET/Dapper访问Sqlite数据库，SqliteHelper帮助类

1. 使用ADO.NET

这个就比较简单了，用过sqlserver的人都知道访问数据库的时候一般都会有个SqlHelper的帮助类，这里就依葫芦画瓢写了个SqliteHelper，很简单：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Thumbnail
{
    public class SqliteHelper
    {
        string dbPath = "E:\\thumbnail.db";
        string conStr = "Data Source=E:\\thumbnail.db";
        static SqliteHelper _instance;
        public static SqliteHelper Instance
        {
            get
            {
                if (_instance == null)
                    _instance = new SqliteHelper();
                return _instance;
            }
        }

        string cmdCreateTable = "CREATE TABLE IF NOT EXISTS Thumbnails (Id integer PRIMARY KEY, OrginFilePath nvarchar NOT NULL UNIQUE, ThumbnailPath nvarchar NOT NULL,LastUpdateTime datetime NOT NULL);";
        string createIndex = "create index if not exists originIndex on Thumbnails (OrginFilePath)";
        private SqliteHelper() { }
        
        //如果需要初始化数据库可调用此方法
        public void Init()
        {
            if (!File.Exists(dbPath))
            {
	            //如果数据库文件不存在，则创建
                SQLiteConnection.CreateFile(dbPath);
            }
            List<TransModel> models = new List<TransModel>();
            models.Add(new TransModel { CmdText = cmdCreateTable });
            models.Add(new TransModel { CmdText = createIndex });
            bool res = ExecTransaction(models);//一个事务：如果表不存在则创建，如果索引不存在则创建
        }
        //执行非查询的sql语句，返回受影响的行数
        public int ExecuteNonQuery(string cmdText, params SQLiteParameter[] paramters)
        {
            using (SQLiteConnection con = new SQLiteConnection(conStr))
            {
                try
                {
                    con.Open();
                    using (SQLiteCommand cmd = new SQLiteCommand(con))
                    {
                        cmd.CommandText = cmdText;
                        if (paramters != null)
                            cmd.Parameters.AddRange(paramters);
                        return cmd.ExecuteNonQuery();
                    }
                }
                catch (SQLiteException ex)
                {
                    //_log.E(ex);
                }
                return -1;
            }
        }
        //执行非查询的sql语句，返回第一行第一列的值
        public object ExecuteScalar(string cmdText, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection(conStr))
            {
                try
                {
                    conn.Open();
                    using (SQLiteCommand cmd = new SQLiteCommand(conn))
                    {
                        cmd.CommandText = cmdText;
                        cmd.Parameters.AddRange(parameters);
                        return cmd.ExecuteScalar();
                    }
                }
                catch (SQLiteException ex)
                {
                    //_log.E(ex);
                }
                return null;
            }
        }
        //执行查询语句，返回查询到的结果
        public DataTable GetDataTable(string cmdText, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection(conStr))
            {
                try
                {
                    conn.Open();
                    using (SQLiteCommand cmd = new SQLiteCommand(conn))
                    {
                        DataTable dt = new DataTable();
                        cmd.CommandText = cmdText;
                        cmd.Parameters.AddRange(parameters);
                        SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                        da.Fill(dt);
                        return dt;
                    }
                }
                catch (SQLiteException ex)
                {
                    //_log.E(ex);

                }
                return null;
            }
        }
        //执行事务，如果出现异常则回滚
        public bool ExecTransaction(List<TransModel> models)
        {
            using (SQLiteConnection con = new SQLiteConnection(conStr))
            {
                try
                {
                    con.Open();
                    using (SQLiteTransaction trans = con.BeginTransaction())
                    {
                        using (SQLiteCommand cmd = new SQLiteCommand(con))
                        {
                            cmd.Transaction = trans;
                            try
                            {
                                foreach (var model in models)
                                {
                                    cmd.CommandText = model.CmdText;
                                    if (model.Paras != null)
                                        cmd.Parameters.AddRange(model.Paras);
                                    cmd.ExecuteNonQuery();
                                }
                                trans.Commit();
                                return true;
                            }
                            catch (SQLiteException ex)
                            {
                                trans.Rollback();
                                //_log.E(ex);
                            }

                        }
                    }

                }
                catch (SQLiteException ex)
                {
                   // _log.E(ex);
                }
            }
            return false;
        }
    }
    public class TransModel
    {
        public string CmdText { get; set; }
        public SQLiteParameter[] Paras { get; set; }
    }
}

这里有几点需要注意，所有之前的SqlConnection都换成SQLiteConnection，command也要用SQLiteCommand，即一切与SQL相关到都要改为SQLite对应的，也包括要用SQLiteParameter。最后用try-catch捕获异常时一定要catch SQLiteExcepiton这个异常，不要捕获Exception否则你拿到的异常对象就会是null。

温故而知新《ADO.NET架构》：

1. ExecuteNonQuery：执行非查询的sql语句，返回受影响的行数
2. ExecuteScalar：执行非查询的sql语句，返回第一行第一列的值
3. ExecuteReader：用来执行查询sql，按需读取（通过while循环），读取期间连接一直打开
4. DataAdapter：用来执行查询sql，一次性读取数据并加载到内存里，通过fill方法填充到DataTable里，查询量太多时可能会OOM

2. 使用Dapper

与ADO.NET明显的一个不同是Dapper操作数据库时不需要自己手动open（事务操作除外）。

基类

    
    public class BaseDAL
    {
        public static T Execute<T>(string connectionString,Func<SQLiteConnection,T> func)
        {
            using(SQLiteConnection con=new SQLiteConnection(connectionString))
            {
                return func(con);
            }
        }
        public static T ExecuteTransaction<T>(string connectionString,Func<SQLiteConnection,SQLiteTransaction,T> func)
        {
            using(var con=new SQLiteConnection(connectionString))
            {
                con.Open();//执行事务时才需要手动open
                using(var trans=con.BeginTransaction())
                {
                    return func(con, trans);
                }
            }
        }
        public static void ExecuteTransaction(string connectionString, Action<SQLiteConnection,SQLiteTransaction> func)
        {
            using (var con = new SQLiteConnection(connectionString))
            {
                con.Open();//执行事务时才需要手动open
                using (var trans = con.BeginTransaction())
                {
                    func(con,trans);
                }
            }
        }
    }

子类：

    public class RegionDAL : BaseDAL
    {
        private static string conStr = ConnectString.StrFoundation;

        public static List<RegionVo> Search(RegionSo so)
        {
            string sql = "select * from JS_REGION where 1=1";
            if (so.regionCode != null)
                sql += " and regionCode=@regionCode";
            if (so.regionLevel != null)
                sql += " and regionLevel=@regionLevel";
            if (so.regionLevels != null)
                sql += " and regionLevel in @regionLevels";
            if (so.superiorRegionCode != null)
                sql += " and superiorRegionCode=@superiorRegionCode";
            var data = Execute(conStr, (con) =>
            {
                return con.Query<RegionVo>(sql, so).ToList();
            });
            return data;
        }

        /// <summary>
        /// 同步数据（事务）
        /// </summary>
        /// <param name="list"></param>
        /// <param name="maxVersion"></param>
        /// <returns></returns>
        public static void SyncData(List<RegionVo> list, long maxVersion)
        {
            ExecuteTransaction(conStr, (con, trans) =>
           {
               var putList = list.Where(o => o.operate != NJS.Entities.operateType.DELETE).ToList();
               var deleteList = list.Where(o => o.operate == NJS.Entities.operateType.DELETE).ToList();

               var count = con.Execute("replace into JS_REGION(namePath,regionCode,regionLevel,superiorRegionCode,superiorRegionId,id) values (@namePath,@regionCode,@regionLevel,@superiorRegionCode,@superiorRegionId,@id)", putList, trans);

               count = con.Execute("delete from JS_REGION where id = @id", deleteList, trans);

               count = con.Execute("update GE_SYNC_TABLE set CUR_VERSION=@version where TABLE_NAME ='JS_REGION'", new { version = maxVersion }, trans);
               trans.Commit();
           });

        }

        public static long GetSyncVersion()
        {
            var data = Execute<long>(conStr, con =>
            {
                var syncTables = con.Query<SysSyncTable>("select CUR_VERSION as curVersion from GE_SYNC_TABLE where TABLE_NAME ='JS_REGION'");
                return syncTables.FirstOrDefault()?.curVersion ?? 0;
            });
            return data;

        }
    }

---

参考：

1. 用了Dapper之后通篇还是SqlConnection，真的看不下去了
2. 温故而知新，由ADO.NET与Dapper所联想到的