SQLite+Dapper框架练习

助手类：

using Dapper;
using DapperExtensions;
using DapperExtensions.Mapper;
using DapperExtensions.Sql;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Linq;
using System.Reflection;
using System.Text;
using Tools;
//除Dapper外需要添加引用  1.System.Data.SQLite.dll  2.SQLite.Interop.dll
namespace demo
{
    public class SqliteHelper
    {
        private static Database SqliteConnection;
        private static string connectionSqlite = string.Empty;

        public SqliteHelper(string connStr)
        {
            connectionSqlite = connStr;
            Initial();
        }

        private void Initial()
        {
            try
            {
                SqliteConnection = CreateConnection();
            }
            catch (Exception ex)
            {
                ex.ToString();
            }
        }

        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <returns></returns>
        public static Database CreateConnection()
        {
            DbConnection conn = GetDbConnection();
            var sqlliteconfig = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new SqliteDialect());
            var sqlliteGenerator = new SqlGeneratorImpl(sqlliteconfig);
            return new Database(conn, sqlliteGenerator);
        }



        public static bool Delete<T>(T obj, IDbTransaction tran = null, int? commandTimeout = null) where T : class
        {
            return SqliteConnection.Delete(obj, tran, commandTimeout);
        }

        public bool Delete<T>(IEnumerable<T> list, IDbTransaction tran = null, int? commandTimeout = null) where T : class
        {
            return SqliteConnection.Delete(list, tran, commandTimeout);
        }
        public void Dispose()
        {
            if (SqliteConnection != null)
            {
                SqliteConnection.Dispose();
                GC.Collect();
                GC.WaitForFullGCComplete();
            }
        }
        public T Get<T>(string id, IDbTransaction tran = null, int? commandTimeout = null) where T : class
        {
            return SqliteConnection.Get<T>(id, tran, commandTimeout);
        }

        public IEnumerable<T> GetAll<T>(object predicate = null, IList<ISort> sort = null, IDbTransaction tran = null, int? commandTimeout = null, bool buffered = true) where T : class
        {
            return SqliteConnection.GetList<T>(predicate, sort, tran, commandTimeout, buffered);
        }
        public IEnumerable<T> GetPage<T>(object predicate, IList<ISort> sort, int page, int pagesize, IDbTransaction tran = null, int? commandTimeout = null, bool buffered = true) where T : class
        {
            return SqliteConnection.GetPage<T>(predicate, sort, page, pagesize, tran, commandTimeout, buffered);
        }
        public static dynamic Insert<T>(T obj, IDbTransaction tran = null, int? commandTimeout = null) where T : class
        {
            return SqliteConnection.Insert(obj, tran, commandTimeout);
        }

        public void Insert<T>(IEnumerable<T> list, IDbTransaction tran = null, int? commandTimeout = null) where T : class
        {
            SqliteConnection.Insert(list, tran, commandTimeout);
        }
        public static bool Update<T>(T obj, IDbTransaction tran = null, int? commandTimeout = null, bool ignoreAllKeyProperties = true) where T : class
        {
            return SqliteConnection.Update(obj, tran, commandTimeout, ignoreAllKeyProperties);
        }

        public bool Update<T>(IEnumerable<T> list, IDbTransaction tran = null, int? commandTimeout = null, bool ignoreAllKeyProperties = true) where T : class
        {
            return SqliteConnection.Update(list, tran, commandTimeout, ignoreAllKeyProperties);
        }
        //public List<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        //{
        //    return SqliteConnection.Connection.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType).AsList();
        //}
        public int Execute<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            return SqliteConnection.Connection.Execute(sql, param, transaction, commandTimeout, commandType);
        }
        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <returns></returns>
        public static DbConnection GetDbConnection()
        {
            return new SQLiteConnection(connectionSqlite, true);
        }

        //public dynamic Insert<T>(T obj, IDbTransaction tran = null, int? commandTimeout = null) where T : class
        //{
        //    return Connection.Insert(obj, tran, commandTimeout);
        //}

        /// <summary>
        /// 查询操作
        /// </summary>
        /// <typeparam name="T">返回集合的类型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数化值</param>
        /// <returns></returns>
        /// 
        public static IEnumerable<T> Query<T>(string sql, object param = null)
        {
            try
            {
                lock (_lock)
                {
                    IEnumerable<T> _list = default(IEnumerable<T>);
                    if (!string.IsNullOrEmpty(sql))
                    {
                        using (DbConnection conn = GetDbConnection())
                        {
                            _list = conn.Query<T>(sql, param);
                        }
                    }
                    return _list;
                }
            }
            catch (Exception ex)
            {
                return null;
            }

        }

        /// <summary>
        /// 和Query<T>的区别是 T之后返回能转换为T类型的集合，这里能返回所有。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static IEnumerable<dynamic> Query(string sql, object param = null)
        {
            try
            {
                lock (_lock)
                {
                    using (DbConnection conn = GetDbConnection())
                    {
                        return conn.Query(sql, param);
                    }
                }
            }
            catch (Exception ex)
            {
                return null;
            }

        }
        /// <summary>
        /// 执行存储过程查询操作
        /// </summary>
        /// <typeparam name="T">返回集合的类型</typeparam>
        /// <param name="storedName">存储过程</param>
        /// <param name="param">参数化值</param>
        /// <returns></returns>
        public static IEnumerable<T> QueryStored<T>(string storedName, object param = null)
        {
            IEnumerable<T> _list = default(IEnumerable<T>);
            if (!string.IsNullOrEmpty(storedName))
            {
                using (DbConnection conn = GetDbConnection())
                {
                    _list = conn.Query<T>(storedName, commandType: CommandType.StoredProcedure);
                }
            }
            return _list;
        }

        /// <summary>
        /// 查询操作返回默认第一条数据(如返回null则创建默认类型)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T FirstOrDefault<T>(string sql, object param = null)
        {
            var model = default(T);
            if (!string.IsNullOrEmpty(sql))
            {
                using (DbConnection conn = GetDbConnection())
                {
                    model = conn.Query<T>(sql, param).FirstOrDefault();
                }
            }
            return model == null ? Activator.CreateInstance<T>() : model;
        }

        public static List<IEnumerable<dynamic>> QueryMultiple(string sql, int readTimes, object param = null)
        {
            List<IEnumerable<dynamic>> resutl = new List<IEnumerable<dynamic>>();
            using (DbConnection conn = GetDbConnection())
            {
                using (var multi = conn.QueryMultiple(sql, param))
                {
                    if (!multi.IsConsumed)
                    {
                        for (int i = 0; i < readTimes; i++)
                        {
                            resutl.Add(multi.Read());
                        }
                    }
                }
            }
            return resutl;
        }

        private static object _lock = new object();

        /// <summary>
        /// 非查询操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int Excute(string sql, object param = null)
        {
            try
            {
                lock (_lock)
                {
                    using (DbConnection conn = GetDbConnection())
                    {
                        return conn.Execute(sql, param);
                    }
                }
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

       
        /// <summary>
        /// 返回首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, object param = null)
        {
            lock (_lock)
            {
                using (DbConnection conn = GetDbConnection())
                {
                    return conn.ExecuteScalar(sql, param);
                }
            }
        }
    }
}

调用方法：

 //这里配置一下连接语句
        public const string configUrl = @"Data Source=D:\kkdownload\牛牛上机题\WindowsFormsApp1\WindowsFormsApp1\sqlite.db;Version=3;Pooling=true;FailIfMissing=false;";
        SqliteHelper SqliteHelper = new SqliteHelper(configUrl);
        private void button1_Click(object sender, EventArgs e)
        {//Base是表格对应的实体类，需要自己创建
            var list = SqliteHelper.Query<Base>("select * from 表名 where 列名=3", null);
            if (list != null)
            {
                var List = list.ToList();

                foreach (var item in List)
                {
                    item.列名（实体类的属性名） = 2;//这里修改对象的属性值，相当于根据对象的属性值去修改数据库中表格的数据
                  SqliteHelper.Update<Base>(item);//注意新用法，对象替代sql语句
                    
                }

            }

          }