/// <summary>
/// 分页查询
/// example
/// var result=connection.GetPageList<User>("userid,username desc"," where userid>@id",new {id=10},2,20);
/// int count=result.Item1;
/// var list=result.Item2;
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="order">排序字段</param>
/// <param name="strWhere"></param>
/// <param name="param"></param>
/// <param name="pageNum"></param>
/// <param name="PageSize"></param>
/// <returns></returns>
public static Tuple <int, IEnumerable <T> > GetPageList <T>(this IDbConnection connection, string order, string strWhere = "", object param = null, int pageNum = 1, int PageSize = 10)
{
int count = connection.ExecuteScalar <int>(FasterCore <T> .GetCountSql() + strWhere, param);
var query = connection.ExecuteQuery <T>(FasterCore <T> .GetPageListSql(order, strWhere, pageNum, PageSize), param);
return(Tuple.Create(count, query));
}
/// <summary>
/// IDataReader转化为实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader"></param>
/// <returns></returns>
private static IEnumerable <T> ReaderToEntity <T>(this IDataReader reader)
{
List <T> list = new List <T>();
var myTable = FasterCore <T> .GetPropTable();
while (reader.Read())
{
T t = Activator.CreateInstance <T>();
foreach (var item in myTable.Columns)
{
if (reader.Contain(item.Alias))
{
var propInfo = typeof(T).GetProperty(item.Name);
propInfo.SetValue(t, reader[item.Alias] == DBNull.Value ? null : reader[item.Alias]);
}
}
;
list.Add(t);
}
return(list);
}
/// <summary>
/// 批量删除实体,生成一条语句。优化速度
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="keys"></param>
/// <returns></returns>
public static int BulkRemove <T>(this IDbConnection connection, IEnumerable <T> param)
{
StringBuilder strSql = new StringBuilder();
return(connection.Execute(command =>
{
//生成语句
var myTable = FasterCore <T> .GetPropTable();
var query = myTable.Columns.Where(m => m.Identity == false);
int i = 0;
foreach (var item in param)
{
strSql.Append($"delete from {myTable.Name} where {string.Join(" and ", query.Select(m => $"{m.Alias}=@{m.Name + i}"))} ;");
i++;
}
command.CommandText = strSql.ToString();
command.AddParams(param);
return command.ExecuteNonQuery();
}));
}
/// <summary>
/// 删除实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="keys"></param>
/// <returns></returns>
public static int Remove <T>(this IDbConnection connection, object param)
{
return(connection.ExecuteNonQuery(FasterCore <T> .GetDeleteSql(), param));
}
/// <summary>
/// 新增实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="keys"></param>
/// <returns></returns>
public static int Add <T>(this IDbConnection connection, object param)
{
return(connection.ExecuteNonQuery(FasterCore <T> .GetInsertSql(), param));
}
/// <summary>
/// 根据主键加载单个实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="param"> new {id=3}</param>
/// <returns></returns>
public static T Get <T>(this IDbConnection connection, object param)
{
return(connection.ExecuteQuery <T>(FasterCore <T> .GetSql(), param).FirstOrDefault());
}
/// <summary>
/// 加载列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="strWhere"> where id=@id and id>@id </param>
/// <param name="param"> new {id=3}</param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
public static IEnumerable <T> GetList <T>(this IDbConnection connection, string strWhere = "", object param = null)
{
return(connection.ExecuteQuery <T>(FasterCore <T> .GetListSql() + strWhere, param));
}
