我是新手刚学net不久,这个是我在asp里面经常用的,速度还可,可以应付百万级的数据量,适用于任何复杂的SQL语句,现在把它转成net三层架构,我对net研究不多用的不多,不知道这样效率怎么样,贴出来让大家看看这样做行不行?
所使用的存储过程
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
CREATE PROCEDURE Page
@iPage int=1, --当前页码
@iPageSize int=10,--每页条数
@StrTable varchar(200),--查询的表
@StrText varchar(1000),--查询的字段
@StrWhere varchar(1000),--条件
@StrIndex varchar(30),--索引
@StrOrder varchar(100)='',--排序字段
@StrTotals int output --返回总条数
AS
--定义变量
declare @SqlCount nvarchar(2000)
declare @Sql nvarchar(2000)
declare @TempOrder nvarchar(1000)
if @StrOrder<>""
begin
set @TempOrder=' order by '+@StrOrder
end
else
begin
set @TempOrder=''
end
--组合sql语句
if @iPage=1
begin
set @Sql='select top '+str(@iPageSize)+' '+@StrText+' from '+@StrTable+' where 1=1 '+@StrWhere+@TempOrder
end
else
begin
set @Sql='select top '+str(@iPageSize)+' '+@StrText+' from '+@StrTable+' where '+@StrIndex+' not in (select top '+str(@iPageSize*(@iPage-1))+' '+@StrIndex+' from '+@StrTable+' where 1=1 '+@StrWhere+@TempOrder+') '+@StrWhere+@TempOrder
end
set @SqlCount='select @StrTotals=isnull(count(*),10000) from '+@StrTable+' where 1=1 '+@StrWhere
--查询总记录数量
exec sp_executesql @SqlCount,N'@StrTotals int output',@StrTotals output
--执行sql语句返回
exec (@Sql)
GO
SQLHelper类
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
public class SQLHelper
{
private SqlConnection conn = null;
private SqlCommand cmd = null;
private SqlDataReader sdr = null;
public SQLHelper()
{
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
conn = new SqlConnection(connStr);
}
/// <summary>
/// 取得数据库的链接
/// </summary>
/// <returns></returns>
private SqlConnection GetConn()
{
#region
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
return conn;
#endregion
}
/// <summary>
/// 执行分页查询
/// </summary>
/// <param name="iPage">当前页码</param>
/// <param name="iPageSize">每页条数</param>
/// <param name="strTable">查询的表</param>
/// <param name="strText">查询的字段</param>
/// <param name="strWhere">查询的条件</param>
/// <param name="strIndex">索引</param>
/// <param name="strOrder">排序字段</param>
/// <param name="outCount">输出数据总条数</param>
/// <returns></returns>
public DataTable ExecutePage(int iPage, int iPageSize, string strTable, string strText, string strWhere, string strIndex, string strOrder, out int outCount)
{
#region
outCount = 0;
DataTable dt = new DataTable();
cmd = new SqlCommand("Page", GetConn());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(new SqlParameter[] {
new SqlParameter("@iPage",iPage),
new SqlParameter("@iPageSize", iPageSize),
new SqlParameter("@StrTable", strTable),
new SqlParameter("@StrText", strText),
new SqlParameter("@StrWhere", strWhere),
new SqlParameter("@StrIndex", strIndex),
new SqlParameter("@StrOrder", strOrder),
new SqlParameter("@StrTotals",outCount)
});
cmd.Parameters["@StrTotals"].Direction = ParameterDirection.Output;
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
outCount = int.Parse(cmd.Parameters["@StrTotals"].Value.ToString());
}
return dt;
#endregion
}
}
Model层
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
/// <summary>
/// 分页实体
/// </summary>
public class PageInfo
{
private int iPage;
/// <summary>
/// 当前页码
/// </summary>
public int IPage
{
get { return iPage; }
set { iPage = value; }
}
private int iPageSize;
/// <summary>
/// 每页条数
/// </summary>
public int IPageSize
{
get { return iPageSize; }
set { iPageSize = value; }
}
private string strTable;
/// <summary>
/// 查询的表
/// </summary>
public string StrTable
{
get { return strTable; }
set { strTable = value; }
}
private string strText;
/// <summary>
/// 查询的字段
/// </summary>
public string StrText
{
get { return strText; }
set { strText = value; }
}
private string strWhere;
/// <summary>
/// 查询的条件
/// </summary>
public string StrWhere
{
get { return strWhere; }
set { strWhere = value; }
}
private string strIndex;
/// <summary>
/// 索引
/// </summary>
public string StrIndex
{
get { return strIndex; }
set { strIndex = value; }
}
private string strOrder;
/// <summary>
/// 排序字段
/// </summary>
public string StrOrder
{
get { return strOrder; }
set { strOrder = value; }
}
}
DAL层
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
/// 分页数据获取类
/// </summary>
public class PageDAO
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
private SQLHelper sqlhelper = null;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public PageDAO()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sqlhelper = new SQLHelper();
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 分页方法
/// </summary>
/// <param name="p">分页实体</param>
/// <param name="outCount">数据总数</param>
/// <returns></returns>
public DataTable Page(PageInfo p, out int outCount)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DataTable dt = new DataTable();
dt = sqlhelper.ExecutePage(p.IPage, p.IPageSize, p.StrTable, p.StrText, p.StrWhere, p.StrIndex, p.StrOrder, out outCount);
return dt;
}
}
BLL层
public class PageBll
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
private PageDAO pDao = null;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public PageBll()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
pDao = new PageDAO();
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 分页方法
/// </summary>
/// <param name="p">分页实体</param>
/// <param name="outCount">数据总数</param>
/// <returns></returns>
public DataTable Page(PageInfo p, out int outCount)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return pDao.Page(p, out outCount);
}
}
页面调用
private PageBll pBll = new PageBll();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
//原SQL语句
//select l.id,l.inDate,l.outDate,u.[name],u.worderNumber,c.[name] from live l
//inner join [user] u on l.userId=u.id
//inner join card c on c.id=u.cardId
//where u.[name] like '杜毛' or u.worderNumber='dl0010'
//order by l.id desc
//加入分页实体
PageInfo p = new PageInfo();
p.IPage = 1;
p.IPageSize = 10;
p.StrTable = "live l inner join [user] u on l.userId=u.id inner join card c on c.id=u.cardId";
p.StrText = "l.id,l.inDate,l.outDate,u.[name],u.worderNumber,c.[name]";
p.StrWhere = "and u.[name] like '杜毛' or u.worderNumber='dl0010'";
p.StrIndex = "l.id";
p.StrOrder = "l.id desc";
int outCount;
GridView1.DataSource = pBll.Page(p, out outCount).DefaultView;
GridView1.DataBind();
Response.Write(outCount);
}
}