Question on SQLCommand in a loop

[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.SqlCo mmand
Dim m_drSQLTop As SqlClient.SqlDa taReader

Do While bContinue
sSQL = "GetPacketDataT op"
m_cmdSQLT = New SqlClient.SqlCo mmand
With m_cmdSQLT
.Connection = TickServerAdoCo n
.CommandType = CommandType.Sto redProcedure
.CommandText = sSQL
End With
m_cmdSQLT.Param eters.Add("@sCo ntract", SqlDbType.VarCh ar, 8)
m_cmdSQLT.Param eters.Add("@dTi me1", SqlDbType.DateT ime, 8)
m_cmdSQLT.Param eters.Add("@dTi me2", SqlDbType.DateT ime, 8)
m_cmdSQLT.Param eters("@sContra ct").Value = txtSymbol.Text
m_cmdSQLT.Param eters("@dTime1" ).Value = dTime1
m_cmdSQLT.Param eters("@dTime2" ).Value = dtime2
m_drSQLTop = m_cmdSQLT.Execu teReader()
If m_drSQLTop.Read Then sOpen = "" & m_drSQLTop("PAC KET_DATA")
m_drSQLTop.Clos e()
m_cmdSQLT.Dispo se()
m_cmdSQLT = Nothing
:
dTime1 = DateAdd(DateInt erval.Minute, 1, dTime1)
dtime2 = DateAdd(DateInt erval.Minute, 1, dTime1)
If DateDiff(DateIn terval.Minute, dLast, dTime1) >= 1 Then
bContinue = False
End If
Loop

[jayeldee]

On Mar 8, 1:44 pm, "fniles" <fni...@pfmail. comwrote:

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.

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.SqlCo mmand
sSQL = "GetPacketDataT op"
With m_cmdSQLT
.Connection = TickServerADOCo n
.CommandType = CommandType.Sto redProcedure
.CommandText = sSQL
End With

m_cmdSQLT.Param eters.Add("@sCo ntract", SqlDbType.VarCh ar, 8)
m_cmdSQLT.Param eters.Add("@dTi me1", SqlDbType.DateT ime, 8)
m_cmdSQLT.Param eters.Add("@dTi me2", SqlDbType.DateT ime, 8)

Do While bContinue
m_cmdSQLT.Param eters("@sContra ct").Value = txtSymbol,text
m_cmdSQLT.Param eters("@dTime1" ).Value = dTime1
m_cmdSQLT.Param eters("@dTime2" ).Value = dTime2
m_drSQLTop = m_cmdSQLT.Execu teReader()
If m_drSQLTop.Read Then sOpen = "" &
m_drSQLTop("PAC KET_DATA")
m_drSQLTop.Clos e()

dTime1 = DateAdd(DateInt erval.Minute, 1, dTime1)
dTime2 = DateAdd(DateInt erval.Minute, 1, dTime1)
If DateDiff(DateIn terval.Minute, dLast, dTime1) >= 1 Then
bContinue = False
End If
Loop

' Close connection etc. here

[fniles]

Thanks, that works !

"jayeldee" <ja******@gmail .comwrote in message
news:11******** *************@h 3g2000cwc.googl egroups.com...

On Mar 8, 1:44 pm, "fniles" <fni...@pfmail. comwrote:

>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.

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.SqlCo mmand
sSQL = "GetPacketDataT op"
With m_cmdSQLT
.Connection = TickServerADOCo n
.CommandType = CommandType.Sto redProcedure
.CommandText = sSQL
End With

m_cmdSQLT.Param eters.Add("@sCo ntract", SqlDbType.VarCh ar, 8)
m_cmdSQLT.Param eters.Add("@dTi me1", SqlDbType.DateT ime, 8)
m_cmdSQLT.Param eters.Add("@dTi me2", SqlDbType.DateT ime, 8)

Do While bContinue
m_cmdSQLT.Param eters("@sContra ct").Value = txtSymbol,text
m_cmdSQLT.Param eters("@dTime1" ).Value = dTime1
m_cmdSQLT.Param eters("@dTime2" ).Value = dTime2
m_drSQLTop = m_cmdSQLT.Execu teReader()
If m_drSQLTop.Read Then sOpen = "" &
m_drSQLTop("PAC KET_DATA")
m_drSQLTop.Clos e()

dTime1 = DateAdd(DateInt erval.Minute, 1, dTime1)
dTime2 = DateAdd(DateInt erval.Minute, 1, dTime1)
If DateDiff(DateIn terval.Minute, dLast, dTime1) >= 1 Then
bContinue = False
End If
Loop

' Close connection etc. here