The release number of this article has been CHS310130
For Microsoft Visual Basic .NET versions of this article, see
309486.
For Microsoft Visual C .NET versions of this article, see
310142.
This article references the following Microsoft .NET Framework Class Bank Name Space:
Microsoft.Data.odbc
This task content
summary
Call syntax sample test item - single input parameter test item - multi-parameter type troubleshooting reference
Summary This step-by-step guidance describes how to use the ODBC .NET managed provider and the Visual C # .NET call parameterized SQL Server stored procedure.
Although the use of the ODBC .NET provider performs the parameterized stored procedure to perform the same storage process with the SQL or OLE DB provider, an important difference is that the stored procedure must be called using the ODBC Call syntax, not the stored procedure The name. For additional information about this CALL syntax, see the "Procedure Calls" topic in the ODBC Programmer's Reference (ODBC programmer) in the MSDN library.
Back to top
Calling syntax sample
This is a call syntax for calling syntax for a actual stored procedure requiring a single input parameter in the Rose text: {CALL CUSTORDERHIST (?)} This is a calling grammar that requires a single input parameter and returns an output parameter and a stored procedure for a return value. Example: The first placeholder represents the return value: {? = CALL Procedure1 (?,?) ODBC .NET host, such as the OLE DB provider, in the location order (starting from 0) instead of by name processing parameters.
Back to top
Test item - single input parameters
Download and install the ODBC.NET managed provider from the following Microsoft Web site (if not yet): http://www.microsoft.com/data launches Visual Studio .NET, then create a Visual C # .NET Windows application (name set). From the project menu, click Add Reference, then double-click Microsoft.Data.odbc.dll to add it to the list of items. Close the reference dialog. Add the following statement to the top of the code window: use system.data;
Using Microsoft.Data.odbc; drag the Button control from the toolbox to the default form. Double-click the inserted button to switch to the code window of the Click event of the button. Enter or paste the following code to the Click event process, modify the SQL Server connection string as needed: ODBCCONNECTION CN;
Odbccommand cmd;
Odbcparameter PRM;
OdbcdataReader DR;
Try {
// Change The Connection String To Use your SQL Server.
CN = New ODBCCONNECTION ("driver = {sql server}; server = servername; database = northwind; trusted_connection = yes");
// Use ODBC CALL SYNTAX.
CMD = new odbccommand ("{Call CustOrderhist (?)}", CN);
PRM = cmd.Parameters.Add ("@ Customerid", ODBCTYPE.CHAR, 5); prm.value = "Alfki";
Cn.open ();
DR = cmd.executeReader ();
// List performance.
While (Dr.Read ())
Console.writeline (Dr.getstring (0));
// Clean Up.
Dr.close ();
Cn.close ();
}
Catch (ODBCEXCEPTION O) {
Messagebox.show (o.Message.toString ());
} Run the project. This code calls the "CustorderHist" stored procedure to pass the CustomerID as a single input parameter and return a result set. In the output window, you should see the list of products ordered by Ross Wen Customer ALFKI. Note: Press the CTRL Alt O grouporder to open the output window.
Back to top
Test item - multi-parameter type
Using Query Analyzer, create the following stored procedure in the Russen Document Database: This stored procedure accepts Customerid as an input parameter and returns a customer order list, and returns the average shipping of each order paying by customers, and returns to customers' orders. As a return value. Create Procedure USP_TESTPARETERS
@Custid char (5),
@AVGFReight Money OUTPUT
AS
Select @avgfreight = avg (freight) from orderrs where customerid = @custid
Select * from Orders Where Customerid = @custid
Return @@ rowcount Repeat the above steps 1 to 6, replace the Click event process of the button to the following code: ODBCCONNECTION CN;
Try {
CN = New ODBCCONNECTION ("driver = {sql server}; server = servername; database = northwind; trusted_connection = yes");
Odbccommand cmd = new odbccommand ("{? = Call usp_testparameters (?,?)}", Cn);
ODBCPARETER PRM = cmd.parameters.add ("@ return_value", odbctype.int);
Prm.direction = parameterDirection.returnValue;
PRM = cmd.Parameters.Add ("@ Customerid", ODBCTYPE.CHAR, 5);
Prm.Value = "Alfki";
PRM = cmd.Parameters.Add ("@ avgfreight", odbctype.double);
prm.direction = parameterdirection.output;
Cn.open ();
ODBCDATAREADER DR = cmd.executeRead ();
While (Dr.Read ())
Console.writeline (Dr.getstring (0));
Dr.close ();
Cn.close ();
Console.writeline ("Average Freight (Output Param): Cmd.Parameters [2] .value);
Console.writeline ("Order Count (Return Value): {0}", cmd.parameters [0] .value);
}
Catch (ODBCEXCEPTION O) {
Messagebox.show (o.Message.toString ());
} Run the project. This code calls the "USP_TestParameters" stored procedure created in step 1 described above, passes CustomerID as a single input parameter, and returns a result set and an output parameter as the return value. In the output window, you should see the list of product lists ordered by Ross Wen Customers, and the customer pays the average freight and orders for each order. Note: Press the CTRL Alt O grouporder to open the output window.
Back to top
Troubleshooting
Commonly used to call the stored procedure ADO syntax (where only process names are provided as CommandText) cannot be used for ODBC .NET managed providers. When the stored procedure returns a result set, the output parameters and return values cannot be seen unless they have access and close the result set. For example, if you slightly go to the second example, we will not get the output parameter and return value. ODBC .NET hosting provider, such as OLE DB provider, in accordance with location order (starting from 0) instead of by name processing parameters. The ODBC .NET hosted provider is not available with Visual Studio .NET, you must download separately.
Back to top
Refer to additional information about ODBC Call syntax, see "Procedure Calls" topics in ODBC Programmer's Reference (ODBC programmers) in the MSDN library.
Back to top
The information in this article applies to:
Microsoft ADO.NET (provided with .NET Frame) Microsoft Visual C # .NET (2002)
Recent Updated: 2002-6-18 (1.0) Keyword KBDATABASE KBHOWTO KBHOWTOMASTER KBODBC KBSYSTEMDATA KB310130
