Reuse paramter list and reuse connection

[tshad]

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

Dim objCmd as New SqlCommand(CommandText,objConn)
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = ByState.SelectedValue
.Add("@City",SqlDbType.char).value = ByCity.SelectedValue
end with
objConn.Open()

ZipCode.DataSource=objCmd.ExecuteReader
ZipCode.DataTextField= "ZipCode"
ZipCode.DataValueField="ZipCode"
ZipCode.databind()

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

 

[tshad]

To clear the parameters, use this syntax:
objCmd.Parameters.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(CommandText,objConn)
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = ByState.SelectedValue
.Add("@City",SqlDbType.char).value = ByCity.SelectedValue
end with
objConn.Open()

ZipCode.DataSource=objCmd.ExecuteReader
ZipCode.DataTextField= "ZipCode"
ZipCode.DataValueField="ZipCode"
ZipCode.databind()

objCmd.Parameters.Clear()

objCmd.CommandText = "Select Counties,CountryCode from Countries where
ClientCode = @ClientCode"
with objCmd.Parameters
.Add("@ClientCode",SqlDbType.VarChar,10).value = ClientCode.Text
end with
objConn.Open()

Countries.DataSource=objCmd.ExecuteReader
Countries.databind()

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

objCmd.CommandText = "Select CarrierCodes, CarrierNames from Carrierswhere
ClientCode = @ClientCode and State = @StateCode"
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = StateCode.Text
end with
objConn.Open()

Carriers.DataSource=objCmd.ExecuteReader
Carriers.databind()
*************************************************************************************'

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

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

To clear the parameters, use this syntax:
objCmd.Parameters.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(CommandText,objConn)
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = ByState.SelectedValue
.Add("@City",SqlDbType.char).value = ByCity.SelectedValue
end with
objConn.Open()

ZipCode.DataSource=objCmd.ExecuteReader
ZipCode.DataTextField= "ZipCode"
ZipCode.DataValueField="ZipCode"
ZipCode.databind()

objCmd.Parameters.Clear()

objCmd.CommandText = "Select Counties,CountryCode from Countries where
ClientCode = @ClientCode"
with objCmd.Parameters
.Add("@ClientCode",SqlDbType.VarChar,10).value = ClientCode.Text
end with
objConn.Open()

Countries.DataSource=objCmd.ExecuteReader
Countries.databind()

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

objCmd.CommandText = "Select CarrierCodes, CarrierNames from Carrierswhere
ClientCode = @ClientCode and State = @StateCode"
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = StateCode.Text
end with
objConn.Open()

Carriers.DataSource=objCmd.ExecuteReader
Carriers.databind()
*************************************************************************************'

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

Thanks,

Tom

 

[tshad]

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

 

[Steve C. Orr [MVP, MCSD]]

You can use the DataReader.NextResult method.
Here's more info:
http://msdn.microsoft.com/msdnmag/issues/05/05/ExtremeASPNET/default.aspx

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

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