/// <summary> /// 返回IList /// </summary> /// <param name="pd">委托对象</param> /// <param name="queryParam">查询字符串</param> /// <param name="count">返回记录总数</param> /// <returns>返回记录集List</returns> public static IList <T> GetEntities <T>(PopulateDelegate <T> pd, QuartzInAction.Common.QueryParam queryParam, string _conStr, out int count) { List <T> lst = new List <T>(); count = 0; using (SqlConnection conn = new SqlConnection(_conStr)) { SqlCommand cmd = new SqlCommand("SupesoftPage", conn); cmd.CommandType = CommandType.StoredProcedure; // 设置参数 cmd.Parameters.Add("@TableName", SqlDbType.NVarChar, 500).Value = queryParam.TableName; cmd.Parameters.Add("@ReturnFields", SqlDbType.NVarChar, 500).Value = queryParam.ReturnFields; cmd.Parameters.Add("@Where", SqlDbType.NVarChar, 500).Value = queryParam.Where; cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = queryParam.PageIndex; cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = queryParam.PageSize; cmd.Parameters.Add("@Orderfld", SqlDbType.NVarChar, 200).Value = queryParam.Orderfld; cmd.Parameters.Add("@OrderType", SqlDbType.Int).Value = queryParam.OrderType; // 执行 conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); Dictionary <string, string> Fileds = new Dictionary <string, string>(); foreach (DataRow var in dr.GetSchemaTable().Select()) { Fileds.Add(var[0].ToString(), var[0].ToString()); } while (dr.Read()) { lst.Add(pd(dr, Fileds, _conStr)); } // 取记录总数 及页数 if (dr.NextResult()) { if (dr.Read()) { count = Convert.ToInt32(dr["RecordCount"]); } } dr.Close(); cmd.Dispose(); conn.Close(); } return(lst); }
/// <summary> /// 返回IList /// </summary> /// <param name="pd">委托对象</param> /// <param name="queryParam">查询字符串</param> /// <param name="i">重载标记字段,任意数值</param> /// <param name="count">返回记录总数</param> /// <returns>返回记录集List</returns> public static IList <T> GetEntities <T>(PopulateDelegate <T> pd, QuartzInAction.Common.QueryParam queryParam, int i, out int count, string _conStr) { IList <T> lst = new List <T>(); count = 0; using (SqlConnection Conn = new SqlConnection(_conStr)) { StringBuilder sb = new StringBuilder(); SqlCommand cmd = new SqlCommand(); SqlDataReader dr = null; cmd.Connection = Conn; int TotalRecordForPageIndex = queryParam.PageIndex * queryParam.PageSize; string OrderBy; string CutOrderBy; if (queryParam.OrderType == 1) { OrderBy = " Order by " + queryParam.Orderfld.Replace(",", " desc,") + " desc "; CutOrderBy = " Order by " + queryParam.Orderfld.Replace(",", " asc,") + " asc "; } else { OrderBy = " Order by " + queryParam.Orderfld.Replace(",", " asc,") + " asc "; CutOrderBy = " Order by " + queryParam.Orderfld.Replace(",", " desc,") + " desc "; } Conn.Open(); // 取记录总数 cmd.CommandText = string.Format("SELECT Count(1) From {0} {1}", queryParam.TableName, queryParam.Where); count = Convert.ToInt32(cmd.ExecuteScalar()); cmd.Parameters.Clear(); int CurrentPageSize = queryParam.PageSize; if ((count - 1) / queryParam.PageSize + 1 == queryParam.PageIndex) { CurrentPageSize = count % queryParam.PageSize; if (CurrentPageSize == 0) { CurrentPageSize = queryParam.PageSize; } } //取记录值 //MYSQL : sb.AppendFormat("SELECT * FROM (SELECT * FROM (SELECT {2} FROM {3} {4} {5} LIMIT {1} ) TB2 {6} LIMIT {0}) TB3 {5} ", CurrentPageSize, TotalRecordForPageIndex, queryParam.ReturnFields, queryParam.TableName, queryParam.Where, OrderBy, CutOrderBy); sb.AppendFormat( "SELECT * FROM (SELECT TOP {0} * FROM (SELECT TOP {1} {2} FROM {3} {4} {5}) TB2 {6}) TB3 {5} ", CurrentPageSize, TotalRecordForPageIndex, queryParam.ReturnFields, queryParam.TableName, queryParam.Where, OrderBy, CutOrderBy); cmd.CommandText = sb.ToString(); dr = cmd.ExecuteReader(); Dictionary <string, string> Fileds = new Dictionary <string, string>(); foreach (DataRow var in dr.GetSchemaTable().Select()) { Fileds.Add(var[0].ToString(), var[0].ToString()); } while (dr.Read()) { lst.Add(pd(dr, Fileds, _conStr)); } dr.Close(); dr.Dispose(); dr.Close(); cmd.Dispose(); Conn.Dispose(); Conn.Close(); } return(lst); }