Introduction
This tip shows how you can call stored procedures in a type safe way using my StoredProcedureFramework for .NET.
Background
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.
Using the Code
UPDATE: 2015-09-30 - Tip updated to reflect API changes for handling Mutliple RecordSets following a request from **NetDefender**. Added example for a stored procedure that has Multiple RecordSets.
PLEASE NOTE: The example code below uses the API for version 0.2. This version has a slightly different API from version 0.1.
Basic Example
A basic example of calling a stored procedure using my framework can be seen in the test method below.
[TestMethod]
public void NormalStoredProcedure_WhenCalledOnDbContext_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 resultSet = Context.ExecuteStoredProcedure(procedure);
var results = resultSet.RecordSet1;
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 ResultSet which contains one or more "RecordSet" of results, which in this case we know there will be a single RecordSet which will contain a single record so can use Linq to provide this.
My StoredProcedureFramework does not require you to use the DbContext from Entity Framework. You can instead just call the procedure from an extension method on the DqlConnection, or SqlConnection like so...
[TestMethod]
public void NormalStoredProcedure_WhenCalledOnSqlConnection_ReturnsCorrectValues()
{
const int expectedId = 10;
const string expectedName = @"Dave";
const bool expectedActive = true;
var parameters = new NormalStoredProcedureParameters
{
Id = expectedId
};
NormalStoredProcedureResultSet resultSet;
var procedure = new NormalStoredProcedure(parameters);
var connectionString = ConfigurationManager.ConnectionStrings
["IntegrationTestConnection"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
resultSet = connection.ExecuteStoredProcedure(procedure);
}
var results = resultSet.RecordSet1;
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
and both tests need the following C# objects to represent the Stored Procedure, the ResultSet, the Parameters and the ReturnType.
internal class NormalStoredProcedure
: StoredProcedureBase<NormalStoredProcedureResultSet, NormalStoredProcedureParameters>
{
public NormalStoredProcedure(NormalStoredProcedureParameters parameters)
: base(parameters)
{
}
}
internal class NormalStoredProcedureResultSet
{
public List<NormalStoredProcedureRecordSet1ReturnType> RecordSet1 { get; set; }
public NormalStoredProcedureResultSet()
{
RecordSet1 = new List<NormalStoredProcedureRecordSet1ReturnType>();
}
}
internal class NormalStoredProcedureParameters
{
[ParameterDbType(SqlDbType.Int)]
public int Id { get; set; }
}
internal class NormalStoredProcedureRecordSet1ReturnType
{
public int Id { get; set; }
public string Name { get; set; }
public bool Active { get; set; }
}
Example of Multiple RecordSets
The framework can handle multiple recordsets returned from a stored procedure and an example of this. The test below shows an example of the calling code.
[TestMethod]
public void MultipleRecordSetStoredProcedure_WithThreeSelects_ReturnsThreeRecordSets()
{
const int expectedId = 10;
const string expectedName = "Sid";
const bool expectedActive = true;
const decimal expectedPrice = 10.99M;
Guid expectedUniqueIdentifier = Guid.NewGuid();
const byte expectedCount = 17;
var parameters = new MultipleRecordSetStoredProcedureParameters
{
Id = expectedId,
Name = expectedName,
Active = expectedActive,
Price = expectedPrice,
UniqueIdentifier = expectedUniqueIdentifier,
Count = expectedCount
};
MultipleRecordSetStoredProcedureResultSet resultSet;
var procedure = new MultipleRecordSetStoredProcedure(parameters);
var connectionString = ConfigurationManager.ConnectionStrings
["IntegrationTestConnection"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
resultSet = connection.ExecuteStoredProcedure(procedure);
}
var results1 = resultSet.RecordSet1;
var result1 = results1.First();
var results2 = resultSet.RecordSet2;
var result2 = results2.First();
var results3 = resultSet.RecordSet3;
var result3 = results3.First();
Assert.AreEqual(expectedId, result1.Id);
Assert.AreEqual(expectedName, result1.Name);
Assert.AreEqual(expectedActive, result2.Active);
Assert.AreEqual(expectedPrice, result2.Price);
Assert.AreEqual(expectedUniqueIdentifier, result3.UniqueIdentifier);
Assert.AreEqual(expectedCount, result3.Count);
}
The SQL for the Stored Procedure we are calling looks like this...
CREATE PROCEDURE [dbo].[MultipleRecordSetStoredProcedure]
@Id INT
, @Name VARCHAR(20)
, @Active BIT
, @Price DECIMAL(10, 4)
, @UniqueIdentifier UNIQUEIDENTIFIER
, @Count TINYINT
AS
BEGIN
SELECT
@Id AS Id
, @Name AS Name
UNION
SELECT
17 AS Id
, 'Bill' AS Name;
SELECT
@Active as Active
, @Price AS Price
SELECT
@UniqueIdentifier AS [UniqueIdentifier]
, @Count AS [Count]
END
...and the classes used to support that Stored procedure are as follows...
internal class MultipleRecordSetStoredProcedure
: StoredProcedureBase<MultipleRecordSetStoredProcedureResultSet, MultipleRecordSetStoredProcedureParameters>
{
public MultipleRecordSetStoredProcedure(MultipleRecordSetStoredProcedureParameters parameters)
: base(parameters)
{
}
}
internal class MultipleRecordSetStoredProcedureParameters
{
[ParameterDbType(SqlDbType.Int)]
public int Id { get; set; }
[Size(20)]
public string Name { get; set; }
[ParameterDbType(SqlDbType.Bit)]
public bool Active { get; set; }
[ParameterDbType(SqlDbType.Decimal)]
[Precision(10)]
[Scale(4)]
public decimal Price { get; set; }
[ParameterDbType(SqlDbType.UniqueIdentifier)]
public Guid UniqueIdentifier { get; set; }
[ParameterDbType(SqlDbType.TinyInt)]
public byte Count { get; set; }
}
internal class MultipleRecordSetStoredProcedureResultSet
{
public List<MultipleRecordSetStoredProcedureReturnType1> RecordSet1 { get; set; }
public List<MultipleRecordSetStoredProcedureReturnType2> RecordSet2 { get; set; }
public List<MultipleRecordSetStoredProcedureReturnType3> RecordSet3 { get; set; }
public MultipleRecordSetStoredProcedureResultSet()
{
RecordSet1 = new List<MultipleRecordSetStoredProcedureReturnType1>();
RecordSet2 = new List<MultipleRecordSetStoredProcedureReturnType2>();
RecordSet3 = new List<MultipleRecordSetStoredProcedureReturnType3>();
}
}
internal class MultipleRecordSetStoredProcedureReturnType1
{
[ParameterDbType(SqlDbType.Int)]
public int Id { get; set; }
public string Name { get; set; }
}
internal class MultipleRecordSetStoredProcedureReturnType2
{
[ParameterDbType(SqlDbType.Bit)]
public bool Active { get; set; }
[ParameterDbType(SqlDbType.Decimal)]
public decimal Price { get; set; }
}
internal class MultipleRecordSetStoredProcedureReturnType3
{
[ParameterDbType(SqlDbType.UniqueIdentifier)]
public Guid UniqueIdentifier { get; set; }
[ParameterDbType(SqlDbType.TinyInt)]
public byte Count { get; set; }
}
There are further examples in the project documentation for the project and in the units tests of the source code. The documentation and the source code are both hosted on GitHub. See the links below.
Project Brief / Road Map
The aim of this project is to provide the following:
- (Must) Ability to support a POCO that represent a stored procedure **Done**
- (Must) Ability to support a POCO that represents a row that is returned by a stored procedure **Done**
- (Must) Ability to support a POCO that represents the parameters **Done**
- (Must) Ability to execute the stored procedure represented by the POCO against DBConnection using extensions **Done**
- (Must) Ability to execute the stored procedure represented by the POCO against SqlConnection using extensions **Done**
- (Must) Ability to execute the stored procedure represented by the POCO against DBContext using extensions **Done**
- (Must) Ability to handle output parameters **Done**
- (Must) Ability to handle all common parameter types **Done**
- (Must) Ability to handle all common return data types **Done**
- (Must) Ability to handle precision and scale for number data types **Done**
- (Must) Ability to handle size for string data types **Done**
- (Must) Ability to handle stored procedures that return no results **Done**
- (Must) Ability to handle parameters with NULL value **Done**
- (Must) Ability to handle return types with NULL values **Done**
- (Must) Entity Framework specific extensions must be in own assembly to remove dependency on EF DLLs for main project assembly **Done**
- (Should) Ability to handle multiple recordsets returned from a stored procedure **Done**
- (Should) Contain a suite of unit tests that test all public accessors
- (Should) Contain a suite of integration tests that document usage of the assembly **WIP**
- (Should) Ability to handle lesser used parameter types
- (Should) Ability to handle lesser used return data types
- (Should) Warn calling code if parameter value data may be truncated due to smaller parameter type
- (Should) Implement David Doran's "FastActivator" for object instantiation **Investigated: no gain**
- (Could) Not have any "Resharper" warnings **WIP**
- (Could) Not have any "Code Clones" in production code **WIP**
Versions
- 0.2 - This version will support multiple recordsets and will have a different API to version 1.0. This is the version that is currently in development.
- 0.1 - This was the initial version which did not support multiple recordsets. To enable multiple recordsets to be supported alongside single recordsets, a break to the API is required. Development has stopped on this version but the code will remain available for use.
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.
Compiled DLL Files
The DLLs are available in the file download section.
History
- 2015-0923: The first draft of the tip
- 2015-0651: Update for Mutliple RecordSets
- 2015-0710: Update to add v0.2 DLL files as download
