Why parameters collection


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 14 of 14

Thread: Why parameters collection

  1. #1
    Bill McCarthy Guest

    Re: Why parameters collection

    Hi Jeff,

    Yep ! How do you do return values in the parameters ?


    "Jeff Pipes" <JeffP622@msn.com> wrote in message news:3cd01eac$1@10.1.10.29...
    >
    > I noticed that ado.net has a parameters collection for when calling stored
    > procedures. What I don't understand is why. It seems like a lot of trouble
    > creating all these parameter objects and adding them to the parameters

    collection.
    > I've never used the parameters collection in ado classic either. I always
    > execute stored procedures using a command (or connection) object and passing
    > it a string like 'exec sp_dostuff parm1, parm2'. Am I missing something
    > by not using these parameter objects?
    >
    > -Jeff




  2. #2
    Jeff Pipes Guest

    Why parameters collection


    I noticed that ado.net has a parameters collection for when calling stored
    procedures. What I don't understand is why. It seems like a lot of trouble
    creating all these parameter objects and adding them to the parameters collection.
    I've never used the parameters collection in ado classic either. I always
    execute stored procedures using a command (or connection) object and passing
    it a string like 'exec sp_dostuff parm1, parm2'. Am I missing something
    by not using these parameter objects?

    -Jeff

  3. #3
    Bill McCarthy Guest

    Re: Why parameters collection

    Hi Jeff,

    That I know of, yes

    I personally like to wrap Property Set/Get's to control access to the parameters.
    This allows strong typing on your interface and can allow you to keep the command
    itself internal to the data tier component, as well as apply business logic rules
    in the Set/Gets.

    OT: I just wish they would do the same to the connection object cause I hate
    connection strings <bg>


    "Jeff Pipes" <JeffP622@msn.com> wrote in message news:3cd02499$1@10.1.10.29...
    >
    > Thx bill, is that the only advantage?
    >
    > -Jeff
    >
    > "Bill McCarthy" <bill_mcc@iprimus.com.au> wrote:
    > >Hi Jeff,
    > >
    > >Yep ! How do you do return values in the parameters ?
    > >
    > >
    > >"Jeff Pipes" <JeffP622@msn.com> wrote in message news:3cd01eac$1@10.1.10.29...
    > >>
    > >> I noticed that ado.net has a parameters collection for when calling stored
    > >> procedures. What I don't understand is why. It seems like a lot of trouble
    > >> creating all these parameter objects and adding them to the parameters

    > >collection.
    > >> I've never used the parameters collection in ado classic either. I always
    > >> execute stored procedures using a command (or connection) object and passing
    > >> it a string like 'exec sp_dostuff parm1, parm2'. Am I missing something
    > >> by not using these parameter objects?
    > >>
    > >> -Jeff

    > >
    > >

    >




  4. #4
    Kathleen Dollard Guest

    Re: Why parameters collection

    Jeff,

    Yeah, there are one or two other advantages.

    The parameters can be established once then filled. They can be
    automatically mapped to your data so you do not have to manually do more
    than "Update" for Update/Insert/Delete stored procs. They contain size and
    type information as well as direction. It is a unit so you can pass it
    around and query it for information.

    I think of all these, the typing is the most significant. I prefer not to
    bother my SQLServer with bad data as I find it easier to debug on the VB
    side ahead of time.

    I agree with Bill that a strongly typed interface for parameter lists is a
    good thing

    Kathleen



  5. #5
    Jeff Pipes Guest

    Re: Why parameters collection


    Thx bill, is that the only advantage?

    -Jeff

    "Bill McCarthy" <bill_mcc@iprimus.com.au> wrote:
    >Hi Jeff,
    >
    >Yep ! How do you do return values in the parameters ?
    >
    >
    >"Jeff Pipes" <JeffP622@msn.com> wrote in message news:3cd01eac$1@10.1.10.29...
    >>
    >> I noticed that ado.net has a parameters collection for when calling stored
    >> procedures. What I don't understand is why. It seems like a lot of trouble
    >> creating all these parameter objects and adding them to the parameters

    >collection.
    >> I've never used the parameters collection in ado classic either. I always
    >> execute stored procedures using a command (or connection) object and passing
    >> it a string like 'exec sp_dostuff parm1, parm2'. Am I missing something
    >> by not using these parameter objects?
    >>
    >> -Jeff

    >
    >



  6. #6
    Bill McCarthy Guest

    Re: Why parameters collection

    Hi Jeff,

    A bit of each <g> The design I like is something like:

    Class Customers

    Default Property Item(index) as Customer

    Property SelectParams() as Customers.SelectParams

    Class SelectParams
    Property SomeSelectParam
    .....
    End Class

    End Class

    Class Customer
    ...
    End Class


    The Customers class holds private reference to the commands, and that reference is
    passed to the SelectParams class constructor. The SelectParams class is only
    created if the SelectParams property is read from (ie check if m_selectParams is
    nothing, if so create a new SelectParams class and make sure the select command
    has been initialised)

    The update, insert,delete, generally don't need extra params, but if they do then
    at present I am using a similar approach. What I want to do is make it so as an
    extra params, such as user id, password etc, can be properties of the Customers
    class, that is shared params for the different commands that aren't part of the
    child item properties.

    I haven't done it with different Select commands for the same collection class. My
    thinking on that is it isn't usually required. What I was thinking of doing
    instead is different collection classes, but using the same collection item, that
    way the item classes could be passed between them easily.

    I can't see any reason why I couldn't have different Select commands I suppose.
    Probably the biggest issue is just the naming of the params classes and the fill
    methods.

    But anyway, the reason I prefer this design is because it is strongly typed, and
    most importantly the developers using the components don't have to enter in long
    parameter lines or even worse, strings as identifiers <g> So code using this
    design would basically read like:

    Dim colCustomers as New Customers

    With colCustomers.SelectParams
    .StartDate = #1/1/2001#
    .EndDate = #1/1/2001#
    End With
    colCustomers.Fill





    "Jeff Pipes" <JeffP622@msn.com> wrote in message news:3cd03ab2$1@10.1.10.29...
    >
    > So you create a class to wrap your stored procedures? Do you usually create
    > one class per stored procedure, or do you wrap up all your stored procedures
    > in one class?
    >
    > -Jeff
    >
    > "Bill McCarthy" <bill_mcc@iprimus.com.au> wrote:
    > >Hi Jeff,
    > >
    > >That I know of, yes
    > >
    > >I personally like to wrap Property Set/Get's to control access to the

    parameters.
    > >This allows strong typing on your interface and can allow you to keep the

    > command
    > >itself internal to the data tier component, as well as apply business logic

    > rules
    > >in the Set/Gets.
    > >
    > >OT: I just wish they would do the same to the connection object cause I

    > hate
    > >connection strings <bg>
    > >
    > >

    >




  7. #7
    Jeff Pipes Guest

    Re: Why parameters collection


    So you create a class to wrap your stored procedures? Do you usually create
    one class per stored procedure, or do you wrap up all your stored procedures
    in one class?

    -Jeff

    "Bill McCarthy" <bill_mcc@iprimus.com.au> wrote:
    >Hi Jeff,
    >
    >That I know of, yes
    >
    >I personally like to wrap Property Set/Get's to control access to the parameters.
    >This allows strong typing on your interface and can allow you to keep the

    command
    >itself internal to the data tier component, as well as apply business logic

    rules
    >in the Set/Gets.
    >
    >OT: I just wish they would do the same to the connection object cause I

    hate
    >connection strings <bg>
    >
    >



  8. #8
    Anthony Jones Guest

    Re: Why parameters collection

    What do you do when one of the parameters has the type VARBINARY?

    --
    Anthony Jones
    Nuesoft Ltd



  9. #9
    Jeff Jones Guest

    Re: Why parameters collection


    >How do you do return values in the parameters ?


    For my own practice, I have yet to find a use for the ADO command object
    (ADO.NET command object is a different story). When I need a parameter back,
    such as the identity field value after an INSERT, I have the sp use it as
    an OUTPUT parameter, and end the sp with a SELECT. I execute the sp via
    the creation of an ADO recordset. I read the value (or values), then dispose
    of the recordset.

    It takes less typing, less code, and simpler for whomever might inherit my
    code later to understand.

    IMHO, the main reason some developers use the ADO command object in VB6 is
    because there were a number of Microsoft examples that showed it, and that
    is how some folks learned it. As for being type-safe with the parameters
    for the sp, strongly type the variables you use to make them, and QA your
    code. I have yet, after several years of doing this, to have a parameter
    type issue in what I pass to the sp.


  10. #10
    Michael Culley Guest

    Re: Why parameters collection

    One big disadvantage to using a command object is speed. If you had to
    execute your stored proc in a loop then your method would be slow. It breaks
    one of the general rules of using a database that as much SQL as possible
    should be pre-compiled and run on the backend. I quite like the command
    object because it is clear what is going on. Maybe you think using it is
    more complicated than it is, most of the samples show 4+ lines of code to
    add each parameter, but in reality only one is needed.

    Like Bill, I wrap each stored proc in a class. I inherit this class from a
    class called "StoredProcBase", which contains some general functions to make
    life easier. So when it comes time to use it, the code would be something
    like this:

    Dim SP as New StoredProcAddUser
    SP.Execute (UserName, Password ,... etc )

    The code in the stored proc class is quite small also.

    --
    Michael Culley
    www.vbdotcom.com



    "Jeff Jones" <jjones4@711Online.net> wrote in message
    news:3cd09624@10.1.10.29...
    >
    > >How do you do return values in the parameters ?

    >
    > For my own practice, I have yet to find a use for the ADO command object
    > (ADO.NET command object is a different story). When I need a parameter

    back,
    > such as the identity field value after an INSERT, I have the sp use it as
    > an OUTPUT parameter, and end the sp with a SELECT. I execute the sp via
    > the creation of an ADO recordset. I read the value (or values), then

    dispose
    > of the recordset.
    >
    > It takes less typing, less code, and simpler for whomever might inherit my
    > code later to understand.
    >
    > IMHO, the main reason some developers use the ADO command object in VB6 is
    > because there were a number of Microsoft examples that showed it, and that
    > is how some folks learned it. As for being type-safe with the parameters
    > for the sp, strongly type the variables you use to make them, and QA your
    > code. I have yet, after several years of doing this, to have a parameter
    > type issue in what I pass to the sp.
    >




  11. #11
    Glen Banta Guest

    Re: Why parameters collection


    Another option ....

    szCommandText = "procName P1, P2, 'Glen''s Proc'"

    set rs = conn.execute(szCommandText)

    NewID = rs(0)

    Or

    Msgbox rs(“username”)
    Msgbox rs(“password”)

    Set rs = nothing

    This is the same as executing a stored proc from Query Analyzer.
    Why write the stored proc and a class wrapper for the data set?

    Although I do agree in creating some type of data access layer object
    to simplify database connections etc.

    set rs = da.executeReturnRS(szCommandText, szConnString)

    or

    vaRS = da.ExecuteReturnArray(szCommandText, szConnString)

    "Jeff Pipes" <JeffP622@msn.com> wrote:
    >
    >I noticed that ado.net has a parameters collection for when calling stored
    >procedures. What I don't understand is why. It seems like a lot of trouble
    >creating all these parameter objects and adding them to the parameters collection.
    >I've never used the parameters collection in ado classic either. I always
    >execute stored procedures using a command (or connection) object and passing
    >it a string like 'exec sp_dostuff parm1, parm2'. Am I missing something
    >by not using these parameter objects?
    >
    >-Jeff



  12. #12
    Jeff Jones Guest

    Re: Why parameters collection


    >One big disadvantage to using a command object is speed.

    Correct, but it is not the deciding factor for me, unless the project is
    heavier on DB writes than DB reads. For me, it is simply unecessary in VB6.

    >I quite like the command object because it is clear what is going on.

    More clear than "cn.Execute" ?

    >Maybe you think using it is more complicated than it is

    No, by definition it is. If I can accomplish a task with 2 objects, where
    is the advantage in adding a 3rd object with no additional benefit? I tried
    them found them without advantage, and decided not to use them in ADO 2.X
    and VB6.

    >but in reality only one is needed.

    You can instantiate, populate, and execute a command object, all in one line?

    >Like Bill, I wrap each stored proc in a class.

    Personally, I have a single class (again we are talking about VB6, not .NET,
    where I handle it differently and actually have a use for comand objects)
    that I use for executing sps or sending in disconnected recordsets for update.
    And it has no command object.

    I am *not* saying it is wrong to use command objects. That is a decision
    every developer must make for himself or herself. I looked at the cost/value
    relationship, and chose not to use them. This thread asked why, and I shared
    why I don't use them. I am reading the thread with interest to see if someone
    can come up with a use for the darned things that would make them worthwhile
    for me. I am still waiting.



  13. #13
    Rob Teixeira Guest

    Re: Why parameters collection


    "Jeff Jones" <jjones4@711Online.net> wrote:
    >
    >I am *not* saying it is wrong to use command objects. That is a decision
    >every developer must make for himself or herself. I looked at the cost/value
    >relationship, and chose not to use them. This thread asked why, and I shared
    >why I don't use them. I am reading the thread with interest to see if someone
    >can come up with a use for the darned things that would make them worthwhile
    >for me. I am still waiting.


    When you execute a command from the connection, it is actually going through
    some common routines that the command object would eventually go to. However,
    when you set up the command object manually, you can specify optimizations
    that would not be possible in a connection execute. This may not seem like
    a big deal for you, but in a server environment with thousands of connections,
    it is.
    Also, certain types of parameter data aren't appropriately or efficiently
    passed in text.

    -Rob

  14. #14
    Michael Culley Guest

    Re: Why parameters collection

    > More clear than "cn.Execute" ?

    Yes, sure. Especially doing a SELECT to get the return value is quite
    obfusticated. Using the command object shows to the developer what the param
    types and (if appropriate) param length is, isn't that more clear?

    > You can instantiate, populate, and execute a command object, all in one

    line?
    If this was your priority then you could write a function to turn it into a
    single line. But it only takes one additional line for each param.

    > No, by definition it is.

    No, it is not complicated, sometimes when you haven't used something much it
    seems complicated.

    > why I don't use them. I am reading the thread with interest to see if

    someone
    > can come up with a use for the darned things that would make them

    worthwhile
    > for me. I am still waiting.


    They are more efficient and flexible and can be executed multiple times with
    speed. What more do you need? You are sacrificing all these real features to
    save a couple of lines of code.

    Maybe you should look at it from the other point of view, if you were using
    commands what arguments are there for going to your method, all I can see is
    that it saves a couple of lines of code.

    --
    Michael Culley
    www.vbdotcom.com



    "Jeff Jones" <jjones4@711Online.net> wrote in message
    news:3cd15321$1@10.1.10.29...
    >
    > >One big disadvantage to using a command object is speed.

    > Correct, but it is not the deciding factor for me, unless the project is
    > heavier on DB writes than DB reads. For me, it is simply unecessary in

    VB6.
    >
    > >I quite like the command object because it is clear what is going on.

    > More clear than "cn.Execute" ?
    >
    > >Maybe you think using it is more complicated than it is

    > No, by definition it is. If I can accomplish a task with 2 objects, where
    > is the advantage in adding a 3rd object with no additional benefit? I

    tried
    > them found them without advantage, and decided not to use them in ADO 2.X
    > and VB6.
    >
    > >but in reality only one is needed.

    > You can instantiate, populate, and execute a command object, all in one

    line?
    >
    > >Like Bill, I wrap each stored proc in a class.

    > Personally, I have a single class (again we are talking about VB6, not

    ..NET,
    > where I handle it differently and actually have a use for comand objects)
    > that I use for executing sps or sending in disconnected recordsets for

    update.
    > And it has no command object.
    >
    > I am *not* saying it is wrong to use command objects. That is a decision
    > every developer must make for himself or herself. I looked at the

    cost/value
    > relationship, and chose not to use them. This thread asked why, and I

    shared
    > why I don't use them. I am reading the thread with interest to see if

    someone
    > can come up with a use for the darned things that would make them

    worthwhile
    > for me. I am still waiting.
    >
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center