Why parameters collection

[Bill McCarthy]

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

[Jeff Pipes]

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

[Bill McCarthy]

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

[Kathleen Dollard]

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

[Jeff Pipes]

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

[Bill McCarthy]

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

[Jeff Pipes]

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

[Anthony Jones]

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

--
Anthony Jones
Nuesoft Ltd

[Jeff Jones]

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

[Michael Culley]

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

[Glen Banta]

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

[Jeff Jones]

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

[Rob Teixeira]

"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

[Michael Culley]

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