Click here to Skip to main content
13,591,301 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


18 bookmarked
Posted 17 Aug 2010
Licenced CPOL

Abstract Simplified Stored Procedure Execution using .NET Interfaces

, 18 Aug 2010
Rate this:
Please Sign up or sign in to vote.
Abstract simplified stored procedure execution using .NET interfaces without dealing with ADO.NET classes


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.


Let's say we have a simple stored procedure.

   @ProductId int
    SELECT ProductID, ProductName, CreateDate, ModifyDate
    FROM Product
    WHERE ProductId = @ProductId

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
@ProductId int
, @ProductName nvarchar(255)
--.... procedure code here
--Procedure #2
--This procedure returns product name by its id using output parameter
@ProductId int
, @ProductName nvarchar(255) OUTPUT
   SELECT @ProductName = ProductName from Product WHERE ProductId = @ProductId

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

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

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

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);
     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) 

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(...)) 
     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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Oleg Vorkunov
Software Developer (Senior) VORLAN Group, Inc.
United States United States
writing code for the past 20 years, and now decided to share some thoughts. Smile | :)
Also known as Oleg Vorkunov.

You may also be interested in...


Comments and Discussions

Generaldin't get the point Pin
J walia18-Aug-10 0:40
memberJ walia18-Aug-10 0:40 
GeneralMy vote of 4 Pin
Pranay Rana17-Aug-10 22:06
memberPranay Rana17-Aug-10 22:06 
GeneralRe: My vote of 4 Pin
HoyaSaxa9318-Aug-10 0:26
memberHoyaSaxa9318-Aug-10 0:26 
GeneralRe: My vote of 4 Pin
Pranay Rana18-Aug-10 0:36
memberPranay Rana18-Aug-10 0:36 
GeneralSource code Pin
Member 326587417-Aug-10 21:26
memberMember 326587417-Aug-10 21:26 
GeneralRe: Source code Pin
Alan Kaplan18-Aug-10 3:29
memberAlan Kaplan18-Aug-10 3:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04 | 2.8.180618.1 | Last Updated 18 Aug 2010
Article Copyright 2010 by Oleg Vorkunov
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid