Click here to Skip to main content
15,868,016 members
Articles / Database Development / SQL Server / SQL Server 2012
Tip/Trick

Strongly Typed Stored Procedures for .NET

Rate me:
Please Sign up or sign in to vote.
4.20/5 (2 votes)
23 Sep 2015CPOL5 min read 24.3K   40   9   12
Call stored procedures in a type safe way using my StoredProcedureFramework for .NET

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.

C#
[TestMethod]
public void NormalStoredProcedure_WhenCalledOnDbContext_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 resultSet = Context.ExecuteStoredProcedure(procedure);
    var results = resultSet.RecordSet1;
    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 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...

C#
[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<NormalStoredProcedureRecordSet1ReturnType> results;
    NormalStoredProcedureResultSet resultSet;
    var procedure = new NormalStoredProcedure(parameters);
    var connectionString = ConfigurationManager.ConnectionStrings
    	["IntegrationTestConnection"].ConnectionString;
    
    // ACT
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        resultSet = connection.ExecuteStoredProcedure(procedure);
    }
    var results = resultSet.RecordSet1;
    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:

SQL
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.

C#
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.

C#
[TestMethod]
public void MultipleRecordSetStoredProcedure_WithThreeSelects_ReturnsThreeRecordSets()
{
    // ARRANGE
    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;

    // ACT
    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
    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...

SQL
CREATE PROCEDURE [dbo].[MultipleRecordSetStoredProcedure]
    @Id                 INT
,   @Name               VARCHAR(20)
,   @Active             BIT
,   @Price              DECIMAL(10, 4)
,   @UniqueIdentifier   UNIQUEIDENTIFIER
,   @Count              TINYINT
AS
BEGIN
    /* First Record Set */
    SELECT 
        @Id     AS Id
    ,   @Name   AS Name
    UNION
    SELECT
        17      AS Id
    ,   'Bill'  AS Name;

    /* Second Record Set */
    SELECT 
        @Active as Active
    ,   @Price  AS Price

    /* Third Record Set */
    SELECT
        @UniqueIdentifier   AS [UniqueIdentifier]
    ,   @Count              AS [Count]
    
END

...and the classes used to support that Stored procedure are as follows...

C#
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

License

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


Written By
Software Developer
United Kingdom United Kingdom
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.

Comments and Discussions

 
QuestionSimilar to Dapper? Pin
Brian Kinnish25-Sep-15 15:23
Brian Kinnish25-Sep-15 15:23 
AnswerRe: Similar to Dapper? Pin
dibley197326-Sep-15 21:39
dibley197326-Sep-15 21:39 
AnswerRe: Similar to Dapper? Pin
dibley197326-Sep-15 21:53
dibley197326-Sep-15 21:53 
Unless i am miss reading about Dapper, Dapper is an ORM and ideally used for CRUD.
The key driver for my framework is a lightweight strongly typed framework more suited to the "Q" side of "CQS", rather than the "C" which i guess an ORM framework would sit better with?

Or have i mis read?
GeneralRe: Similar to Dapper? Pin
Brian Kinnish27-Sep-15 1:16
Brian Kinnish27-Sep-15 1:16 
QuestionCool Pin
NetDefender23-Sep-15 6:56
NetDefender23-Sep-15 6:56 
AnswerRe: Cool Pin
dibley197323-Sep-15 10:07
dibley197323-Sep-15 10:07 
AnswerBranched and working on Multiple RecordSets Pin
dibley197324-Sep-15 20:08
dibley197324-Sep-15 20:08 
GeneralRe: Branched and working on Multiple RecordSets Pin
NetDefender25-Sep-15 12:13
NetDefender25-Sep-15 12:13 
GeneralRe: Branched and working on Multiple RecordSets Pin
dibley197327-Sep-15 20:00
dibley197327-Sep-15 20:00 
GeneralRe: Branched and working on Multiple RecordSets Pin
NetDefender28-Sep-15 6:30
NetDefender28-Sep-15 6:30 
AnswerInitial work on multiple RecordSets complete Pin
dibley197329-Sep-15 8:32
dibley197329-Sep-15 8:32 
AnswerWork on Multiple RecordSets now complete Pin
dibley197329-Sep-15 20:20
dibley197329-Sep-15 20:20 

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.