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