/// <summary> /// 查询一系列实体对象 /// </summary> /// <param name="where"></param> /// <returns></returns> public List <T> Query(string @where, IDictionary <string, object> paras, string order = "") { if (SQLWordFilte.CheckKeyWord(@where)) { throw new Exception("您提供的关键字有可能危害数据库,已阻止执行"); } var entity = new T(); string sql = entity.GetQuerySQL(@where); if (!order.IsNullOrEmpty()) { sql += " order by " + order; } ISQLContext sqlContext = new SQLContext(Session); var paramters = new List <SqlParameter>(); if (paras != null) { foreach (var para in paras) { paramters.Add(new SqlParameter(para.Key, para.Value)); } } var reader = sqlContext.ExecuteQueryReader(sql, paramters.ToArray()); return(DataReaderHelper.ReaderToList <T>(reader)); }
/// <summary> /// 分页查询 /// </summary> /// <param name="where"></param> /// <param name="order"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="total"></param> /// <returns></returns> public PageResult <T> QueryPage(string @where, IDictionary <string, object> paras, string order, int pageIndex, int pageSize) { if (SQLWordFilte.CheckKeyWord(@where)) { throw new Exception("您提供的关键字有可能危害数据库,已阻止执行"); } var parasList = new List <SqlParameter>(); var parasListData = new List <SqlParameter>(); if (paras != null) { foreach (var para in paras) { parasList.Add(new SqlParameter(para.Key, para.Value)); parasListData.Add(new SqlParameter(para.Key, para.Value)); } } ISQLContext sqlContext = new SQLContext(Session); var entity = new T(); string sql = entity.GetQuerySql(@where); var total = sqlContext.GetResult <int>(string.Format("SELECT COUNT(1) FROM ({0}) a", sql), parasList.ToArray()); int start = (pageIndex - 1) * pageSize; var tempsql = "select *,ROW_NUMBER() OVER(ORDER BY " + order + ") rn from ({0}) a "; sql = string.Format(tempsql, sql); sql = "SELECT TOP " + pageSize + " * FROM (" + sql + ") query WHERE rn > " + start + " ORDER BY rn"; var reader = (DbDataReader)sqlContext.ExecuteQueryReader(sql, parasListData.ToArray()); List <T> listdata = new List <T>(); using (reader) { var tuple = reader.GetDeserializerState <T>(); while (reader.Read()) { listdata.Add((T)tuple.Func(reader)); } } /*foreach (var data in entities) * { * data.ClearStack(); * }*/ return(new PageResult <T>(listdata, total)); }
/// <summary> /// 获取一个实体对象 /// </summary> /// <param name="pk"></param> /// <returns></returns> public T GetModel(object pk) { var entity = new T(); string sql = entity.GetModelSql(pk); ISQLContext sqlContext = new SQLContext(Session); var reader = sqlContext.ExecuteQueryReader(sql); using (reader) { if (!reader.Read()) { return(null); } var tuple = reader.GetDeserializerState <T>(); return((T)tuple.Func(reader)); } }
/// <summary> /// 获取一个实体对象 /// </summary> /// <param name="pk"></param> /// <returns></returns> public T GetModel(object pk) { var entity = new T(); string sql = entity.GetModelSQL(pk); ISQLContext sqlContext = new SQLContext(Session); var reader = sqlContext.ExecuteQueryReader(sql); var read = reader.Read(); if (!read) { reader.Close(); reader.Dispose(); return(null); } entity = entity.GetEntityFromDataReader(reader); reader.Close(); reader.Dispose(); return(entity); }
/// <summary> /// 分页查询 /// </summary> /// <param name="where"></param> /// <param name="order"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="total"></param> /// <returns></returns> public PageResult <T> QueryPage(string @where, IDictionary <string, object> paras, string order, int pageIndex, int pageSize) { if (SQLWordFilte.CheckKeyWord(@where)) { throw new Exception("您提供的关键字有可能危害数据库,已阻止执行"); } var parasList = new List <SqlParameter>(); var parasListData = new List <SqlParameter>(); if (paras != null) { foreach (var para in paras) { parasList.Add(new SqlParameter(para.Key, para.Value)); parasListData.Add(new SqlParameter(para.Key, para.Value)); } } ISQLContext sqlContext = new SQLContext(Session); var entity = new T(); string sql = entity.GetQuerySQL(@where); var total = sqlContext.GetResult <int>(string.Format("SELECT COUNT(1) FROM ({0}) a", sql), parasList.ToArray()); int start = (pageIndex - 1) * pageSize; var tempsql = "select *,ROW_NUMBER() OVER(ORDER BY " + order + ") rn from ({0}) a "; sql = string.Format(tempsql, sql); sql = "SELECT TOP " + pageSize + " * FROM (" + sql + ") query WHERE rn > " + start + " ORDER BY rn"; var reader = sqlContext.ExecuteQueryReader(sql, parasListData.ToArray()); var entities = DataReaderHelper.ReaderToList <T>(reader); return(new PageResult <T>(entities, total)); }
/// <summary> /// 查询一系列实体对象 /// </summary> /// <param name="where"></param> /// <param name="paras"></param> /// <param name="order"></param> /// <returns></returns> public List <T> Query(string @where, IDictionary <string, object> paras, string order = "") { if (SQLWordFilte.CheckKeyWord(@where)) { throw new Exception("您提供的关键字有可能危害数据库,已阻止执行"); } var entity = new T(); string sql = entity.GetQuerySql(@where); if (!order.IsNullOrEmpty()) { sql += " order by " + order; } ISQLContext sqlContext = new SQLContext(Session); var paramters = new List <SqlParameter>(); if (paras != null) { foreach (var para in paras) { paramters.Add(new SqlParameter(para.Key, para.Value)); } } var reader = sqlContext.ExecuteQueryReader(sql, paramters.ToArray()); List <T> listdata = new List <T>(); using (reader) { var tuple = reader.GetDeserializerState <T>(); while (reader.Read()) { listdata.Add((T)tuple.Func(reader)); } } return(listdata); }