/--------存储过程代码-------------/SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER procedure SqlDataPaging
@tbName varchar(255), --表名
@tbFields varchar(1000), --返回字段
@OrderField varchar(255), --排序的字段名
@PrimaryField varchar(255), --主键
@PageSize int, --页尺寸
@PageIndex int, --页码
@OrderType bit, --排序类型,1是升序,0是降序
@strWhere varchar(1500), --查询条件
@Total int output --返回总记录数
as
declare @strSql varchar(5000) --主语句
declare @strOrder varchar(200) --排序
declare @strSqlCount nvarchar(500)--查询记录总数主语句
declare @strtemp varchar(50) --排序附加语句
--------------排序-----------------1是降序,0未升序
if @OrderType <>0
begin
set @strtemp= '>(select max('
set @strOrder='order by ' + @OrderField + ' asc '
end
else
begin
set @strtemp= '<(select min('
set @strOrder='order by '+ @OrderField + ' desc'
end
--------------总记录数---------------
if @strWhere <>''
begin
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName + ' where '+ @strWhere
end
else
begin
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName
end
--------------如果是第一页------------
if @PageIndex =1
begin
if @strWhere <>''
begin
set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName +' where ' + @strWhere + ' ' + @strOrder
end
else
begin
set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName + ' ' + @strOrder
end
end
else
begin
if @strWhere <>''
begin
set @strSql='Select top ' +str(@PageSize) + ' ' + @tbFields
+' from ' + @tbName + ' where '+@PrimaryField +' '
+ @strtemp+ ' ' +@PrimaryField+ ') from (select top'
+str((@PageIndex-1)*@PageSize) + ' '+@PrimaryField +' from '+@tbName
+' where ' +
+ @strWhere+ ' '+ @strOrder +') as tb)'
+ ' AND ' + @strWhere + ' ' +
+ @strOrder
end
else
begin
set @strSql='Select top ' + str(@PageSize) + ' ' + @tbFields
+' from ' + @tbName + ' where '+@PrimaryField + ' '
+ @strtemp+ ' ' +@PrimaryField+ ') from (select top'
+str((@PageIndex-1)*@PageSize) + ' '+@PrimaryField +' from '+@tbName
+ ' '+ @strOrder +') as tb)'+ @strOrder
end
end
print @strSqlCount
print @strSql
exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output
exec(@strSql)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/--------调用代码-------------/ 不知道这里怎么调用这个存储过程?这里有问题
SqlParameter[] parms = new SqlParameter[]
{
new SqlParameter("@tbName",SqlDbType.Int),
};
parms[0].Value = "Company"; SqlDataReader Dr = Rsyll.DAL.SqlHelper.ExeDr(CommandType.StoredProcedure, "SqlDataPaging", parms);
/--------SqlHelper-------------/
public static SqlDataReader ExeDr(CommandType Tyle, string SqlStr, params SqlParameter[] parameters)
{
SqlCommand ComDr = GetCommand(Tyle, SqlStr, parameters);
SqlDataReader Dr = ComDr.ExecuteReader(CommandBehavior.CloseConnection);
ComDr.Parameters.Clear();
return Dr;
}
//一个被重用的得到Command命令对象的方法
private static SqlCommand GetCommand(CommandType commandtype, string sql, params SqlParameter[] parameters)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = new SqlConnection(SqlLoading);
cmd.CommandType = commandtype;
cmd.CommandText = sql;
if (parameters != null)
{
foreach (SqlParameter p in parameters)
{
cmd.Parameters.Add(p);
}
}
cmd.Connection.Open();
return cmd;
}/--------这是原始的绑定-------------/
IList<Company_Model> CompanyList = Rsyll.BLL.Company_BLL.CompanyList();
AspNetPager.RecordCount = CompanyList.Count;
PagedDataSource Page = new PagedDataSource();
Page.DataSource = CompanyList;
Page.AllowPaging = true;
Page.CurrentPageIndex = AspNetPager.CurrentPageIndex - 1;
Page.PageSize = AspNetPager.PageSize;
HtmCompanyList.DataSource = Page;
HtmCompanyList.DataBind();
GO
SET ANSI_NULLS ON
GOALTER procedure SqlDataPaging
@tbName varchar(255), --表名
@tbFields varchar(1000), --返回字段
@OrderField varchar(255), --排序的字段名
@PrimaryField varchar(255), --主键
@PageSize int, --页尺寸
@PageIndex int, --页码
@OrderType bit, --排序类型,1是升序,0是降序
@strWhere varchar(1500), --查询条件
@Total int output --返回总记录数
as
declare @strSql varchar(5000) --主语句
declare @strOrder varchar(200) --排序
declare @strSqlCount nvarchar(500)--查询记录总数主语句
declare @strtemp varchar(50) --排序附加语句
--------------排序-----------------1是降序,0未升序
if @OrderType <>0
begin
set @strtemp= '>(select max('
set @strOrder='order by ' + @OrderField + ' asc '
end
else
begin
set @strtemp= '<(select min('
set @strOrder='order by '+ @OrderField + ' desc'
end
--------------总记录数---------------
if @strWhere <>''
begin
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName + ' where '+ @strWhere
end
else
begin
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName
end
--------------如果是第一页------------
if @PageIndex =1
begin
if @strWhere <>''
begin
set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName +' where ' + @strWhere + ' ' + @strOrder
end
else
begin
set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName + ' ' + @strOrder
end
end
else
begin
if @strWhere <>''
begin
set @strSql='Select top ' +str(@PageSize) + ' ' + @tbFields
+' from ' + @tbName + ' where '+@PrimaryField +' '
+ @strtemp+ ' ' +@PrimaryField+ ') from (select top'
+str((@PageIndex-1)*@PageSize) + ' '+@PrimaryField +' from '+@tbName
+' where ' +
+ @strWhere+ ' '+ @strOrder +') as tb)'
+ ' AND ' + @strWhere + ' ' +
+ @strOrder
end
else
begin
set @strSql='Select top ' + str(@PageSize) + ' ' + @tbFields
+' from ' + @tbName + ' where '+@PrimaryField + ' '
+ @strtemp+ ' ' +@PrimaryField+ ') from (select top'
+str((@PageIndex-1)*@PageSize) + ' '+@PrimaryField +' from '+@tbName
+ ' '+ @strOrder +') as tb)'+ @strOrder
end
end
print @strSqlCount
print @strSql
exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output
exec(@strSql)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/--------调用代码-------------/ 不知道这里怎么调用这个存储过程?这里有问题
SqlParameter[] parms = new SqlParameter[]
{
new SqlParameter("@tbName",SqlDbType.Int),
};
parms[0].Value = "Company"; SqlDataReader Dr = Rsyll.DAL.SqlHelper.ExeDr(CommandType.StoredProcedure, "SqlDataPaging", parms);
/--------SqlHelper-------------/
public static SqlDataReader ExeDr(CommandType Tyle, string SqlStr, params SqlParameter[] parameters)
{
SqlCommand ComDr = GetCommand(Tyle, SqlStr, parameters);
SqlDataReader Dr = ComDr.ExecuteReader(CommandBehavior.CloseConnection);
ComDr.Parameters.Clear();
return Dr;
}
//一个被重用的得到Command命令对象的方法
private static SqlCommand GetCommand(CommandType commandtype, string sql, params SqlParameter[] parameters)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = new SqlConnection(SqlLoading);
cmd.CommandType = commandtype;
cmd.CommandText = sql;
if (parameters != null)
{
foreach (SqlParameter p in parameters)
{
cmd.Parameters.Add(p);
}
}
cmd.Connection.Open();
return cmd;
}/--------这是原始的绑定-------------/
IList<Company_Model> CompanyList = Rsyll.BLL.Company_BLL.CompanyList();
AspNetPager.RecordCount = CompanyList.Count;
PagedDataSource Page = new PagedDataSource();
Page.DataSource = CompanyList;
Page.AllowPaging = true;
Page.CurrentPageIndex = AspNetPager.CurrentPageIndex - 1;
Page.PageSize = AspNetPager.PageSize;
HtmCompanyList.DataSource = Page;
HtmCompanyList.DataBind();
解决方案 »
- 关于C# winform窗体程序自动编号的问题
- 请问水晶报表如何传递参数字段值
- 格式11/Jul/2009:00:10:49 转换 datetime
- .net 2。0不能读写
- OleDbDataAdapter.update无法更新到access数据库,搞不定,高分求教,速度结帖
- 请教一个C#+后台SQL的问题!
- 如何让 HyperLink 控件的 Text属性内容闪烁啊???
- 如何在datagrid控件中添加一列按钮
- DataGird 问题 100分
- HELP,请推荐一个高性能文本编辑器带折叠功能
- SQL连接时的界面更新问题(线程相关)
- SOCKET一服务器对100客户端,是服务器端是选择一个端口还是100个端口啊
那如果数据量大的话,那不是要直接翻死,这种直接用控件,是数据量小