/// <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)); }