Calling Stored Procedures from ADO.NET
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
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.
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:
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:
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.
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.
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.
Contributors : John Paul Cook
Last modified 2005-04-12 06:21 AM
How to retrieve XML from Oracle stored procedure in C#?
What is the equivalent solution for Oracle? In ODP.NET, OracleCommand does have ExecuteXMLReader method. Can you please explain how to write a simple stored procedure (that returns XML) and get XML in C#?
Thanks,
Amit
Replies to this comment