/// <summary>
/// 获取Model集合（没有Where条件）
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static async Task <IEnumerable <T> > GetAllAsync <T>() where T : class, new()
{
using (var conn = DapperConfig.GetSqlConnection())
{
return(await conn.GetAllAsync <T>());
}
}
/// <summary>
/// 获取Model-Key为string类型
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
public static async Task <T> GetAsync <T>(string id, IDbTransaction transaction = null, int?commandTimeout = null) where T : class, new()
{
using (var conn = DapperConfig.GetSqlConnection())
{
return(await conn.GetAsync <T>(id, transaction, commandTimeout));
}
}
/// <summary>
/// 插入一个Model
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <param name="sqlAdapter"></param>
/// <returns></returns>
public static async Task <int> InsertAsync <T>(T model, IDbTransaction transaction = null, int?commandTimeout = null) where T : class, new()
{
using (var conn = DapperConfig.GetSqlConnection())
{
return(await conn.InsertAsync <T>(model, transaction, commandTimeout));
}
}
/// <summary>
/// 删除一个Model
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
public static async Task <bool> DeleteAsync <T>(T model, IDbTransaction transaction = null, int?commandTimeout = null) where T : class, new()
{
using (var conn = DapperConfig.GetSqlConnection())
{
bool b = await conn.DeleteAsync <T>(model, transaction, commandTimeout);
return(b);
}
}
/// <summary>
/// 异步分页查询数据集合
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="connection">连接字符串</param>
/// <param name="cmd">sql语句</param>
/// <param name="param">参数</param>
/// <param name="flag">true存储过程，false sql语句</param>
/// <returns>t</returns>
public async Task <IList <T> > FindToListByPageAsync <T>(string connection, string cmd, DynamicParameters param, bool flag = true) where T : class, new()
{
IDataReader dataReader = null;
using (IDbConnection con = DapperConfig.GetSqlConnection(connection))
{
if (flag)
{
dataReader = await con.ExecuteReaderAsync(cmd, param, null, null, CommandType.StoredProcedure);
}
else
{
dataReader = await con.ExecuteReaderAsync(cmd, param, null, null, CommandType.Text);
}
if (dataReader == null || !dataReader.Read())
{
return(null);
}
Type type = typeof(T);
List <T> tlist = new List <T>();
while (dataReader.Read())
{
T t = new T();
foreach (var item in type.GetProperties())
{
for (int i = 0; i < dataReader.FieldCount; i++)
{
//属性名与查询出来的列名比较
if (item.Name.ToLower() != dataReader.GetName(i).ToLower())
{
continue;
}
var kvalue = dataReader[item.Name];
if (kvalue == DBNull.Value)
{
continue;
}
item.SetValue(t, kvalue, null);
break;
}
}
if (tlist != null)
{
tlist.Add(t);
}
}
return(tlist);
}
}
/// <summary>
/// 更新一个Model
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="entityToUpdate"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
public static async Task <T> UpdateAsync <T>(T model, IDbTransaction transaction = null, int?commandTimeout = null) where T : class, new()
{
using (var conn = DapperConfig.GetSqlConnection())
{
bool b = await conn.UpdateAsync <T>(model, transaction, commandTimeout);
if (b)
{
return(model);
}
else
{
return(null);
}
}
}
/// <summary>
/// 同步查询操作
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="connection">连接字符串</param>
/// <param name="cmd">sql语句</param>
/// <param name="param">参数</param>
/// <param name="flag">true存储过程，false sql语句</param>
/// <returns>object</returns>
public object ExecuteScalar <T>(string connection, string cmd, DynamicParameters param, bool flag = true) where T : class, new()
{
object result = null;
using (IDbConnection con = DapperConfig.GetSqlConnection(connection))
{
if (flag)
{
result = con.ExecuteScalar(cmd, param, null, null, CommandType.StoredProcedure);
}
else
{
result = con.ExecuteScalar(cmd, param, null, null, CommandType.Text);
}
}
return(result);
}
/// <summary>
/// 增、删、改异步操作
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="connection">链接字符串</param>
/// <param name="cmd">sql语句</param>
/// <param name="param">参数</param>
/// <param name="flag">true存储过程，false sql语句</param>
/// <returns>int</returns>
public async Task <int> ExcuteNonQueryAsync <T>(string connection, string cmd, DynamicParameters param, bool flag = true) where T : class, new()
{
int result = 0;
using (IDbConnection con = DapperConfig.GetSqlConnection(connection))
{
if (flag)
{
result = await con.ExecuteAsync(cmd, param, null, null, CommandType.StoredProcedure);
}
else
{
result = await con.ExecuteAsync(cmd, param, null, null, CommandType.Text);
}
}
return(result);
}
/// <summary>
/// 分页查询(为什么不用out，请参考：http://www.cnblogs.com/dunitian/p/5556909.html)
/// </summary>
/// <param name="sql">查询语句</param>
/// <param name="p">动态参数</param>
/// <param name="sqlTotal">total语句</param>
/// <param name="p2">Total动态参数</param>
/// <returns></returns>
public static async Task <string> PageLoadAsync <T>(string sql, object p = null, string sqlTotal = "", object p2 = null)
{
//var rows = await QueryAsync<T>(sql.ToString(), p);
//var total = rows.Count();
//if (!sqlTotal.IsNullOrWhiteSpace()) { total = await ExecuteScalarAsync<int>(sqlTotal, p2); }
//return new { rows = rows, total = total }.ObjectToJson();
using (var conn = DapperConfig.GetSqlConnection())
{
var rows = await conn.QueryAsync <T>(sql, p);
var total = rows.AsList().Count;
if (!string.IsNullOrWhiteSpace(sqlTotal))
{
total = await conn.ExecuteScalarAsync <int>(sqlTotal, p2);
}
return("");
}
}