DapperHelper

Dapper初级使用，适用于Sqlite数据库（修改SQLiteConnection可以适用于其他数据库）

using Dapper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SQLite;
using System.Linq;

namespace WebApplication1
{
    public class DapperHelper<T>
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private static readonly string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        /// <summary>
        /// 查询返回列表List《T》
        /// .QueryToList("select * from students where id=@id and name=@name", new { id = 1 ,name= "张小花" })
        /// </summary>
        /// <param name="sql">查询的sql</param>
        /// <param name="param">替换参数</param>
        /// <returns></returns>

        public static List<T> QueryToList(string sql, object param = null)
        {
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                return con.Query<T>(sql, param).ToList();
            }
        }

        /// <summary>
        /// 查询返回DataTable
        /// .QueryToTable("select * from students where id=???常规sql语句")
        /// </summary>
        /// <param name="sql">查询的sql</param>
        /// <param name="param">替换参数</param>
        /// <returns></returns>

        public static DataTable QueryToTable(string sql, object param = null)
        {
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                DataTable dt = new DataTable();
                var reader = con.ExecuteReader(sql);
                dt.Load(reader);
                return dt;
            }
        }

        /// <summary>
        /// 查询第一个数据,返回一个实体T
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T QueryFirstOrNull(string sql, object param = null)
        {
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                return con.Query<T>(sql, param).ToList().SingleOrDefault();
            }
        }

        /// <summary>
        /// 执行single-增删改-("insert/delete/update T_User set username=@username where uid=@uid", new { username = "李四", uid = 1})
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns>执行影响行数 int 0or1</returns>
        public static int Execute(string sql, object param)
        {
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                return con.Execute(sql, param);
            }
        }

        /// <summary>
        /// 执行many-增删改-("insert/delete/update T_User set username=@username where uid=@uid", List《T》)
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns>执行影响行数 int 0or1+</returns>
        /// List<students> listStu = new List<students>();
        public static int ExecuteList(string sql, List<T> t)
        {
            //  listStu.Add(new students { id = 1, name = "新1", address = "年1" });
            //  listStu.Add(new students { id = 2, name = "快1", address = "乐1" });
            //  var a = DapperHelper<students>.ExecuteList("update students set name = @name, address = @address where id=@id", listStu);
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                return con.Execute(sql, t);
            }
        }

        /// <summary>
        /// 无参事务-List《stringSql》
        /// </summary>
        /// <param name="sqlarr">多条SQL</param>
        /// <param name="param">param</param>
        /// <returns></returns>
        public static int ExecuteTransaction(List<string> sqlList)
        {
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {                    
                con.Open();//在dapper中使用事务，需要手动打开连接
                using (var transaction = con.BeginTransaction())
                {
                    try
                    {
                        int result = 0;
                        foreach (var sql in sqlList)
                        {
                            result += con.Execute(sql, null, transaction);
                        }

                        transaction.Commit();
                        con.Close();//在dapper中使用事务，需要手动关闭连接
                        return result;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        con.Close();//在dapper中使用事务，需要手动关闭连接
                        return 0;
                    }
                }

            }
        }

        /// <summary>
        /// 带参事务-Dictionary.Add（"Insert into Users values (@UserName, @Email, @Address)"，new { UserName = "jack", Email = "380234234@qq.com", Address = "上海" })
        /// </summary>
        /// <param name="dic"></param>
        /// <returns></returns>
        public static int ExecuteTransactionDic(Dictionary<string, T> dic)
        {
           //Dictionary<string, students> dic = new Dictionary<string, students>();
           //dic.Add("delete from students where id=@id", new students { id = 1 });
           //dic.Add("delete from students where id=@id ", new students { id = 2 });
            using (SQLiteConnection con = new SQLiteConnection(connectionString))
            {
                con.Open();//在dapper中使用事务，需要手动打开连接
                using (var transaction = con.BeginTransaction())
                {
                    try
                    {
                        int result = 0;
                        foreach (var param in dic)
                        {
                            result += con.Execute(param.Key, param.Value, transaction);
                        }
                        transaction.Commit();
                        con.Close();//在dapper中使用事务，需要手动关闭连接

                        return result;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        con.Close();//在dapper中使用事务，需要手动关闭连接
                        return 0;
                    }
                }
            }
        }

    }
}