Abstract Simplified Stored Procedure Execution using .NET Interfaces

• Download source - 133.74 KB

Introduction

This article will show how we can access database without dealing with any ADO.NET objects, and just working with strongly typed interfaces. Let's say we have to call Stored Procedure and using just plain ADO.NET we have to work with Connection, Command, and Parameter objects writing tons of boring code. What if calling stored procedure would be just a simple method call. And yes, there are quite a few frameworks, which simplify this task, and I am not claiming to overpower these frameworks. For example, LINQ to SQL where you can drop Stored Procedure in the designer, and method call will be generated for you. But how many developers have switched to use latest versions of .NET Framework? This is why I made this code fully compatible with .NET 2.0 and up.

But I think what I am about to show you goes a little bit deeper. Most of the coding you would have to do is to create simple interfaces. Framework will build classes to implement these interfaces on the fly (cached) and will perform the hard work of working with ADO.NET for you.

Background

Let's say we have a simple stored procedure.

CREATE PROCEDURE ReadData
   @ProductId int
AS
BEGIN
    SELECT ProductID, ProductName, CreateDate, ModifyDate
    FROM Product
    WHERE ProductId = @ProductId
END

In order to execute this procedure using ADO.NET objects, we would have to create command object, add parameters to this object, get reader or DataSet and only after that we can, let's say, bind it to a grid. What if we could just have a method to call something like that:

IEnumerable<IProduct> products = ReadData(55);

Where IProduct interface has just a few properties defined like this:

public interface IProduct
{
     int ProductId {get;}
     string ProductName {get;set;}
     DateTime CreateDate {get;}
     ModifyDate {get;set;}
}

Then you can bind result to, let's say, a DataGrid:

dataGrid.DataSource = new List<IProduct>(ReadData(55));

I think it looks pretty simple.

Using the Code

This simple framework will allow you to execute a stored procedure by calling a method. You will be able to get results as enumerable collection, pass parameters to a stored procedure just as you would pass parameters to a method call, get output parameters from stored procedure, as well as get return value from stored procedure. There are many variations of using stored procedure, and I will try to outline just a few, while code for this article has more examples in Test project.

Let's start with list of Stored Procedures in our database.

--Procedure #1
--This procedure will just inserting data into some table
CREATE PROCEDURE InsertData
@ProductId int
, @ProductName nvarchar(255)
AS
--.... procedure code here
--
--Procedure #2
--This procedure returns product name by its id using output parameter
CREATE PROCEDURE GetProductName1
@ProductId int
, @ProductName nvarchar(255) OUTPUT
AS
   SELECT @ProductName = ProductName from Product WHERE ProductId = @ProductId

--
--Procedure #3
--This procedure returns product name as scalar value
CREATE PROCEDURE GetProductName2
@ProductId int
AS
   SELECT ProductName FROM Product WHERE ProductId = @ProductId

--
--Procedure #4
--This procedure returns list of products
CREATE PROCEDURE GetProducts
AS
   SELECT ProductId, ProductName FROM Product        

First, what we have to do is to define an interface, which defines methods with parameters and return types.

[ConnectionName(Name="TestConnection")]
public interface IMyProcs 
{ 
   void InsertData(int productId, string productName);
   void GetProductName1(int productId, out string productName);
   string GetProductName2(int productId);
   IEnumerable<IProduct> GetProducts();
}

For simplicity, I named method and parameters the same as Stored Procedure names and Stored Procedure parameters. As you see, there is another interface IProduct. Here it is:

public interface IProduct 
{ 
    int ProductId {get;}
    string ProductName {get;set;}
}

Once you have interfaces, you do not have to worry about implementation of these interfaces. Framework will do it for you. Please note that in IProduct interface, I marked one property just with the getter. Framework will be able to set the value of this property anyway, but you have the ability to lock access to the property setter in the code, which is using this interface.

Now let's see how we can execute these Stored Procedures. Execution is done using DatabaseContext<T> class where T is one of the interfaces, which has methods. In our case, it is IMyProcs interface. Here is a code snippet for execution:

string productName = null;

using(DatabaseContext<IMyProcs> context = new DatabaseContext<IMyProcs>()) 
{ 
     context.Execute.InsertData(1234, "Some product #1 name here"); 
     context.Execute.GetProductName1(1234, out productName);
     Console.WriteLine(productName);
 
     context.Execute.InsertData(2345, "Some product #2 name here");
     productName = context.Execute.GetProductName2(2);
     IEnumerable<IProduct> products = context.Execute.GetProducts();
     foreach(IProduct product in products) 
     { 
         Console.WriteLine(product.ProductName); 
     } 
}

Here I have executed 4 Stored Procedures under a single connection, which is defined by ConnectionName attribute. ConnectionName attribute for IMyProcs interface indicates to DatabaseContext to load connection string from configuration file using the name specified in attribute. This attribute is optional and you can pass connection name to DatabaseContext constructor like this:

new DatabaseContext("TestConnection")

or use existing connection, which is opened like this:

using(SqlConnection conn = new SqlConnection(...)) 
{
     conn.Open();
     using(DatabaseContext<IMyProcs> context = new DatabaseContext<IMyProcs>(conn))
     { ....

As you saw from this simple example, you can execute stored procedure, get output parameters from it, as well as scalar values, and result sets.

If your stored procedure returns multiple result sets, you can use class ResultSet<T>.

using(DatabaseContext<IMyProcs> context = new DatabaseContext<IMyProcs>())
{
    ResultSet<IMyFirstResult> results = context.Execute.GetMoreResults();
    foreach(IMyFirstResult result in results)
    {
        ....
    }
    ResultSet<MyNextResult> nextResults = results.Next<MyNextResult>();
    foreach(MyNextResult result in nextResults)
    {
      ....
    }

As you noticed, I used class and not interface: MyNextResult. You can use not only interfaces, but classes as well for IEnumerable<T> and ResultSet<T> generic types. The only requirement for a class would be: have default constructor, and all properties should have setters.

You can find more examples in nUnit project, which is part of the ZIP file. Any comments are welcome.