Click here to Skip to main content
Click here to Skip to main content

Code First Stored Procedures

, 28 Jul 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
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 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. The most recent update includes support for Async and data Streaming to and from SQL Server.

Background

The problem with calling Stored Procedures from a strongly typed language is that they're messy. They can accept data either through 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
{
    [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 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. Using these classes, we can now define a StoredProc object which defines the Stored Procedure.

// Simple Stored Proc declarations in DbContext
public class testing : DbContext
{
    // StoredProc object to call a proc named "testone"
    [StoredProcAttributes.Name("testone")]
    [StoredProcAttributes.ReturnTypes(typeof(TestOneResultSet))]
    public StoredProc<testone> test { get; set; }

    // stored proc with no parameters, but retrieves the return code
    [StoredProcAttributes.Name("testtwo")]
    [StoredProcAttributes.ReturnTypes(typeof(TestTwoResultSet))]
    public StoredProc<testtwo> testtwo { get; set; }

    // Stored Proc with no parms & two return types
    public StoredProc test3 { get; set; }

    // Constructor
    public testing()
    {
        // Must be called prior to using any stored proc objects
        this.InitializeStoredProcs();

        // Use Fluent API instead of attributes
        test3.HasName("testthree")
            .HasOwner("dbo")
            .ReturnsTypes(typeof(TestOneResultSet), typeof(TestTwoResultSet));
    }
}
</testtwo></testone>

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.

// Example of calling a simple stored proc
using (testing db = new testing())
{
    // create input parameters object
    var test1parms = new testone() { inparm = "abcd" };
    // call the stored proc
    var test1results = db.test.CallStoredProc(test1parms);
    // get our results into a usable object
    List<testoneresultset> results = test1results.ToList<testoneresultset>();
}
</testoneresultset></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 the StoredProc object:

// Example of calling a simple stored proc
using (testing db = new testing())
{
    var results2new = db.testtwo.CallStoredProc(testtwodata);
    var r2new = results2new.ToList<testtworesultset>();
}
</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.

// Example of calling a simple stored proc
using (testing db = new testing())
{
    // Call proc with no parms
    var test3results = db.test3.CallStoredProc();
    var t3_first = test3results.ToList<testoneresultset>();
    var t3_second = test3results.ToArray<testtworesultset>();
}
</testtworesultset></testoneresultset>

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. 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 P. To use, simply give the table valued parameter a list or array of the underlying class type.

// StoredProc with Table Valued parameter
public class testing : DbContext
{

    // stored proc with a table valued parameter
    [StoredProcAttributes.Name("testfour")]
    [StoredProcAttributes.ReturnTypes(typeof(TestFourResultSet))]
    public StoredProc<testfourdata> testfournew { get; set; }

    // Constructor
    public testing()
    {
        // Must be called prior to using any stored proc objects
        this.InitializeStoredProcs();

    }
}

// Example of calling a simple stored proc
using (testing db = new testing())
{
    // new parameters object for testfour
    testfourdata fourdata = new testfourdata();

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

    var test4results = db.testfour.CallStoredProc(fourdata);
}
</sample></testfourdata>

Async and Streaming

Using the async/await calling pattern is very straightforward. The standard StoredProc object has a "CallStoredProcAsync" method that supports async calling patterns. 

// Using the async/await calling pattern
var test4results = await db.testfour.CallStoredProcAsync(fourdata);

Using async is a natural fit with streaming large data sets to and from SQL Server. Streaming data to SQL Server requires declaring the data property type to be of (or derived from) Stream, and to have a Size attribute equal to -1.

public class testfileupdata
{
    [StoredProcAttributes.ParameterType(System.Data.SqlDbType.NVarChar)]
    public String filename { get; set; }

    // set size=-1 to tell sql to stream data
    [StoredProcAttributes.ParameterType(System.Data.SqlDbType.VarBinary)]
    [StoredProcAttributes.Size(-1)]
    public Stream filedata { get; set; }
}

// Stored Proc declarations in DbContext
public class testing : DbContext
{

    // test streaming  - upload
    [StoredProcAttributes.Name("testfileup")]
    public StoredProc<testfileupdata> testfileup { get; set; }

    // Constructor
    public testing()
    {
        // Must be called prior to using any stored proc objects
        this.InitializeStoredProcs();
    }
}
</testfileupdata>

For streaming data from SQL Server, two types of target streams are supported: FileStream and MemoryStream. FileStreams require a field in the returned data row that contains the filename. MemoryStreams can be written to data fields of type Stream, Byte[] and String.

public class testfiledowndata
{
    public String filename { get; set; }
}

public class testfiledownresults
{
    [StoredProcAttributes.ParameterType(System.Data.SqlDbType.NVarChar)]
    public String filename { get; set; }

    // Get data back in a stream, leave the stream open so we can access it
    [StoredProcAttributes.StreamToMemory(Buffered = true, LeaveStreamOpen=true)]
    public Stream filedata { get; set; }

    // Streamed data will be read into a byte array
    [StoredProcAttributes.StreamToMemory(Buffered = true)]
    public Byte[] filedataByteArray { get; set; }

    // Streamed data will be read into a string using the indicated encoding
    [StoredProcAttributes.StreamToMemory(Buffered = true, Encoding = "UTF8")]
    public String filedataString { get; set; }

    [StoredProcAttributes.StreamToFile(Buffered = true, Location = "C:\\Temp", FileNameField = "filename", LeaveStreamOpen = false)]
    public String filedata { get; set; }
}

// Stored Proc declarations in DbContext
public class testing : DbContext
{
    // test streaming - download
    [StoredProcAttributes.Name("testfiledown")]
    [StoredProcAttributes.ReturnTypes(typeof(testfiledownresults))]
    public StoredProc<testfiledowndata> testfiledown { get; set; }

    // Constructor
    public testing()
    {
        // Must be called prior to using any stored proc objects
        this.InitializeStoredProcs();
    }
}
</testfiledowndata>

Using the streaming upload and download methods looks exactly like what we're used to seeing: Objects get created, loaded with data and then shuffled back and forth to SQL Server:

// Example of calling a simple stored proc
using (testing db = new testing())
{
    // send "file" data as byte array to database using streaming
    testfileupdata f = new testfileupdata() { filename = "testing", filedata = new MemoryStream(Encoding.UTF8.GetBytes("123498;llk;asf010uro;nj ewf ojiru093rcn8ury9tyt4nu8qo38423845743poiwejhafp98yu91298ujhf")) };
    var fileupresults = db.testfileup.CallStoredProc(200, f);

    // now get it streamed back
    testfiledowndata getfile = new testfiledowndata() { filename = "testing" };
    var filedownresults = db.testfiledown.CallStoredProc(getfile);
    var returnedfiles = filedownresults.ToList<testfiledownresults>();
    int dat = returnedfiles[0].filedata.ReadByte();
}
</testfiledownresults>

When writing a MemoryStream back to a String data type, the Encoding attribute defines how the bytes are interpreted. The valid values for Encoding are: "ASCII", "BigEndianUniCode", "Default", "Unicode", "UTF32", "UTF7", and "UTF8".

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 3 includes a much needed update to the interface, and includes support for Async calling patterns and data streaming to and from SQL Server.

  • 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.5
    • Updated to support EF 5   
  • Version 2.6
    • Updated to support EF 6   
    • Added an optional parameter to allow setting the Command Timeout value on the stored procedure call for long running processes.   
  • Version 3.0

License

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

Share

About the Author

aureolin

United States United States
No Biography provided

Comments and Discussions

 
QuestionAlmost what I needed PinmemberOffApps7-Mar-12 6:38 
AnswerRe: Almost what I needed [modified] Pinmemberaureolin7-Mar-12 6:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 28 Jul 2014
Article Copyright 2011 by aureolin
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid