HOW TO: Call a Parameterized Stored Procedure by Using ADO .NET and Visual C++ .NET

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

On This Page

SUMMARY

There are several ways to use ADO.NET to call a stored procedure and to get back return values and output parameters, including:
  • Use a DataAdapter or DataSet object to gather the returned rows and to work with these rows in addition to the return values and the output parameters.
  • Use a DataReader object to gather the returned rows, to move through these rows, and to gather return values and output parameters.
  • Use the ExecuteScalar method to return the value from the first column of the results' first row with the return values and the output parameters. This is most useful with aggregate functions.
  • Use the ExecuteNonQuery method to return only the output parameters and the return values. Any returned rows are discarded. This is most useful for executing action queries.
This article demonstrates the last three methods and uses both the SqlCommand and the OleDbCommand objects. Make sure that you copy only the code for the managed provider that you are using. If you are not sure which managed provider you should use, visit the following Microsoft Developer Network Web site:
.NET Data Providers
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconadonetproviders.asp
In each of the samples in this article, the parameters are added to the Parameters collection of the Command object. The SQL Server .NET data provider (SqlClient) supports named parameters. Therefore, when you use the SqlCommand object, you do not have add the parameters in any particular order, but the parameters must have the correct name. In this case, you must add the at sign (@) prefix to the parameter names.

Alternatively, the OLE DB .NET data provider does not support named parameters. Therefore, when you use the OleDbCommand object, you must add the parameters in the correct order (which is the same order that is defined at the backend stored procedure). In this case, you do not need to add the "@" prefix to the parameter names, and the parameter names do not have to match those that are defined at the stored procedure.

Use DataReader to Return Rows and Parameters

You can use the DataReader object to return a read-only, forward-only stream of data. The information that the DataReader contains can come from a stored procedure. This example uses the DataReader object to run a stored procedure that has an input and an output parameter and then moves through the returned records to view the return parameters.
  1. In the Pubs sample database, create the following stored procedure on the computer that is running Microsoft SQL Server:
    Create Procedure TestProcedure
        (
            @au_idIN varchar (11),
            @numTitlesOUT Integer OUTPUT
        )
    As
    
    select A.au_fname, A.au_lname, T.title 
    from authors as A join titleauthor as TA on
    A.au_id=TA.au_id
    join titles as T
    on T.title_id=TA.title_id
    where A.au_id=@au_idIN
    set @numTitlesOUT = @@Rowcount
    return (5) 
    					
  2. Start Microsoft Visual Studio .NET, and then create a new Managed C++ Application project in Visual C++ .NET.
  3. In Solution Explorer, double-click the source (.cpp) file.
  4. In the source file, replace the default code with the following code:

    SQL Client

    Note You must change the User ID <username> value and the password =<strong password> value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
    #include "stdafx.h"
    #using <mscorlib.dll>
    #include <tchar.h>
    #using <system.dll>
    using namespace System;
    #using <system.data.dll>
    using namespace System::Data;
    using namespace System::Data::SqlClient;
    
    // This is the entry point for this application.
    int _tmain(void)
    {
        try{
            SqlConnection *myCon = new SqlConnection("Data Source=mySQLServer;User ID=<username>;
                                                      Password=<strong password>;initial catalog=pubs;");
            myCon->Open();
            
            SqlCommand *myCmd = new SqlCommand("TestProcedure", myCon);
            myCmd->CommandType = CommandType::StoredProcedure;
                    
            // Parameter order does not matter because SqlClient supports named 
            // parameters. Type the exact parameter names as declared in the
            // stored procedure definition at the backend.
            // Parameters should be prefixed with @.
            myCmd->Parameters->Add("@au_idIN",SqlDbType::VarChar,11);
            myCmd->Parameters->get_Item(0)->Value=S"213-46-8915";
    		
            SqlParameter *retParam;
            retParam=myCmd->Parameters->Add("RetVal",SqlDbType::Int,4);
            retParam->Direction=ParameterDirection::ReturnValue;
    
            SqlParameter *outParam;
            outParam=myCmd->Parameters->Add("@numTitlesOUT",SqlDbType::Int,4);
            outParam->Direction=ParameterDirection::Output;
    	
            SqlDataReader *myReader;	
            myReader=myCmd->ExecuteReader();
    	
            while(myReader->Read())
            {
                for(int col=0;col<myReader->FieldCount;col++)
                {
                    Console::Write("{0}: {1}",(myReader->GetName(col))->ToString(),
                                              (myReader->GetValue(col))->ToString());
                    Console::WriteLine();
                }
                    Console::WriteLine();
            }
            myReader->Close();
            Console::WriteLine("Output Param:{0}; Return Value:{1}",
                                outParam->Value,retParam->Value);
            myCon->Close();
        }
        catch(SqlException *mySqlEx)
        {	
            for(int i=0;i<mySqlEx->Errors->Count;i++)
            {
                Console::WriteLine("Source={0};Message={1};",mySqlEx->Errors->
                                   Item[i]->Source,mySqlEx->Errors->Item[i]->Message);
            }
        }
        catch(System::Exception *ex)
        {
            Console::WriteLine(ex->get_Message());
        }
    }
    					

    OLE DB Data Provider

    #include "stdafx.h"
    #using <mscorlib.dll>
    #include <tchar.h>
    #using <system.dll>
    using namespace System;
    #using <system.data.dll>
    using namespace System::Data;
    using namespace System::Data::OleDb;
    
    // This is the entry point for this application.
    int _tmain(void)
    {
        try{
            OleDbConnection *myCon = new OleDbConnection("Provider=SQLOLEDB.1;
                                                          Data Source=mySQLServer;User ID=<username>;
                                                          Password=<strong password>;initial catalog=pubs;");
            myCon->Open();
            
            OleDbCommand *myCmd = new OleDbCommand("TestProcedure", myCon);
            myCmd->CommandType = CommandType::StoredProcedure;
    
            // The following notation also works. To test this notation,
            // comment out the above two lines, and uncomment the next two lines.
            // OleDbCommand *myCmd = new OleDbCommand("{?=call TestProcedure(?,?)}", myCon);
            // myCmd->CommandType = CommandType::Text;
    
    
            // The parameter order is important in this sample. Parameters are
            // matched with stored procedure parameters in the order they are supplied.
            // Names can be anything. You do not have to prefix them with "@".
            OleDbParameter* retParam=myCmd->Parameters->Add("RetVal",OleDbType::Integer,4);
            retParam->Direction=ParameterDirection::ReturnValue;
    
            myCmd->Parameters->Add("au_idIN",OleDbType::VarChar,11);
            myCmd->Parameters->Item[1]->Value=S"213-46-8915";
    
            OleDbParameter *outParam=myCmd->Parameters->Add("numTitlesOUT",OleDbType::Integer,4);
            outParam->Direction=ParameterDirection::Output;
    	
            OleDbDataReader *myReader;	
            myReader=myCmd->ExecuteReader();
    	
            int rowCnt=0;
    	
            while(myReader->Read())
            {
                for(int col=0;col<myReader->FieldCount;col++)
                {
                    Console::Write("{0}: {1}",(myReader->GetName(col))->ToString(),
                                              (myReader->GetValue(col))->ToString());
                    Console::WriteLine();
                }
             			
                Console::WriteLine();
                rowCnt++;
            }
    
            myReader->Close();
            Console::WriteLine("Output Param:{0}; Return Value:{1}",outParam->
                                Value,retParam->Value);
            myCon->Close();
        }
        catch(OleDbException *mySqlEx)
        {
            for(int i=0;i<mySqlEx->Errors->Count;i++)
            {
                Console::WriteLine("Source={0};Message={1};",mySqlEx->Errors->
                                    Item[i]->Source,mySqlEx->Errors->Item[i]->Message);
            }
        }
        catch(System::Exception *ex)
        {
            Console::WriteLine(ex->get_Message());
        }
    }
    					
  5. Modify the connection string for the Connection object to point to the computer that is running SQL Server.
  6. Press the CTRL+F5 key combination to compile and run the project. The Output window displays the titles of two books, the return value of 5, and the output parameter, which contains the number of records (2). Notice that you must close the DataReader in the code to see the parameter values. Additionally, note that you do not have to move through all of the records to see the return parameters if the DataReader is closed.

Use the ExecuteScalar Method of the Command Object

You can use the ExecuteScalar method of the Command object to retrieve parameter values. Additionally, ExecuteScalar returns the first column of the first row of the stored procedure. This is most useful for aggregate functions as in the following example.
  1. Create the following stored procedure on the computer that is running SQL Server:
    Create Procedure TestProcedure2
        (
            @au_idIN varchar (11)
        )
    As
    select count (T.title) 
    from authors as A join titleauthor as TA on
    A.au_id=TA.au_id
    join titles as T
    on T.title_id=TA.title_id
    where A.au_id=@au_idIN
    Return(5)
    					
  2. Start Visual Studio .NET, and then create a new Managed C++ Application project in Visual C++ .NET.
  3. In Solution Explorer, double-click the source (.cpp) file.
  4. In the source file, replace the default code with the following code:

    SQL Client

    Note You must change the User ID <username> value and the password =<strong password> value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
    #include "stdafx.h"
    #using <mscorlib.dll>
    #include <tchar.h>
    #using <system.dll>
    using namespace System;
    #using <system.data.dll>
    using namespace System::Data;
    using namespace System::Data::SqlClient;
    // This is the entry point for this application.
    int _tmain(void)
    {
    
        try{
            SqlConnection *myCon = new SqlConnection("Data Source=mySQLServer;User ID=<username>;
                                                      Password=<strong password>;initial catalog=pubs;");
            myCon->Open();
            
            SqlCommand *myCmd = new SqlCommand("TestProcedure2", myCon);
            myCmd->CommandType = CommandType::StoredProcedure;
    
            SqlParameter* retParam=myCmd->Parameters->Add("RetVal",SqlDbType::Int,4);
            retParam->Direction=ParameterDirection::ReturnValue;
    
            myCmd->Parameters->Add("@au_idIN",SqlDbType::VarChar,11);
            myCmd->Parameters->Item[1]->Value=S"213-46-8915";
    	
            String *cnt;
            cnt=myCmd->ExecuteScalar()->ToString();
            Console::WriteLine("Cnt:{0}; Return Value:{1}",cnt,retParam->Value);
    
            myCon->Close();
    
        }
        catch(SqlException *mySqlEx)
        {
            for(int i=0;i<mySqlEx->Errors->Count;i++)
            {
                Console::WriteLine("Source={0};Message={1};",mySqlEx->Errors->
                                    Item[i]->Source,mySqlEx->Errors->Item[i]->Message);
            }
        }
        catch(System::Exception *ex)
        {
            Console::WriteLine(ex->get_Message());
        }
    
    }
    					

    OLE DB Data Provider

    #include "stdafx.h"
    #using <mscorlib.dll>
    #include <tchar.h>
    #using <system.dll>
    using namespace System;
    #using <system.data.dll>
    using namespace System::Data;
    using namespace System::Data::OleDb;
    
    // This is the entry point for this application.
    int _tmain(void)
    {
        try{
            OleDbConnection *myCon = new OleDbConnection("Provider=SQLOLEDB.1;
                                                          Data Source=mySQLServer;User ID=<username>;
                                                          Password=<strong password>;initial catalog=pubs;");
            myCon->Open();
            
            OleDbCommand *myCmd = new OleDbCommand("{?=call TestProcedure2(?)}", myCon);
            myCmd->CommandType = CommandType::Text;
    
            OleDbParameter * retParam=myCmd->Parameters->Add("RetVal",OleDbType::Integer,4);
            retParam->Direction=ParameterDirection::ReturnValue;
    
            myCmd->Parameters->Add("au_idIN",OleDbType::VarChar,11);
            myCmd->Parameters->Item[1]->Value=S"213-46-8915";
    	
            String *cnt;
            cnt=myCmd->ExecuteScalar()->ToString();
            Console::WriteLine("Count:{0}; Return Value:{1}",cnt,retParam->Value);
    
            myCon->Close();
        
        }
        catch(OleDbException *mySqlEx)
        {
            for(int i=0;i<mySqlEx->Errors->Count;i++)
            {
                Console::WriteLine("Source={0};Message={1};",mySqlEx->Errors->
                                    Item[i]->Source,mySqlEx->Errors->Item[i]->Message);
            }
        }
        catch(System::Exception *ex)
        {
            Console::WriteLine(ex->get_Message());
        }
    
    }	
    					
  5. Modify the connection string for the Connection object to point to the computer that is running SQL Server.
  6. Press CTRL+F5 to compile and run the project. The Output window displays the value of the first column of the first row (Count) as well as the return value.

Use the ExecuteNonQuery Method of the Command Object

This sample uses the ExecuteNonQuery method to run the query and to return the parameter values. ExecuteNonQuery also returns the number of records that are affected after the query runs. However, ExecuteNonQuery does not return any rows or columns from the stored procedure.

The ExecuteNonQuery method is most useful when you use INSERT, UPDATE, or DELETE statements if you only have to know how many rows are changed. In a stored procedure in which you are using only a SELECT statement, you receive -1 because no rows are affected by the query.
  1. Create the following stored procedure on the computer that is running SQL Server:
    Create Procedure TestProcedure3
        (
            @au_idIN varchar (11),
            @au_fnam varchar (30)
        )
    
    As
    Update authors set au_fname = @au_fnam
    where au_id = @au_idin	
    return (5)
    					
  2. Start Visual Studio .NET, and then create a new Managed C++ Application project in Visual C++ .NET.
  3. In Solution Explorer, double-click the source (.cpp) file.
  4. In the source file, replace the default code with the following code:

    SQL Client

    Note You must change the User ID <username> value and the password =<strong password> value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
    #include "stdafx.h"
    #using <mscorlib.dll>
    #include <tchar.h>
    #using <system.dll>
    using namespace System;
    #using <system.data.dll>
    using namespace System::Data;
    using namespace System::Data::SqlClient;
    // This is the entry point for this application.
    int _tmain(void)
    {
        try{
            SqlConnection *myCon = new SqlConnection("Data Source=mySQLServer;User ID=<username>;
                                                      Password=<strong password>;initial catalog=pubs;");
            myCon->Open();
            
            SqlCommand *myCmd = new SqlCommand("TestProcedure3", myCon);
            myCmd->CommandType = CommandType::StoredProcedure;
    
            SqlParameter* retParam=myCmd->Parameters->Add("RetVal",SqlDbType::Int,4);
            retParam->Direction=ParameterDirection::ReturnValue;
    
            myCmd->Parameters->Add("@au_idIN",SqlDbType::VarChar,11);
            myCmd->Parameters->Item[1]->Value=S"213-46-8915";
    
            myCmd->Parameters->Add("@au_fnam",SqlDbType::VarChar,30);
            myCmd->Parameters->Item[2]->Value=S"Marjorie";
    
            int cnt;
            cnt=myCmd->ExecuteNonQuery();
            Console::WriteLine("Number of rows affected:{0}; Return Value:{1}",
                                cnt.ToString(),retParam->Value);
    
            myCon->Close();
    
        }
        catch(SqlException *mySqlEx)
        {
            for(int i=0;i<mySqlEx->Errors->Count;i++)
            {
                Console::WriteLine("Source={0};Message={1};",mySqlEx->Errors->
                        Item[i]->Source,mySqlEx->Errors->Item[i]->Message);
            }
        }
        catch(System::Exception *ex)
        {
            Console::WriteLine(ex->get_Message());
        }
    }
    					

    OLE DB Data Provider

    #include "stdafx.h"
    #using <mscorlib.dll>
    #include <tchar.h>
    #using <system.dll>
    using namespace System;
    #using <system.data.dll>
    using namespace System::Data;
    using namespace System::Data::OleDb;
    
    // This is the entry point for this application.
    int _tmain(void)
    {
        try{
            OleDbConnection *myCon = new OleDbConnection("Provider=SQLOLEDB.1;
                                                          Data Source=mySQLServer;User ID=<username>;
                                                          Password=<strong password>;initial catalog=pubs;");
            myCon->Open();
            
            OleDbCommand *myCmd = new OleDbCommand("{?=call TestProcedure3(?,?)}", myCon);
            myCmd->CommandType = CommandType::Text;
    
            OleDbParameter *retParam=myCmd->Parameters->Add("RetVal",OleDbType::Integer,4);
            retParam->Direction=ParameterDirection::ReturnValue;
    
            myCmd->Parameters->Add("au_idIN",OleDbType::VarChar,11);
            myCmd->Parameters->Item[1]->Value=S"213-46-8915";
    			
            myCmd->Parameters->Add("au_fnam",OleDbType::VarChar,30);
            myCmd->Parameters->Item[2]->Value=S"Marjorie";
    	
            int cnt;
            cnt=myCmd->ExecuteNonQuery();
            Console::WriteLine("Number of rows affected:{0}; Return Value:{1}",
                                cnt.ToString(),retParam->Value);
    
            myCon->Close();
    
        }
        catch(OleDbException *mySqlEx)
        {
            for(int i=0;i<mySqlEx->Errors->Count;i++)
            {
                Console::WriteLine("Source={0};Message={1};",mySqlEx->Errors->
                                    Item[i]->Source,mySqlEx->Errors->Item[i]->Message);
            }
        }
        catch(System::Exception *ex)
        {
            Console::WriteLine(ex->get_Message());
        }
    
    }	 
    					
  5. Modify the connection string for the Connection object to point to the computer that is running SQL Server.
  6. Press CTRL+F5 to compile and run the project. The Output window displays the number of rows that are affected (intRowAffected) and the value of the return parameter.

REFERENCES

For additional information, visit the following MSDN Web sites:
Introduction to the .NET Framework Class Library
http://msdn.microsoft.com/en-us/library/hfa3fa08.aspx

Retrieving Data Using the DataReader
http://msdn.microsoft.com/en-us/library/haa3afyz.aspx

Properties

Article ID: 310071 - Last Review: October 31, 2003 - Revision: 3.2
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: 
kbhowtomaster kbsqlclient kbsystemdata KB310071

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