Code Generation - Stored Procedure Wrappers for Data Access Layer
by David Hayden ( Florida .NET C# SQL Server Developer ), Filed: Code Generation
I have a new project that includes quite a number of stored procedures in the database for reporting needs and thought I would take some time last night and today to create a simple class that acts as a stored procedure wrapper to make it very easy to call the stored procedures from the data access layer. The concept of a stored procedure wrapper is not new to code generation. I know that Subsonic and LLBLGen Pro will generate them and I could have easily used those code generation tools as well as others.
I decided to use my own code generation tool, however, for kicks. Here is a few images to show the experience:
Using the Northwind Database, my database explorer and code generation tool will find the stored procedures as well as the parameters. I select all the stored procedures in Northwind, choose the stored procedure wrapper template, and have it generate the files into a class library in Visual Studio. Each Stored Procedure is represented by a C# Class.
I have a couple of code generation templates. This one uses Enterprise Library. The constructor will default to the default database specified by the Enterprise Library Data Access Application Block or allow you to choose a specific database:
CustOrdersDetail custOrdersDetail =
new CustOrdersDetail("Northwind");
As you might expect, any parameters required by the stored procedure will be converted to arguments in the various methods. The CustOrdersDetail takes an integer, orderId, that maps to the @OrderId parameter and can be used as follows to return a DataTable:
DataTable dt = custOrdersDetail.ExecuteDataTable(10248);
With the results in a DataTable as you would expect:
I see this particular useful for situations like mine where I have a lot of stored procedures particular to reporting. It would bore me to tears to manually deal with it, so leveraging code generation allows me to generate the classes in seconds. The fact that I have a template for Enterprise Library gives me quick support for multiple databases and a pretty robust data access helper.
Obviously it doesn't have to be just for reporting. The InsertOrderDetail Stored Procedure Wrapper will allow one to insert an Order Details Record:
InsertOrderDetail insertOrderDetail = new InsertOrderDetail();
insertOrderDetail.ExecuteNonQuery(10248, 14, 14m, 12, 1F);
Feels like a TableAdapter when used in such a way :)
There is also support for output parameters, too, etc.
It is times like these when code generation is a big hit in the data access layer.
Recent Code Generation Posts:
Source: David Hayden ( Florida .NET C# SQL Server Developer )
Filed: Code Generation