Question on SQLCommand in a loop

  • Thread starter Thread starter fniles
  • Start date Start date
F

fniles

I am using .Net 2003 and querying a SQL Server 2000 database.
I read the database in a loop, but on every iteration I set the SQLCommand
to new, set it, execute it, dispose and set it to nothing.
Is there any way so that I do not have to do that ? In VB6, I can set a
recordset to new once, set it and execute it within the loop, and close it
after the loop.
Thanks.

Dim m_cmdSQLT As SqlClient.SqlCommand
Dim m_drSQLTop As SqlClient.SqlDataReader

Do While bContinue
sSQL = "GetPacketDataTop"
m_cmdSQLT = New SqlClient.SqlCommand
With m_cmdSQLT
.Connection = TickServerAdoCon
.CommandType = CommandType.StoredProcedure
.CommandText = sSQL
End With
m_cmdSQLT.Parameters.Add("@sContract", SqlDbType.VarChar, 8)
m_cmdSQLT.Parameters.Add("@dTime1", SqlDbType.DateTime, 8)
m_cmdSQLT.Parameters.Add("@dTime2", SqlDbType.DateTime, 8)
m_cmdSQLT.Parameters("@sContract").Value = txtSymbol.Text
m_cmdSQLT.Parameters("@dTime1").Value = dTime1
m_cmdSQLT.Parameters("@dTime2").Value = dtime2
m_drSQLTop = m_cmdSQLT.ExecuteReader()
If m_drSQLTop.Read Then sOpen = "" & m_drSQLTop("PACKET_DATA")
m_drSQLTop.Close()
m_cmdSQLT.Dispose()
m_cmdSQLT = Nothing
:
dTime1 = DateAdd(DateInterval.Minute, 1, dTime1)
dtime2 = DateAdd(DateInterval.Minute, 1, dTime1)
If DateDiff(DateInterval.Minute, dLast, dTime1) >= 1 Then
bContinue = False
End If
Loop
 
I am using .Net 2003 and querying a SQL Server 2000 database.
I read the database in a loop, but on every iteration I set the SQLCommand
to new, set it, execute it, dispose and set it to nothing.
Is there any way so that I do not have to do that ? In VB6, I can set a
recordset to new once, set it and execute it within the loop, and close it
after the loop.
Thanks.
Click to expand...

You'll want to put the initialization of the Command object and the
parameter declarations outside the loop so the Command isn't created
and destroyed on each iteration.
Inside the loop, you'll assign the parameter values, execute the
command, and perform your logic on the values you get from the
DataReader.

When the loop ends, you can Dispose the command and close the
connection. Might look something like:

m_cmdSQLT = New SqlClient.SqlCommand
sSQL = "GetPacketDataTop"
With m_cmdSQLT
.Connection = TickServerADOCon
.CommandType = CommandType.StoredProcedure
.CommandText = sSQL
End With

m_cmdSQLT.Parameters.Add("@sContract", SqlDbType.VarChar, 8)
m_cmdSQLT.Parameters.Add("@dTime1", SqlDbType.DateTime, 8)
m_cmdSQLT.Parameters.Add("@dTime2", SqlDbType.DateTime, 8)

Do While bContinue
m_cmdSQLT.Parameters("@sContract").Value = txtSymbol,text
m_cmdSQLT.Parameters("@dTime1").Value = dTime1
m_cmdSQLT.Parameters("@dTime2").Value = dTime2
m_drSQLTop = m_cmdSQLT.ExecuteReader()
If m_drSQLTop.Read Then sOpen = "" &
m_drSQLTop("PACKET_DATA")
m_drSQLTop.Close()

dTime1 = DateAdd(DateInterval.Minute, 1, dTime1)
dTime2 = DateAdd(DateInterval.Minute, 1, dTime1)
If DateDiff(DateInterval.Minute, dLast, dTime1) >= 1 Then
bContinue = False
End If
Loop

' Close connection etc. here
 
Thanks, that works !

jayeldee said:
You'll want to put the initialization of the Command object and the
parameter declarations outside the loop so the Command isn't created
and destroyed on each iteration.
Inside the loop, you'll assign the parameter values, execute the
command, and perform your logic on the values you get from the
DataReader.

When the loop ends, you can Dispose the command and close the
connection. Might look something like:

m_cmdSQLT = New SqlClient.SqlCommand
sSQL = "GetPacketDataTop"
With m_cmdSQLT
.Connection = TickServerADOCon
.CommandType = CommandType.StoredProcedure
.CommandText = sSQL
End With

m_cmdSQLT.Parameters.Add("@sContract", SqlDbType.VarChar, 8)
m_cmdSQLT.Parameters.Add("@dTime1", SqlDbType.DateTime, 8)
m_cmdSQLT.Parameters.Add("@dTime2", SqlDbType.DateTime, 8)

Do While bContinue
m_cmdSQLT.Parameters("@sContract").Value = txtSymbol,text
m_cmdSQLT.Parameters("@dTime1").Value = dTime1
m_cmdSQLT.Parameters("@dTime2").Value = dTime2
m_drSQLTop = m_cmdSQLT.ExecuteReader()
If m_drSQLTop.Read Then sOpen = "" &
m_drSQLTop("PACKET_DATA")
m_drSQLTop.Close()

dTime1 = DateAdd(DateInterval.Minute, 1, dTime1)
dTime2 = DateAdd(DateInterval.Minute, 1, dTime1)
If DateDiff(DateInterval.Minute, dLast, dTime1) >= 1 Then
bContinue = False
End If
Loop

' Close connection etc. here
Click to expand...
 
Back
Top