public class DbHelper
{
static string connstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
//public static IDbConnection connection = new SqlConnection(connstr);
///
/// 执行sql 增删改。
/// 示例：
/// sql：Insert into Users values (@UserName, @Email, @Address)
/// model：new Users(){Email = "123456qq.com",Address = "广州",UserName = "AAA" }
///
///
///
///
///
public static int Execute(string sql, T t)
{
using (var connection = new SqlConnection(connstr))
{
int res = connection.Execute(sql, t);
return res;
}
}
///
/// 执行sql 增删改 批量。
/// 示例：
/// sql：Insert into Users values (@UserName, @Email, @Address)
/// model：List usersList = new List() { new Users(){Email = "123456qq.com",Address = "广州",UserName = "AAA" } }
///
///
///
///
///
public static int ExecuteBatch(string sql, List list)
{
using (var connection = new SqlConnection(connstr))
{
int res = connection.Execute(sql, list);
return res;
}
}
///
/// 执行新增 扩展方法
///
///
///
///
public static int Insert(T t) where T : class
{
using (var connection = new SqlConnection(connstr))
{
int res = connection.Insert(t);
return res;
}
}
///
/// 批量新增
///
///
///
///
public static bool InsertBatch(List list) where T : class
{
using (var connection = new SqlConnection(connstr))
{
bool res = false;
foreach (var item in list)
{
connection.Insert(item);
}
res = true;
return res;
}
}
///
/// 执行修改 扩展方法
///
///
///
///
public static bool Update(T t) where T : class
{
using (var connection = new SqlConnection(connstr))
{
bool res = connection.Update(t);
return res;
}
}
///
/// 执行删除 扩展方法
///
///
///
///
public static bool Delete(T t) where T : class
{
using (var connection = new SqlConnection(connstr))
{
bool res = connection.Delete(t);
return res;
}
}
///
/// 批量删除
///
///
///
///
public static bool DeleteBatch(List list) where T : class
{
using (var connection = new SqlConnection(connstr))
{
bool res = false;
foreach (var item in list)
{
connection.Delete(item);
}
res = true;
return res;
}
}
///
/// 执行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 } }
///
///
///
///
///
public static List Query(string sql, DynamicParameters obj)
{
using (var connection = new SqlConnection(connstr))
{
var query = connection.Query(sql, obj);
return query.ToList();
}
}
///
/// 查询第一行
/// 示例：
/// 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 } }
///
///
///
///
///
public static T QueryFirstOrDefault(string sql, DynamicParameters obj)
{
using (var connection = new SqlConnection(connstr))
{
var query = connection.QueryFirstOrDefault(sql, obj);
return query;
}
}
///
/// 查询一个实体
///
///
///
///
public static T Get(string id) where T : class
{
using (var connection = new SqlConnection(connstr))
{
T res = connection.Get(id);
return res;
}
}
///
/// 以条件查询 扩展
///
///
///
///
///
public static List GetList(DynamicParameters where = null, IList sort = null) where T : class
{
//obj = new { id = 2 };
using (var connection = new SqlConnection(connstr))
{
List res = connection.GetList(where, sort).ToList();
return res;
}
}
///
/// 分页查询 扩展
///
///
///
///
///
///
///
///
public static List GetPage(int pageIndex, int pageSize, out long allRowsCount, DynamicParameters where = null, IList sort = null) where T : class
{
using (var connection = new SqlConnection(connstr))
{
List res = connection.GetPage(where, sort, pageIndex, pageSize).ToList();
allRowsCount = connection.Count(where);
return res;
}
}
///
/// 查询全部
///
///
///
public static List GetListAll() where T : class
{
using (var connection = new SqlConnection(connstr))
{
List res = connection.GetList().ToList();
return res;
}
}
///
/// Dapper通用分页方法
///
/// 查询实体
/// 查询条件
///
public static PageDataView GetPageListForSQL(PageCriteria pageCriteria)
{
var result = new PageDataView();
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().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;
}
}
}