Execute SQL parameterized stored procedures using the ODBC. NET provider and Visual C #. Net

Http://support2.microsoft.com/kb/310130/zh-cn

This step-through article describes how to use the ODBC. NET Managed Provider and Visual C #. NET to invoke parameterized SQL Server stored procedures.

Although using an ODBC. NET provider to perform parameterized stored procedures is not very different from executing the same stored procedure using SQL or OLE DB providers, one important difference is that the stored procedure must be called using the ODBC call syntax and cannot use the name of the stored procedure. For additional information about this call syntax, see the "Procedure Calls" (Procedure Call) topic in the MSDN Library in the ODBC Programmer ' s Reference (ODBC Programmer's Reference).

Invocation Syntax Example

1. Here is an example invocation syntax for an actual stored procedure that requires a single input parameter in the Northwind sample database:

   {call CustOrderHist (?)}

2. This is an example of a call syntax that requires a single input parameter and returns an output parameter and a stored procedure that returns a value: The first placeholder represents the return value:

   {? = Call Procedure1 (?,?)

3. ODBC. NET managed providers, such as OLE DB providers, handle parameters in the order of location (starting at 0) instead of by name.

test Project-Single input parameters

2. Download and install the Odbc.net managed provider (if not already in progress) from the following Microsoft Web site: http://www.microsoft.com/data
4. Start visual Studio. NET, and then create a new Visual C #. NET Windows application (name Custom).
6. From the Project menu, click Add Reference , and then double-click Microsoft.Data.ODBC.dll to add it to the selected items list. Close the References dialog box.
8. Add the following statement to the top of the code window:

   Using system.data;using Microsoft.Data.Odbc;

10. Drag the Button control from the toolbox to the default form.
12. Double-click the inserted button to switch to the button's click Event Code window. Enter or paste the following code into the Click event procedure and 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 S erver.cn = new OdbcConnection ("Driver={sql Server}; Server=servername;database=northwind; Trusted_connection=yes "),//use ODBC Call syntax.cmd = New OdbcCommand (" {call CustOrderHist (?)} ", cn);p rm = cmd. Parameters.Add ("@CustomerID", OdbcType.Char, 5);p rm. Value = "ALFKI"; CN. Open ();d r = cmd. ExecuteReader ();//list each product.while (Dr. Read ()) Console.WriteLine (Dr. GetString (0));//clean Up.dr.Close (); CN. Close ();} catch (OdbcException o) {MessageBox.Show (o.message.tostring ());}

14. Run the project. This code calls the "custorderhist" stored procedure, passing CustomerID as a single input parameter and returning a result set. In the Output window, you should see the list of products ordered by the Northwind customer ALFKI.
    
    Note : Press the Ctrl+alt+o key to open the Output window.

test Project-Multi-parameter type

2. use Query Analyzer to create the following stored procedure in the Northwind sample database: This stored procedure accepts CustomerID as an input parameter and returns a list of customer orders, returning the average shipping cost for each order paid by the customer as an output parameter, Returns the number of orders returned by the customer as the return value.

   create PROCEDURE usp_testparameters@custid CHAR (5), @AvgFreight money outputasselect @AvgFreight = AVG (Freight) from orders where CustomerID = @CustIDSELECT * from orders where CustomerID = @CustIDRETURN @ @ROWCOUNT 

   /li>
4. Repeat steps 1 through 6 above to replace the button's Click event procedure with 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); OdbcParameter PRM = cmd. Parameters.Add ("@RETURN_VALUE", OdbcType.Int);p rm. Direction = PARAMETERDIRECTION.RETURNVALUE;PRM = cmd. Parameters.Add ("@CustomerID", OdbcType.Char, 5);p rm. Value = "ALFKI";p rm = cmd. Parameters.Add ("@AvgFreight", odbctype.double);p rm. Direction = PARAMETERDIRECTION.OUTPUT;CN. Open (); OdbcDataReader dr = cmd. ExecuteReader (); while (Dr. Read ()) Console.WriteLine (Dr. GetString (0));d R. Close (); CN. Close (); Console.WriteLine ("Average Freight (output param): {0}", cmd. PARAMETERS[2]. Value); Console.WriteLine ("Order Count (return value): {0}", cmd. Parameters[0]. Value);} catch (OdbcException o) {MessageBox.Show (o.message.tostring ());} 

6. Run the project. This code calls the "usp_testparameters" stored procedure created in step 1 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 products ordered by the Northwind customer ALFKI, the average freight and the number of orders paid by the customer for each order.

Execute SQL parameterized stored procedures using the ODBC. NET provider and Visual C #. Net