SixPack Library -- StoredProcedure

Introduction

This article is part of an on-going series of tutorials explaining how to use the SixPack library. In this example, we show how to use the StoredProcedure class to invoke Stored Procedures in a simple, yet extremely powerful way.

Background

About the SixPack library

The SixPack rapid development library is a collection of classes for rapid development on the .NET/Mono platform. It is released under the Limited General Public License 2.1 (LGPL), and is available through Google code.

Using the code

Why did we develop Stored Procedures support?

Stored Procedure invocation is a very common activity in most enterprise or web development projects. Microsoft's ADO.NET technology makes it very simple to do, so why go through the hassle of using a library to do it?

Here are some reasons of why we implemented this feature of the library:

• Even though it's powerful, ADO.NET lacks a few features that you will likely need to develop when supporting large sites (for example, handling deadlocks).
• As a best practice, a lot of people do not want developers to use all features of ADO.NET (typically, the DataReaders need to be handled very carefully).
• We wanted the Stored Procedure invocation code to be database independent.
• We have a large number of Stored Procedures to support and we wanted the developer to be able to generate the invocation code, instead of writing it.
• We wanted to be able to easily trace Stored Procedure performance during development while not having a performance bottleneck at release.

Basic usage

We "stole" the StoredProcedure class basic signature from an early version of SubSonic. This makes it easier for developers used to that library to adapt their code to our own.

DataSet ds = new StoredProcedure("myStoredProcName")
             .AddParameter("@first", 42, DbType.Int, 0)
             .AddParameter("@second", "hello", DbType.String, 50)
             .GetDataSet();

This will invoke a Stored Procedure called myStoredProcName with two parameters and return the result as a DataSet.

Please take note of a couple of things.

Firstly, you don't have to pass a connection or connection name. The library will automatically use the first connection string available in your Web.config. In 99% of cases, you only have one connection string per project, so this is perfectly adequate.

Secondly, the parameter type that you pass is not database specific. The library reads the SQL client from your connection string and automatically uses the correct ADO.NET layer. This is important because the code you are writing is database-independent. If you need to support different databases, this is great, because as long as they have Stored Procedures with the same name and signature, everything will work out-of-the-box and without recompiling.

The connection string resolution and SQL client creation will be performed only at start. There is no performance penalty associated.

Working with a code generator

The invocation code can be automatically generated. Given the list of Stored Procedures and their parameters, it is relatively simple to develop a general tool, or use a code generator like CodeSmith, to generate code like this:

using System.Data;
using SixPack.Data;

namespace example
{
  public static class StoredProcedures
  {
    public static StoredProcedure ExecuteWidget(string widgetName, string another)
    {
      return new StoredProcedure("executeWidget")
        .AddParameter("@widgetName", widgetName, DbType.AnsiString, 255)
        .AddParameter("@another", another, DbType.AnsiString, 255);
    }

    // More stored procedures wrapped as static methods go here...

  }
}

Given the above generated code, a programmer could then invoke a Stored Procedure simply like this:

DataSet ds = StoredProcedures.ExecuteWidget("foo", "bar").GetDataSet();

Please take note again of the fact that both the generated and handwritten code are RDBMS-independent.

Different invocation types

You might wonder why we did not include the GetDataSet method in the generated code above. That is because you can invoke a Stored Procedure in different ways. Here's an example of the different invocation commands:

StoredProcedure procedure = new StoredProcedure("myStoredProcName")
                                .AddParameter("@first", 42, DbType.Int, 0)
                                .AddParameter("@second", "hello", DbType.String, 50);
DataSet ds = procedure.GetDataSet();
procedure.Execute();
Object o = procedure.ExecuteScalar();

Using a different connection string

What if you don't want to use the first connection string but a different one? You can pass the connection string name to the StoredProcedure constructor, and that will be used.

StoredProcedure procedure = new StoredProcedure("spName", "connectionName");

Note that you cannot directly pass a connection string. The class needs to know the connection string and the provider at creation time for caching.

DbConnections and DbTransactions

The class plays nice with existing systems. All invocation methods support passing a DbConnection or a DbTransaction.

StoredProcedure procedure = new StoredProcedure("spName", "connectionName");
procedure.Execute(myDbTransaction);

If you pass a class of one of these two types, it will be used in the invocation. Note that you have to be careful about the concrete type you are passing. You cannot create a StoredProcedure instance using a SQL Server connection string and then pass an OleDbTransaction, for example. This will result in a run-time exception.

Using output parameters

Apart from the AddParameter method that binds only an input parameter, you can also use the AddOutParameter method to bind an out parameter to your Stored Procedure invocation. You can then use the GetOutputParameter method to retrieve the value.

StoredProcedure procedure = new StoredProcedure("foo")
                                .AddOutParameter("@bar", DbType.AnsiString, 50);
procedure.Execute();
string myResult = (string)procedure.GetOutputParameter("@bar");

Enabling deadlock support

Note: this feature is only supported under SQL Server.

In high load environments, it is possible that Stored Procedures will begin having deadlock events.

In other words, you might have two Stored Procedure invocations each waiting for the other to release a particular database lock. The problem has a series of workarounds and best practices, but there is no 100% fool proof way of making sure this never happens on complex systems under load.

SQL Server, in these cases, kills one of the two invocations, usually the one that has been less expensive so far, so that the other can complete. It is good practice in these cases to resubmit the deadlock victim Stored Procedure -- but, up to a point: there has to be a limit of times that this resubmit can be performed.

The StoredProcedure class can handle this for you automatically, but it does not do so by default. To enable deadlock victim resubmission, you can add the following key to your web.config or app.config file:

<configuration>
  <appsettings>
    <!-- This will let the StoreProcedure class 
         retry submitting deadlock victims 5 times. -->
    <add value="5" name="StoredProcedureDeadlockRetries">
  </add>
</appsettings>

Tracing stored procedures

When developing a project, you might need to understand how your Stored Procedures are performing for a series of reasons.

Simply use the debug version of the SixPack library, configure the logging system, and all Stored Procedure calls will be traced with parameter values and times of execution.

Points of interest

• In a future article, we will explain how to configure and use the logging system. For now, you can take a quick peek at the Log class wiki page.
• The library is lacking deadlock retries on RDBMS systems different from SQL Server. We would be grateful if anyone has a "deadlocking" system under a different RDBMS so we can actually write and test the code under those conditions. If you want to know more about deadlocks, you can read this article.
• The SixPack library is open source, and available on this site on Google code.

History

First release.