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;
}
}
}
浙公网安备 33010602011771号