HI,

Afraid this is another one of those "how do you do it in .net?" then 
questions.

Pre .net when you were calling stored procedures which required 
parameters to be passed in you could quite easily find the correct 
values for all the types etc of the parameters by doing a 
command.parameters.refresh which would go to the database get the values 
it needed and populate the parameters collection for you.

Anyone know how to do this in dotnet? I had a nice little asp page which 
would automatically write the asp code for accessing the stored 
procedures and would really like to ba able to write an aspx one.

PAt

you take a BIG hit in performance doing that....its just as easy to type
them in....
----- Original Message -----
From: "Pat Hastings" <pat.hastings@s...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Thursday, May 16, 2002 9:50 AM
Subject: [ado_dotnet] command.parameters.refresh in .net


HI,

Afraid this is another one of those "how do you do it in .net?" then
questions.

Pre .net when you were calling stored procedures which required parameters
to be passed in you could quite easily find the correct values for all the
types etc of the parameters by doing a command.parameters.refresh which
would go to the database get the values it needed and populate the
parameters collection for you.

Anyone know how to do this in dotnet? I had a nice little asp page which
would automatically write the asp code for accessing the stored procedures
and would really like to ba able to write an aspx one.

PAt

I am not certain I understand the BIG performance hit issue.  If you aren't
loading a large recordset, it seems reasonable.

The logic you need is hard to find in current textbooks - been looking some
myself.  You can find it about p.857 in Visual Basic.NET Platform: An
Advanced Guide by Andrew Troelsen published by Apress 2002.  The exact order
of statements is important....

(Sorry  Wrox Press  - this is NOT covered in the Professional VB.NET Version
2 text nor in Professional ASP.NET  -- issue is: How to create and use
sqlDataAdapter.UpdateCommand.Parameters and
sqlDataAdapter.SelectCommand.Parameters, etc. when you have assigned a
stored procedure.  If someone needs
to code <like I do> so that the update etc. tests concurrency with either a
timeStamp or a record hit counter, then
you can not use the script created by the CommandBuilder object.  This would
be a very helpful addition to the Professional level texts)

----- Original Message -----
From: "joe ostrosky" <jostrosky@t...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Thursday, May 16, 2002 8:36 PM
Subject: [ado_dotnet] Re: command.parameters.refresh in .net


> you take a BIG hit in performance doing that....its just as easy to type
> them in....
> ----- Original Message -----
> From: "Pat Hastings" <pat.hastings@s...>
> To: "ADO.NET" <ado_dotnet@p...>
> Sent: Thursday, May 16, 2002 9:50 AM
> Subject: [ado_dotnet] command.parameters.refresh in .net
>
>
> HI,
>
> Afraid this is another one of those "how do you do it in .net?" then
> questions.
>
> Pre .net when you were calling stored procedures which required parameters
> to be passed in you could quite easily find the correct values for all the
> types etc of the parameters by doing a command.parameters.refresh which
> would go to the database get the values it needed and populate the
> parameters collection for you.
>
> Anyone know how to do this in dotnet? I had a nice little asp page which
> would automatically write the asp code for accessing the stored procedures
> and would really like to ba able to write an aspx one.
>
> PAt
>
>
>
>

Hi Pat,

What you are looking for is the static method "DeriveParameters" of the 
SqlCommandBuilder class. Below is some explanation from the MSDN about 
this.

The overhead that was mentioned earlier in this thread relates to the 
round trip(s) that have to be made when retrieving the parameter info. 
Since you are using this to generate code at design time, this won't be a 
problem. Using this in production code is indeed not recommended as it 
causes a lot of overhead.

HtH

Imar

=====================================================================
Deriving Parameter Information
Parameters can also be derived from a stored procedure using the 
CommandBuilder class. Both the SqlCommandBuilder and OleDbCommandBuilder 
classes provide a static method, DeriveParameters, which will 
automatically populate the Parameters collection of a Command object with 
parameter information from a stored procedure. Note that DeriveParameters 
will overwrite any existing parameter information for the Command.

Deriving parameter information does require an added trip to the data 
source for the information. If parameter information is known at design-
time, you can improve the performance of your application by setting the 
parameters explicitly.

The following code example shows how to populate the Parameters 
collection of a Command object using CommandBuilder.DeriveParameters.

[Visual Basic]
Dim nwindConn As SqlConnection = New SqlConnection("Data 
Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;")
Dim salesCMD As SqlCommand = New SqlCommand("Sales By Year", nwindConn)
salesCMD.CommandType = CommandType.StoredProcedure

nwindConn.Open()
SqlCommandBuilder.DeriveParameters(salesCMD)
nwindConn.Close()

> HI,

Afraid this is another one of those "how do you do it in .net?" then 
questions.

Pre .net when you were calling stored procedures which required 
parameters to be passed in you could quite easily find the correct 
values for all the types etc of the parameters by doing a 
command.parameters.refresh which would go to the database get the values 
it needed and populate the parameters collection for you.

Anyone know how to do this in dotnet? I had a nice little asp page which 
would automatically write the asp code for accessing the stored 
procedures and would really like to ba able to write an aspx one.

PAt

Yep after some searching last week I've found that and finished building 
a little tool to generate the Aspx code needed to call a stored 
procedure with parameters. Needs a little more testing then I'll post it 
up here.
Pat

-----Original Message-----
From: Imar@S... [mailto:Imar@S...]
Sent: 18 May 2002 12:33
To: ADO.NET
Subject: [ado_dotnet] Re: command.parameters.refresh in .net


Hi Pat,

What you are looking for is the static method "DeriveParameters" of the
SqlCommandBuilder class. Below is some explanation from the MSDN about
this.

The overhead that was mentioned earlier in this thread relates to the
round trip(s) that have to be made when retrieving the parameter info.
Since you are using this to generate code at design time, this won't be 
a
problem. Using this in production code is indeed not recommended as it
causes a lot of overhead.

HtH

Imar

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Deriving Parameter Information
Parameters can also be derived from a stored procedure using the
CommandBuilder class. Both the SqlCommandBuilder and OleDbCommandBuilder 

classes provide a static method, DeriveParameters, which will
automatically populate the Parameters collection of a Command object 
with
parameter information from a stored procedure. Note that 
DeriveParameters
will overwrite any existing parameter information for the Command.

Deriving parameter information does require an added trip to the data
source for the information. If parameter information is known at design-
time, you can improve the performance of your application by setting the 

parameters explicitly.

The following code example shows how to populate the Parameters
collection of a Command object using CommandBuilder.DeriveParameters.

[Visual Basic]
Dim nwindConn As SqlConnection =3D New SqlConnection("Data
Source=3Dlocalhost;Initial Catalog=3DNorthwind;Integrated 
Security=3DSSPI;")
Dim salesCMD As SqlCommand =3D New SqlCommand("Sales By Year", 
nwindConn)
salesCMD.CommandType =3D CommandType.StoredProcedure

nwindConn.Open()
SqlCommandBuilder.DeriveParameters(salesCMD)
nwindConn.Close()

> HI,

Afraid this is another one of those "how do you do it in .net?" then =3D
questions.

Pre .net when you were calling stored procedures which required =3D
parameters to be passed in you could quite easily find the correct =3D
values for all the types etc of the parameters by doing a =3D
command.parameters.refresh which would go to the database get the values 
=3D
it needed and populate the parameters collection for you.=3D20

Anyone know how to do this in dotnet? I had a nice little asp page which 
=3D
would automatically write the asp code for accessing the stored =3D
procedures and would really like to ba able to write an aspx one.=3D20

PAt

--------------------------------------------------------

The contents of this e-mail are confidential to the ordinary user of the 
e-mail address to which it was addressed, or in the case of an 
incorrectly addressed e-mail message, the intended recipient. No-one 
else may copy, use, disseminate or forward all or any part of it in any 
form.

Although this email, including any attachments, is believed to be free 
of any virus, or other defect which might affect any computer or IT 
system into which it is received and opened, it is the responsibility of 
the recipient to ensure that it is virus free, and no responsibility is 
accepted for any loss or damage arising in any way from its use.

The views expressed in this e-mail are those of the sender and not 
necessarily the employees company.

If you receive this e-mail in error please accept our apology.  If this 
is the case we would be obliged if you would contact the sender and then 
delete the e-mail.

--------------------------------------------------------