NetCore中Dapper访问数据库
Dapper访问数据库：
using Kogel.Dapper.Extension;
using Kogel.Dapper.Extension.MsSql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using XYDataBaseHelper.XYDataBase;
namespace XYDataBaseHelper
{
/// <summary>
/// ssqlserver数据库
/// </summary>
public class MsSqlDataBaseHelper : IDataBaseHelper
{
#region 构造函数
/// <summary>
/// 构造函数
/// </summary>
/// <param name="sqlIndex"></param>
public MsSqlDataBaseHelper()
{
}
/// <summary>
/// 回收机制
/// </summary>
public void Dispose()
{
}
#endregion
#region 获取连接
/// <summary>
/// 获取数据库连接
/// </summary>
/// <returns></returns>
public DbConnection GetSqlDBConnection()
{
return DataBaseConfigHelper.GetDataBasebConnect();
}
#endregion
#region 获取
/// <summary>
/// 获取所有数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public List<T> GetAll<T>()
{
using (DbConnection conn = DataBaseConfigHelper.GetDataBasebConnect())
{
conn.Open();
return conn.QuerySet<T>().ToList();
}
}
/// <summary>
/// 根据条件获取数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="predicate"></param>
/// <returns></returns>
public List<T> GetList<T>(Expression<Func<T, bool>> predicate)
{
using (DbConnection conn = DataBaseConfigHelper.GetDataBasebConnect())
{
conn.Open();
return conn.QuerySet<T>().Where(predicate).ToList();
}
}
/// <summary>
/// 通过Sql查询数据对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sqlwhere"></param>
/// <returns></returns>
///
public List<T> GetListBySql<T>(string sqlwhere)
{
using (DbConnection conn = DataBaseConfigHelper.GetDataBasebConnect())
{
conn.Open();
return conn.QuerySet<T>().Where(sqlwhere).ToList();
}
}
/// <summary>
/// 根据条件获取数据分页
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="predicate"></param>
/// <returns></returns>
public PageList<T> GetListPage<T>(Expression<Func<T, bool>> predicate, int pageIndex, int pageCount, string orderBy)
{
using (DbConnection conn = DataBaseConfigHelper.GetDataBasebConnect())
{
conn.Open();
if (predicate == null)
{
return conn.QuerySet<T>().OrderBy(orderBy).PageList(pageIndex, pageCount);
}
return conn.QuerySet<T>().Where(predicate).OrderBy(orderBy).PageList(pageIndex, pageCount);
}
}
/// <summary>
/// 根据条件获取单条数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="predicate"></param>
/// <returns></returns>
public T GetOne<T>(Expression<Func<T, bool>> predicate)
{
using (DbConnection conn = DataBaseConfigHelper.GetDataBasebConnect())
{
conn.Open();
return conn.QuerySet<T>().Where(predicate).Get();
}
}
#endregion
#region 删除
/// <summary>
/// 删除表数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public int Delete<T>()
{
using (DbConnection conn = DataBaseConfigHelper.GetDataBasebConnect())
{
conn.Open();
return conn.CommandSet<T>().Delete();
}
}
/// <summary>
/// 分页查询老方法
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
public IEnumerable<T> GetPageOldFunction<T>(string sql)
{
using (DbConnection conn = DataBaseConfigHelper.GetDataBasebConnect())
{
return SqlMapper.Query<T>(conn, sql);
}
}
/// <summary>
/// 删除单条数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="deleteObject"></param>
/// <returns></returns>
public int Delete<T>(T deleteObject)
{
using (DbConnection conn = DataBaseConfigHelper.GetDataBasebConnect())
{
conn.Open();
return conn.CommandSet<T>().Delete();
}
}
/// <summary>
/// 按条件删除列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="predicate"></param>
/// <returns></returns>
public int DeleteList<T>(Expression<Func<T, bool>> predicate)
{
using (DbConnection conn = DataBaseConfigHelper.GetDataBasebConnect())
{
conn.Open();
return conn.CommandSet<T>().Where(predicate).Delete();
}
}
#endregion
#region 插入
/// <summary>
/// 插入一条对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="insertObject"></param>
/// <returns></returns>
public long Insert<T>(T insertObject)
{
using (DbConnection conn = DataBaseConfigHelper.GetDataBasebConnect())
{
conn.Open();
return conn.CommandSet<T>().InsertIdentity(insertObject);
}
}
/// <summary>
/// 批量插入
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="insertList"></param>
/// <returns></returns>
public int InsertList<T>(List<T> insertList)
{
using (DbConnection conn = DataBaseConfigHelper.GetDataBasebConnect())
{
conn.Open();
return conn.CommandSet<T>().Insert(insertList);
}
}
#endregion
#region 修改
/// <summary>
/// 根据条件修改对象信息
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="predicate"></param>
/// <param name="updateObject"></param>
/// <returns></returns>
public int Update<T>(Expression<Func<T, bool>> predicate, T updateObject)
{
using (DbConnection conn = DataBaseConfigHelper.GetDataBasebConnect())
{
conn.Open();
return conn.CommandSet<T>().Where(predicate).Update(updateObject);
}
}
/// <summary>
/// 更新单条数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="updateObject"></param>
/// <returns></returns>
public int Update<T>(T updateObject)
{
using (DbConnection conn = DataBaseConfigHelper.GetDataBasebConnect())
{
conn.Open();
return conn.CommandSet<T>().Update(updateObject);
}
}
/// <summary>
/// 批量更新
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="updateList"></param>
/// <returns></returns>
public int UpdateList<T>(List<T> updateList, IDbDataAdapter dbDataAdapter)
{
using (DbConnection conn = DataBaseConfigHelper.GetDataBasebConnect())
{
conn.Open();
return conn.CommandSet<T>().Update(updateList, dbDataAdapter);
}
}
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="predicate"></param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="orderBy"></param>
/// <returns></returns>
/// <exception cref="NotImplementedException"></exception>
PageList<T> IDataBaseHelper.GetListPage<T>(Expression<Func<T, bool>> predicate, int pageIndex, int pageCount, string orderBy)
{
throw new NotImplementedException();
}
#endregion
#region 其他各种使用说明
/*
*
*
*
如果想使用事务
using (var conn = new SqlConnection(mysqlConnection))
{
//必须先打开数据库
conn.Open();
//创建事务对象
var transaction = conn.BeginTransaction();
//使用事务对象做修改
var result = conn.CommandSet<Comment>(transaction)
.Where(x => x.Id.Equals(1))
.Update(x => new Comment()
{
Content = "test"
});
//提交事务，回滚使用 transaction.Rollback();
transaction.Commit();
}
连表查询
Join<主表,副表>(主表关联字段，副表关联字段)
var list = conn.QuerySet<users>()
.Where(x => x.code != "1")
.Join<users, project_Role>(x => x.roleId, y => y.id)
.ToList();
任意条件连表
var list = conn.QuerySet<users>()
.Where(x => x.code != "1")
.Join<users, project_Role>((x,y)=>x.roleId==y.id)
.ToList();
还可以设置连表的方式(默认是Left Join)
var list = conn.QuerySet<users>()
.Where(x => x.code != "1")
.Join<users, project_Role>((x,y)=>x.roleId==y.id, JoinMode.LEFT)
.ToList();
连表查询可以渲染成指定实体类,例如动态类型(dynamic)
var list = conn.QuerySet<users>()
.Where(x => x.code != "1")
.Join<users, project_Role>(x => x.roleId, y => y.id)
.ToList<dynamic>();
多表任意联查
var users = conn.QuerySet<users>()
.Join<users, project_Role>((a, b) => a.roleId == b.id)
.Where<users, project_Role>((a, b) => a.id == 3 && b.id == 3)
.Get<dynamic>();
Where函数和匿名返回类型支持比较复杂的函数判断
var comment1 = conn.QuerySet<Comment>()
.Join<Comment, News>((a, b) => a.ArticleId == b.Id)
.Where(x => x.Id.Between(80, 100)
&& x.SubTime.AddDays(-10) < DateTime.Now && x.Id > 10
&& x.Id > new QuerySet<News>(conn, new MySqlProvider()).Where(y => y.Id < 3 && x.Id<y.Id).Sum<News>(y => y.Id)
)
.From<Comment, News>()
.OrderBy<News>(x => x.Id)
.PageList(1, 1, (a, b) => new
{
test = new List<int>() { 3, 3, 1 }.FirstOrDefault(y => y == 1),
aaa = "6666" + "777",
Content = a.Content + "'test'" + b.Headlines + a.IdentityId,
bbb = conn.QuerySet<Comment>()
.Where(y => y.ArticleId == b.Id && y.Content.Contains("test")).Sum<Comment>(x => x.Id),
ccc = a.IdentityId,
ddd = Function.ConcatSql<int>("(select count(1) from Comment)"),
a.Id
});
需要读取数据库的支持Sum和Count函数
不需要读取数据库的函数都支持，例如
test = new List<int>() { 3, 3, 1 }.FirstOrDefault(y => y == 1)
自定义导航查询
var ContentList = conn.QuerySet<Comment>()
.ToList(x => new CommentDto()
{
Id = x.Id,
ArticleIds = x.ArticleId,
count = conn.QuerySet<News>().Where(y => y.Id == x.ArticleId).Count(),
NewsList = new QuerySet<News>().Where(y => y.Id == x.ArticleId).ToList(y => new NewsDto()
{
Id = y.Id,
Contents = y.Content
}).ToList()
});
分组聚合查询
var commne = conn.QuerySet<Comment>()
.Where(x => x.Id > 0 && array1.Contains(x.Id) && x.Content.Replace("1", "2") == x.Content)
.Where(x => x.Id.In(array1))
.GroupBy(x => new { x.ArticleId })
.Having(x => Function.Sum(x.Id) >= 5)
.ToList(x => new
{
x.ArticleId,
test1 = Function.Sum(x.Id)
});
*/
#endregion
}
}
浙公网安备 33010602011771号