qdzx2008的专栏

不能则学,不知则问,耻于问人,决无长进.如何能得到快乐:抛弃仇恨、远离烦恼、生活简单、淡泊名利、为人着想、笑口常开...

通过DataSet绑定多个表的例子

相关存储过程:

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE procedure [dbo].[sp_PageChange]
         @PageIndex INT,                            
         @PageSize  INT,                            
         @RecordCount INT OUT,               
         @PageCount INT OUT,                 
         @strGetFields nvarchar(1000),       
         @tableName nvarchar(500) ,          
         @ID nvarchar(100),                  
         @strWhere  nvarchar(1000) ='',      
         @sortName nvarchar(50) =' asc ' ,   
         @orderName nvarchar(100)            

AS
declare @countSelect nvarchar(2000) 
--设置统计查询语句
if len(@strWhere) =0
--如果没有查询条件
    begin
        set @countSelect=N'SELECT @CountRecord = COUNT(*)  FROM '+@tableName
    end
else
--否则
    begin
        set @countSelect=N'SELECT @CountRecord = COUNT(*)  FROM '+@tableName+' where '+@strWhere
    end
--执行并返回总数
exec sp_executesql @countSelect,N'@CountRecord int output',@RecordCount output
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)

SET NOCOUNT ON

DECLARE @SQLSTR NVARCHAR(3000)
--实际总共的页码小于当前页码 或者 最大页码
if @PageCount>=0
    --如果分页后页数大于0
    begin
        if @PageCount<=@PageIndex and  @PageCount>0   --如果实际总共的页数小于datagrid索引的页数
            --or @PageCount=1
            begin
                --设置为最后一页
    set @PageIndex=@PageCount-1
            end
        else if @PageCount<=@PageIndex and  @PageCount=0
            begin
                set @PageIndex=0;
            end
    end
--如果用普通的sql而不使用存储过程调用
declare @ID_temp varchar(100)
set @ID_temp='cast('+@ID+' as nvarchar(100)) '
declare @returnValue nvarchar(100)
set @returnValue=','''+cast(@RecordCount as nvarchar(100)) +'|'+cast(@PageCount as nvarchar(100))+'|'+'''+'+@ID_temp+' as [returnValue] '
--如果用普通的sql而不使用存储过程调用
IF @PageIndex = 0 OR @PageCount <= 1  --如果为第一页
    begin
        if len(@strWhere) =0
            begin
                SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+@strGetFields+@returnValue+' FROM  '+@tableName+' ORDER BY '+@orderName+@sortName
            end
        else
            begin
                SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+@strGetFields+@returnValue+'  FROM  '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName
            end
    end
ELSE IF     @PageIndex = @PageCount - 1 --如果为最后一页           
    begin
        if len(@strWhere) =0
            begin
                SET @SQLSTR =N' SELECT '+@strGetFields+@returnValue+'  FROM '+@tableName+' where '+@ID+' not in  ( SELECT TOP '+STR(/*@RecordCount - */@PageSize * @PageIndex )+@ID+'  FROM  '+@tableName+' ORDER BY '+@orderName+@sortName+' )   ORDER BY '+@orderName+@sortName
            end
        else
            begin
                SET @SQLSTR =N' SELECT '+@strGetFields+@returnValue+'  FROM '+@tableName+' where '+@ID+' not in  ( SELECT TOP '+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+'  FROM  '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName+' )  and '+@strWhere+' ORDER BY '+@orderName+@sortName
            end
    end
ELSE                                                              --否则执行 
    begin
         if len(@strWhere) =0
            begin
                SET @SQLSTR =N' SELECT TOP  '+STR( @PageSize )+@strGetFields+@returnValue+' FROM '+@tableName+' where '+@ID+' not in  ( SELECT TOP '+STR( /*@RecordCount - */@PageSize * @PageIndex )+' '+@ID+'  FROM  '+@tableName+' ORDER BY '+@orderName+@sortName+' )  ORDER BY '+@orderName+@sortName
            end
         else
            begin
                SET @SQLSTR =N' SELECT TOP  '+STR( @PageSize )+@strGetFields+@returnValue+' FROM '+@tableName+' where '+@ID+' not in  (SELECT TOP '+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+'  FROM  '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName+' )and '+@strWhere+'ORDER BY '+@orderName+@sortName
            end
    end
EXEC (@SQLSTR)

set nocount off
GO


CREATE PROCEDURE sp_Get_FS
AS
BEGIN
execute sp_PageChange 0,12,0,0,'*','[SXGGW]','id','yesno=1',' addtime desc',''
execute sp_PageChange 1,12,0,0,'*','[SXGGW]','id','yesno=1',' addtime desc','' 
execute sp_PageChange 2,12,0,0,'*','[SXGGW]','id','yesno=1',' addtime desc',''
execute sp_PageChange 3,12,0,0,'*','[SXGGW]','id','yesno=1',' addtime desc','' 
END
GO


页面代码:
 SqlDataAdapter adpt = new SqlDataAdapter("sp_Get_FS", ConfigurationManager.AppSettings["ConnectionString"]);
            DataSet ds = new DataSet();
            adpt.Fill(ds, "talbe");
            dl_list1.DataSource = ds.Tables[0];
            dl_list1.DataBind();
            dl_list2.DataSource = ds.Tables[1];
            dl_list2.DataBind();
            dl_list3.DataSource = ds.Tables[2];
            dl_list3.DataBind();
            dl_list4.DataSource = ds.Tables[3];
            dl_list4.DataBind();

阅读更多
个人分类: .Net
上一篇asp.net中窗口相关操作总结(javascript)(转)
下一篇修改UrlRewrite以对域名进行重写,即实现二级或多级域名
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭