Introduction

This article touches on some concepts that most developers would not have used, but would prove to be very useful when developing applications using a data source that does not support stored procedures (or when requiring additional flexibility beyond that which is currently available – see “Future Upgrades” at the end of this article). It involves using an XML configuration file to develop a custom stored procedure framework for your .NET application.

Scenario

I recently had to design & develop an application that used a CA-Datacom database, which only runs on an IBM Mainframe. Due to this fact, the database is obviously geared towards the Mainframe developer rather than the Windows developer. One main drawback of using this database was that it only supports stored procedures that are written in SQL and embedded within a COBOL program. Unfortunately, due to severe time constraints and lack of available COBOL skills, I was unable to make use of stored procedures within CA-Datacom.

I decided that a stored procedure framework was necessary to gain as much flexibility as possible and allow creation of new stored procedures, as well as modifications to stored procedures post go-live (if necessary). The rest of this article explains how I went about doing this (considering the lack of suitable resources available on the net to help me out).

Documenting the Stored Procedures

I battled to find any kind of article on the net that dealt with this issue, and so was left largely to do it alone. I did manage to find one other resource on CodeProject, which helped a lot. I’ve referenced this article here so you can see what parts of it I’ve used. I took the basic shell provided by the CodeProject article and adapted it to suit my needs so you will definitely see a few similarities, although this solution is completely different in many respects.

The stored procedures need to be documented in XML to give a simple, flexible infrastructure for modification and maintenance. Hard-coding stored procedures is painful when changes or additions come along (needless to say highly not recommended), so I decided to avoid this at all costs. I did not want to recompile purely because of a stored procedure change!

This is a code snippet of how the stored procedures were documented in the app.config file:

Note that the stored procedures are contained as DataOperation elements within an array. This allows multiple DataOperations to be coded and used in whatever way you see fit. Each DataOperation has a Name, Type, CommandText, and a series of Parameters, which are optional. Each Parameter then also has a Key (name), Type, Size, and Direction. This is a very simple kind of stored procedure, and as you can assume, this framework can easily be extended to support more complex requirements. The Size attribute of the DataParameter element has the same rules applied as the OdbcParameter.Size property. As per documentation in MSDN:

For variable-length data types, Size describes the maximum amount of data to transmit to the server. For example, for a Unicode string value, Size could be used to limit the amount of data sent to the server to the first one hundred characters.

For fixed-length data types, the value of Size is ignored. It can be retrieved for informational purposes, and returns the maximum amount of bytes the provider uses when transmitting the value of the parameter to the server.

Onto the Class Framework...

The rest of this article deals with the classes (written in C#) that forms the framework from which these stored procedures can be called. I won’t go through all the code as a lot of it is very self-explanatory and I’m sure you don’t want to know simple things, like how to do operator overloading or custom conversions.

Note: Custom conversion was implemented in a previous version of this framework, but has been removed due to lack of support when using serialization.

The framework consists of a handful of classes, most of which are a code representation of the XML as documented above, or just general utility classes. Here’s a basic rundown of the classes and their purpose:

The StoredProcedures Class

This class is composed entirely of static methods to make usage of the class as simple as possible. I’ll go through the code regions (#region… #endregion) and explain what each region does, going into a little detail wherever necessary.

Fields

The class has only one static field, which is a DataOperationsCollection. This field stores the collection of DataOperations as defined in the app.config file.

Load Methods

This is a very simple method that merely loads the DataOperationsCollection field based on the configuration file, by calling the ConfigurationSettings.GetConfig(“configSection”) method.

Command Methods

This method accepts a command name as a parameter and returns an OdbcCommand. This entire framework is geared around an ODBC data source, as I assume that most OleDb data sources support the use of stored procedures (although it could be useful for MS Access). It can be very easily modified to support OleDb (or any other data source for that matter) if need be. The GetCommand method loops through all DataOperations in the DataOperationsCollection until it finds an operation with the name supplied by the caller. It then casts the DataOperation to an OdbcCommand (using a custom conversion method in the DataOperation class).

Utility Methods

This region contains two very similar methods. One attaches an array of OdbcParameters to an OdbcCommand, and the other adds parameters to an OdbcCommand based on an array of object parameters.

There is also a Deserialize method that accepts a file name and returns a DataOperationsCollection. This is responsible for deserializing the XML file in which all the stored procedures are documented into a DataOperationsCollection for use within the framework.

An earlier version of this framework had all stored procedures documented directly in the app.config (or web.config for ASP.NET applications), but this was a severe limitation to the flexibility that one could leverage. I decided to get rid of the custom conversion method which allowed conversion to an OdbcCommand by using (OdbcCommand)obj to do the casting, and rather implement a far superior XML serialization model. The current version of the framework now has all stored procedures documented in a separate XML file that can be stored anywhere on the server (or anywhere on the network for that matter). The app.config (or web.config) now has a single entry that holds the location of the stored procedures file so that it can be deserialized. I intend on building a Developer’s Tool that can be used to graphically create DataOperations and DataParameters, and then have these automatically serialized for you. That would save the job of having to code them manually (although it doesn’t take too long once you’ve got it going).

ExecuteNonQuery, ExecuteScalar, ExecuteDataset

These three code regions imitate the Data Access Application Block functionality by providing a simple way of interacting with the class. All three method types are essentially the same, so I’ll just explain briefly how they all work. The methods are called by passing in either an OdbcConnection or an OdbcTransaction, a command name, and a params array of either objects or OdbcParameters. An OdbcCommand is created by calling the GetCommand method, and it is then associated with the connection. The parameters are attached via the utility methods, and the connection is opened (unless it is already in the Open state). The appropriate method type (NonQuery, Scalar, or Dataset) is then executed and the results returned to the caller. Due to the fact that these methods can only be called with an OdbcConnection or an OdbcTransaction, I’ve forced the caller to manage the lifetime of these objects. I do check whether the connection has been opened or not, but do not close the connection before exiting the method. This is due to the fact that the connection object was created outside of the framework, and closing a connection can cause problems later down the line. So for now the caller is responsible for closing connections.

How to use it

Once you’ve set up the framework and added your stored procedures to the XML file, you’re ready to use it in your main application. So how do you go about calling one of your stored procedures??? Here’s a code sample, based on the XML snippet at the top of this article:

Firstly you need to get the stored procedures from the XML file to the StoredProcedures class. Very simply add the following line of code to your WinForms constructor:

You can add this anywhere you want, so long as you call it before you try to call one of the ExecuteXXX methods.

The following code snippet is used to call a stored procedure called “TestCommand2” and passes in an int parameter of 5.

You’ll need to modify this code to work with a database that you’ve configured, but the concept stays the same. It’s extremely simple to call your stored procedures. If you’re familiar with the MS Data Access Application Block, then the learning curve to use this will be virtually non-existent!

The End

I’m sure you can see that there is vast room for improvement within this framework, but it’s just a starting point for now and I’ll hopefully do some revisions to it in the near future. This is not a final project! It is still under development and has not undergone code optimization or any kind of performance review. Any suggestions or comments are very welcome! Please let me know where I can improve on this! Please submit comments, criticism, queries, suggestions, etc. and I’ll be sure to respond!