How to loop through the records in ADO.NET sqldatareader ?

Question

0.00/5 (No votes)

See more:

I have a VB.NET code which originally had ADO connections with Recordsets to get the data from databases and do what it supposed to. But now I have replaced all the ADO connections with ADO.NET SqlDataReader() and no more Recordsets I have in the code. So previously the code had recordset.MoveNext() method to move to the next row in the records once it's done the work. But now since I do not have any recordsets and since I have SqlDataReader() to keep track of records how should I move to the next row or next record?

So the question again is, in the current version it does not loop to the next record automatically like it used to do with rs1.MoveNext(). What should I do here?

What I have tried:

VB

Example what I had before :

strSelectStatement = "SELECT * FROM " & Trim(strALL_LABOR_TABLE) & " WHERE posted = '' AND work_order IS NOT NULL ORDER BY SOURCE_ID"
FileIO.WriteLog("<--------------- Begining of posting records --------------->")

If rs1.State = 1 Then rs1.Close()
rs1.Open(strSelectStatement, cnADOEDGE_DSN, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)

strStatus = ""

Do Until rs1.EOF
'Do whatever the job it has to do

rs1.MoveNext()

Loop


Example what I have now :

strSelectStatement = "SELECT * FROM " & Trim(strALL_LABOR_TABLE) & " WHERE posted = '' AND work_order IS NOT NULL ORDER BY SOURCE_ID"
FileIO.WriteLog("<--------------- Begining of posting records --------------->")

Dim Sql3 = "SELECT * FROM " & Trim(strALL_LABOR_TABLE) & " WHERE posted = '' AND work_order IS NOT NULL ORDER BY SOURCE_ID"
Dim postCount3 As SqlDataReader
Dim newServer3 As String = ConfigurationManager.AppSettings("newServer")

Try
Using cn3 As New SqlConnection(newServer3),
cmd3 As New SqlCommand(Sql3, cn3)
cn3.Open()
postCount3 = cmd3.ExecuteReader()
If postCount3.HasRows Then
While postCount3.Read()

Try
'Do whatever the job it has to do
Catch ex As Exception
FileIO.WriteLog("Error : " & ex.Message)
End Try

End While
Else
FileIO.WriteLog("Error : There are no records to select from ALL_LABOR_DETAILS table")
End If

postCount3.Close()
cn3.Close()
End Using
Catch ex As Exception
FileIO.WriteLog(strOrderNumber & " There was an error getting the laborDetails FROM [MESDEV].[dbo].[ALL_LABOR_DETAILS] WHERE posted = '' AND work_order IS NOT NULL ORDER BY SOURCE_ID. Error Messege : " & ex.Message)
End Try

Posted 22-Oct-21 8:13am

GlennAshan

Updated 22-Oct-21 8:26am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 15329613 · Answer 1 · 2021-10-22T08:26:00

Edit, sorry, just remembered you are doing VB.Net.

You already have the code to go next. You have

While postCount3.Read()

which will read a record until it hits the end of your while and then it will call .Read() again and again until there are no more records.

End Edit

A lot has changed since then including the use of usings. So, your code should look something like this:

C#

try
{
  String sql = "StoredProcedureName";
  using (SqlConnection sqlCon = new SqlConnection(connectionString))
  {
    sqlCon.Open();

    using (SqlCommand cmd = new SqlCommand(sql, sqlCon))
    {
      cmd.CommandType = System.Data.CommandType.StoredProcedure;

      // add parameters if necessary

      using (SqlDataReader dr = cmd.ExecuteReader())
      {
        if (dr.HasRows)
        {
          while (dr.Read()){
          {
             someVar.field1 = dr["field1"].ToString();
             // ....

          }
        }   
       }
     }
   }      
}
catch (Exception ex)
{
  Log.LogError(ex);
//  do whatever
}