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()
{
const int expectedId = 10;
const string expectedName = @"Dave";
const bool expectedActive = true;
var parameters = new NormalStoredProcedureParameters
{
Id = expectedId
};
var procedure = new NormalStoredProcedure(parameters);
var results = Context.ExecuteStoredProcedure(procedure);
var result = results.First();
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()
{
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;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
results = connection.ExecuteStoredProcedure(procedure);
}
var result = results.First();
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.
Duane has worked in a commercial software development environment for 9 years, with all but three for a global fashion retailer.
He is proficient in ASP.Net, MVC, C#, HTML, CSS, JavaScript, SQL Server TSQL.