43
.Net Core中使用Dapper构建泛型仓储
source link: http://www.cnblogs.com/zhangnever/p/11926045.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
前言：Dapper是.NET的简单对象映射器， 在速度方面 拥有 ORM 之 王 的称号， 与使用原始ADO.NET读取数据一样快。 ORM是对象关系映射器，它负责数据库和编程语言之间的映射。
仓储主要是用来解耦业务逻辑层与数据访问层,降低耦合,使得程序在后期有很强的扩展性。
首先新建四个类库
IRepository:
Repository:
引用IRepository
IServices:
Services:
引用IServices、IRepository
导入两个包：Dapper,Npgsql(我用的是pgsql数据库)
万事俱备,上代码了。
IBaseRepository
public interface IBaseRepository<TEntity> where TEntity : class, new()
{
/// <summary>
/// 查询数据集合
/// </summary>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回指定实体泛型</returns>
Task<TEntity> QueryFirst(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
/// <summary>
/// 返回 dataset
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
Task<DataSet> Query(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
/// <summary>
/// 查询数据集合
/// </summary>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="buffered">是否将查询结果缓存到内存中（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回指定泛型集合</returns>
Task<IEnumerable<TEntity>> QueryList(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);
/// <summary>
/// 简单分页，返回分页后的泛型集合
/// </summary>
/// <typeparam name="T">分页后的泛型集合</typeparam>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="totalCount">返回 总记录数</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="buffered">是否将查询结果缓存到内存中（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回分页后的泛型集合</returns>
Task<Tuple<IEnumerable<TEntity>, int>> QueryPagination(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);
/// <summary>
/// 2条Sql语句查询
/// </summary>
/// <typeparam name="TFirst">实体集合一</typeparam>
/// <typeparam name="TSecond">实体集合二</typeparam>
/// <param name="sql">2条查询语句</param>
/// <param name="tfList">返回第一条语句的实体集合</param>
/// <param name="tsList">返回第二条语句的实体集合</param>
/// <param name="param">参数值（可选）</param>
Task<Tuple<List<TFirst>, List<TSecond>>> QueryMultiple<TFirst, TSecond>(string sql, object param = null);
/// <summary>
/// 3条Sql语句查询
/// </summary>
/// <typeparam name="TFirst">实体集合一</typeparam>
/// <typeparam name="TSecond">实体集合二</typeparam>
/// <typeparam name="TThird">实体集合三</typeparam>
/// <param name="sql">5条查询语句</param>
/// <param name="tfList">返回第一条语句的实体集合</param>
/// <param name="tsList">返回第二条语句的实体集合</param>
/// <param name="ttList">返回第三条语句的实体集合</param>
/// <param name="param">参数值（可选）</param>
Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>>> QueryMultiple<TFirst, TSecond, TThird>(string sql, object param = null);
/// <summary>
/// 4条Sql语句查询
/// </summary>
/// <typeparam name="TFirst">实体集合一</typeparam>
/// <typeparam name="TSecond">实体集合二</typeparam>
/// <typeparam name="TThird">实体集合三</typeparam>
/// <typeparam name="TFour">实体集合四</typeparam>
/// <param name="sql">5条查询语句</param>
/// <param name="tfList">返回第一条语句的实体集合</param>
/// <param name="tsList">返回第二条语句的实体集合</param>
/// <param name="ttList">返回第三条语句的实体集合</param>
/// <param name="tfourList">返回第四条语句的实体集合</param>
/// <param name="param">参数值（可选）</param>
Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>>> QueryMultiple<TFirst, TSecond, TThird, TFour>(string sql, object param = null);
/// <summary>
/// 5条Sql语句查询
/// </summary>
/// <typeparam name="TFirst">实体集合一</typeparam>
/// <typeparam name="TSecond">实体集合二</typeparam>
/// <typeparam name="TThird">实体集合三</typeparam>
/// <typeparam name="TFour">实体集合四</typeparam>
/// <typeparam name="TFive">实体集合五</typeparam>
/// <param name="sql">5条查询语句</param>
/// <param name="tfList">返回第一条语句的实体集合</param>
/// <param name="tsList">返回第二条语句的实体集合</param>
/// <param name="ttList">返回第三条语句的实体集合</param>
/// <param name="tfourList">返回第四条语句的实体集合</param>
/// <param name="tfiveList">返回第五条语句的实体集合</param>
/// <param name="param">参数值（可选）</param>
Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>, List<TFive>>> QueryMultiple<TFirst, TSecond, TThird, TFour, TFive>(string sql, object param = null);
/// <summary>
/// 查询单个实体类型
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="buffered">是否将查询结果缓存到内存中（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>泛型实体类型</returns>
Task<TEntity> QueryOne(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);
/// <summary>
/// 执行sql语句，返回受影响的行数
/// </summary>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回受影响的行数</returns>
Task<int> Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
/// <summary>
/// 执行sql语句，返回第一行第一列
/// </summary>
/// <typeparam name="T">指定类型</typeparam>
/// <param name="sql">查询Sql语句</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回返回第一行第一列</returns>
Task<TEntity> ExecuteScalar(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
/// <summary>
/// 执行存储过程，返回第一行第一列
/// </summary>
/// <typeparam name="T">指定类型</typeparam>
/// <param name="command">存储过程名称</param>
/// <param name="paras">参数键值对</param>
/// <returns>返回第一行第一列</returns>
Task<TEntity> Execute(string command, Dictionary<string, object> paras);
}
BaseRepository
public class BaseRepository<TEntity> : IBaseRepository<TEntity> where TEntity : class, new()
{
private readonly IConfiguration _configuration;
public BaseRepository(IConfiguration configuration) => this._configuration = configuration;
/// <summary>
/// 创建数据库连接，并打开连接
/// 连接字符串写在 json 配置文件里面
/// </summary>
/// <returns>IDbConnection</returns>
public IDbConnection GetOpenConn()
{
IDbConnection con = null;
string connectionString = _configuration["Connection:dbContent"];
con = new NpgsqlConnection(connectionString);
try
{
con.Open();
}
catch (Exception ex)
{
throw new Exception("数据库连接错误:" + ex.Message);
}
return con;
}
/// <summary>
/// 查询数据集合
/// </summary>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回指定实体泛型</returns>
public async Task<TEntity> QueryFirst(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
{
using (IDbConnection con = this.GetOpenConn())
{
return con.QueryFirst<TEntity>(sql, param, transaction, commandTimeout, commandType);
}
}
/// <summary>
/// 返回 dataset
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public async Task<DataSet> Query(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
{
using (IDbConnection con = GetOpenConn())
{
IDataReader reader = con.ExecuteReader(sql, param, transaction, commandTimeout, commandType);
DataSet ds = new XDataSet();
ds.Load(reader, LoadOption.OverwriteChanges, null, new DataTable[] { });
return ds;
}
}
/// <summary>
/// 查询数据集合
/// </summary>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="buffered">是否将查询结果缓存到内存中（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回指定泛型集合</returns>
public async Task<IEnumerable<TEntity>> QueryList(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
{
using (IDbConnection con = GetOpenConn())
{
return con.Query<TEntity>(sql, param, transaction, buffered, commandTimeout, commandType);
}
}
/// <summary>
/// 简单分页，返回分页后的泛型集合
/// </summary>
/// <typeparam name="T">分页后的泛型集合</typeparam>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="totalCount">返回 总记录数</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="buffered">是否将查询结果缓存到内存中（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回分页后的泛型集合</returns>
public async Task<Tuple<IEnumerable<TEntity>, int>> QueryPagination(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
{
using (IDbConnection con = GetOpenConn())
{
var multi = con.QueryMultiple(sql, param, transaction, commandTimeout, commandType);
int totalCount = int.Parse(multi.Read<long>().Single().ToString());
return Tuple.Create<IEnumerable<TEntity>, int>(multi.Read<TEntity>(), totalCount);
}
}
/// <summary>
/// 2条Sql语句查询
/// </summary>
/// <typeparam name="TFirst">实体集合一</typeparam>
/// <typeparam name="TSecond">实体集合二</typeparam>
/// <param name="sql">2条查询语句</param>
/// <param name="tfList">返回第一条语句的实体集合</param>
/// <param name="tsList">返回第二条语句的实体集合</param>
/// <param name="param">参数值（可选）</param>
public async Task<Tuple<List<TFirst>, List<TSecond>>> QueryMultiple<TFirst, TSecond>(string sql, object param = null)
{
using (IDbConnection con = GetOpenConn())
{
var multi = con.QueryMultiple(sql, param);
var tfList = new List<TFirst>();
var tsList = new List<TSecond>();
if (!multi.IsConsumed)
{
tfList = multi.Read<TFirst>().ToList();
tsList = multi.Read<TSecond>().ToList();
}
return Tuple.Create<List<TFirst>, List<TSecond>>(tfList, tsList);
}
}
/// <summary>
/// 3条Sql语句查询
/// </summary>
/// <typeparam name="TFirst">实体集合一</typeparam>
/// <typeparam name="TSecond">实体集合二</typeparam>
/// <typeparam name="TThird">实体集合三</typeparam>
/// <param name="sql">5条查询语句</param>
/// <param name="tfList">返回第一条语句的实体集合</param>
/// <param name="tsList">返回第二条语句的实体集合</param>
/// <param name="ttList">返回第三条语句的实体集合</param>
/// <param name="param">参数值（可选）</param>
public async Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>>> QueryMultiple<TFirst, TSecond, TThird>(string sql, object param = null)
{
using (IDbConnection con = GetOpenConn())
{
var multi = con.QueryMultiple(sql, param);
var tfList = new List<TFirst>();
var tsList = new List<TSecond>();
var ttList = new List<TThird>();
if (!multi.IsConsumed)
{
tfList = multi.Read<TFirst>().ToList();
tsList = multi.Read<TSecond>().ToList();
ttList = multi.Read<TThird>().ToList();
}
return Tuple.Create<List<TFirst>, List<TSecond>, List<TThird>>(tfList, tsList, ttList);
}
}
/// <summary>
/// 4条Sql语句查询
/// </summary>
/// <typeparam name="TFirst">实体集合一</typeparam>
/// <typeparam name="TSecond">实体集合二</typeparam>
/// <typeparam name="TThird">实体集合三</typeparam>
/// <typeparam name="TFour">实体集合四</typeparam>
/// <param name="sql">5条查询语句</param>
/// <param name="tfList">返回第一条语句的实体集合</param>
/// <param name="tsList">返回第二条语句的实体集合</param>
/// <param name="ttList">返回第三条语句的实体集合</param>
/// <param name="tfourList">返回第四条语句的实体集合</param>
/// <param name="param">参数值（可选）</param>
public async Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>>> QueryMultiple<TFirst, TSecond, TThird, TFour>(string sql, object param = null)
{
using (IDbConnection con = GetOpenConn())
{
var multi = con.QueryMultiple(sql, param);
var tfList = new List<TFirst>();
var tsList = new List<TSecond>();
var ttList = new List<TThird>();
var tfourList = new List<TFour>();
if (!multi.IsConsumed)
{
tfList = multi.Read<TFirst>().ToList();
tsList = multi.Read<TSecond>().ToList();
ttList = multi.Read<TThird>().ToList();
tfourList = multi.Read<TFour>().ToList();
}
return Tuple.Create<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>>(tfList, tsList, ttList, tfourList);
}
}
/// <summary>
/// 5条Sql语句查询
/// </summary>
/// <typeparam name="TFirst">实体集合一</typeparam>
/// <typeparam name="TSecond">实体集合二</typeparam>
/// <typeparam name="TThird">实体集合三</typeparam>
/// <typeparam name="TFour">实体集合四</typeparam>
/// <typeparam name="TFive">实体集合五</typeparam>
/// <param name="sql">5条查询语句</param>
/// <param name="tfList">返回第一条语句的实体集合</param>
/// <param name="tsList">返回第二条语句的实体集合</param>
/// <param name="ttList">返回第三条语句的实体集合</param>
/// <param name="tfourList">返回第四条语句的实体集合</param>
/// <param name="tfiveList">返回第五条语句的实体集合</param>
/// <param name="param">参数值（可选）</param>
public async Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>, List<TFive>>> QueryMultiple<TFirst, TSecond, TThird, TFour, TFive>(string sql, object param = null)
{
using (IDbConnection con = GetOpenConn())
{
var multi = con.QueryMultiple(sql, param);
var tfList = new List<TFirst>();
var tsList = new List<TSecond>();
var ttList = new List<TThird>();
var tfourList = new List<TFour>();
var tfiveList = new List<TFive>();
if (!multi.IsConsumed)
{
tfList = multi.Read<TFirst>().ToList();
tsList = multi.Read<TSecond>().ToList();
ttList = multi.Read<TThird>().ToList();
tfourList = multi.Read<TFour>().ToList();
tfiveList = multi.Read<TFive>().ToList();
}
return Tuple.Create<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>, List<TFive>>(tfList, tsList, ttList, tfourList, tfiveList);
}
}
/// <summary>
/// 查询单个实体类型
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="buffered">是否将查询结果缓存到内存中（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>泛型实体类型</returns>
public async Task<TEntity> QueryOne(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
{
var dataResult = await QueryList(sql, param, transaction, buffered, commandTimeout, commandType);
return dataResult != null && dataResult.Count() > 0 ? dataResult.ToList()[0] : new TEntity();
}
/// <summary>
/// 执行sql语句，返回受影响的行数
/// </summary>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回受影响的行数</returns>
public async Task<int> Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
{
using (IDbConnection con = GetOpenConn())
{
return con.Execute(sql, param, transaction, commandTimeout, commandType);
}
}
/// <summary>
/// 执行sql语句，返回第一行第一列
/// </summary>
/// <typeparam name="T">指定类型</typeparam>
/// <param name="sql">查询Sql语句</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回返回第一行第一列</returns>
public async Task<TEntity> ExecuteScalar(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
{
using (IDbConnection con = GetOpenConn())
{
return con.ExecuteScalar<TEntity>(sql, param, transaction, commandTimeout, commandType);
}
}
/// <summary>
/// 执行存储过程，返回第一行第一列
/// </summary>
/// <typeparam name="T">指定类型</typeparam>
/// <param name="command">存储过程名称</param>
/// <param name="paras">参数键值对</param>
/// <returns>返回第一行第一列</returns>
public async Task<TEntity> Execute(string command, Dictionary<string, object> paras)
{
using (IDbConnection con = GetOpenConn())
{
IDbCommand com = con.CreateCommand();
com.CommandText = command;
com.CommandType = CommandType.StoredProcedure;
if (paras != null)
{
foreach (var item in paras.Keys)
{
IDbDataParameter para = com.CreateParameter();
para.Value = paras[item];
para.ParameterName = item;
com.Parameters.Add(para);
}
}
return (TEntity)com.ExecuteScalar();
}
}
/// <summary>
/// 数据适配器，扩展Fill方法
/// .NET的DataSet.Load方法，底层调用DataAdapter.Fill(DataTable[], IDataReader, int, int)
/// Dapper想要返回DataSet，需要重写Load方法，不必传入DataTable[]，因为数组长度不确定
/// </summary>
public class XLoadAdapter : DataAdapter
{
/// <summary>
/// 数据适配器
/// </summary>
public XLoadAdapter()
{
}
/// <summary>
/// 读取dataReader
/// </summary>
/// <param name="ds"></param>
/// <param name="dataReader"></param>
/// <param name="startRecord"></param>
/// <param name="maxRecords"></param>
/// <returns></returns>
public int FillFromReader(DataSet ds, IDataReader dataReader, int startRecord, int maxRecords)
{
return this.Fill(ds, "Table", dataReader, startRecord, maxRecords);
}
}
/// <summary>
/// 扩展Load方法
/// </summary>
public class XDataSet : DataSet
{
/// <summary>
/// Dapper想要返回DataSet，需要重写Load方法
/// </summary>
/// <param name="reader">IDataReader</param>
/// <param name="loadOption">LoadOption</param>
/// <param name="handler">FillErrorEventHandler</param>
/// <param name="tables">DataTable</param>
public override void Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler handler, params DataTable[] tables)
{
XLoadAdapter adapter = new XLoadAdapter
{
FillLoadOption = loadOption,
MissingSchemaAction = MissingSchemaAction.AddWithKey
};
if (handler != null)
{
adapter.FillError += handler;
}
adapter.FillFromReader(this, reader, 0, 0);
if (!reader.IsClosed && !reader.NextResult())
{
reader.Close();
}
}
}
}
IBaseServices
public interface IBaseServices<TEntity> where TEntity : class
{
/// <summary>
/// 查询数据集合
/// </summary>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回指定实体泛型</returns>
Task<TEntity> QueryFirst(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
/// <summary>
/// 返回 dataset
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
Task<DataSet> Query(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
/// <summary>
/// 查询数据集合
/// </summary>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="buffered">是否将查询结果缓存到内存中（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回指定泛型集合</returns>
Task<IEnumerable<TEntity>> QueryList(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);
/// <summary>
/// 简单分页，返回分页后的泛型集合
/// </summary>
/// <typeparam name="T">分页后的泛型集合</typeparam>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="totalCount">返回 总记录数</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="buffered">是否将查询结果缓存到内存中（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回分页后的泛型集合</returns>
Task<Tuple<IEnumerable<TEntity>, int>> QueryPagination(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);
/// <summary>
/// 2条Sql语句查询
/// </summary>
/// <typeparam name="TFirst">实体集合一</typeparam>
/// <typeparam name="TSecond">实体集合二</typeparam>
/// <param name="sql">2条查询语句</param>
/// <param name="tfList">返回第一条语句的实体集合</param>
/// <param name="tsList">返回第二条语句的实体集合</param>
/// <param name="param">参数值（可选）</param>
Task<Tuple<List<TFirst>, List<TSecond>>> QueryMultiple<TFirst, TSecond>(string sql, object param = null);
/// <summary>
/// 3条Sql语句查询
/// </summary>
/// <typeparam name="TFirst">实体集合一</typeparam>
/// <typeparam name="TSecond">实体集合二</typeparam>
/// <typeparam name="TThird">实体集合三</typeparam>
/// <param name="sql">5条查询语句</param>
/// <param name="tfList">返回第一条语句的实体集合</param>
/// <param name="tsList">返回第二条语句的实体集合</param>
/// <param name="ttList">返回第三条语句的实体集合</param>
/// <param name="param">参数值（可选）</param>
Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>>> QueryMultiple<TFirst, TSecond, TThird>(string sql, object param = null);
/// <summary>
/// 4条Sql语句查询
/// </summary>
/// <typeparam name="TFirst">实体集合一</typeparam>
/// <typeparam name="TSecond">实体集合二</typeparam>
/// <typeparam name="TThird">实体集合三</typeparam>
/// <typeparam name="TFour">实体集合四</typeparam>
/// <param name="sql">5条查询语句</param>
/// <param name="tfList">返回第一条语句的实体集合</param>
/// <param name="tsList">返回第二条语句的实体集合</param>
/// <param name="ttList">返回第三条语句的实体集合</param>
/// <param name="tfourList">返回第四条语句的实体集合</param>
/// <param name="param">参数值（可选）</param>
Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>>> QueryMultiple<TFirst, TSecond, TThird, TFour>(string sql, object param = null);
/// <summary>
/// 5条Sql语句查询
/// </summary>
/// <typeparam name="TFirst">实体集合一</typeparam>
/// <typeparam name="TSecond">实体集合二</typeparam>
/// <typeparam name="TThird">实体集合三</typeparam>
/// <typeparam name="TFour">实体集合四</typeparam>
/// <typeparam name="TFive">实体集合五</typeparam>
/// <param name="sql">5条查询语句</param>
/// <param name="tfList">返回第一条语句的实体集合</param>
/// <param name="tsList">返回第二条语句的实体集合</param>
/// <param name="ttList">返回第三条语句的实体集合</param>
/// <param name="tfourList">返回第四条语句的实体集合</param>
/// <param name="tfiveList">返回第五条语句的实体集合</param>
/// <param name="param">参数值（可选）</param>
Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>, List<TFive>>> QueryMultiple<TFirst, TSecond, TThird, TFour, TFive>(string sql, object param = null);
/// <summary>
/// 查询单个实体类型
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="buffered">是否将查询结果缓存到内存中（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>泛型实体类型</returns>
Task<TEntity> QueryOne(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);
/// <summary>
/// 执行sql语句，返回受影响的行数
/// </summary>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回受影响的行数</returns>
Task<int> Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
/// <summary>
/// 执行sql语句，返回第一行第一列
/// </summary>
/// <typeparam name="T">指定类型</typeparam>
/// <param name="sql">查询Sql语句</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回返回第一行第一列</returns>
Task<TEntity> ExecuteScalar(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
/// <summary>
/// 执行存储过程，返回第一行第一列
/// </summary>
/// <typeparam name="T">指定类型</typeparam>
/// <param name="command">存储过程名称</param>
/// <param name="paras">参数键值对</param>
/// <returns>返回第一行第一列</returns>
Task<TEntity> Execute(string command, Dictionary<string, object> paras);
}
BaseServices
public class BaseServices<TEntity> : IBaseServices<TEntity> where TEntity : class, new()
{
public IBaseRepository<TEntity> BaseDal;
/// <summary>
/// 查询数据集合
/// </summary>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回指定实体泛型</returns>
public async Task<TEntity> QueryFirst(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
{
return await BaseDal.QueryFirst(sql, param, transaction, commandTimeout, commandType);
}
public Task<DataSet> Query(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
{
return BaseDal.Query(sql, param, transaction, commandTimeout, commandType);
}
/// <summary>
/// 查询数据集合
/// </summary>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="buffered">是否将查询结果缓存到内存中（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回指定泛型集合</returns>
public async Task<IEnumerable<TEntity>> QueryList(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
{
return await BaseDal.QueryList(sql, param, transaction, buffered, commandTimeout, commandType);
}
/// <summary>
/// 简单分页，返回分页后的泛型集合
/// </summary>
/// <typeparam name="T">分页后的泛型集合</typeparam>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="totalCount">返回 总记录数</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="buffered">是否将查询结果缓存到内存中（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回分页后的泛型集合</returns>
public async Task<Tuple<IEnumerable<TEntity>, int>> QueryPagination(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
{
return await BaseDal.QueryPagination(sql, param, transaction, buffered, commandTimeout, commandType);
}
/// <summary>
/// 2条Sql语句查询
/// </summary>
/// <typeparam name="TFirst">实体集合一</typeparam>
/// <typeparam name="TSecond">实体集合二</typeparam>
/// <param name="sql">2条查询语句</param>
/// <param name="tfList">返回第一条语句的实体集合</param>
/// <param name="tsList">返回第二条语句的实体集合</param>
/// <param name="param">参数值（可选）</param>
public async Task<Tuple<List<TFirst>, List<TSecond>>> QueryMultiple<TFirst, TSecond>(string sql, object param = null)
{
return await BaseDal.QueryMultiple<TFirst, TSecond>(sql, param);
}
/// <summary>
/// 3条Sql语句查询
/// </summary>
/// <typeparam name="TFirst">实体集合一</typeparam>
/// <typeparam name="TSecond">实体集合二</typeparam>
/// <typeparam name="TThird">实体集合三</typeparam>
/// <param name="sql">5条查询语句</param>
/// <param name="tfList">返回第一条语句的实体集合</param>
/// <param name="tsList">返回第二条语句的实体集合</param>
/// <param name="ttList">返回第三条语句的实体集合</param>
/// <param name="param">参数值（可选）</param>
public async Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>>> QueryMultiple<TFirst, TSecond, TThird>(string sql, object param = null)
{
return await BaseDal.QueryMultiple<TFirst, TSecond, TThird>(sql, param);
}
/// <summary>
/// 4条Sql语句查询
/// </summary>
/// <typeparam name="TFirst">实体集合一</typeparam>
/// <typeparam name="TSecond">实体集合二</typeparam>
/// <typeparam name="TThird">实体集合三</typeparam>
/// <typeparam name="TFour">实体集合四</typeparam>
/// <param name="sql">5条查询语句</param>
/// <param name="tfList">返回第一条语句的实体集合</param>
/// <param name="tsList">返回第二条语句的实体集合</param>
/// <param name="ttList">返回第三条语句的实体集合</param>
/// <param name="tfourList">返回第四条语句的实体集合</param>
/// <param name="param">参数值（可选）</param>
public async Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>>> QueryMultiple<TFirst, TSecond, TThird, TFour>(string sql, object param = null)
{
return await BaseDal.QueryMultiple<TFirst, TSecond, TThird, TFour>(sql, param);
}
/// <summary>
/// 5条Sql语句查询
/// </summary>
/// <typeparam name="TFirst">实体集合一</typeparam>
/// <typeparam name="TSecond">实体集合二</typeparam>
/// <typeparam name="TThird">实体集合三</typeparam>
/// <typeparam name="TFour">实体集合四</typeparam>
/// <typeparam name="TFive">实体集合五</typeparam>
/// <param name="sql">5条查询语句</param>
/// <param name="tfList">返回第一条语句的实体集合</param>
/// <param name="tsList">返回第二条语句的实体集合</param>
/// <param name="ttList">返回第三条语句的实体集合</param>
/// <param name="tfourList">返回第四条语句的实体集合</param>
/// <param name="tfiveList">返回第五条语句的实体集合</param>
/// <param name="param">参数值（可选）</param>
public async Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>, List<TFive>>> QueryMultiple<TFirst, TSecond, TThird, TFour, TFive>(string sql, object param = null)
{
return await BaseDal.QueryMultiple<TFirst, TSecond, TThird, TFour, TFive>(sql, param);
}
/// <summary>
/// 查询单个实体类型
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="buffered">是否将查询结果缓存到内存中（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>泛型实体类型</returns>
public async Task<TEntity> QueryOne(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
{
return await BaseDal.QueryOne(sql, param, transaction, buffered, commandTimeout, commandType);
}
/// <summary>
/// 执行sql语句，返回受影响的行数
/// </summary>
/// <param name="sql">查询Sql语句或存储过程名称</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回受影响的行数</returns>
public async Task<int> Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
{
return await BaseDal.Execute(sql, param, transaction, commandTimeout, commandType);
}
/// <summary>
/// 执行sql语句，返回第一行第一列
/// </summary>
/// <typeparam name="T">指定类型</typeparam>
/// <param name="sql">查询Sql语句</param>
/// <param name="param">参数值（可选）</param>
/// <param name="transaction">事务名称（可选）</param>
/// <param name="commandTimeout">超时时间（可选）</param>
/// <param name="commandType">指定如果解释sql字符串：语句/存储过程（可选）</param>
/// <returns>返回返回第一行第一列</returns>
public async Task<TEntity> ExecuteScalar(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
{
return await ExecuteScalar(sql, param, transaction, commandTimeout, commandType);
}
/// <summary>
/// 执行存储过程，返回第一行第一列
/// </summary>
/// <typeparam name="T">指定类型</typeparam>
/// <param name="command">存储过程名称</param>
/// <param name="paras">参数键值对</param>
/// <returns>返回第一行第一列</returns>
public async Task<TEntity> Execute(string command, Dictionary<string, object> paras)
{
return await BaseDal.Execute(command, paras);
}
}
下面写一个简单的查询,用户登录判断用户名与密码
public interface IUserInfoRepository: IBaseRepository<UserInfo>
{
}
public class UserInfoRepository : BaseRepository<UserInfo>, IUserInfoRepository
{
public UserInfoRepository(IConfiguration configuration) : base(configuration)
{
}
}
public interface IUserInfoServices:IBaseServices<UserInfo>
{
Task<IEnumerable<UserInfo>> QueryUserInfo(string userName, string password);
}
public class UserInfoServices : BaseServices<UserInfo>, IUserInfoServices
{
IUserInfoRepository _dal;
public UserInfoServices(IUserInfoRepository dal)
{
this._dal = dal;
base.BaseDal = dal;
}
public async Task<IEnumerable<UserInfo>> QueryUserInfo(string userName, string password)
{var Result = await _dal.QueryList($"SELECT * FROM user_info where user_name='{userName}' and password='{password}' limit 1 OFFSET 0");
return Result;
}
}
在控制器中以依赖注入的形式使用(前提是你要借助IOC容器注入好你要使用的对象)，如果对此有困惑,可看下面这篇博客：
https://www.cnblogs.com/zhangnever/p/12324133.html
至此就没有了,各位同学可以再行封装。
如有不足的地方,还望见谅！
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK