The release number of this article has been CHS311274
For Microsoft Visual Basic .NET versions of this article, see
309490.
This article references the following Microsoft .NET Framework Class Bank Name Space:
System.data.oledb system.data.sqlclient
This task content
summary
Technical Description Requirements Creating Projects and Add Code Reference
This article describes a general function that can be used to handle multiple records and other messages that are returned by stored procedures or returning when performing batch SQL statements.
Back to top
Technical Description ActiveX Data Object (ADO) can receive 5 different types of data from the server:
Recording a set of recorded number information messages modified by operation queries (such as INSERT, UPDATE, DELETE or SELECT INTO) or WARNING error message stored procedure return value and output parameters can be used when reading the result of the batch SQL statement
NextResult method
DataReader is placed in the next result of the result set.
Back to top
Require the following list lists the recommended hardware, software, network structure, and service pack required:
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 Foundation and Syntax
Back to top
Creating a project and adding code This sample code uses the Authors table of the SQL Server Pubs sample database.
Paste the following statements to the SQL Query Analyzer tool or ISQL utility: CREATE PROC MyPROC
AS
Select * from authors
Select * from authors where stat = 'ca'
Go starts Visual Studio .NET. Create a Windows application project in Visual C # .NET. The Form1 is added to the project by default. Make sure your project contains a reference to System.Data namespace, if not included, add a reference to this namespace. Place a command button on Form1. Change the button's Name property to btnTest to change the Text property to Test. Using the Imports statement for system, system.data.oledb and system.data.sqlclient namespace, this will not be required to qualify in the code in the code. Add the following code to the "General Declarations" section of the Form1: use system;
Using system.data.oledb;
Using system.data.sqlclclient; Add the following code to the BTNTest_Click event: string myconnstring = "User ID = sa; password = sa; initial catalog = public = sa; initial catalog = public = sa; 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.
Recordcount = RecordCount 1;
}
Messagebox.show ("Authors from California:" RecordCount.toString ());
}
Catch (Exception EX)
{
Messagebox.show (ex.totring ());
}
Finally
{
MyConnection.Close ();
} Modify the connection string (MyConnString) accordingly according to your environment. Save the project. On the Debug menu, click Start to run your project. Click Test. You will notice that the data returned by the stored procedure is displayed in the message box.
Back to top
Refer to additional information about calling the stored procedure, click the article number below to view the article in the Microsoft Knowledge Base:
306574 How to: Adjust additional information about error handling in ASP.NET, click the article number below to view the article in the Microsoft Knowledge Base:
308650 How to: Use ADO.NET in Visual C # .NET to get the base provider error For additional information about parameters and stored procedures, click the article number below to view the article in the Microsoft Knowledge Base:
308621 PRB: None of the output parameters when running the ADO.NET command in Visual C # .NET For more information on ADO.NET objects and syntax, see the following Microsoft .NET Framework Software Development Kit (SDK) document:
Use ADO.NET access data http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconaccessingDatawithadonet.asp
Back to top
The information in this article applies to:
Microsoft ADO.NET (provided with .NET Frame) Microsoft Visual C # .NET (2002)
Recent Updated: 2002-6-18 (1.0) Keyword Kbdsupport KBGRPDSMDAC KBGRPDSVBDB KBHOWTO KBHOWTOMASTER KBSQLCLIENT KBSYSTEMDATA KB311274
