Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server

Code First Stored Procedures

Rate me:
Please Sign up or sign in to vote.
4.87/5 (81 votes)
19 May 2015CPOL7 min read 347.4K   6.8K   183   109
Access Stored Procedures using Code First.

Introduction

Code First is a new, lightweight database interface provided by the same team that brought you MVC3 and Razor. While it does many things well, one hole in it's interface is stored procedures. This project provides routines that can call stored procedures and will properly handle input and output parameters, return codes and multiple result sets.

Background

The problem with calling stored procedures from a strongly typed language is that they're messy. They can accept data via either throught scalar parameters or a table (T-SQL) and can return data via their parameters, by a return code and through multiple result sets, each of which may be completely different. Until the very clever people at Microsoft add stored procedure support to Code First (Entity Framework 4.1), you can use this interface routine. This project provides classes and extension methods to allow you to call stored procedures and deal with all that messiness in a relatively clean way.

Using the code

Let's begin with some sample stored procedures.

SQL
//
-- Stored procedure with input and output parameters, and a single result set
create proc testone @in varchar(5), @out int out
as
begin
	select table_name, column_name from INFORMATION_SCHEMA.COLUMNS
	set @out = @@ROWCOUNT
end
go

-- Stored procedure with no parameters, a return code and a single result set
create proc testtwo
as
begin
	select TABLE_CATALOG, TABLE_NAME
	from INFORMATION_SCHEMA.TABLES
	return @@ROWCOUNT
end
go

-- Stored procedure with no parameters and multiple result sets
create proc testthree
as 
begin
	select table_name, column_name from INFORMATION_SCHEMA.COLUMNS
	select TABLE_CATALOG, TABLE_NAME
	from INFORMATION_SCHEMA.TABLES
end
go

These three example procedures do most of the messy things that we expect from stored procedures: Input and Output parameters, return codes and multiple result sets. It's probably important to note that any one stored procedure can do all of these to return data to the caller.

To use this code, we'll need to follow a fairly straightforward calling pattern: create classes for parameters and result sets, populate the parameters class, call the extension method and then process the outputs. Here's the example classes for the first stored procedure above

C#
/// <summary>
/// Parameters object for the 'testoneproc' stored procedure
/// </summary>
public class testone
{
    // Override the parameter name. The parameter name is "in", but that's not a valid property
    // name in C#, so we must name the property something else and provide an override to set
    // the parameter name.
    [StoredProcAttributes.Name("in")]
    [StoredProcAttributes.ParameterType(System.Data.SqlDbType.VarChar)]
    public String inparm { get; set; }

    // This time we not only override the parameter name, we're also setting the parameter
    // direction, indicating that this property will only receive data, not provide data
    // to the stored procedure. Note that we must include the size in bytes.
    [StoredProcAttributes.Name("out")]
    [StoredProcAttributes.Direction(System.Data.ParameterDirection.Output)]
    [StoredProcAttributes.Size(4)]
    public Int32 outparm { get; set; }
}

/// <summary>
/// Results object for the 'testoneproc' stored procedure
/// </summary>
public class TestOneResultSet
{
    // match by the attribute name rather than the property name
    [StoredProcAttributes.Name("table_name")]
    public string table { get; set; }

    // match by the ordinal (column order, count from 0) rather than the property name
    [StoredProcAttributes.Ordinal(1))]
    public string column { get; set; }
}

To process the output parameter, we decorate the appropriate property with the Direction attribute and give it the value ParameterDirection.Output. When the call to the stored procedure returns, this will automatically hold the output value set within the stored procedure. We also set the Size parameter to 4 bytes to match the size of the integer return value. If the Size parameter is too small, your returned values will be truncated. The returned data is matched by default between the column name in the result set and the property name in the destination class object. This can be overridden using the Name or Ordinal attributes. Using these classes we can now define a StoredProc object which defines the stored procedure

C#
// simple stored proc
public StoredProc<testone> testoneproc = new StoredProc<testone>(typeof(TestOneResultSet));

The definition of the stored procedure contains the parameters class, and the constructor contains each of the expected return set types, in the same order they are created within the stored procedure. In this case, we're expecting one return set so we provide one type object. It's important to note that any type provided as a result set type must have a default constructor, that is a constructor that takes no parameters. Now that we have the data source and destination classes and our stored procedure defined, we can make the call to the database.

C#
using (testentities te = new testentities())
{
    //-------------------------------------------------------------
    // Simple stored proc
    //-------------------------------------------------------------
    var parms1 = new testone() { inparm = "abcd" };
    var results1 = te.CallStoredProc<testone>(te.testoneproc, parms1);
    var r1 = results1.ToList<TestOneResultSet>();
}

Note that the names of the parameters in the parameters class should match the names of the parameters declared in the stored procedure definition. If this is not possible (as in our example above: "in" and "out" are not valid property names), then the Name attribute can be used to override the default and specify the parameter name.

In keeping with the Code First philosophy of using lightweight POCO objects as data carriers, result set values are copied into the output object by matching the name of the column in the result set with the name of the property in the return type. This 'copy by property' is sensitive to the NotMappedAttribute used to identify object properties that should not be mapped to database I/O, and can be overridden using the Name attribute.

The ToList<T> accessor method in the Result Set object will search for the result set containing objects of that particular type and return the first one found, casting it to a List of the correct type. The data returned in the output parameter "out" defined in the stored procedure is automatically routed back to the mapped property ("outparm") in the parameters object.

The second example stored procedure has both a result set and a return code. To process the return code, we could create a parameters class and decorate the property with the Direction attribute and give it the value ParameterDirection.ReturnValue. When the call to the stored procedure returns, this will hold the return code set within the stored procedure. Note that in SQL Server, this must be an integer value. If, for whatever reason, you wished to ignore the return code parameter, you can call the non-generic version of CallStoredProc:

C#
// stored proc with no parameters
public StoredProc testtwo = new StoredProc("testtwo", typeof(TestTwoResultSet));

//-------------------------------------------------------------
// Simple stored proc with no parameters
//-------------------------------------------------------------
var results2 = te.CallStoredProc(testtwo);
var r2 = results2.ToList<TestTwoResultSet>();

In this case, we're intentionally discarding the return code parameter that will be returned by the stored procedure. This does not cause an error. It's also possible to ignore result sets. The CallStoredProc routine will only save result sets for which a type was specified in the method call. Conversely, it will not cause an error if the stored procedure returns fewer result sets than you provide types for.

The third example returns multiple result sets. Since this stored procedure does not accept parameters or return a return code, the set up is simple - just call the procedure. In this case, we're using Fluent API style methods to assign values to the StoredProc object.

C#
StoredProc testthree = new StoredProc()
    .HasName("testthree")
    .ReturnsTypes(typeof(TestOneResultSet), typeof(TestTwoResultSet));

//-------------------------------------------------------------
// Stored proc with no parameters and multiple result sets
//-------------------------------------------------------------
var results3 = te.CallStoredProc(testthree);
var r3_one = results3.ToList<TestOneResultSet>();
var r3_two = results3.ToArray<TestTwoResultSet>();

The ToList<T>() method of the ResultsList searches for the first result set containing the indicated type, so we can simplify accessing the return values by specifying the type we want and let the ResultsList figure out the right result set for us. If the result set could not be found, an empty list of the correct type is returned, so the return from ToListwill never be null. If your stored procedure returns more than one instance of the same result set, the ToList method will return the first result set. You can use the array indexer [] or create an enumerator over the ResultsList to process all the result sets.

Table Valued Parameters

Sql Server can accept a table as a parameter to a stored procedure. In the database we need to create a user type for the table and then declare the stored procedure parameter using this type. The 'Readonly' modifier on the parameter is required.

SQL
-- Create Table variable 
create type [dbo].[testTVP] AS TABLE(
 [testowner] [nvarchar] (50) not null, 
 [testtable] [nvarchar] (50) NULL,
 [testcolumn] [nvarchar](50) NULL
)
GO

-- Create procedure using table variable
create proc testfour @tt testTVP readonly
as
begin
	select table_schema, table_name, column_name from INFORMATION_SCHEMA.COLUMNS
	inner join @tt
	on table_schema = testowner
	where (testtable is null or testtable = table_name)
	and (testcolumn is null or testcolumn = column_name)
end
go

On the .Net side of things we need to create a class to represent rows in this table, and we need to duplicate the table definition so that the data rows can be processed appropriately. We will create a class to represent the rows of the table being passed to the table valued parameter, and decorate that class with attributes that match the SQL table definition.

C#
/// <summary>
/// Class representing a row of data for a table valued parameter.
/// Property names (or Name attribute) must match table type column names
/// </summary>
[StoredProcAttributes.Schema("dbo")]
[StoredProcAttributes.TableName("testTVP")]
public class sample
{
    [StoredProcAttributes.Name("testowner")]
    [StoredProcAttributes.ParameterType(SqlDbType.VarChar)]
    [StoredProcAttributes.Size(50)]
    public string owner { get; set; }

    [StoredProcAttributes.ParameterType(SqlDbType.VarChar)]
    [StoredProcAttributes.Size(50)]
    public string testtable { get; set; }

    [StoredProcAttributes.ParameterType(SqlDbType.VarChar)]
    [StoredProcAttributes.Size(50)]
    public string testcolumn { get; set; }
}

The Schema and TableName attributes identify the table definition we're passing to the table valued parameter, and the methods matching each column must be decorated with the appropriate SQL column definition attributes.

C#
/// <summary>
/// Parameter object for 'testfour' stored procedure
/// </summary>
public class testfour
{
    [StoredProcAttributes.ParameterType(SqlDbType.Structured)]
    [StoredProcAttributes.Name("tt")]
    public List<sample> tabledata { get; set; }
}


/// <summary>
/// output class for proc test four
/// </summary>
public class TestFourResultSet
{
    public string table_schema { get; set; }
    public string table_name { get; set; }
    public string column_name { get; set; }
}

Now that we have a class matching our table definition, we can create an instance of a table valued parameter in the parameters object. Create a property with an IEnumerable<> type and give it the SqlDbType Structured. The CodeFirstStoredProc routines will build the table definition dynamically and pass the table to the stored procedure. To use, simply give the table valued parameter a list or array of the underlying class type.

C#
//-------------------------------------------------------------
// Stored proc with a table valued parameter
//-------------------------------------------------------------

// new parameters object for testfour
testfour four = new testfour();

// load data to send in the table valued parameter
four.tabledata = new List<sample>()
{
    new sample() { owner = "tester" },
    new sample() { owner = "dbo" }
};

// call stored proc
var ret4 = te.CallStoredProc<testfour>(te.testfour, four);
var retdata = ret4.ToList<TestFourResultSet>();

Acknowledgements

Thanks and acknowledgements to everyone from whom I've learned these techniques, all those bloggers who took the time to post tips that have helped my knowledge, and with a special mention to StackOverflow, without which this could not exist.

History

Version 1.0 Initial Release

Version 2.0

  • Breaking Changes!
  • Complete replacement of the interface, making this much simpler to use and much more in line with the Code First style.

Version 2.1

  • Breaking Changes! Yes, it's a bad habit, but in this case it's a good thing.
  • Removal of the "TableType" class and replacing it with the simpler and more "code first" style of defining a table valued parameter through POCO objects and attributes.
  • Added precision and scale attributes.

Version 2.6

  • Add support for Entity Framework 6
  • Add Command Timeout parameter for controlling long running procedure calls

Version 3.0

  • Much needed interface updates
  • Add support for Async and Streaming

Version 3.2

  • Updated to fix an issue with Async stored procedure calls.
  • Streaming data to and from the stored procedure now requires manually setting "CommandBehavior.SequentialAccess"

Version 3.3

  • Updated to work with the Glimpse.EF6 library.

Version 3.5

  • Fix version number mismatch.

Version 3.6

  • Don't send a parameter for null or empty (zero row count) Table Valued Parameter. Fixes error for empty or null TVP's.

Version 3.7

  • Bug fix and a bit of code refactoring.

Version 3.8

  • Allow return column assignment to properties by ordinal value as well as by name.

Version 3.9

  • Bug fixes! Connections passed in are no longer inadvertently closed.

License

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


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralRe: After migrate to EF 6.1.1 module crashed in some scenario Pin
aureolin28-Jul-14 9:16
aureolin28-Jul-14 9:16 
QuestionWhat reference for StoredProcAttributes? Pin
Rob Achmann10-Mar-14 14:49
Rob Achmann10-Mar-14 14:49 
AnswerRe: What reference for StoredProcAttributes? Pin
aureolin11-Mar-14 5:46
aureolin11-Mar-14 5:46 
QuestionSchema not recognizing Pin
Member 1055351427-Jan-14 11:07
Member 1055351427-Jan-14 11:07 
AnswerRe: Schema not recognizing Pin
aureolin27-Jan-14 12:02
aureolin27-Jan-14 12:02 
GeneralRe: Schema not recognizing Pin
Member 1055351428-Jan-14 8:33
Member 1055351428-Jan-14 8:33 
GeneralRe: Schema not recognizing Pin
aureolin28-Jan-14 9:12
aureolin28-Jan-14 9:12 
GeneralRe: Schema not recognizing Pin
Member 1055351428-Jan-14 11:38
Member 1055351428-Jan-14 11:38 
GeneralRe: Schema not recognizing Pin
aureolin28-Jan-14 12:44
aureolin28-Jan-14 12:44 
GeneralRe: Schema not recognizing Pin
Member 1055351429-Jan-14 8:14
Member 1055351429-Jan-14 8:14 
GeneralRe: Schema not recognizing Pin
aureolin29-Jan-14 8:57
aureolin29-Jan-14 8:57 
GeneralRe: Schema not recognizing Pin
dibley197324-Jul-15 7:17
dibley197324-Jul-15 7:17 
GeneralRe: Schema not recognizing Pin
dibley197324-Jul-15 7:37
dibley197324-Jul-15 7:37 
QuestionAwesome! Pin
sadeghhp23-Jan-14 20:39
sadeghhp23-Jan-14 20:39 
QuestionVery good article! Pin
Volynsky Alex30-Dec-13 9:31
professionalVolynsky Alex30-Dec-13 9:31 
QuestionEmpty table value parameter Pin
Anton Kalcik13-Dec-13 5:15
Anton Kalcik13-Dec-13 5:15 
AnswerRe: Empty table value parameter Pin
Anton Kalcik17-Dec-13 1:36
Anton Kalcik17-Dec-13 1:36 
GeneralRe: Empty table value parameter Pin
Member 1199235817-Sep-15 9:11
Member 1199235817-Sep-15 9:11 
QuestionExample Code Help Pin
Member 91258187-Nov-13 6:06
Member 91258187-Nov-13 6:06 
AnswerRe: Example Code Help Pin
aureolin21-Nov-13 6:10
aureolin21-Nov-13 6:10 
QuestionEF - a big fat wrapper for ADO Pin
Member 436622017-Jul-13 11:34
Member 436622017-Jul-13 11:34 
AnswerRe: EF - a big fat wrapper for ADO Pin
aureolin17-Jul-13 11:48
aureolin17-Jul-13 11:48 
GeneralRe: EF - a big fat wrapper for ADO Pin
Member 43662205-Aug-13 9:27
Member 43662205-Aug-13 9:27 
GeneralRe: EF - a big fat wrapper for ADO Pin
aureolin5-Aug-13 10:13
aureolin5-Aug-13 10:13 
AnswerMessage Closed Pin
4-Nov-14 0:49
_GP4-Nov-14 0:49 

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.