How To Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider and Visual C# .NET

Article translations Article translations
Article ID: 310130 - View products that this article applies to.
This article was previously published under Q310130
Expand all | Collapse all

On This Page

SUMMARY

This step-by-step describes how to call a parameterized SQL Server stored procedure using the ODBC .NET Managed Provider and Visual C# .NET.

While executing a parameterized stored procedure using the ODBC .NET Provider is little different from executing the same procedure using the SQL or the OLE DB Provider, there is one important difference: the stored procedure must be called using the ODBC CALL syntax rather than the name of the stored procedure. For additional information on this CALL syntax, see the topic "Procedure Calls" in the ODBC Programmer's Reference in the MSDN Library.

Call Syntax Examples

  1. Here is an example of the call syntax for an actual stored procedure in the Northwind sample database that expects one input parameter:
    {CALL CustOrderHist (?)}
  2. Here is an example of the call syntax for a stored procedure that expects one input parameter and returns one output parameter and a return value. The first placeholder represents the return value:
    {? = CALL Procedure1 (?, ?)
  3. The ODBC .NET Managed Provider, like the OLE DB Provider, processes parameters by ordinal position (zero-based) and not by name.

Test Project - Single Input Parameter

  1. If you have not already done so, download and install the ODBC .NET Managed Provider from the following Microsoft Web site:
    http://msdn.microsoft.com/en-us/data/aa937729.aspx
  2. Start Visual Studio .NET, and then create a new Visual C# .NET Windows Application with a name of your choosing.
  3. On 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.
  4. At very top of the code window, add the following statements:
    using System.Data;
    using Microsoft.Data.Odbc;
    					
  5. Drag a Button control from the toolbox to the default form.
  6. Double-click the command button to switch to the code window for the button's Click event. Enter or paste the code below in the Click event procedure, modifying the SQL Server connection string as necessary:
    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 each product.
        while (dr.Read()) 
    	Console.WriteLine(dr.GetString(0));
    
        //Clean up.
        dr.Close();
        cn.Close();
    }
    catch (OdbcException o) {
        MessageBox.Show(o.Message.ToString());
    }
    					
  7. Run the project. This code calls the "CustOrderHist" stored procedure, passing in the CustomerID as a single input parameter and returns a resultset. In the Output Window, you should see the list of products ordered by Northwind customer ALFKI.

    NOTE: Press CTRL+ALT+O to open the Output Window.

Test Project - Multiple Parameter Types

  1. Using Query Analyzer, create the following stored procedure in the Northwind sample database. This stored procedure accepts a CustomerID as an input parameter and returns a list of orders placed by the customer, returns the average freight per order paid by that customer as an output parameter, and returns the number of orders placed by the customer as a return value.
    CREATE PROCEDURE usp_TestParameters
    @CustID CHAR(5),
    @AvgFreight MONEY OUTPUT
    AS
    SELECT @AvgFreight = AVG(Freight) FROM Orders WHERE CustomerID = @CustID
    SELECT * FROM Orders WHERE CustomerID = @CustID
    RETURN @@ROWCOUNT
    					
  2. Repeat steps 1 through 6 above, substituting the following code in the Click event procedure of the command button:
    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);
        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.ExecuteReader();
    
        while (dr.Read())
             Console.WriteLine(dr.GetString(0));
    
        dr.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());
    }
    					
  3. Run the project. This code calls the "usp_TestParameters" stored procedure that we created in step 1 above, passing in the CustomerID as a single input parameter, and returns a resultset, an output parameter, and a return value. In the Output window, you should see the list of orders placed by Northwind customer ALFKI, the average freight the customer paid per order, and the count of orders.

    NOTE: Press CTRL+ALT+O to open the Output window.

Troubleshooting

  • The ADO syntax that is usually used to call stored procedures, where the name of the procedure alone is provided as the CommandText, cannot be used with the ODBC .NET Managed Provider.
  • When a stored procedure returns a resultset, the output parameter(s) and return value are not available until the resultset has been accessed and closed. For example, if we omitted the line "dr.Close()" in the second sample above, we would be unable to retrieve the values for the output parameter and the return value.
  • The ODBC .NET Managed Provider, like the OLE DB Provider, processes parameters by ordinal position (zero-based) and not by name.
  • The ODBC .NET Managed Provider does not ship with Visual Studio .NET, but must be downloaded separately.

REFERENCES

For additional information on the ODBC CALL syntax, see the topic "Procedure Calls" in the ODBC Programmer's Reference in the MSDN Library.

Properties

Article ID: 310130 - Last Review: July 1, 2004 - Revision: 3.4
APPLIES TO
  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.NET 1.1
  • Microsoft Visual C# .NET 2002 Standard Edition
  • Microsoft Visual C# .NET 2003 Standard Edition
Keywords: 
kbdatabase kbhowtomaster kbsystemdata KB310130

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com