Calling Stored Procedures from ADO.NET

by John Paul Cook

 

The Microsoft .NET Framework offers improved performance in accessing stored procedures through ADO.NET when compared to ADO. The ADO.NET classes have multiple overloaded methods, so it’s important to understand the syntax sufficiently well to get the most from your code. Although the focus of the article is on SQL Server stored procedures, the techniques shown will also work with Oracle and other OLE DB compliant databases.

Creating a Test Project

Creating a Test Project

Start a new project in Visual Studio using your preferred language and use the Windows Application template. You can use the SQL Query Analyzer to create a new stored procedure. Or you might prefer to create a new stored procedure by using the convenience of the Server Explorer as shown below.

cook6-1.gif

 

Notice that although the steps shown above are similar to the steps for creating a function as described in an earlier article, "T-SQL Debugging Using Visual Studio .NET” there is a difference. The function in the earlier article was created by using a Data Connection. Although a Data Connection could be used to facilitate the procedure, it’s not necessary to have a Data Connection for this project. After selecting New Stored Procedure, a stored procedure creation template appears. Replace the entire contents of the template code with this:

 

CREATE PROCEDURE dbo.UpdatePrices
(
  @PriceMultiplier decimal,
  @NbrRows int output
)
AS
   IF @PriceMultiplier IS NULL
      RETURN -1 -- invalid input, return a bad status
   ELSE
      BEGIN
         UPDATE Products
         SET UnitPrice = UnitPrice * @PriceMultiplier

 

         SET @NbrRows = @@ROWCOUNT
         RETURN 0 -- return          a successful completion status
       END

 

Clicking the Save icon (floppy disk) on the Visual Studio .NET toolbar creates the procedure in the database. Because an example of an output parameter is needed, the number of rows affected is returned as an output parameter. It is acknowledged that some people prefer that the number of rows affected should be obtained as the return value of a procedure, not as an output parameter. The stored procedure code is available here in updateprices.sql. The finished project looks like this:

cook6-2.gif

Specifying the Namespaces

ADO.NET provides an object oriented paradigm for database access. In the code, the objects are instantiated from class definitions. The classes are grouped into containers called namespaces. It’s a convenience, not a requirement to create namespace references. Here is the code you would have to write if you did not have a reference to the System.Data.SqlClient namespace:

 

System.Data.SqlClient.SqlConnection conn = new
System.Data.SqlClient.SqlConnection(); // fully qualified names

 

Here is the code after creating a reference to the namespace:

SqlConnection conn = new SqlConnection(); // shorter, more convenient

 

You will need to specify three namespaces for accessing SQL Server from your .NET code. The syntax for specifying a namespace is language specific and appears at the top of the file containing the code.

 

using System.Data;            // C#, ADO.NET class definitions
using System.Data.SqlClient;  // C#, for SQL 7 and later clients
using System.Data.SqlDbTypes; // C#, SQL data types for parameters

Imports System.Data            ' VB.NET, ADO.NET class definitions
Imports System.Data.SqlClient  ' VB.NET, for SQL 7 and later clients
Imports System.Data.SqlDbTypes ' VB.NET, SQL data types for parameters

 

The SQLClient namespace contains the classes that comprise the SQL Server Managed Provider. The SQL Server Managed Provider classes are optimized for SQL 7 and later. They provide a more efficient interface to SQL Server than OLE dB Older versions of SQL Server must use the OLE dB namespace. The OLE dB namespace contains the classes for generic database access. The OLE dB Provider works with Access, SQL Server (including SQL 7 and later) and Oracle.

 

Using System.Data;       // C#, ADO.NET class definitions
using System.Data.OleDb; // C#, for OLE dB compliant data sources

Imports System.Data       ' VB.NET, ADO.NET class definitions
Imports System.Data.OleDb ' VB.NET, for OLE dB compliant data sources

 

There are Managed Providers developed specifically for Oracle. Both Oracle and Microsoft have written .NET Managed Providers optimized for faster data access to Oracle databases than the generic OLE dB Managed Provider. You will have to download the Oracle provider from Microsoft or Oracle and then install it on the client that will use it. Additionally, you will have to add a reference to the provider to your project. Go to the project’s Solution Explorer, right-click on the project name and select Add Reference as shown:

cook6-3.gif

The Add Reference dialog box appears. Select either the Oracle.DataAccess.dll or the System.Data.OracleClient.dll. This causes the Select button to become enabled. Click the Select button.

cook6-4.gif

The selected dll appears in the list of Selected Components. Click OK to complete the process of adding the Oracle provider’s reference to the project.

 

Using System.Data;              // C#, ADO.NET class definitions
using Oracle.DataAccess.Client; // C#, Oracle's Oracle provider
using Oracle.DataAccess.Types;  // C#, Oracle data types for parameters

 

Imports System.Data              ' VB.NET, ADO.NET class definitions
Imports Oracle.DataAccess.Client ' VB.NET, Oracle's Oracle provider
Imports Oracle.DataAccess.Types  ' VB.NET, Oracle data types for parameters

 

using System.Data;              // C#, ADO.NET class definitions
using System.Data.OracleClient; // C#, Microsoft's Oracle provider

 

Imports System.Data              ' VB.NET, ADO.NET class definitions
Imports System.Data.OracleClient ' VB.NET, Microsoft's Oracle provider

 

Should you use the Oracle provider from Microsoft or Oracle? It would take another article to answer that question. The short answer is that it depends on your needs, so check the documentation for both, and by all means, test both.

 

From a coding perspective, these different data client namespaces provide almost identical APIs for accessing databases using the following naming

convention:

 

Provider-nameClass-name

 

For the Connection class, the following classes are provided by the various data client namespaces:

 

   SqlConnection in System.Data.SqlClient
    OleDbConnection in System.Data.OleDb
    OracleConnection in System.Data.OracleClient from both Microsoft and Oracle

 

For the Command class, the following classes are provided by the various data client namespaces:

 

    SqlCommand in System.Data.SqlClient
    OleDbCommand in System.Data.OleDb
    OracleCommand in System.Data.OracleClient from both Microsoft and Oracle

 

The example code was developed and tested using a SQL 2000 database, so the System.Data.SqlClient namespace was tested. By changing the class names from SQL… to OleDb… or Oracle…, you should be able to work with stored procedures on other database products without having to make major changes to the code.

The Connection Class

A SqlConnection object has 13 methods, 10 properties, and 3 events. You need to use at least these:

 

SqlConnection conn =          new SqlConnection();
conn.ConnectionString = "server=(local);Integrated Security=SSPI;"
                        + "database=Northwind";
// TODO instantiate and populate SqlCommand object
// TODO instantiate and populate SqlParameter objects
conn.Open();
// TODO execute stored procedure
// TODO get return value and result from stored procedure
conn.Close(); // alternatively, conn.Dispose

 

The number of lines of code can be reduced by combining certain steps. For now, we want to clearly delineate each and every step. The complete downloadable code file shows both the step-by-step code as well as the abbreviated code.

The Command Class

A SqlCommand object has 16 methods, 10 properties, and 1 event.

 

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "UpdatePrices";
// TODO instantiate and populate SqlParameter objects
cmd.ExecuteNonQuery(); // Results are returned as parameters only.

 

There are four different execute methods:

 

ExecuteReader use when rows are being returned (e.g., from a SELECT)
ExecuteNonQuery use for INSERT, UPDATE, DELETE, SET
ExecuteScalar use when a single value such as count(*) is being returned
ExecuteXmlReader XmlReader instance is built from the CommandText

 

The UpdatePrices stored procedure returns data as parameters, not rows, so ExecuteNonQuery is the appropriate method to use. To retrieve data from a SELECT statement, use either the ExecuteReader or the ExecuteScalar method. The ExecuteScalar method is for a SELECT statement that only returns a single value (e.g., SELECT COUNT(*) …).

The Parameter Class

When working with parameterized stored procedures, a separate Parameter object  must be instantiated for each parameter of the stored procedure. Each Parameter  object must be added to the Command object’s Parameters collection. If you want  to retrieve a stored procedure’s return value, it must be added to the  Parameters collection first. Notice that the SqlDbType enumeration is used.

cook6-5.gif

Enumerations are named groups of constants. They are exposed through Intellisense. The SqlDbType enumeration provides constants representing each of the SQL Server datatypes. There is a DbType enumeration for other databases.

 

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "UpdatePrices";

SqlParameter paramReturnValue = new SqlParameter();
paramReturnValue.ParameterName = "@return_value";
paramReturnValue.SqlDbType = SqlDbType.Int;
paramReturnValue.Direction = ParameterDirection.ReturnValue;

SqlParameter paramPAF = new SqlParameter();
paramPAF.ParameterName = "@PriceMultiplier";
paramPAF.SqlDbType = SqlDbType.Decimal;
paramPAF.Direction = ParameterDirection.Input;
paramPAF.Value = textBoxPAF.Text;

SqlParameter paramNbrRows = new SqlParameter();
paramNbrRows.ParameterName = "@NbrRows";
paramNbrRows.SqlDbType = SqlDbType.Int;
paramNbrRows.Direction = ParameterDirection.Output;

cmd.Parameters.Add(paramReturnValue); // must be added first, parameter 0
cmd.Parameters.Add(paramPAF); // parameter 1
cmd.Parameters.Add(paramNbrRows); // parameter

 

This code can be collapsed into fewer lines by taking advantage of the overloaded constructors of the classes being instantiated.

Obtaining Returned Data From the Parameters Collection

After the stored procedure has been executed by calling the appropriate execute method, the Parameters collection is used to retrieve data.

int returnValue = (int) cmd.Parameters["@return_value"].Value;

 

labelNbrPrices.Text = cmd.Parameters["@NbrRows"].Value.ToString()

 

                      + " prices updated";

 

Alternatively, ordinals could be used to retrieve values from the Parameters collection.

 

int returnValue = (int) cmd.Parameters[0].Value;

 

labelNbrPrices.Text = cmd.Parameters[2].Value.ToString()

 

                      + " prices updated";

The Transaction Class

When implicit transactions are not suitable because programmatic control of commit and rollback is needed, the Transaction class can be used. Only three additional lines of code are needed between the Open and Close methods of the Connection object to enable transactional support.

 

conn.Open();

 

// Lines 1 and 2 of 3 new lines added for explicit transaction support.
SqlTransaction trans = conn.BeginTransaction();    // connection must be open
cmd.Transaction = trans;

 

cmd.ExecuteNonQuery();

 

int returnValue = (int) cmd.Parameters["@return_value"].Value;
labelNbrPrices.Text = cmd.Parameters["@NbrRows"].Value.ToString()
                      + " prices updated";

 

// Line 3 of 3 new lines added for explicit transaction support.
trans.Commit();  // or trans.Rollback(), must be before connection is closed

 

conn.Close();   // alternatively, conn.Dispose

 

To better understand how things work, download the code and experiment with it. Comment out the three lines of explicit transaction code and run the application. Uncomment the code and sometimes use the Commit method and other times use the Rollback method.

Working with Select Statements in Stored Procedures

Since working with parameters has been covered, it is time to switch to a stored procedure that uses a select statement to return data as rows, not parameters. The Ten Most Expensive Products stored procedure in the Northwind database meets these requirements. The SqlCommand object is replaced by a SqlDataAdapter object (there are also OleDbDataAdapter and OracleDataAdapter classes).

 

Because our stored procedure’s name contains embedded blanks, brackets are required as delimiters.

 

SqlConnection conn = new SqlConnection(
               "server=(local);Integrated Security=SSPI;database=Northwind");

 

SqlDataAdapter da = new SqlDataAdapter("[Ten Most Expensive Products]",conn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;

 

// Pass the name of the DataSet through the overloaded constructor of
// the DataSet class. When the DataSet is represented as XML, this name
// is used as the name of the XML document element.
DataSet dsNorthwind = new DataSet("Northwind");

 

conn.Open();
da.Fill(dsNorthwind);
// You could call a second stored procedure by using
// da.SelectCommand.CommandText followed by da.Fill
conn.Close();  // alternatively, conn.Dispose

 

grdNorthwind.DataSource = dsNorthwind.Tables[0];

 

It is the Fill method of the DataAdapter object that executes the stored procedure.

Debugging a Stored Procedure from Code

By default, Visual Studio .NET does not step into stored procedures when stepping through application source code. It is necessary to use the Server Explorer to create a breakpoint in the stored procedure before running the application in debug mode. Additionally, the project properties must be set to allow SQL debugging. For more information, see “T-SQL Debugging Using Visual Studio .NET.”

C# Code

The C# code is available here in runprocs.cs. To use this code, create a Windows Application project. Replace all of the existing Form1.cs code with the downloaded code.

VB.NET Code

The VB.NET code is available here in RunProcs.vb. To use this code, create a Windows Application project. Replace all of the existing Form1.vb code with the downloaded code.
Conclusion

 

As you have seen, the .NET Framework classes have many different options for processing stored procedures. The tasks discussed in this article could have been accomplished differently. Code enhancements such as error handling are left as an exercise for the reader.

 

John Paul Cook is a database and .NET consultant. He also teaches .NET, XML, SQL Server, and Oracle courses at Southern Methodist University’s location in Houston, Texas.