使用存储过程

 

//Start C#--------------------------------------------------------------------------------------------------------------------------------

public System.Data.DataTable GetDormHealthOfClass(OurHelper.PageHelper<Dorm_DormitoryHealth> ph)
{
string classID = "";
foreach (var item in ph.QueryKeyValues)
{
if (item.Key == "ClassID")
{
classID = item.Value;
}
}

IDataParameter[] parameter2 = { new SqlParameter("ClassID", SqlDbType.VarChar, 50), new SqlParameter("PageIndex", SqlDbType.Int), new SqlParameter("PageSize", SqlDbType.Int) };
parameter2[0].Value = classID;
parameter2[1].Value = ph.PageIndex;
parameter2[2].Value = ph.PageSize;
int totalCount=0;
DataTable dt= base.ProcForDataTable("Proc_GetDormHealthOfClass", parameter2,totalCount);
ph.TotalCount= totalCount;
return dt;
}

//调用

list = OurHelper.DataTableToListHelper<Dorm_DormitoryHealth>.ConvertToModel(service.GetDormHealthOfClass(page));

 

 

public class ModelConvertHelper<T> where T : new()
{
public static IList<T> ConvertToModel(DataTable dt)
{
// 定义集合
IList<T> ts = new List<T>();

// 获得此模型的类型
Type type = typeof(T);
string tempName = "";

foreach (DataRow dr in dt.Rows)
{
T t = new T();
// 获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
tempName = pi.Name; // 检查DataTable是否包含此列

if (dt.Columns.Contains(tempName))
{
// 判断此属性是否有Setter
if (!pi.CanWrite) continue;

object value = dr[tempName];
if (value != DBNull.Value)
pi.SetValue(t, value, null);
}
}
ts.Add(t);
}
return ts;
}
}

 

//End C#--------------------------------------------------------------------------------------------------------------------------------

 

//Start分页存储过程模版------------------------------------------------------------------------------------------------------------------------------

CREATE PROC Proc_ProcName
(
@参数1 NVARCHAR(50) ,
@参数2 NVARCHAR(50) ,
-- ...
--最后两个参数必须是pageindex 和pagesize用于分页
@pageIndex INT ,
@pageSize INT
)
AS
BEGIN
SELECT *
FROM ( SELECT DISTINCT
ROW_NUMBER() OVER ( ORDER BY ID ) AS RowId ,
查询字段1,查询字段1...

FROM yourTable
) AS A
WHERE A.RowId BETWEEN ( ( @pageIndex - 1 ) * @pageSize + 1 )
AND ( @pageIndex * @pageSize );
END

//End 分页存储过程模版------------------------------------------------------------------------------------------------------------------------------