Question

毕业项目求c#高效分页，不要存储过程，只想要后台的

Answer 1

/// <summary>
/// 根据sql取分页数据，第一个表是取的分页数据，第二个表第一列是数据总数, 如果sql出错，抛出异常，自动把select * from 表 where条件 order by 列 转换成 select count(1) from 表 where 条件
/// </summary>
/// <param name="sql">sql语句，最外层的sql语句，查询条件后，有且只有 order by</param>
/// <param name="aPage">要取的第几页的数据，从1开始</param>
/// <param name="aPageSize">每页显示行数</param>
/// <returns></returns>
public static DataSet GetPageDataSet(string sql,int aPage,int aPageSize)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
DataSet ds = new DataSet();
con.Open();
try
{
int posOfSelect = sql.IndexOf("select", StringComparison.CurrentCultureIgnoreCase);
int posOfFrom = sql.IndexOf("from", StringComparison.CurrentCultureIgnoreCase);
int posOfOrder = sql.LastIndexOf(" order by", StringComparison.CurrentCultureIgnoreCase);
string countSql = sql.Substring(0, posOfSelect + 7);
countSql += " count(1) ";
countSql += sql.Substring(posOfFrom, posOfOrder - posOfFrom + 1);
DataTable dtCount = new DataTable();
dtCount.TableName = "tbCount";
cmd.CommandText = countSql;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dtCount);
int page = aPage;
int TotalRowCount = int.Parse(dtCount.Rows[0][0].ToString());
if (TotalRowCount <= ((aPage - 1) * aPageSize))
{
page = 1;
}
cmd.CommandText = sql;
da = new SqlDataAdapter(cmd);
da.Fill(ds, (aPage - 1) * aPageSize, aPageSize, "first");
ds.Tables.Add(dtCount);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
}
}