This article contains a generic function that you can use to process multiple recordsets and other messages that are returned from stored procedures or the execution of batch SQL statements.

Description of the Technique

ActiveX Data Objects (ADO) can receive five different types of data from the server:

• Recordset
• Number of records that are modified by an action query (such as INSERT, UPDATE, DELETE, or SELECT INTO)
• Informational message or warning
• Error message
• Stored procedure return values and output parameters

When you read the results of a batch SQL statement, you can use the NextResult method to position the DataReader at the next result in the resultset.

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

• Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
• Microsoft Visual Studio .NET
• Microsoft SQL Server 7.0 or later

This article assumes that you are familiar with the following topics:

• Visual Studio .NET
• ADO.NET fundamentals and syntax

Create Project and Add Code

This sample code uses the Authors table of the SQL Server Pubs sample database.

1. Paste the following statements into the SQL Query Analyzer tool or the ISQL utility:

   CREATE PROC MyProc
   AS
       SELECT * FROM Authors
       SELECT * FROM Authors WHERE State = 'CA'
   GO 
   					

2. Start Visual Studio .NET.
3. Create a new Windows Application project in Visual C# .NET. Form1 is added to the project by default.
4. Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not.
5. Place a Command button on Form1. Change the Name property of the button to btnTest, and change the Text property to Test.
6. Use the using statement on the System, System.Data.OleDb, and System.Data.SqlClient namespaces so that you are not required to qualify declarations in those namespaces later in your code. Add the following code to the General Declarations section of Form1:

   using System;
   using System.Data.OleDb;
   using System.Data.SqlClient;
   					

7. Add the following code to the btnTest_Click event:
   
   Note You must change the User ID <user name> account value to an account that has the appropriate permissions to perform these operations on the database.

       String myConnString  = "User ID=<username>;password=<strong password>;Initial Catalog=pubs;Data Source=myServer";
       SqlConnection myConnection = new SqlConnection(myConnString);
       SqlCommand myCommand = new SqlCommand();
       SqlDataReader myReader ;
   
       myCommand.CommandType = CommandType.StoredProcedure;
       myCommand.Connection = myConnection;
       myCommand.CommandText = "MyProc";
       int RecordCount=0; 
   
       try
       {
           myConnection.Open();
   	myReader = myCommand.ExecuteReader();
   
   	while (myReader.Read())
   	{
    	    //Write logic to process data for the first result.
   	    RecordCount = RecordCount + 1;
   	}
   	MessageBox.Show("Total number of Authors: " + RecordCount.ToString());
   
   	myReader.NextResult();
   	RecordCount = 0;
   
   	while (myReader.Read())
   	{
   	    //Write logic to process data for the second result.
   	    RecordCount = RecordCount + 1;
   	}
   	MessageBox.Show("Authors from California: " + RecordCount.ToString());
       }
       catch(Exception ex) 
       {
          MessageBox.Show(ex.ToString());
       }
       finally
       {
   	myConnection.Close();
       }
   					

8. Modify the Connection string (myConnString) as appropriate for your environment.
9. Save your project. On the Debug menu, click Start to run your project.
10. Click Test. Notice that the messages boxes display the data that the stored procedure returns.

For additional information about calling stored procedures, click the article number below to view the article in the Microsoft Knowledge Base: For additional information about error handling, click the article number below to view the article in the Microsoft Knowledge Base: For additional information about parameters and stored procedures, click the article number below to view the article in the Microsoft Knowledge Base: For more information on ADO.NET objects and syntax, refer to the following Microsoft .NET Framework Software Development Kit (SDK) documentation: