public static DataSet GetPageList(string selectCommandText, List<SqlParameter> paramList, int pageSize, int pageIndex, string filedOrder, out string recordTotal)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
StringBuilder SQLString = new StringBuilder();
SQLString.Append(@"
BEGIN
DECLARE @beginRow INT;
DECLARE @endRow INT;
SET NOCOUNT ON
SET @beginRow = (@pageIndex - 1) * @pageSize + 1;
SET @endRow = @pageIndex * @pageSize;
--输出参数为总记录数
SELECT @recordTotal = COUNT(1) FROM ("+ @selectCommandText + @") AS my_temp;
--主查询返回结果集
SELECT * FROM (SELECT ROW_NUMBER() OVER (order by @orderString) AS rows ,* FROM (" + @selectCommandText + @") AS A) AS main_temp
WHERE rows BETWEEN CAST(@beginRow AS VARCHAR) AND CAST(@endRow AS VARCHAR);
SET NOCOUNT OFF
END");
connection.Open();
SqlCommand cmd = new SqlCommand(SQLString.ToString(), connection);
if (paramList != null && paramList.Count > 0)
{
foreach (SqlParameter param in paramList)
{
if ((param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Input) &&
(param.Value == null))
{
param.Value = DBNull.Value;
}
cmd.Parameters.Add(param);
}
}
recordTotal = "0";
cmd.Parameters.Add(new SqlParameter("@recordTotal", SqlDbType.Int, 8) { Value = int.Parse(recordTotal), Direction = ParameterDirection.Output });
cmd.Parameters.Add(new SqlParameter("@pageSize", SqlDbType.Int, 8) { Value = pageSize });
cmd.Parameters.Add(new SqlParameter("@pageIndex", SqlDbType.Int, 8) { Value = pageIndex });
cmd.Parameters.Add(new SqlParameter("@orderString", SqlDbType.VarChar, 1000) { Value = filedOrder });
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = cmd;
DataSet ds = new DataSet();
sqlDA.Fill(ds);
recordTotal = cmd.Parameters["@recordTotal"].Value != DBNull.Value ? cmd.Parameters["@recordTotal"].Value.ToString() : "0";
connection.Close();
cmd.Parameters.Clear();
return ds;
}
catch (Exception ex)
{
throw ex;
}
}
}
C#SQL SERVER分页
最新推荐文章于 2022-03-24 10:35:26 发布
这段代码展示了如何使用C#进行SQL分页查询,同时处理输入参数和输出参数,计算总记录数,并通过SqlDataAdapter填充DataSet。核心部分是利用ROW_NUMBER()函数实现分页,以及设置参数值为DBNull.Value来避免空值错误。
摘要由CSDN通过智能技术生成