Introduction
It is generally a good idea to create a wrapper method for every stored procedure that an application needs to call. Such methods can then be grouped into a single data access utility class. This approach improves type safety and portability.
These methods generally call the Command.ExecuteScalar, Command.ExecuteNonQuery and Command.ExecuteReader ADO.NET methods. They also perform tasks like checking if the Connection object is still connected, adding stored procedure parameters to the Command object, making sure the DataReader object is properly disposed, etc.
On the average, there are about fifty lines of code for a properly written method that wraps an ExecuteReader call! Writing these methods easily eats into overall development time on data-intensive projects that access many stored procedures.
Developers usually resort to copying and pasting code from other wrapper methods and modifying the code to suit the stored procedure call. This process often leads to bugs due to human error.
I figured that since most of these methods share a common programming pattern; it should be possible to describe what your stored procedure looks like to a code generation tool and have the tool generate these methods. Nothing as complex as AutoSproc, just a light tool that lets a developer specify details of the stored procedure, and then generate the wrapper method code.
Implementation
This tool was developed with ASP.NET 2.0. It makes code generation decisions based on information provided by the user – pretty much the same way a human developer would make coding decisions.
It supports .NET 1.1 and .NET 2.0 features, for instance it would create nullable variables for nullable fields if .NET 2.0 is selected. It supports the SQL Server and ODBC data providers.
The actual code generation code (no pun intended) is in APP_Code\MethodGen.cs while the user interface code is in the sprocmethodgen.aspx.
The code generation code can easily be used by another application with a different user interface (for instance, a desktop-application that supplies most of the input from the actual database schema). It can also be easily modified to follow a different programming pattern or support more ADO.NET features.
The meat of the code generation code lies in the GenerateMethod, GenerateTryClause and GenerateResultsWrapperClass methods of the MethodGenerator class. The GenerateMethod method generates the non-varying parts of the method such as sections that add parameters to a command object. It also calls the GenerateTryClause method and optionally the GenerateResultsWrapperClass method.
The GenerateTryClause method generates the big try clause in the method which varies greatly, depending on what type of execution was selected.
The GenerateResultsWrapperClass method generates a class which stores results returned by a DataReader. (It’s better to return a list of strongly typed objects than returning a DataTable.)
Using the Tool
This example uses the ‘Sales by Year’ stored procedure in the Northwind database.
1) Run the ASP.NET solution, and navigate to the web page.
2) Specify the .NET Version, Data Provider, Stored Procedure name, and Type of Execution.
3) The Sales by Year stored procedure has two input parameters, so specify the two input parameters:
4) After running the stored procedure, it is discovered that it has four columns in the result set, so specify the four result columns:
5) Specify the name of the class that will store the results and the name of the generated method.
6) Click the Generate Code! button to generate the code. You may need to scroll down on the page.
(To view the generated code, click the expand source button below.)
internal static List<SalesInfo> GetSalesByYear(DateTime StartDate, DateTime EndDate, SqlConnection DBConn )
{
//TODO: Insert method into your data access utility class
//Check if connection is null
if(DBConn == null)
{
throw new ArgumentNullException("DBConn");
}
//Open connection if it's closed
bool connectionOpened = false;
if(DBConn.State == ConnectionState.Closed)
{
DBConn.Open();
connectionOpened = true;
}
//TODO: Move constant declaration below directly into containing class
const string sprocGetSalesByYear = "[Sales by Year]";
string sproc = sprocGetSalesByYear;
SqlCommand cmd = new SqlCommand(sproc,DBConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Beginning_Date",SqlDbType.DateTime ).Value = StartDate;
cmd.Parameters.Add("@Ending_Date",SqlDbType.DateTime ).Value = EndDate;
List<SalesInfo> result = new List<SalesInfo>();
SqlDataReader rdr;
try
{
rdr = cmd.ExecuteReader();
try
{
if (rdr.HasRows)
{
int shippeddateOrdinal = rdr.GetOrdinal("ShippedDate");
int orderidOrdinal = rdr.GetOrdinal("OrderID");
int subtotalOrdinal = rdr.GetOrdinal("Subtotal");
int yearOrdinal = rdr.GetOrdinal("Year");
while (rdr.Read())
{
// declare variables to store retrieved row data
DateTime? shippeddateParam;
int orderidParam;
decimal subtotalParam;
string yearParam;
// get row data
if (rdr.IsDBNull(shippeddateOrdinal))
{
shippeddateParam = null;
}
else
{
shippeddateParam = rdr.GetDateTime(shippeddateOrdinal);
}
orderidParam = rdr.GetInt32(orderidOrdinal);
subtotalParam = rdr.GetDecimal(subtotalOrdinal);
if (rdr.IsDBNull(yearOrdinal))
{
yearParam = null;
}
else
{
yearParam = rdr.GetString(yearOrdinal);
}
// add new SalesInfo object to result list
result.Add(new SalesInfo(shippeddateParam,orderidParam,subtotalParam,yearParam));
}
}
}
finally
{
rdr.Close();
}
}
catch(Exception ex)
{
//TODO: Handle Exception
throw ex;
}
finally
{
cmd.Dispose();
if(connectionOpened ) // close connection if this method opened it.
{
DBConn.Close();
}
}
return result;
}
[Serializable]
public class SalesInfo
{
//TODO: Integrate this class with any existing data object class
private DateTime? shippeddate;
private int orderid;
private decimal subtotal;
private string year;
public SalesInfo(DateTime? ShippedDate, int OrderID, decimal SubTotal, string Year)
{
shippeddate = ShippedDate;
orderid = OrderID;
subtotal = SubTotal;
year = Year;
}
public SalesInfo()
{
shippeddate = null;
orderid = 0;
subtotal = 0;
year = null;
}
public DateTime? ShippedDate
{
get { return shippeddate; }
set { shippeddate = value; }
}
public int OrderID
{
get { return orderid; }
set { orderid = value; }
}
public decimal SubTotal
{
get { return subtotal; }
set { subtotal = value; }
}
public string Year
{
get { return year; }
set { year = value; }
}
}
7) Copy the generated code into your project.
8) Add the following namespaces to your project.
using System.Data;
using System.Data.SqlClient; //if using SQL Server Data Provider
using System.Data.Odbc; //if using ODBC Provider
using System.Collections.Generic; //if using .NET 2.0 or later
using System.Collections //if using .NET 1.1
9) Look for //TODO: comments in the generated code and act accordingly. The code will still work, even if the //TODO: comments are ignored.
10) Now you can simply access the sales data from your project with the following statements:
//Create Sql connection
SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True");
//Get Sales Information
List<SalesInfo> sales = GetSalesByYear(new DateTime(1992,1,1),new DateTime(2008,1,1),conn);
Considerations and Limitations
Obviously, this tool cannot generate code for every conceivable ADO.NET database access scenario, however, it’s a lot better to generate a great deal of the code and then modify the code as needed, than to type everything by hand.
Some limitations of this tool include:
Generates only C# code: The best way to make this tool to be language neutral would be to use CodeDom to generate the code, however, this approach would make this tool harder to maintain and extend – It would be an overkill for the scope of this project.
Fortunately, there are lots of C#-to-VB.NET code conversion tools available for VB.NET developers who would like to use this tool.
Lacks support for Output Parameters: This tool only supports input parameters and optionally returns the stored procedure return parameter. The generated code can be manually modified to accommodate other types of parameters.
Lacks support for OLEDB and Oracle Data Providers: This tool only generates code for ODBC and SQL Server Data providers.
Reads only the first Result Set: If your stored procedure returns multiple
result sets, one way to handle this is to generate a method for the first result
set (choose ExecuteReader), then generate another method as if the second result set were actually the first
result set, copy the code that reads the result data and paste it into the first method after calling rdr.NextResults(), change the name of the results variable in the pasted code and pass it back as an out parameter.
Do this for every result set returned.
Lacks support for DbCommand object properties: If you are looking for Transaction, CommandTimeOut, CommandBehavior objects etc. -- It’s easy to modify the generated code for these properties.
Unsuitable for Large Result sets: This tool generates code which returns result sets as an ArrayList or
an List of strongly-typed objects. This tool will perform poorly if your stored procedure returned hundreds of thousands of rows because it would have to store all these rows. You should write your own data access method for such scenarios.
Moreover, if your stored procedure returns hundreds of thousands of rows, I recommend you look into implementing some kind of paging mechanism to reduce the number of rows returned.