HOW TO: Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider and Visual C++ .NET

This step-by-step article describes how to call a parameterized Microsoft SQL Server stored procedure using the Open Database Connectivity (ODBC) .NET managed provider and Microsoft Visual C++ .NET.

Executing a parameterized stored procedure by using the ODBC .NET Provider is slightly different from executing the same procedure by using the OLE DB or SQL Sever .NET data provider. For example, with ODBC .NET Provider, you must use the ODBC CALL syntax to call the stored procedure instead of by using the name of the stored procedure. For additional information about the ODBC CALL syntax, visit the following Microsoft Developer Network (MSDN) Web site:

Call Syntax Examples

• 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 (?)}
  					

• 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 (?, ?)
  					

• The ODBC .NET managed provider, like the OLE DB .NET data 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.
   
   To download the ODBC .NET managed provider, visit the following Microsoft Web site:
   http://msdn.microsoft.com/en-us/data/aa937729.aspx (http://msdn.microsoft.com/en-us/data/aa937729.aspx)
2. In Microsoft Visual Studio .NET, create a new Microsoft Visual C++ Managed C++ Application.
3. In Solution Explorer, double-click the source (.cpp) file.
4. To resolve a reference to Microsoft.Data.Odbc.dll, follow these steps:
   
   
   1. Open the Property Pages dialog box for the project.
   2. Click the C/C++ folder.
   3. Click the General tab.
   4. Modify the Resolve #using References property to include the path for the folder that contains Microsoft.Data.Odbc.dll (for example, type C:\Program Files\Microsoft.NET\Odbc.Net\).
   NOTE: Alternatively, add the path to the using statement. For example:

   #using "C:\Program Files\Microsoft.NET\Odbc.Net\Microsoft.Data.Odbc.dll"
   					

   
   Note To resolve a reference to Microsoft.Data.Odbc.dll in Visual C++ .NET 2003 follow the below mentioned step
   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.
5. Remove the default code from the source file, and then paste the following code in the file:

   #include "stdafx.h"
   #using <mscorlib.dll>
   #include <tchar.h>
   #using <system.xml.dll>
   using namespace System::Xml;
   #using <system.dll>
   using namespace System;
   #using <system.data.dll>
   using namespace System::Data;
   #using <Microsoft.Data.Odbc.dll>
   using namespace Microsoft::Data::Odbc;
   
   // This is the entry point for this application
   
   int _tmain(void)
   {
   
   OdbcConnection *myCon = new OdbcConnection("Driver={Sql Server};server=mySQL;trusted_connection=yes;database=northwind;");
   try{
     myCon->Open();
     OdbcCommand *myCmd = new OdbcCommand("{call CustOrderHist(?)}",myCon);
   
     //The following does not work:
     //OdbcCommand *myCmd = new OdbcCommand("CustOrderHist",myCon);
     //myCmd->CommandType=CommandType::StoredProcedure;
   
     myCmd->Parameters->Add("CustId",OdbcType::Char,5);
     myCmd->Parameters->Item[0]->Value=S"ALFKI";
     OdbcDataReader *myReader = myCmd->ExecuteReader();
   
     while (myReader->Read())
     {
         for(Int32 i=0;i<myReader->FieldCount;i++)
         {
   Console::WriteLine("{0}:{1}",(myReader->GetName(i))->ToString(),(myReader->GetValue(i))->ToString());
         }
         Console::WriteLine();
      }
   
      myReader->Close();
   }
   
   catch(OdbcException *myEx)
   {
         for (int i=0;i<myEx->Errors->Count;i++)
         {
   Console::WriteLine("Source:{0};Message={1}",myEx->Errors->Item[i]->Source,myEx->Errors->Item[i]->Message);
         }
   }
      myCon->Close();
      return 0;
   }
   					

6. Change the connection string appropriately. Press CTRL+F5 to compile and run the project.
   
   

Result:

This code calls the CustOrderHist stored procedure, passing in the Customer ID as a single input parameter, and returns a result set. In the Output window, you should see the list of products ordered by Northwind customer ALFKI.

Test Project - Multiple Parameter Types

1. The following stored procedure accepts a CustomerID as an input parameter, and returns:
   
   
   • A list of orders that the customer placed.
   • The average freight per order that is paid by that customer as an output parameter.
   • The number of orders that the customer placed as a return value.
   
   In Query Analyzer, create the following stored procedure in the Northwind sample database:

   CREATE PROCEDURE MultiParamSP
   @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 2 through 6 in the "Single Input Parameter" section of this article, but use the following code:

   #include "stdafx.h"
   #using <mscorlib.dll>
   #include <tchar.h>
   #using <system.xml.dll>
   using namespace System::Xml;
   #using <system.dll>
   using namespace System;
   #using <system.data.dll>
   using namespace System::Data;
   #using <Microsoft.Data.Odbc.dll>
   using namespace Microsoft::Data::Odbc;
   
   // This is the entry point for this application
   int _tmain(void)
   {
   OdbcConnection *myCon = new OdbcConnection("Driver={Sql Server};server=mySQL;trusted_connection=yes;database=northwind;");
   
   try{
      myCon->Open();
      OdbcCommand *myCmd = new OdbcCommand("{?=call MultiParamSP(?,?)}",myCon);
      myCmd->Parameters->Add("RetVal",OdbcType::Int);
      myCmd->Parameters->Item[0]->Direction=ParameterDirection::ReturnValue;
      myCmd->Parameters->Add("CustId",OdbcType::Char,5);
      myCmd->Parameters->Item[1]->Value=S"ALFKI";
      myCmd->Parameters->Add("AvgFr",OdbcType::Double);
      myCmd->Parameters->Item[2]->Direction=ParameterDirection::Output;
      //The following does not work:
      //OdbcCommand *myCmd = new OdbcCommand("MultiParamSP",myCon);
      //myCmd->CommandType=CommandType::StoredProcedure;
   
      OdbcDataReader *myReader = myCmd->ExecuteReader();
      while (myReader->Read())
      {
        Console::WriteLine();
        for(Int32 i=0;i<myReader->FieldCount;i++)
        {
   Console::WriteLine("{0}:{1}",(myReader->GetName(i))->ToString(),(myReader->GetValue(i))->ToString());
        }
      }
      myReader->Close();
    Console::WriteLine("OutputParamVal={0};ReturnVal={1}",myCmd->Parameters->Item[2]->Value,myCmd->Parameters->Item[0]->Value);
     }
   catch(OdbcException *myEx)
   {
    for (int i=0;i<myEx->Errors->Count;i++)
    {
   Console::WriteLine("Source:{0};Message={1}",myEx->Errors->Item[i]->Source,myEx->Errors->Item[i]->Message);
         }
   }
   catch(System::Exception *myEx)
   {
      Console::WriteLine("Source:{0};Message={1}",myEx->Source,myEx->Message);
   }
   myCon->Close();
   return 0;
   }
   					

Result:

The code calls the MultiParamSP stored procedure, which you created in the "Single Input Parameter" section, passing in the CustomerID as a single input parameter, and returns a result set, 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.

Troubleshooting

• You cannot use the ADO syntax that you typically use to call stored procedures with the ODBC .NET managed provider; typically, the name of the procedure alone is provided as the CommandText.
• With SQL Server driver, when a stored procedure returns a result set, the output parameters and the return value are not available until the result set has been accessed and closed. For example, if you omitted the line

  dr.Close()
  						

  in the second code sample, you cannot retrieve the values for the output parameter and the return value.
• The ODBC .NET managed provider, like the OLE DB .NET Provider, processes parameters by ordinal position (zero-based) and not by name.
• The ODBC .NET managed provider is not included with Visual Studio .NET; you have to down load it separately.

For additional information, see the ODBC .NET data provider reference documentation that comes with ODBC .NET data provider and the following MSDN Web site: