DBA > Articles

Calling Stored Procedures from ADO.NET

By:
To read more DBA articles, visit http://dba.fyicenter.com/article/

Introduction
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

Full article...


Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/