Strongly Typed Stored Procedures for .NET

• Download source code from Github

Introduction

If like me, you enjoy the type safety of the .NET Framework and you also enjoy the benefits that using stored procedures bring to queries on a SQL Server database, then also like me you may have been waiting for Microsoft to roll out a framework for .NET that allows you to define stored procedures, their parameters and return types in a type safe way and call them from a Connection object or DBContext. Especially in light of the rise in popularity of CQS and CQRS splitting data reading out into a separate stack from the data writing.

However, this does not appear to be an area Microsoft is interested in focusing upon. So it is down to the rest of us to come up with a solution. So based upon the excellent work by "bluemoonsailor" at "Mindless Passenger" (here), I have been working on my own framework which allows the user to create objects that represent stored procedures, their parameters and return types and call them from a SqlConnection, a DBConnection or a DBContext.

A basic example of calling a stored procedure using my framework can be seen in the test method below.

[TestMethod]
public void NullValueParameterProcedure_WithNullableParamatersAndReturnType_ReturnsCorrectValues()
{
    // ARRANGE  
    const int expectedId = 10;
    const string expectedName = @"Dave";
    const bool expectedActive = true;

    var parameters = new NormalStoredProcedureParameters
    {
        Id = expectedId
    };
    var procedure = new NormalStoredProcedure(parameters);

    // ACT
    var results = Context.ExecuteStoredProcedure(procedure);
    var result = results.First();

    // ASSERT
    Assert.AreEqual(expectedId, result.Id);
    Assert.AreEqual(expectedName, result.Name);
    Assert.AreEqual(expectedActive, result.Active);
}

So reading down through the test, we can see first we are setting up our expected result (based upon what we know the stored procedure SHOULD return). We then need to instantiate and populate a parameters object. We can then use the parameters object to instantiate our stored procedure giving us everything set up and ready to go.

The Context in this test inherits from an entity Framework DbContext so I can execute the stored procedure by calling Context.ExecuteStoredProcedure(...) passing in the instantiated stored procedure object. This will return a list of results, which in this case we know will be a single record so can use LinQ to provide this.

Calling the Stored Procedures from Code using SqlConnectionExtensions

In addition to using the DbContext extension methods for Entity Framework, we can also call stored procedures using SqlConnection extension methods. And example of calling a basic stored procedure using just a SqlConnection is shown below:

[TestMethod]
public void NormalStoredProcedure_WhenCalledOnSqlConnection_ReturnsCorrectValues()
{
    // ARRANGE  
    const int expectedId = 10;
    const string expectedName = @"Dave";
    const bool expectedActive = true;

    var parameters = new NormalStoredProcedureParameters
    {
        Id = expectedId
    };
    List<NormalStoredProcedureReturnType> results;
    var procedure = new NormalStoredProcedure(parameters);
    var connectionString = ConfigurationManager.ConnectionStrings
    	["IntegrationTestConnection"].ConnectionString;

    // ACT
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        results = connection.ExecuteStoredProcedure(procedure);
    }
    var result = results.First();

    // ASSERT
    Assert.AreEqual(expectedId, result.Id);
    Assert.AreEqual(expectedName, result.Name);
    Assert.AreEqual(expectedActive, result.Active);
}

Both of these examples are based upon the following simple stored procedure:

CREATE PROCEDURE dbo.NormalStoredProcedure
    @Id  INT
AS
BEGIN
    SELECT 
        @Id AS Id
    ,   'Dave' AS Name
    ,   CAST(1 AS BIT) AS Active
END

There are further examples in the documentation for the project. The documentation and the source code are hosted on GitHub.

Documentation

Documentation for using my Stored Procedure Framework can be found here. Stored procedure framework documentation.

Source Code

The source code for this project is available on GitHub here. Please feel free to use, add to or adapt.