Introduction
Most systems that use Stored Procedures make use of SQL parameters to send and receive information from the database. The code to create these SQL parameters and then populating them is repeated throughout the system. This article describes how to automate this supposedly boring but important task of coding for developers.
Background
Most data driven applications make use of Stored Procedures and their benefits. As the number of tables grow, so too the Stored Procedures. Some of the procedures may even have more than 10 parameters. Since creating and populating them is always required, we end up having lots of code that just does this. Worst still is the copy-paste errors that occur by copying one set of parameters into another and forgetting to change the data type, and so on. Developers should be freed from doing this type of code repetition.
This article addresses this issue, and is primarily meant for architects or technical leads who develop frameworks from which applications are built.
This article uses SqlParameters for SQL Server, and the example is a web application, but the classes can be used with Windows applications as well. The equivalent of SqlParameters is OleDbParameter, and the porting can be done for other data sources with ease.
Using the Code
The code can be readily used in the Business Logic Layer of systems implementing an N-Tier architecture. The SqlParametersCodeGen class together with the AppConfiguration class (attached source) can be used to obtain the parameters or for obtaining and assigning values to them. These should be part of the core module of the framework.
Parameter Configuration
To implement this, we need to create an XML configuration file which would tell us the different sets of parameter arrays present. We name the configuration as .config to benefit from ASP.NET handlers which do not serve this type of file requests.
<SqlParameterItems>
<SqlParameterItem id="ExceptionManager_ExceptionLog_Retrive_ParametersArray">
<SqlParameters>
<SqlParameter name="@ExceptionID" type="Int" />
<SqlParameter name="@ExceptionSource" type="VarChar" size="15" />
</SqlParameters>
</SqlParameterItem>
<SqlParameterItem id="AccountManager_Users_Create_ParametersArray">
<SqlParameters>
<SqlParameter name="@Username" type="VarChar" size="50" />
<SqlParameter name="@Password" type="VarChar" size="50" />
</SqlParameters>
</SqlParameterItem>
</SqlParameterItems>
The ID is usually the name used in Stored Procedures with the addition of ParametersArray. This can depend on your naming convention. The size is a required field for VarChar and Char etc., but not required for data types that don't require a size to be specified. The type name should be the same as in SqlDBType. Refer to SqlDBType for the naming convention for other types.
Many possibilities exist for generating this config. You can add it manually, or have a generator read all Stored Procedures from the database and generate it at start-up and so on. You can also generate simple Stored Procedures after entering the values in the config file.
It is possible to use a parameters array for more than one Stored Procedure having the same signature, but care should be taken that the original author does not change the signature, which would break the reuse.
With regard to OUTPUT parameters, I am sorry I am not a big fan of them, and prefer selecting the value(s) in a row. You can customize this if that is required.
Web.Config or App.Config Entry
Now that we have declared the different sets of parameter arrays, we now have to make this file available for the application. We do this by adding an entry in either of the above files. Here, I have used Web.Config.
<appSettings>
<add key="SqlParameterItems_SettingsFile"
value="C:\Inetpub\wwwroot\YourWebSitePath\Config\SqlParameters.config"/>
<add key="SqlParameterItems_SettingsFile_QA"
value="C:\Inetpub\wwwroot\YourWebSitePath\Config\SqlParameters.config"/>
<add key="SqlParameterItems_SettingsFile_Dev"
value="C:\YourWebSitePath\Config\SqlParameters.config"/>
</appSettings>
AppConfiguration handles this type of entry, and an explanation of this can be found in my previous article.
SQL Parameter Code Generator
After the settings have been made. SqlParametersCodeGen can be used. This class essentially loads the XML configuration, and caches it to the ASP.NET cache with this file dependency if it exists (so that it can be used in Windows applications too; we can customize this caching logic). When a request is made for the parameters array identified by its key, it loads those nodes and returns the parameters collection. If an assignment is also required, it creates the parameters array and then assigns the values from Hashtable or DataRow inputs. The various uses of methods for using the above AccountManager_Users_Create_ParametersArray are:
For obtaining the SQL parameter array collection:
SqlParameter[] parUser =
SqlParametersCodeGen.GetParameters(
"AccountManager_Users_Create_ParametersArray");
parUser[0].Value = username;
parUser[1].Value = password;
For assigning the SQL parameter array with a Hashtable. The Hashtable key should match the parameter name without the @ symbol.
Hashtable data = null;
data = new Hashtable();
data.Add("Username", txtUsername.Text);
data.Add("Password", txtPassword.Text);
SqlParameter[] parUser =
SqlParametersCodeGen.AssignParameters(
"AccountManager_Users_Create_ParametersArray", data);
For assigning the SQL parameter array with a DataRow:
SqlParameter[] parUser = null;
DataRow userDataRow = null;
userDataRow = data.Tables["User"].Rows[0];
parUser = SqlParametersCodeGen.AssignParameters(
"AccountManager_Users_Create_ParametersArray", userDataRow);
Points to Note
- At present, we have cached only the config file to reduce file IO. Caching of
SqlParameter[] is possible provided the cache is able to give a deep copy of the existing collection. The cache that comes with ASP.NET gives a live copy. Since a single SqlParameter[] can't be assigned to two SqlCommand objects, this cache can't be used. You need to implement a caching framework where you can specify if you want a copy of the reference before attempting to cache SqlParameter[]. This requires another article of its own. - I have given support for commonly used Data Types in SQL Server. If you require other data types, edit SqlParametersCodeGen.cs to add your type(s). You need to add in two methods:
private static SqlParameter[] GenerateParameters(XmlNode sqlParameterItemRoot) and private static SqlDbType GetSqlDBType(string columnType). - Methods are made
static because they are always used by the entire application, and provides faster and thread safe access.
History
- 18 July 2007: Initial version.
