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