Reuse paramter list and reuse connection

[tshad]

I can't seem to find where to reset the parameter list.

Dim objCmd as New SqlCommand(Comm andText,objConn )
with objCmd.Paramete rs
.Add("@StateCod e",SqlDbType.Ch ar,2).value = ByState.Selecte dValue
.Add("@City",Sq lDbType.char).v alue = ByCity.Selected Value
end with
objConn.Open()

ZipCode.DataSou rce=objCmd.Exec uteReader
ZipCode.DataTex tField= "ZipCode"
ZipCode.DataVal ueField="ZipCod e"
ZipCode.databin d()

Now I want to use the same SqlCommand object and the same data connection to
do another select.

How do I clear the old parameters to allow me to readd the new ones and do I
need to do an objConn.close and another objConn.Open to do this?

Thanks,

Tom

[Steve C. Orr [MVP, MCSD]]

To clear the parameters, use this syntax:
objCmd.Paramete rs.Clear()

Yes, you should always close the connection as soon as you can after
executing a query, and don't open a connection until just before you execute
a query. The built-in ADO.NET connection pooling makes this very efficient
in almost all circumstances.

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net

"tshad" <ts**********@f tsolutions.com> wrote in message
news:e7******** ******@TK2MSFTN GP14.phx.gbl...

I can't seem to find where to reset the parameter list.

Dim objCmd as New SqlCommand(Comm andText,objConn )
with objCmd.Paramete rs
.Add("@StateCod e",SqlDbType.Ch ar,2).value = ByState.Selecte dValue
.Add("@City",Sq lDbType.char).v alue = ByCity.Selected Value
end with
objConn.Open()

ZipCode.DataSou rce=objCmd.Exec uteReader
ZipCode.DataTex tField= "ZipCode"
ZipCode.DataVal ueField="ZipCod e"
ZipCode.databin d()

Now I want to use the same SqlCommand object and the same data connection
to do another select.

How do I clear the old parameters to allow me to readd the new ones and do
I need to do an objConn.close and another objConn.Open to do this?

Thanks,

Tom

[tshad]

"Steve C. Orr [MVP, MCSD]" <St***@Orr.ne t> wrote in message
news:uq******** *****@TK2MSFTNG P09.phx.gbl...

To clear the parameters, use this syntax:
objCmd.Paramete rs.Clear()
That was what I was looking for here.

Yes, you should always close the connection as soon as you can after
executing a query, and don't open a connection until just before you
execute a query. The built-in ADO.NET connection pooling makes this very
efficient in almost all circumstances.
What if I am doing a 3 or 4 selects in a row to fill dropdowns or a
datagrid - one right after another?

If I am doing a datareader, I have to wait until I am done reading it before
I can close it, so wouldn't be better to just do something like:

*************** *************** *************** *************** *************** *************** *
Dim objCmd as New SqlCommand(Comm andText,objConn )
with objCmd.Paramete rs
.Add("@StateCod e",SqlDbType.Ch ar,2).value = ByState.Selecte dValue
.Add("@City",Sq lDbType.char).v alue = ByCity.Selected Value
end with
objConn.Open()

ZipCode.DataSou rce=objCmd.Exec uteReader
ZipCode.DataTex tField= "ZipCode"
ZipCode.DataVal ueField="ZipCod e"
ZipCode.databin d()

objCmd.Paramete rs.Clear()

objCmd.CommandT ext = "Select Counties,Countr yCode from Countries where
ClientCode = @ClientCode"
with objCmd.Paramete rs
.Add("@ClientCo de",SqlDbType.V arChar,10).valu e = ClientCode.Text
end with
objConn.Open()

Countries.DataS ource=objCmd.Ex ecuteReader
Countries.datab ind()

' I assume I would not need to do an objCmd.Paramete rs.Clear() here, since
I am using the same paramter and adding another (although my assumption may
be incorrect).

objCmd.CommandT ext = "Select CarrierCodes, CarrierNames from Carrierswhere
ClientCode = @ClientCode and State = @StateCode"
with objCmd.Paramete rs
.Add("@StateCod e",SqlDbType.Ch ar,2).value = StateCode.Text
end with
objConn.Open()

Carriers.DataSo urce=objCmd.Exe cuteReader
Carriers.databi nd()
*************** *************** *************** *************** *************** **********'

Or should I close and open the connection between each select?

Thanks,

Tom


--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net

"tshad" <ts**********@f tsolutions.com> wrote in message
news:e7******** ******@TK2MSFTN GP14.phx.gbl...

I can't seem to find where to reset the parameter list.

Dim objCmd as New SqlCommand(Comm andText,objConn )
with objCmd.Paramete rs
.Add("@StateCod e",SqlDbType.Ch ar,2).value = ByState.Selecte dValue
.Add("@City",Sq lDbType.char).v alue = ByCity.Selected Value
end with
objConn.Open()

ZipCode.DataSou rce=objCmd.Exec uteReader
ZipCode.DataTex tField= "ZipCode"
ZipCode.DataVal ueField="ZipCod e"
ZipCode.databin d()

Now I want to use the same SqlCommand object and the same data connection
to do another select.

How do I clear the old parameters to allow me to readd the new ones and
do I need to do an objConn.close and another objConn.Open to do this?

Thanks,

Tom

[Steve C. Orr [MVP, MCSD]]

Well, if your code works then it works. Why fix what ain't broken?
Then it just becomes a matter of what is most efficient and how much effort
you are willing to put in to squeeze out every last bit of performance.
Technically, the performance would be best if you rolled all these queries
into a single stored procedure that returns multiple result sets.

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net
"tshad" <ts**********@f tsolutions.com> wrote in message
news:ed******** ******@TK2MSFTN GP10.phx.gbl...

"Steve C. Orr [MVP, MCSD]" <St***@Orr.ne t> wrote in message
news:uq******** *****@TK2MSFTNG P09.phx.gbl...

To clear the parameters, use this syntax:
objCmd.Paramete rs.Clear()

That was what I was looking for here.

Yes, you should always close the connection as soon as you can after
executing a query, and don't open a connection until just before you
execute a query. The built-in ADO.NET connection pooling makes this very
efficient in almost all circumstances.

What if I am doing a 3 or 4 selects in a row to fill dropdowns or a
datagrid - one right after another?

If I am doing a datareader, I have to wait until I am done reading it
before I can close it, so wouldn't be better to just do something like:

*************** *************** *************** *************** *************** *************** *
Dim objCmd as New SqlCommand(Comm andText,objConn )
with objCmd.Paramete rs
.Add("@StateCod e",SqlDbType.Ch ar,2).value = ByState.Selecte dValue
.Add("@City",Sq lDbType.char).v alue = ByCity.Selected Value
end with
objConn.Open()

ZipCode.DataSou rce=objCmd.Exec uteReader
ZipCode.DataTex tField= "ZipCode"
ZipCode.DataVal ueField="ZipCod e"
ZipCode.databin d()

objCmd.Paramete rs.Clear()

objCmd.CommandT ext = "Select Counties,Countr yCode from Countries where
ClientCode = @ClientCode"
with objCmd.Paramete rs
.Add("@ClientCo de",SqlDbType.V arChar,10).valu e = ClientCode.Text
end with
objConn.Open()

Countries.DataS ource=objCmd.Ex ecuteReader
Countries.datab ind()

' I assume I would not need to do an objCmd.Paramete rs.Clear() here,
since I am using the same paramter and adding another (although my
assumption may be incorrect).

objCmd.CommandT ext = "Select CarrierCodes, CarrierNames from Carrierswhere
ClientCode = @ClientCode and State = @StateCode"
with objCmd.Paramete rs
.Add("@StateCod e",SqlDbType.Ch ar,2).value = StateCode.Text
end with
objConn.Open()

Carriers.DataSo urce=objCmd.Exe cuteReader
Carriers.databi nd()
*************** *************** *************** *************** *************** **********'

Or should I close and open the connection between each select?

Thanks,

Tom

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net

"tshad" <ts**********@f tsolutions.com> wrote in message
news:e7******** ******@TK2MSFTN GP14.phx.gbl...

I can't seem to find where to reset the parameter list.

Dim objCmd as New SqlCommand(Comm andText,objConn )
with objCmd.Paramete rs
.Add("@StateCod e",SqlDbType.Ch ar,2).value = ByState.Selecte dValue
.Add("@City",Sq lDbType.char).v alue = ByCity.Selected Value
end with
objConn.Open()

ZipCode.DataSou rce=objCmd.Exec uteReader
ZipCode.DataTex tField= "ZipCode"
ZipCode.DataVal ueField="ZipCod e"
ZipCode.databin d()

Now I want to use the same SqlCommand object and the same data
connection to do another select.

How do I clear the old parameters to allow me to readd the new ones and
do I need to do an objConn.close and another objConn.Open to do this?

Thanks,

Tom

[tshad]

"Steve C. Orr [MVP, MCSD]" <St***@Orr.ne t> wrote in message
news:uK******** ******@TK2MSFTN GP10.phx.gbl...

Well, if your code works then it works. Why fix what ain't broken?
Then it just becomes a matter of what is most efficient and how much
effort you are willing to put in to squeeze out every last bit of
performance.
Technically, the performance would be best if you rolled all these queries
into a single stored procedure that returns multiple result sets.
I don't know if this works (I assume it does). I am just trying to find
other and better ways to do it.

As far as the multiple results sets go, I am not sure yet how to handle the
result sets when they get back.

Tom
--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net
"tshad" <ts**********@f tsolutions.com> wrote in message
news:ed******** ******@TK2MSFTN GP10.phx.gbl...

"Steve C. Orr [MVP, MCSD]" <St***@Orr.ne t> wrote in message
news:uq******** *****@TK2MSFTNG P09.phx.gbl...

To clear the parameters, use this syntax:
objCmd.Paramete rs.Clear()

That was what I was looking for here.

Yes, you should always close the connection as soon as you can after
executing a query, and don't open a connection until just before you
execute a query. The built-in ADO.NET connection pooling makes this
very efficient in almost all circumstances.

What if I am doing a 3 or 4 selects in a row to fill dropdowns or a
datagrid - one right after another?

If I am doing a datareader, I have to wait until I am done reading it
before I can close it, so wouldn't be better to just do something like:

*************** *************** *************** *************** *************** *************** *
Dim objCmd as New SqlCommand(Comm andText,objConn )
with objCmd.Paramete rs
.Add("@StateCod e",SqlDbType.Ch ar,2).value = ByState.Selecte dValue
.Add("@City",Sq lDbType.char).v alue = ByCity.Selected Value
end with
objConn.Open()

ZipCode.DataSou rce=objCmd.Exec uteReader
ZipCode.DataTex tField= "ZipCode"
ZipCode.DataVal ueField="ZipCod e"
ZipCode.databin d()

objCmd.Paramete rs.Clear()

objCmd.CommandT ext = "Select Counties,Countr yCode from Countries where
ClientCode = @ClientCode"
with objCmd.Paramete rs
.Add("@ClientCo de",SqlDbType.V arChar,10).valu e = ClientCode.Text
end with
objConn.Open()

Countries.DataS ource=objCmd.Ex ecuteReader
Countries.datab ind()

' I assume I would not need to do an objCmd.Paramete rs.Clear() here,
since I am using the same paramter and adding another (although my
assumption may be incorrect).

objCmd.CommandT ext = "Select CarrierCodes, CarrierNames from
Carrierswhere ClientCode = @ClientCode and State = @StateCode"
with objCmd.Paramete rs
.Add("@StateCod e",SqlDbType.Ch ar,2).value = StateCode.Text
end with
objConn.Open()

Carriers.DataSo urce=objCmd.Exe cuteReader
Carriers.databi nd()
*************** *************** *************** *************** *************** **********'

Or should I close and open the connection between each select?

Thanks,

Tom

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net

"tshad" <ts**********@f tsolutions.com> wrote in message
news:e7******** ******@TK2MSFTN GP14.phx.gbl...
I can't seem to find where to reset the parameter list.

Dim objCmd as New SqlCommand(Comm andText,objConn )
with objCmd.Paramete rs
.Add("@StateCod e",SqlDbType.Ch ar,2).value = ByState.Selecte dValue
.Add("@City",Sq lDbType.char).v alue = ByCity.Selected Value
end with
objConn.Open()

ZipCode.DataSou rce=objCmd.Exec uteReader
ZipCode.DataTex tField= "ZipCode"
ZipCode.DataVal ueField="ZipCod e"
ZipCode.databin d()

Now I want to use the same SqlCommand object and the same data
connection to do another select.

How do I clear the old parameters to allow me to readd the new ones and
do I need to do an objConn.close and another objConn.Open to do this?

Thanks,

Tom

[Steve C. Orr [MVP, MCSD]]

You can use the DataReader.Next Result method.
Here's more info:
http://msdn.microsoft.com/msdnmag/is...T/default.aspx

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net
"tshad" <ts**********@f tsolutions.com> wrote in message
news:Oj******** ******@TK2MSFTN GP14.phx.gbl...

"Steve C. Orr [MVP, MCSD]" <St***@Orr.ne t> wrote in message
news:uK******** ******@TK2MSFTN GP10.phx.gbl...

Well, if your code works then it works. Why fix what ain't broken?
Then it just becomes a matter of what is most efficient and how much
effort you are willing to put in to squeeze out every last bit of
performance.
Technically, the performance would be best if you rolled all these
queries into a single stored procedure that returns multiple result sets.

I don't know if this works (I assume it does). I am just trying to find
other and better ways to do it.

As far as the multiple results sets go, I am not sure yet how to handle
the result sets when they get back.

Tom

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net
"tshad" <ts**********@f tsolutions.com> wrote in message
news:ed******** ******@TK2MSFTN GP10.phx.gbl...

"Steve C. Orr [MVP, MCSD]" <St***@Orr.ne t> wrote in message
news:uq******** *****@TK2MSFTNG P09.phx.gbl...
To clear the parameters, use this syntax:
objCmd.Paramete rs.Clear()

That was what I was looking for here.
Yes, you should always close the connection as soon as you can after
executing a query, and don't open a connection until just before you
execute a query. The built-in ADO.NET connection pooling makes this
very efficient in almost all circumstances.

What if I am doing a 3 or 4 selects in a row to fill dropdowns or a
datagrid - one right after another?

If I am doing a datareader, I have to wait until I am done reading it
before I can close it, so wouldn't be better to just do something like:

*************** *************** *************** *************** *************** *************** *
Dim objCmd as New SqlCommand(Comm andText,objConn )
with objCmd.Paramete rs
.Add("@StateCod e",SqlDbType.Ch ar,2).value = ByState.Selecte dValue
.Add("@City",Sq lDbType.char).v alue = ByCity.Selected Value
end with
objConn.Open()

ZipCode.DataSou rce=objCmd.Exec uteReader
ZipCode.DataTex tField= "ZipCode"
ZipCode.DataVal ueField="ZipCod e"
ZipCode.databin d()

objCmd.Paramete rs.Clear()

objCmd.CommandT ext = "Select Counties,Countr yCode from Countries where
ClientCode = @ClientCode"
with objCmd.Paramete rs
.Add("@ClientCo de",SqlDbType.V arChar,10).valu e = ClientCode.Text
end with
objConn.Open()

Countries.DataS ource=objCmd.Ex ecuteReader
Countries.datab ind()

' I assume I would not need to do an objCmd.Paramete rs.Clear() here,
since I am using the same paramter and adding another (although my
assumption may be incorrect).

objCmd.CommandT ext = "Select CarrierCodes, CarrierNames from
Carrierswhere ClientCode = @ClientCode and State = @StateCode"
with objCmd.Paramete rs
.Add("@StateCod e",SqlDbType.Ch ar,2).value = StateCode.Text
end with
objConn.Open()

Carriers.DataSo urce=objCmd.Exe cuteReader
Carriers.databi nd()
*************** *************** *************** *************** *************** **********'

Or should I close and open the connection between each select?

Thanks,

Tom

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net

"tshad" <ts**********@f tsolutions.com> wrote in message
news:e7******** ******@TK2MSFTN GP14.phx.gbl...
>I can't seem to find where to reset the parameter list.
>
> Dim objCmd as New SqlCommand(Comm andText,objConn )
> with objCmd.Paramete rs
> .Add("@StateCod e",SqlDbType.Ch ar,2).value = ByState.Selecte dValue
> .Add("@City",Sq lDbType.char).v alue = ByCity.Selected Value
> end with
> objConn.Open()
>
> ZipCode.DataSou rce=objCmd.Exec uteReader
> ZipCode.DataTex tField= "ZipCode"
> ZipCode.DataVal ueField="ZipCod e"
> ZipCode.databin d()
>
> Now I want to use the same SqlCommand object and the same data
> connection to do another select.
>
> How do I clear the old parameters to allow me to readd the new ones
> and do I need to do an objConn.close and another objConn.Open to do
> this?
>
> Thanks,
>
> Tom
>