如何在循环中保持增加Sql Server偏移量 [英] How Do I Keep Incrementing Sql Server Offset In A Loop

问题描述

Hello Everyone，



i我正在开发一个项目，需要我使用sFS server 2012的OFFSET和FETCH NEXT关键字，这对我来说很新，我希望能够增加OFFSET以便我可以批量获取记录，下面是我的代码：

Hello Everyone,

i am working on a project that requires me to use OFFSET and FETCH NEXT Keyword of the sql server 2012, this is pretty new to me, i want to be able to increment the OFFSET so i could fetch records in batches, below is my code:

private static void EdmsLands()
       {
           var catid = "";
           var strCon = ConfigurationManager.ConnectionStrings["edms"].ConnectionString;

           try
           {
               SqlDataReader rder = null;
               using (
                   var connection = new SqlConnection( strCon ) )
               {
                   var command = new SqlCommand( "select  top 1 catid from csowner.CatRegionMap where CatName = 'lands'", connection );
                   command.CommandType = CommandType.Text;
                   connection.Open( );
                   rder = command.ExecuteReader( );
                   while ( rder.Read( ) )
                   {
                       catid = ( rder["catid"].ToString( ) );
                   }
               }
               rder.Close( );
               rder.Dispose( );
           }
           catch ( Exception ex )
           {
               MessageBox.Show( ex.Message );
           }

           try
           {

               using (
                   var connection = new SqlConnection( strCon ) )
               {
                   var listAttributeRegion = new List<long>( );
                   SqlDataReader reader = null;
                   const string squery =
                       "SELECT id from csowner.LLAttrData join csowner.dtree d on  ID = d.DataID join csowner.DVersData dv on d.VersionNum = dv.Version and d.DataID = dv.DocID where AttrType = -18 and DefID = @DefId and LLAttrData.VerNum = dv.Version ORDER BY id ASC OFFSET (@OFFSET-@ROWSIZE) + @ROWSIZE ROWS FETCH NEXT @ROWSIZE ROWS ONLY";
                   var command = new SqlCommand( squery, connection );
                   command.CommandType = CommandType.Text;
                   connection.Open( );
                   command.Parameters.Add( "@DefID", SqlDbType.Int ).Value = catid;
                   command.Parameters.Add( "@OFFSET", SqlDbType.Int ).Value = 0;
                   command.Parameters.Add( "@ROWSIZE", SqlDbType.Int ).Value = 15;
                   reader = command.ExecuteReader( );


                   while ( reader.Read( ) )
                   {
                       listAttributeRegion.Add( Convert.ToInt64( reader["id"].ToString( ) ) );
                   }

                   var cdAttrlist = new List<CDocumentAttr>( );
                   foreach ( long k in listAttributeRegion )
                   {
                       var dataId = k;

                       const string sqlquery =
                           "Select l.AttrID , l.ValStr from csowner.LLAttrData l join csowner.dtree d on  ID = d.DataID join csowner.DVersData dv on d.VersionNum = dv.Version and d.DataID = dv.DocID where d.DataID =@DataID and l.VerNum = dv.Version";
                       using (
                           var con =
                               new SqlConnection( strCon ) )
                       {
                           using ( var com = new SqlCommand( sqlquery, con ) )
                           {
                               com.CommandType = CommandType.Text;
                               con.Open( );
                               com.Parameters.Add( "@DataID", SqlDbType.Int ).Value = dataId;
                               SqlDataReader rdr = com.ExecuteReader( );

                               var cdAttr = new CDocumentAttr( );
                               Int64 count = 0;
                               Int64 attrId = 0;
                               while ( rdr.Read( ) )
                               {
                                    attrId = Convert.ToInt64( rdr["AttrID"].ToString( ) );
                                   var value = rdr["ValStr"].ToString( );
                                   count = attrId;
                                   //var value = rdr.IsDBNull( 1 ) ? "" : rdr.GetString( 1 );
                                   switch ( count )
                                   {
                                       case 3:
                                           cdAttr.Lga = value;
                                           break;
                                       case 4:
                                           cdAttr.Area = value;
                                           break;
                                       case 6:
                                           cdAttr.CofO = value;
                                           break;
                                       case 7:
                                           cdAttr.RegistrationNumber = value;
                                           break;
                                       case 8:
                                           cdAttr.ApplicantName = value;
                                           break;
                                       case 10:
                                           cdAttr.Location  = value;
                                           break;
                                       case 12:
                                           cdAttr.Grantor = value;
                                           break;
                                       case 13:
                                           cdAttr.Grantee = value;
                                           break;
                                       case 14:
                                           if (string.Equals(value, DocumentTypeEnum.Commercial.ToString(),
                                               StringComparison.OrdinalIgnoreCase))
                                               cdAttr.LandPurpose = DocumentTypeEnum.Commercial;
                                           else
                                               cdAttr.LandPurpose = DocumentTypeEnum.Residential;
                                           break;
                                       case 15:
                                           cdAttr.ApplicationType = value;
                                           break;
                                       case 16:
                                           //DateTime appDate = DateTime.ParseExact( value, "dd MM yyyy", CultureInfo.InvariantCulture );
                                           //cdAttr.ApplicationDate = appDate;
                                           break;
                                   }

                                   count++;
                               }

                               cdAttrlist.Add( cdAttr );

                           }
                       }
                   }


                   reader.Close( );
                   reader.Dispose();
                   var postData = new DataImportClient( );
                   postData.postDocumentAttribute(cdAttrlist.ToArray());

               }
           }
           catch ( SqlException ex )
           {
               var w = ex.Message;
           }
       }

来自代码，第一次传递取0-15条记录，我想能够循环它，以便第二次传递将从16-30记录中取出，请问我该如何实现？任何协助将不胜感激。在此先感谢。

from the code, the first pass fetches 0-15 records, i want to able to loop it so that the second pass will fetch from 16-30 records, Please how do i achieve this? any assistance will be appreciated. Thanks in advance.

推荐答案

这篇关于如何在循环中保持增加Sql Server偏移量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！