Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Reuse paramter list and reuse connection

Reply
Thread Tools

Reuse paramter list and reuse connection

 
 
tshad
Guest
Posts: n/a
 
      05-16-2005
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


 
Reply With Quote
 
 
 
 
Steve C. Orr [MVP, MCSD]
Guest
Posts: n/a
 
      05-16-2005
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.

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



"tshad" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>



 
Reply With Quote
 
 
 
 
tshad
Guest
Posts: n/a
 
      05-16-2005
"Steve C. Orr [MVP, MCSD]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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


>
> --
> I hope this helps,
> Steve C. Orr, MCSD, MVP
> http://SteveOrr.net
>
>
>
> "tshad" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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
>>

>
>



 
Reply With Quote
 
Steve C. Orr [MVP, MCSD]
Guest
Posts: n/a
 
      05-16-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "Steve C. Orr [MVP, MCSD]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>
>
>>
>> --
>> I hope this helps,
>> Steve C. Orr, MCSD, MVP
>> http://SteveOrr.net
>>
>>
>>
>> "tshad" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>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
>>>

>>
>>

>
>



 
Reply With Quote
 
tshad
Guest
Posts: n/a
 
      05-17-2005
"Steve C. Orr [MVP, MCSD]" <(E-Mail Removed)> wrote in message
news:uKB$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> "Steve C. Orr [MVP, MCSD]" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>
>>
>>>
>>> --
>>> I hope this helps,
>>> Steve C. Orr, MCSD, MVP
>>> http://SteveOrr.net
>>>
>>>
>>>
>>> "tshad" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>>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
>>>>
>>>
>>>

>>
>>

>
>



 
Reply With Quote
 
Steve C. Orr [MVP, MCSD]
Guest
Posts: n/a
 
      05-17-2005
You can use the DataReader.NextResult 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "Steve C. Orr [MVP, MCSD]" <(E-Mail Removed)> wrote in message
> news:uKB$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> "Steve C. Orr [MVP, MCSD]" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>
>>>
>>>>
>>>> --
>>>> I hope this helps,
>>>> Steve C. Orr, MCSD, MVP
>>>> http://SteveOrr.net
>>>>
>>>>
>>>>
>>>> "tshad" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>>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
>>>>>
>>>>
>>>>
>>>
>>>

>>
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
To reuse or not to reuse jacob navia C Programming 19 12-18-2006 07:22 AM
code reuse and design reuse sailor.gu@gmail.com C Programming 16 02-12-2006 09:09 PM
To reuse or not to reuse.... Hylander Java 0 02-26-2004 12:00 AM
compare an input to see if it's contained within a paramter list Maxd out C++ 4 08-30-2003 05:44 PM
How to define paramter-types for WebMethods AND Client? Daniel Barisch ASP .Net Web Services 0 08-22-2003 09:43 AM



Advertisments