Contents
This article includes a utility to generate wrappers for stored procedures that improve performance and eliminate certain run-time errors, and a Framework for unit testing of the wrappers.
If you are using an ORM Framework for managing data persistence (like NHibernate
), then you don't have the problem this article tackles, but if your code has a hand-crafted data access layer and executes stored procedures with SqlDataReader
, then read on.
Hand written DAL code is repetitive and dull, so that alone is one reason to auto-generate it. But if you have followed advice from Microsoft, you may well be using run-time discovery of stored procedure parameters (for example, using their SqlHelper
class) before executing each command. OK, so the parameters are cached, but before the first use of each stored procedure, there is an extra database query to get the parameter array. This is likely to have a noticeable effect on performance, especially at start up, as each stored procedure is executed for the first time. A few tests show that cutting out the parameter discovery query can speed up the overall time for the first execution of a stored procedure by about a third.
Moreover, if there is a mismatch between the parameters the stored procedure uses and those that the DAL prepares, you can end up with run-time errors that are not found at compile time.
Similarly, run-time errors can occur when there is a mismatch between the number, name or type of output columns from the stored procedure and the columns the DAL expects.
This article provides a utility that generates wrappers for stored procedures that:
- Pre-codes the correct array of parameters (no need for run-time discovery of parameters, no run-time errors due to stored procedure/DAL mismatch)
- Pre-codes the correct set of output columns (no run-time errors due to stored procedure/DAL mismatch)
- Provides strongly typed properties to set input parameters and get output values
- Reduces lines of DAL code by encapsulating the basic mechanics of stored procedure execution
This article also includes a Framework for verifying that wrappers are still correct at build time using unit tests that verify each wrapper against its associated stored procedure by re-deriving the wrapper properties.
In this way, an automated build with units tests can carry out end to end verification of your DAL code with respect to a given database. So, if your stored procedure wrapper passed its unit test, and your DAL code builds against the stored procedure wrapper, then you know it will run without errors.
SqlProcedureWriter
utility - use this EXE to write the wrapper classes SqlProcedure
base class - include this in your DAL code SqlOutputField
class - include this in your DAL code SqlTypeMapper
helper class to map SQL types to C# types - include this in your DAL code SqlHelper
helper class from Microsoft - include this in your DAL code SqlProcedureTestHelper
unit test helper - include this in your suite of unit tests
Incorporating SqlProcedure
into your DAL follows this process:
- Create the stored procedure in the database
- Use
SqlProcedureWriter
to create a wrapper class for the stored procedure - Include the
SqlProcedure
wrapper into your DAL source and use it in your DAL methods - Include unit tests to verify the wrapper against the database
Do this however you want.
We need to generate the wrappers for your chosen stored procedures.
- Run SqlProcedureWriter.exe
- Click the button to connect to a database that contains the stored procedure(s) you want to create wrappers for
- Select the folder to contain the output wrapper classes
- Select the procedures to process, can be all in the database or a number of manually selected ones
- In either case, you can apply a regular expression filter to the procedures read from the database
- Specify the namespace that the output classes will belong to (this will probably be the namespace of your DAL code
- Add some
using
namespaces if required - Click Generate
You can call SqlProcedureWriter.exe with the following command line parameters:
-f="folder" | Output folder |
-n="namespace" | Namespace for output classes |
-u="using list" | List of namespaces to include in the using block (separated by ; ) |
-r="regex" | Regular expression filter to apply to procedure names |
-s="server" | Name of the SQL server to log into |
-d="database" | Name of the database to use |
-i | Integrated login |
-user="user" | Username for the database |
-pass="pwd" | Password for the database |
For example:
SqlProcedureWriter.exe -f="c:\tmp\wrappers" -ns="MyAppNS"
-u="System.Data" -s=(local) -d="MyDB" -i
Note
Please note that in order to determine the output fields, the stored procedure is executed. However, it is executed with all NULL
parameters, and is performed within a transaction that is never committed. It will therefore not make any changes to your database, but the procedure WILL be executed.
The SqlProcedure
wrapper now needs to be incorporated into the DAL and the DAL code written to use it.
The auto-generated wrapper has properties for the input parameters (that start with Param_
) and properties that hold the ordinal values for the returned fields (that start with Ordinal_
).
A lightweight nested class is also created to hold the data from the returned record. It has a strongly typed property for each output field.
The ToParameterString()
method is useful for logging or reporting exceptions, as it gives you the full details of the stored procedure call, including the input parameter values.
There are a number of other methods that work behind the scenes, but if you are interested, it is probably easier to look at the code.
A typical DAL method may look like this:
public AuthorInfo GetAuthor_OldSkool(string forename, string surname)
{
AuthorInfo author = null;
using (SqlConnection connection =
new SqlConnection(_ConnectionString))
{
SqlParameter[] parameters =
SQLHelperParameterCache.GetAndCacheSPParameters
(connection, "spAuthor_SEL_NAMES");
parameters[0].Value = forename;
parameters[1].Value = surname;
using (SqlDataReader reader = SQLHelper.ExecuteReader
(connection, CommandType.StoredProcedure,
"spAuthor_SEL_NAMES", parameters))
{
if (reader.HasRows)
{
#region Get column information
int colid = reader.GetOrdinal("id");
int colForename = reader.GetOrdinal("Forename");
int colSurname = reader.GetOrdinal("Surname");
#endregion
if (reader.Read())
{
#region Get data from each row in the database
int vid = reader.GetInt32(colid);
string vforename = reader.GetString(colForename);
string vsurname = reader.GetString(colSurname);
#endregion
author = new AuthorInfo();
author.id = vid;
author.Forename = vforename;
author.Surname = vsurname;
}
}
}
}
return author;
}
Once an SqlProcedure
wrapper has been used instead, it would look like this:
public AuthorInfo GetAuthor_UsingSqlProcedure
(string forename, string surname)
{
AuthorInfo author = null;
Author_SEL_NAMES procedure = new Author_SEL_NAMES(forename, surname);
if (procedure.ExecuteReadValue(connectionString))
{
author = new AuthorInfo();
author.id = procedure.Value.id;
author.Forename = procedure.Value.Forename;
author.Surname = procedure.Value.Surname;
}
return author;
}
To break this down. the steps required to use an SqlProcedure
wrapper are:
Include Classes
You will need to include the SqlProcedure
base class, SqlOutputField
and the SqlHelper
in your DAL.
Create an Instance
Each stored procedure has its own class with a name based on the procedure name. All the required input parameters can be supplied in the class constructor, using strongly types values.
Author_SEL_NAMES procedure = new Author_SEL_NAMES(forename, surname);
Set the Input Parameters
This can be done either using the constructor (see above), or the properties of the wrapper (both are strongly typed).
Author_SEL_NAMES procedure = new Author_SEL_NAMES();
procedure.Forename = forename;
procedure.Surname = surname;
Execute the Stored Procedure
You can execute the procedure by calling one of three methods supplied by the SqlProcedure
base class, depending on what you expect to be returned, just like if you are using SqlHelper
. This are:
ExecuteNonQuery
- no returned data ExecuteScalar
- returns a scalar ExecuteReader
- returns a result set
ExecuteNonQuery
Create your connection / transaction in the usual way, then call the ExecuteNonQuery
method on the SqlProcedure
wrapper. There is no return value.
using (SqlConnection connection = new SqlConnection(_ConnectionString))
{
proceudre.ExecuteNonQuery(connection);
}
ExecuteScalar
If you expect a scalar to be returned, then you can call one of a variety of ExecuteScalar
methods. If you are expecting an int
to be returned (e.g. returning @@IDENTITY
) then call ExecuteScalarInt
, otherwise call ExecuteScalar
and handle the returned object
yourself. Both have overloads to pass in an SqlConnection
or an SqlTransaction
.
using (SqlConnection connection = new SqlConnection(_ConnectionString))
{
int newId = procedure.ExecuteScalarInt(connection);
...
}
ExecuteReader
If the stored procedure is doing a SELECT
with multiple columns, then call ExecuteReadValue
or ExecuteReadValues
, depending on whether you are expecting a single row or multiple rows respectively.
Both methods have overloads to pass in a connection string or a connection object. I have assumed that no-one wants to execute a SELECT
within a transaction.
If there are no rows returned, then ExecuteReadValue(s)
just returns false
.
If there is data, then each returned record is represented by an instance of a nested data class that is also created by SqlProcedureWriter
. This class has strongly typed properties for each output field.
public class Author_SEL_NAMES_Values
{
public int id;
public string Forename;
public string Surname;
}
For a single returned row, you can use the Value
property of the wrapper that always holds the last record:
if (procedure.ExecuteReadValue(_ConnectionString))
{
author = new AuthorInfo();
author.id = procedure.Value.id;
author.Forename = procedure.Value.Forename;
author.Surname = procedure.Value.Surname;
...
}
But for multiple rows, you can iterate over the array of records using the Values()
method like this:
if (procedure.ExecuteReadValues(_ConnectionString))
{
foreach(Author_SEL_NAMES.Author_SEL_NAMES_Values v in
procedure.Values())
{
AuthorInfo author = new AuthorInfo();
author.id = v.id;
author.Forename = v.Forename;
author.Surname = v.Surname;
...
}
}
Your DAL will now be fully functional, but it is sensible to include a test in your build process that checks if the wrappers stay in sync with the procedures defined in the database. The tests will fail if any of the input parameters or output fields differ between the wrapper and the procedure defined in the database.
Include Classes
You will need to include the SqlProcedureTestHelper
class and the SqlHelper
in your DAL.
Write Test
Then you can write an NUnit test that will compare each SqlProcedure
wrapper against a database.
Use the SqlProcedureTestHelper.GetAllProcedures
method to create instances of all classes that inherit from SqlProcedure
in a given assembly, then for each of these, call SqlProcedureTestHelper.TestSqlProcedure
to carry out the comparison and perform NUnit Asserts on the various properties of the input parameters and output fields.
[Test]
public void TestAllProcedures()
{
SqlProcedure[] procedures = SqlProcedureTestHelper.GetAllProcedures
(Assembly.GetAssembly(typeof(SqlProcedure)),
"MyAssem", typeof(SqlProcedure));
foreach(SqlProcedure sp in procedures)
{
SqlProcedureTestHelper.TestSqlProcedure(myConnectionString, sp);
}
}
History
- 23rd November, 2007 - Initial release (1.0.0)