如何在循环中保持增加Sql Server偏移量

本文介绍了如何在循环中保持增加Sql Server偏移量的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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.

推荐答案