I’m working on an app that has to iterate through hundreds of thousands of records in a SQL database. Since VB.NET isn’t my strongest skill, and is required by the organization paying me to write it, I decided to look for suggestions on how exactly to do it. I initially thought I’d need to use a Do While Loop. I Googled for an example and found an
article by Microsoft that seemed to have a good way to iterate through the records.
It works great, as long as speed isn’t a priority. If you need speed, try a DO WHILE Loop. In my testing I found that it’s about 10x faster than the For Next loop.
The sample code below may have some formatting issues due to text wrapping. Also I left out the declarations.
Here’s the sample code I used to test the For Next loop:
objSQLCmd = New SqlCommand("SELECT [Territory], [Company],” & _ “[Policyno], [Module], [Covatot], [CovctotS], [CovdtotS], [CovetotS],” & _ “[Yearbuilt], [Consttype], [Stories], [retrofit], [Covaded], [Term],” & _
“[TermS], [poltype1] FROM [DataBaseName]", objSQLConn)
objSQLConn.Open()
objSQLRdr = objSQLCmd.ExecuteReader
While objSQLRdr.Read()
Dim i As New Integer
For i = 0 To objSQLRdr.FieldCount - 1
varTPErr = System.DateTime.Now.ToString("HH:mm:ss.ffff", Nothing) & _
"Testme" & " "
varKeyCompany = objSQLRdr("Company")
varKeyPolicyno = objSQLRdr("Policyno")
varKeyModule = objSQLRdr("Module")
'begin Write
objSQLCmdW = New SqlCommand("UPDATE “ & _ “[DataBaseName] SET [TerrErr] = @varTPErr” & _
“WHERE [Company] = @Company AND [Policyno] = @Policyno” & _
“AND [Module] = @Module", objSQLConnW)
objSQLCmdW.Parameters.Add("@varTPErr", varTPErr)
objSQLCmdW.Parameters.Add("@Company", varKeyCompany)
objSQLCmdW.Parameters.Add("@Policyno", varKeyPolicyno)
objSQLCmdW.Parameters.Add("@Module", varKeyModule)
objSQLCmdW.Connection.Open()
objSQLCmdW.ExecuteNonQuery()
objSQLCmdW.Connection.Close()
'end Write
Next
End While
objSQLRdr.Close()
objSQLConn.Close()
Here’s the sample code I used to test the Do While Loop:
objSQLCmd = New SqlCommand("SELECT [Territory], [Company], [Policyno], [Module], [Covatot], [CovctotS], [CovdtotS], [CovetotS], [Yearbuilt], [Consttype], [Stories], [retrofit], [Covaded], [Term], [TermS], [poltype1] FROM [DataBaseName]", objSQLConn)
objSQLConn.Open()
objSQLRdr = objSQLCmd.ExecuteReader
If objSQLRdr.HasRows Then
Do While objSQLRdr.Read()
varTrace = varTrace + 1
varTPErr = System.DateTime.Now.ToString("HH:mm:ss.ffff", Nothing) & " Testme" & " " & varTrace
varKeyCompany = objSQLRdr("Company")
varKeyPolicyno = objSQLRdr("Policyno")
varKeyModule = objSQLRdr("Module")
'begin Write
objSQLCmdW = New SqlCommand("UPDATE [DataBaseName] SET [TerrErr] = @varTPErr WHERE [Company] = @Company AND [Policyno] = @Policyno AND [Module] = @Module", objSQLConnW)
objSQLCmdW.Parameters.Add("@varTPErr", varTPErr)
objSQLCmdW.Parameters.Add("@Company", varKeyCompany)
objSQLCmdW.Parameters.Add("@Policyno", varKeyPolicyno)
objSQLCmdW.Parameters.Add("@Module", varKeyModule)
objSQLCmdW.Connection.Open()
objSQLCmdW.ExecuteNonQuery()
objSQLCmdW.Connection.Close()
'end Write
Loop
Else
Console.WriteLine("No rows returned.")
End If
objSQLRdr.Close()
objSQLConn.Close()
