ADO Parameters

[Guest]

I am confused, What is the deal with ADONET parameters with stored
procedures??. In the 'old' ADO using stored proc. with SQL server, you
simply...
Set Conn1 = New ADODB.Connection
Set Cmd1 = New ADODB.Command
Conn1.Open "Provider=sqloledb;Data Source=(local);Initial Catalog=db;User
ID=sa;Password=;"
Cmd1.ActiveConnection = Conn1
Conn1.CursorLocation = adUseServer
Cmd1.CommandType = adCmdStoredProc
Cmd1.CommandText = "myStoredProc"
Cmd1.Parameters(1).value = somevalue ****ADO 'KNOWS'
about this parameter
Cmd1.Execute ..... simple, it worked *****

With ADONET, it seems not to 'know' about the parameter
Does ADONET force you you to define the parameters twice, once in the stored
proc(like normal) and once in code like:

Dim parameterItemId As New SqlParameter("@ItemId", SqlDbType.Int, 4) '
Parameter name and type here

or does it not force you to do it? It seems very foolish to remove that
awareness ADO used to have.
I can't seemt to get a good answer whether yes or no, and can't find any
documentation on it. I tried this code as a test and sure enough,
it throws up without a design time defined param:

In and ASP.NET project (VB) I have a SQLconnection called SQLDB and a
SQLCommand called SQLCmd
placed on a component called SQLComponent... I have a test Stored Proc on
SQL server that accepts one param for test...

I have a webform that has the code in a class
Imports System.data.Sqlclient

class def..
Protected tmpSQL As New SQLComponent
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
tmpSQL.SQLDB.Open() ********This Works
tmpSQL.SQLcmd.CommandType = CommandType.StoredProcedure ******This Works
tmpSQL.SQLcmd.CommandText = "mp_GetTestData" ****This Works
tmpSQL.SQLcmd.Parameters(1).Value = "%"
*****BOOM***** says parameter not in SQLparameter collection ???? HUNH?
IFI leave it out though, it pukes saying parameter is missing???

Dim myreader As SqlDataReader = tmpSQL.SQLcmd.ExecuteReader
End sub
end class def...

It sure would be time consuming to RE-CREATE every parameter in code again
so the engine knows what the params are. Am I doing something
wrong, or did Microsoft remove the parameter awareness from the new ADONET
framework?
Anybody have an answer for this, would be appreciated.. K

 

[Marina]

The command builder classes have a DeriveParameters method which allows you
to go to the database and fetch all the parameters, without having to
manually add them. However, this involves a round trip to the db server (as
it had to have with ADO as well).

Now, is:

myCommand.Parameters(0).Value = "something"

really so much easier then:

myCommand.Parameters.Add("@myParam","something")

? There are also other overloaded version of Add if you need to define the
length of the parameter, etc.

But personally, I dont' really see the latter as being any more difficult.
In fact, it saves you a trip to the db server, and you are explicitly adding
all the parameters - so you know exactly what parameters you have or have
not defined.

 

[David Browne]

I am confused, What is the deal with ADONET parameters with stored
procedures??. In the 'old' ADO using stored proc. with SQL server, you
simply...
Set Conn1 = New ADODB.Connection
Set Cmd1 = New ADODB.Command
Conn1.Open "Provider=sqloledb;Data Source=(local);Initial Catalog=db;User
ID=sa;Password=;"
Cmd1.ActiveConnection = Conn1
Conn1.CursorLocation = adUseServer
Cmd1.CommandType = adCmdStoredProc
Cmd1.CommandText = "myStoredProc"
Cmd1.Parameters(1).value = somevalue ****ADO 'KNOWS'
about this parameter
Cmd1.Execute ..... simple, it worked *****

No it didn't. You had to add cmd1.Parameters.Refresh, which causes an
expensive round-trip to the server.

Do do the same thing in ADO.NET you can use
SQLCommandBuilder.DeriveParameters.

It's just expensive to do it this way, just like it was in ADO.

Davud

 

[Harold Ommert[MS]]

Hi,

The execution of a stored procedure with ADO.NET is similar to ADO in some
respects. You need to create the parameter object, being sure to include
the parameters name, data type, size, and value, and add to the command
object's parameters collection. You can then use the DataAdapter to fill
the DataSet with the results of the stored procedure, should it be one
which return's data. There is an excellent explanation of using stored
procedure's with ADO.NET, and some great sample code, in the MSDN link
below:

Using Stored Procedures in Visual Basic .NET
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadvnet/ht
ml/vbnet09102002.asp

Also, the following articles also contain some very good information and
sample code regarding stored procedures and ADO.NET:

306574 HOW TO: Call SQL Server Stored Procedures in ASP.NET by Using Visual
http://support.microsoft.com/?id=306574

309486 HOW TO: Execute SQL Parameterized Stored Procedures by Using the ODBC
http://support.microsoft.com/?id=309486

308049 HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and
http://support.microsoft.com/?id=308049

Hope this is helpful information.

Harold Ommert
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.


Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.