Code First Stored Procedures

• Download source - 589.2 KB

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.

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

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

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

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:

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

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.

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

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

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

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