Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

SqlProcedure - Improve Database Performance, Eliminate Errors and Reduce Code

4.66/5 (16 votes)
23 Nov 2007CPOL8 min read 1   1.4K  
Provides a utility to generate a wrapper for stored procedures to improve performance and eliminate certain run-time errors
SqlProcedureWriter screenshot

Contents

Summary

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.

Background

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.

Pitfalls of a DAL

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.

Pitfalls of a DAL

The Solution

This article provides a utility that generates wrappers for stored procedures that:

  1. 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)
  2. Pre-codes the correct set of output columns (no run-time errors due to stored procedure/DAL mismatch)
  3. Provides strongly typed properties to set input parameters and get output values
  4. 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.

A better DAL

The Toolkit

  1. SqlProcedureWriter utility - use this EXE to write the wrapper classes
  2. SqlProcedure base class - include this in your DAL code
  3. SqlOutputField class - include this in your DAL code
  4. SqlTypeMapper helper class to map SQL types to C# types - include this in your DAL code
  5. SqlHelper helper class from Microsoft - include this in your DAL code
  6. SqlProcedureTestHelper unit test helper - include this in your suite of unit tests

The Process

Incorporating SqlProcedure into your DAL follows this process:

Overview of the SqlProcedure process
  1. Create the stored procedure in the database
  2. Use SqlProcedureWriter to create a wrapper class for the stored procedure
  3. Include the SqlProcedure wrapper into your DAL source and use it in your DAL methods
  4. Include unit tests to verify the wrapper against the database

1. Create the Procedure

Do this however you want.

2. Generate the Wrapper

We need to generate the wrappers for your chosen stored procedures.

Using the GUI

  1. Run SqlProcedureWriter.exe
  2. Click the button to connect to a database that contains the stored procedure(s) you want to create wrappers for
  3. Select the folder to contain the output wrapper classes
  4. Select the procedures to process, can be all in the database or a number of manually selected ones
  5. In either case, you can apply a regular expression filter to the procedures read from the database
  6. Specify the namespace that the output classes will belong to (this will probably be the namespace of your DAL code
  7. Add some using namespaces if required
  8. Click Generate

Command Line

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:

plain
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.

3. Include SqlProcedure in your DAL

The SqlProcedure wrapper now needs to be incorporated into the DAL and the DAL code written to use it.

The Wrapper Class

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.

Class diagram for a SqlProcedure wrapper

DAL Code

A typical DAL method may look like this:

C#
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:

C#
public AuthorInfo GetAuthor_UsingSqlProcedure
    (string forename, string surname)
{
    AuthorInfo author = null;

    // Create an instance of our SqlProcedure wrapper
    // Use the constructor to provide the input parameters
    Author_SEL_NAMES procedure = new Author_SEL_NAMES(forename, surname);

    // This call sets the values of Value.Forename etc. 
    // to the values returned by the SP
    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.

C#
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).

C#
// An alternative to the above example
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.

C#
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.

C#
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.

C#
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:

C#
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:

C#
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;
        ...
    }
}

4. Unit Testing

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.

Class diagram for SqlProcedureTestHelper

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.

C#
[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)

License

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