Microsoft KB Archive/308049

Knowledge Base

How To Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic .NET

Article ID: 308049

Article Last Modified on 8/17/2007

---

APPLIES TO

• Microsoft .NET Framework 1.1 Service Pack 1
• Microsoft ADO.NET 1.1
• Microsoft Visual Basic .NET 2002 Standard Edition
• Microsoft Visual Basic .NET 2003 Standard Edition

---

This article was previously published under Q308049

For a Microsoft Visual C++ .NET version of this article, see 310071.
For a Microsoft Visual C# .NET version of this article, see 310070.
For a Microsoft Visual J# .NET version of this article, see 320627.

For a Microsoft Visual Basic 6.0 version of this article, see 194792.

This article refers to the following Microsoft .NET Framework Class Library namespaces:

• System.Data
• System.Data.SqlClient
• System.Data.OleDb

IN THIS TASK

SUMMARY

• Use DataReader to Return Rows and Parameters
• Use the ExecuteScalar Method of the Command Object
• Use the ExecuteNonQuery Method of the Command Object

REFERENCES

SUMMARY

There are several ways to use ADO.NET to call a stored procedure and to get back return values and return parameters, including:

• Use a DataSet object to gather the returned rows and to work with these rows in addition to the return values and the return parameters.
• Use a DataReader object to gather the returned rows, to move through these rows, and to gather return values and return parameters.
• Use the ExecuteScalar method to return the value from the first column of the results' first row with the return values and the return parameters. This is most useful with aggregate functions.
• Use the ExecuteNonQuery method to return only the return 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. 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. When you use the OleDbCommand object, you must add the parameters in the correct order, and you cannot use the parameters by name.

back to the top

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. Create the following stored procedure on the server 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. Use the Imports statement on the System and the System.Data namespaces so that you do not have to qualify declarations in those namespaces later in your code. You must use the Imports statement prior to any other declarations. Make sure to copy only the code for the provider that you have chosen.SQL Client

   Imports System.Data.SqlClient                 

   OLE DB Data Provider

   Imports System.Data.OleDb                    

3. Add the following code to the Form_Load event:SQL Client

   Dim PubsConn As SqlConnection = New SqlConnection & _
       ("Data Source=server;integrated security=sspi;" & _
       "initial Catalog=pubs;")
   
   Dim testCMD As SqlCommand = New SqlCommand & _
       ("TestProcedure", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetValue As SqlParameter = testCMD.Parameters.Add & _
       ("RetValue", SqlDbType.Int)
   RetValue.Direction = ParameterDirection.ReturnValue
   Dim auIDIN As SqlParameter = testCMD.Parameters.Add & _
       ("@au_idIN", SqlDbType.VarChar, 11)
   auIDIN.Direction = ParameterDirection.Input
   Dim NumTitles As SqlParameter = testCMD.Parameters.Add & _
       ("@numtitlesout", SqlDbType.Int)
   NumTitles.Direction = ParameterDirection.Output
   
   auIDIN.Value = "213-46-8915"
   PubsConn.Open()
   
   Dim myReader As SqlDataReader = testCMD.ExecuteReader()
   Console.WriteLine("Book Titles for this Author:")
   Do While myReader.Read
       Console.WriteLine("{0}", myReader.GetString(2))
   Loop
   myReader.Close()
           
   Console.WriteLine("Return Value: " & (RetValue.Value))
   Console.WriteLine("Number of Records: " & (NumTitles.Value))
                       

   OLE DB Data Provider

   Dim PubsConn As OleDbConnection = New OleDbConnection & _
       ("Provider=sqloledb;Data Source=server;" & _
       "integrated security=sspi;initial Catalog=pubs;")
   
   Dim testCMD As OleDbCommand = New OleDbCommand & _
       ("TestProcedure", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetValue As OleDbParameter = testCMD.Parameters.Add & _
       ("RetValue", OleDbType.Integer)
   RetValue.Direction = ParameterDirection.ReturnValue
   Dim auIDIN As OleDbParameter = testCMD.Parameters.Add & _
       ("@au_idIN", OleDbType.VarChar, 11)
   auIDIN.Direction = ParameterDirection.Input
   Dim NumTitles As OleDbParameter = testCMD.Parameters.Add & _
       ("@numtitlesout", OleDbType.Integer)
   NumTitles.Direction = ParameterDirection.Output
   
   auIDIN.Value = "213-46-8915"
   PubsConn.Open()
   
   Dim myReader As OleDbDataReader = testCMD.ExecuteReader()
   Console.WriteLine("Book Titles for this Author:")
   Do While myReader.Read
       Console.WriteLine("{0}", myReader.GetString(2))
   Loop
   myReader.Close()
   
   Console.WriteLine("Return Value: " & (RetValue.Value))
   Console.WriteLine("Number of Records: " & (NumTitles.Value))
                       

4. Modify the connection string for the Connection object to point to the server that is running SQL Server.
5. Run the code. Notice that the DataReader retrieves the records and then returns the parameter values. You can use the Read method of the DataReader object to move through the returned records.
   
   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.

back to the top

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 server that is running SQL Server:

   Create Procedure TestProcedure2
    (
      @au_idIN varchar (11)
     )
   As
   /* set nocount on */ 
   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. Use the Imports statement on the System and the System.Data namespaces so that you do not have to qualify declarations in those namespaces later in your code. You must use the Imports statement prior to any other declarations. Make sure that you copy only the code for the provider that you have chosen.SQL Client

   Imports System.Data.SqlClient                   

   OLE DB Data Provider

   Imports System.Data.OleDb                    

3. Add the following code to the Form_Load event:SQL Client

   Dim PubsConn As SqlConnection = New SqlConnection & _
       ("Data Source=server;integrated security=sspi;" & _
       "initial Catalog=pubs;")
   
   Dim testCMD As SqlCommand = New SqlCommand & _
       ("TestProcedure2", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetValue As SqlParameter = testCMD.Parameters.Add & _
       ("RetValue", SqlDbType.Int)
   RetValue.Direction = ParameterDirection.ReturnValue
   Dim auIDIN As SqlParameter = testCMD.Parameters.Add & _
       ("@au_idIN", SqlDbType.VarChar, 11)
   auIDIN.Direction = ParameterDirection.Input
   
   auIDIN.Value = "213-46-8915"
   PubsConn.Open()
   
   Dim intCount As Integer = testCMD.ExecuteScalar
   Console.WriteLine(intCount)
   Console.WriteLine("Return Value: " & (RetValue.Value))
                       

   OLE DB Data Provider

   Dim PubsConn As OleDbConnection = New OleDbConnection & _
       ("Provider=SQLOLEDB;Data Source=server;" & _
       "integrated Security=sspi;initial catalog=pubs;")
   
   Dim testCMD As OleDbCommand = New OleDbCommand & _
       ("TestProcedure2", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetVal As OleDbParameter = testCMD.Parameters.Add & _
       ("RetVal", OleDbType.Integer)
   RetVal.Direction = ParameterDirection.ReturnValue
   Dim IdIn As OleDbParameter = testCMD.Parameters.Add & _
       ("@au_idIN", OleDbType.VarChar, 11)
   IdIn.Direction = ParameterDirection.Input
   
   IdIn.Value = "213-46-8915"
           
   PubsConn.Open()
   
   Dim intCount As Integer = testCMD.ExecuteScalar
   
   Console.WriteLine("Number of Rows: " & intCount)
   Console.WriteLine(RetVal.Value)
                       

4. Modify the connection string for the Connection object to point to the server that is running SQL Server.
5. Run the code. Notice that the ExecuteScalar method of the Command object returns the parameters. ExecuteScalar also returns the value of column 1, row 1 of the returned rowset. Thus, the value of intCount is the result of the count function from the stored procedure.

back to the top

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 server that is running SQL Server:

   Create Procedure TestProcedure3
     (
       @au_idIN varchar (11),
       @au_fnam varchar (30)
     )
   
   As
   /* set nocount on */ 
   Update authors set au_fname = @au_fnam
   where au_id = @au_idin  
   return (5)
                       

2. Use the Imports statement on the System and the System.Data namespaces so that you do not have to qualify declarations in those namespaces later in your code. You must use the Imports statement prior to any other declarations. Make sure that you copy only the code for the provider that you have chosen.SQL Client

   Imports System.Data.SqlClient                   

   OLE DB Data Provider

   Imports System.Data.OleDb                    

3. Add the following code to the Form_Load event:SQL Client

   Dim PubsConn As SqlConnection = New SqlConnection & _
       ("Data Source=server;integrated security=sspi;" & _
       "initial Catalog=pubs;")
   
   Dim testCMD As SqlCommand = New SqlCommand & _
       ("TestProcedure3", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetValue As SqlParameter = testCMD.Parameters.Add & _
       ("RetValue", SqlDbType.Int)
   RetValue.Direction = ParameterDirection.ReturnValue
   Dim auIDIN As SqlParameter = testCMD.Parameters.Add & _
       ("@au_idIN", SqlDbType.VarChar, 11)
   auIDIN.Direction = ParameterDirection.Input
   Dim auFname As SqlParameter = testCMD.Parameters.Add & _
       ("@au_fnam", SqlDbType.VarChar, 30)
   auFname.Direction = ParameterDirection.Input
   
   auIDIN.Value = "213-46-8915"
   auFname.Value = "Marjorie"
   PubsConn.Open()
   
   Dim rvRows As Integer = testCMD.ExecuteNonQuery
   Console.WriteLine(rvRows)
   Console.WriteLine(RetValue.Value)
                       

   OLE DB Data Provider

   Dim PubsConn As OleDbConnection = New OleDbConnection & _
       ("Provider=SQLOLEDB;Data Source=server;" & _
       "integrated Security=sspi;initial catalog=pubs;")
   
   Dim testCMD As OleDbCommand = New OleDbCommand & _
       ("TestProcedure3", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetVal As OleDbParameter = testCMD.Parameters.Add & _
       ("RetVal", OleDbType.Integer)
   RetVal.Direction = ParameterDirection.ReturnValue
   Dim IdIn As OleDbParameter = testCMD.Parameters.Add & _
       ("@au_idIN", OleDbType.VarChar, 11)
   IdIn.Direction = ParameterDirection.Input
   Dim FnameIn As OleDbParameter = testCMD.Parameters.Add & _
       ("@au_fname", OleDbType.VarChar, 30)
   FnameIn.Direction = ParameterDirection.Input
   
   IdIn.Value = "213-46-8915"
   FnameIn.Value = "Marjorie"
   
   PubsConn.Open()
   
   Dim intRowAffected As Integer = testCMD.ExecuteNonQuery
   
   Console.WriteLine("Number of Rows affected: " & intRowAffected)
   Console.WriteLine(RetVal.Value)
                       

4. Modify the connection string for the Connection object to point to the server that is running SQL Server.
5. Run the code. The Output window displays the number of affected rows (intRowAffect) and the value of the return parameter.

back to the top

REFERENCES

For additional information, visit the following MSDN Web sites:

Introduction to the .NET Framework Class Library
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconthenetframeworkclasslibrary.asp

Retrieving Data Using the DataReader
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp

For more general information about ADO.NET or Visual Basic .NET, visit the following MSDN newsgroups:

microsoft.public.dotnet.framework.adonet

microsoft.public.dotnet.languages.vb

For more information, see the following book:

Connell, John. Coding Techniques for Microsoft Visual Basic .NET. Microsoft Press, 2001.

For more information, see the following Microsoft Training & Certification course:

2389 Programming with ADO.NET
http://www.microsoft.com/learning/syllabi/en-us/2389Bfinal.mspx

back to the top

Keywords: kbhowtomaster kbsqlclient kbstoredproc kbsystemdata KB308049

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.