How to use the ATL OLE DB Consumer Wizard to Consume SQL Server Stored Procedures

This article explains how to use a MSSQL server stored procedure in your MFC application developed with Visual Studio 2012.

Start by creating a very simple table in MSSQL server.  Give it two columns - ID, and a nvarchar(50) called PersonName.  It is important that you start with a simple table, otherwise you might have problems making it work. That problem might be related to various column data type idiosyncrasies. So learn the basics first.

Make the ID column a primary key int that autoincrements.

Give the table a name - Persons.

Enter a few records.

Now you have a simple, two column table with a few records.




Now we'll create the stored procedure.

We want a stored procedure that inserts a record and returns that record's ID value.

Now to consume it in our MFC application.

In Solution Explorer, right-click on your project and click Add->Class.

In the Add Class Dialog box, select Visual C++ -->ATL-->ATL OLEDB Consumer.

The OLEDB Consumer Wizard will appear.

 Select Data Source.

Use Microsoft OLE DB Provider for SQL Server.

 

Select the server name.  I use localhost.  Specify user name, password, and select the database.

Test the connection.

Then click OK.

Open up Stored Procedures, and select Insert Person.  That is the stored procedure we created in an earlier step.

That will bring you back to the OLE DB Consumer Wizard Screen.

Notice that the consumer class and header file have been named after the stored procedure.

In order to build the project, you must comment out the #error directive in the newly created consumer class header file.

You must manually add the password to the connection string.  "Password=whatever;"


Include the consumer class header file in your Dialog box header file.

Declare an instance of the consumer class.

Here is the code that implements the consumer class to insert a record and obtain the new record's ID.

Let me know if you have any questions or other problems you want to solve.