Reuse paramter list and reuse connection

Discussion in 'ASP .Net' started by tshad, May 16, 2005.

  1. tshad

    tshad Guest

    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
    tshad, May 16, 2005
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    >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], May 16, 2005
    #2
    1. Advertising

  3. tshad

    tshad Guest

    "Steve C. Orr [MVP, MCSD]" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:...
    >>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
    >>

    >
    >
    tshad, May 17, 2005
    #3
  4. 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" <> wrote in message
    news:...
    > "Steve C. Orr [MVP, MCSD]" <> wrote in message
    > news:...
    >> 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" <> wrote in message
    >> news:...
    >>>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], May 17, 2005
    #4
  5. tshad

    tshad Guest

    "Steve C. Orr [MVP, MCSD]" <> wrote in message
    news:uKB$...
    > 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" <> wrote in message
    > news:...
    >> "Steve C. Orr [MVP, MCSD]" <> wrote in message
    >> news:...
    >>> 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" <> wrote in message
    >>> news:...
    >>>>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
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
    tshad, May 17, 2005
    #5
  6. 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


    "tshad" <> wrote in message
    news:...
    > "Steve C. Orr [MVP, MCSD]" <> wrote in message
    > news:uKB$...
    >> 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" <> wrote in message
    >> news:...
    >>> "Steve C. Orr [MVP, MCSD]" <> wrote in message
    >>> news:...
    >>>> 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" <> wrote in message
    >>>> news:...
    >>>>>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], May 17, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Hylander

    To reuse or not to reuse....

    Hylander, Feb 26, 2004, in forum: Java
    Replies:
    0
    Views:
    410
    Hylander
    Feb 26, 2004
  2. Maxd out
    Replies:
    4
    Views:
    351
    Kevin Goodsell
    Aug 30, 2003
  3. code reuse and design reuse

    , Feb 7, 2006, in forum: C Programming
    Replies:
    16
    Views:
    1,000
    Malcolm
    Feb 12, 2006
  4. jacob navia

    To reuse or not to reuse

    jacob navia, Nov 5, 2006, in forum: C Programming
    Replies:
    19
    Views:
    512
    Dave Thompson
    Dec 18, 2006
  5. Daniel Barisch

    How to define paramter-types for WebMethods AND Client?

    Daniel Barisch, Aug 22, 2003, in forum: ASP .Net Web Services
    Replies:
    0
    Views:
    102
    Daniel Barisch
    Aug 22, 2003
Loading...

Share This Page