DbHelper(Dapper)

public class DbHelper
    {
        static string connstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        //public static IDbConnection connection = new SqlConnection(connstr);



        /// <summary>
        /// 执行sql  增删改。
        /// 示例：
        /// sql：Insert into Users values (@UserName, @Email, @Address)
        /// model：new Users(){Email =  "123456qq.com",Address = "广州",UserName = "AAA" }
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="t"></param>
        /// <returns></returns>
        public static int Execute<T>(string sql, T t)
        {
            using (var connection = new SqlConnection(connstr))
            {
                int res = connection.Execute(sql, t);
                return res;
            }

        }


        /// <summary>
        /// 执行sql 增删改 批量。
        /// 示例：
        /// sql：Insert into Users values (@UserName, @Email, @Address)
        /// model：List<Users> usersList = new List<Users>() { new Users(){Email =  "123456qq.com",Address = "广州",UserName = "AAA" } }
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public static int ExecuteBatch<T>(string sql, List<T> list)
        {
            using (var connection = new SqlConnection(connstr))
            {
                int res = connection.Execute(sql, list);
                return res;
            }

        }


        /// <summary>
        /// 执行新增 扩展方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public static int Insert<T>(T t) where T : class
        {

            using (var connection = new SqlConnection(connstr))
            {
                int res = connection.Insert<T>(t);
                return res;
            }

        }

        /// <summary>
        /// 批量新增
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        public static bool InsertBatch<T>(List<T> list) where T : class
        {

            using (var connection = new SqlConnection(connstr))
            {
                bool res = false;
                foreach (var item in list)
                {
                    connection.Insert<T>(item);
                }
                res = true;
                return res;
            }


        }

        /// <summary>
        /// 执行修改 扩展方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public static bool Update<T>(T t) where T : class
        {
            using (var connection = new SqlConnection(connstr))
            {

                bool res = connection.Update<T>(t);
                return res;
            }


        }

        /// <summary>
        /// 执行删除 扩展方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public static bool Delete<T>(T t) where T : class
        {

            using (var connection = new SqlConnection(connstr))
            {
                bool res = connection.Delete<T>(t);
                return res;
            }


        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        public static bool DeleteBatch<T>(List<T> list) where T : class
        {
            using (var connection = new SqlConnection(connstr))
            {
                bool res = false;
                foreach (var item in list)
                {
                    connection.Delete<T>(item);
                }
                res = true;
                return res;
            }


        }




        /// <summary>
        /// 执行sql 查询 返回一个结果集
        /// 示例：
        /// sql：select * from Users where UserName=@UserName
        /// obj：new { UserName = "jack" }
        /// 多个示例：
        /// sql：select * from Users where UserID in @Ids
        /// obj：new { Ids = new int[2] { 5, 6 } }
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static List<T> Query<T>(string sql, DynamicParameters obj)
        {
            using (var connection = new SqlConnection(connstr))
            {

                var query = connection.Query<T>(sql, obj);
                return query.ToList();

            }

        }

        /// <summary>
        /// 查询第一行
        /// 示例：
        /// sql：select * from Users where UserName=@UserName
        /// obj：new { UserName = "jack" }
        /// 多个示例：
        /// sql：select * from Users where UserID in @Ids
        /// obj：new { Ids = new int[2] { 5, 6 } }
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static T QueryFirstOrDefault<T>(string sql, DynamicParameters obj)
        {
            using (var connection = new SqlConnection(connstr))
            {
                var query = connection.QueryFirstOrDefault<T>(sql, obj);
                return query;

            }


        }




        /// <summary>
        /// 查询一个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public static T Get<T>(string id) where T : class
        {
            using (var connection = new SqlConnection(connstr))
            {

                T res = connection.Get<T>(id);
                return res;

            }


        }




        /// <summary>
        /// 以条件查询 扩展
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public static List<T> GetList<T>(DynamicParameters where = null, IList<ISort> sort = null) where T : class
        {
            //obj = new { id = 2 };

            using (var connection = new SqlConnection(connstr))
            {

                List<T> res = connection.GetList<T>(where, sort).ToList();
                return res;

            }

        }




        /// <summary>
        /// 分页查询 扩展
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="allRowsCount"></param>
        /// <param name="where"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public static List<T> GetPage<T>(int pageIndex, int pageSize, out long allRowsCount, DynamicParameters where = null, IList<ISort> sort = null) where T : class
        {
            using (var connection = new SqlConnection(connstr))
            {
                List<T> res = connection.GetPage<T>(where, sort, pageIndex, pageSize).ToList();
                allRowsCount = connection.Count<T>(where);
                return res;

            }

        }





        /// <summary>
        /// 查询全部
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static List<T> GetListAll<T>() where T : class
        {
            using (var connection = new SqlConnection(connstr))
            {
                List<T> res = connection.GetList<T>().ToList();
                return res;

            }

        }



        /// <summary>
        /// Dapper通用分页方法
        /// </summary>
        /// <typeparam name="T">查询实体</typeparam>
        /// <param name="pageCriteria">查询条件</param>
        /// <returns></returns>
        public static PageDataView<T> GetPageListForSQL<T>(PageCriteria pageCriteria)
        {
            var result = new PageDataView<T>();
            string sql = "SELECT * from(SELECT " + pageCriteria.Fields + ",row_number() over(order by " + pageCriteria.Sort + ") rownum FROM " + pageCriteria.TableName + " where " + pageCriteria.Condition + ") t where rownum>@minrownum and rownum<=@maxrownum";
            string countSql = "select count(1) from " + pageCriteria.TableName + "  where " + pageCriteria.Condition;
            int minrownum = (pageCriteria.CurrentPage - 1) * pageCriteria.PageSize;
            int maxrownum = minrownum + pageCriteria.PageSize;
            var p = new DynamicParameters();
            p.Add("minrownum", minrownum);
            p.Add("maxrownum", maxrownum);
            if (pageCriteria.ParameterList != null)
            {
                foreach (var param in pageCriteria.ParameterList)
                {
                    p.Add(param.ParamName, param.ParamValue);
                }
            }
            using (var connection = new SqlConnection(connstr))
            {

                var reader = connection.QueryMultiple(sql + ";" + countSql, p);
                result.Items = reader.Read<T>().ToList();
                result.TotalNum = reader.Read<int>().First<int>();
                result.CurrentPage = pageCriteria.CurrentPage;
                result.TotalPageCount = result.TotalNum / pageCriteria.PageSize + (result.TotalNum % pageCriteria.PageSize == 0 ? 0 : 1);
                return result;
            }


        }



    }