Nx.DBUtility 2.1.0

• • Nx (>= 2.4.1.3)

//先执行存储过程以支持翻页功能，请勿修改存储过程名称
     
       --用于DBUtility翻页功能 BEGIN
       CREATE PROCEDURE [dbo].[sp_DataGroupPageForDuoTable]
       @rowcount int output, --总记录数，共有几条信息
       @PageSize int, --每页显示记录条数
       @PageIndex int, --第几页
       @wheresql nvarchar(1000), --SQL条件语句
       @ordersql nvarchar(200), --SQL排序语句
       @TableName nvarchar(1000), --查询表名称
       @filed nvarchar(1000)
       
       AS
       declare @mainsql nvarchar(4000)
       declare @fieldsql nvarchar(4000)
       declare @rownumbersql nvarchar(4000)
       declare @exesql nvarchar(4000)
       declare @countsql nvarchar(4000)

       set @mainsql = @TableName +' where 1=1 ' + @wheresql
       set @fieldsql = ' '+@filed+' '   
       set @rownumbersql = 'select row_number() over('+@ordersql+') as pos,'+@fieldsql+' from '+@mainsql
       set @exesql = 'SELECT * FROM ('+@rownumbersql+') AS sp WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)
       set @countsql='select @totalcount=count(*) from '+ @mainsql
       print @exesql
       exec sp_executesql @countsql,N'@totalcount int out',@rowcount output
       SET NOCOUNT ON;
       exec (@exesql)
       SET NOCOUNT OFF;
       --用于DBUtility翻页功能 END
       
     //配置App.config or web.config
       <configuration>
         <appSettings>
           <add key="ConnectionString" value="Data Source=192.168.1.99;Initial Catalog=testdb;User ID=sa;Password=yourpwd"/>
         </appSettings>
       </configuration>
       
     //Demo  
     static void Main(string[] args)
     {
         //翻页读取数据
         SqlPagedData pagedData = new SqlPagedData(
             startPageIndex: 1,
             pageSize: 200,
             sqlWhere: " and cid>10000",
             tableName: "Client",
             sqlOrderBy: " order by cid desc  ",
             fileds: "cid,tel,email");

         while (true)
         {
             var item = pagedData.Next();
             if (item == null)
             {
                 break;
             }
             //注意这里动态数据的属性，是大小写敏感的。我只测试了sql2005。
             //情况1：如果构造函数时传入的fields为星号(*), 那么属性名的大小写必须和数据库字段大小写相同。
             //情况2：如果构造函数时传入的fields为自定义，如本例的（cid,tel,email）那么属性名的大小写必须和构造类时传入的参数相同。
             Console.WriteLine("{0} {1} {2} i:{3}", item.cid, item.tel, item.email, item.tel + " i:" + pagedData.Position.PageIndex);
         }

         //如果数据源没有改变，可以随时保留位置以便下次继续处理剩余数据
         var pos = pagedData.Position;

         //模拟下次处理
         pagedData = new SqlPagedData(pos);
         while (true)
         {
             var item = pagedData.Next();
             if (item == null)
             {
                 break;
             }
             Console.WriteLine("{0} {1} {2} i:{3}", item.cid, item.tel, item.email, item.tel + " i:" + pagedData.Position.PageIndex);
         }
     }