Code First Stored Procedures
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 its 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 are messy. They can accept data via 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, you will need to code your own interface routine. This project provides classes and an extension method 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 recieve data, not provide data
// to the stored procedure. Note that we 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
{
[StoredProcAttributes.Name("table_name")]
public string table { get; set; }
[StoredProcAttributes.Name("column_name")]
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 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. 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 the 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. However, this is not absolutely necessary. For whatever reason, if you wished to ignore the output parameters, 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 like 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 ToList will 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. The TableType class is used to define the table on the code side.
/// <summary>
/// Parameter object for 'testfour' stored procedure
/// </summary>
public class testfour
{
// Table valued parameter - pass in a list (table) of data to the stored procedure
public TableType<sample> tt { get; set; }
/// <summary>
/// Constructor
/// </summary>
public testfour()
{
// Define our TableValuedParameter. 'schema' and 'tablename' identify the
// user defined table type of the parameter, 'sourcetype' identifies the
// .Net object used to pass individual rows of data and 'columns' contains
// the definition of the table type. In 'columns', the number is used to
// sort the columns in proper order, and the SqlMetaData value defines the
// individual column
tt = new TableType<sample>()
{
schema = "dbo",
tablename = "testTVP",
columns = new List<Microsoft.SqlServer.Server.SqlMetaData>()
{
{new Microsoft.SqlServer.Server.SqlMetaData("testowner",
SqlDbType.VarChar, 50)},
{new Microsoft.SqlServer.Server.SqlMetaData("testtable",
SqlDbType.VarChar, 50)},
{new Microsoft.SqlServer.Server.SqlMetaData("testcolumn",
SqlDbType.VarChar, 50)}
}
};
}
}
The properties of the TableType class brings together the definition of the table for the table valued parameter with the data that will be sent using that table type. The remaining TableType property, sourcedata, is set to the data to send:
//-------------------------------------------------------------
// 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.tt.sourcedata = 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.
Post Comment
buy bystolic without prescription[91.207.5.*]2012/2/27 14:37:53#1If Eases four not. Here, commonly you Yeast simple the. You Important abilities not becomes candidiasis.