By: David Clark

May 31st, 2004

Reader Rating: 8

The Command objects in ADO.NET (such as OleDbCommand and SqlCommand) are a central aspect of the .NET database access strategy. When used properly, they provide excellent performance and security.

But, reaping these benefits requires that you first give .NET quite a bit of information about the query to execute and the exact parameters to be used. For this configuration, you can use any of several options, including the .NET IDE form designer itself (such as when you add an OleDbCommand or SqlCommand object directly to a form) or code, as in:

// Instantiate a new OleDbCommand object
 OleDbCommand cmdQuery = new OleDbCommand();

 // Configure the actual query string and set the type (Text vs. StoredProcedure)
 cmdQuery.CommandText = "SELECT * FROM Customer WHERE Id=@CustId";
 cmdQuery.CommandType = CommandType.Text;

 // Configure any parameters (variables) that need to be passed into the query
 cmdQuery.Parameters.Add("@CustId", OleDbType.Integer);

Both of these methods have a slight drawback. Hard-coding the Command configuration into a program means that every time you need to make the slightest change (maybe to change a database object name, increase the maximum length of a string parameter, fix a bug in a JOIN, etc.) you must modify your code, recompile, and redeploy -- a process that's not to be taken lightly.

One other option is to store configuration information about all your queries in a config file or settings database, and use this information at run-time, rather than hard-coding the exact properties of your query at design-time. This way, it's possible to decouple your database queries from your actual application, while still retaining the strong-typing benefits provided by properly building the Command objects. This allows you the flexibility of modifying your query definitions and having those changes take effect immediately, without a recompile or redeployment step. And, with the combination of a very basic XML syntax and parsing routine, this option can be a part of your toolkit.

The XML Syntax

After years of writing custom configuration files for my applications, I've finally realized (along with much of the rest of the world) that, for this purpose, XML provides far more functionality with far less effort. The syntax that I settled on for the query configurations is quite basic. Initially, I specify the actual text (or name) of the query and type (for which I use the values of "Text" versus "SP" or "StoredProcedure"), and possibly a timeout value (specified in seconds):

<query command='' type='' timeout='' />

This is followed by one or more optional parameter descriptions:

<param name='' type='' length='' default='' direction='' />

Technically, all of the information above is optional, but in practice the configuration string will be useless without certain pieces of information. In the initial query description, the "command" tag is required, and for a parameter description the "name" and "type" tags should always be specified.

So, how does this syntax work in practice? Well, suppose you have an MS SQL Server stored procedure with a definition such as:

GetCustomerList @StoreId int, @CustomerType varchar(50)

Imagine that this stored procedure is in the Northwind database. Your XML configuration string would look something like:

<query command='Northwind.dbo.GetCustomerList' type='SP'>
   <param name='retval' type='int' direction='return' />
   <param name='StoreId' type='int' />
   <param name='CustomerType' type='varchar' length='50' />
 </query>

Notice that I specified the fully-qualified name of the stored procedure (including database and owner) -- as an effortless performance enhancement, I try to always do this with my stored procedure calls (it saves SQL Server the effort of internally looking up exactly where the procedure resides). I also included the return value of the procedure as a parameter, which you may or may not need in your application. Unlike the other (input) parameters in this query, you can't set the value of "retval", but you will be able to read its value after you execute the query.

If you needed to override the ADO.NET timeout for your stored procedure (which, by default, is 30 seconds), you could modify the initial portion of the query configuration to read:

<query command='Northwind.dbo.GetCustomerList' type='SP' timeout='300'>

This would set the timeout for the query to 300 seconds, i.e. 5 minutes.

For a basic text query, i.e. anything that isn't a stored procedure, simply paste your entire query into the "command" attribute, like this:

<query command='SELECT * FROM Transactions
           WHERE TransType NOT IN ("Sales", "Service")' type='Text' />

If you need to, you can include parameters in this type of query, such as:

<query command='SELECT * FROM Transactions
         WHERE TransType=@TransType' type='Text'>
   <param name='TransType' type='varchar' length='50' default='Sales' />
 </query>

When the query executes, you can pass in the value of the "TransType" parameter that you want substituted into the query. The parameter has a default value of "Sales" so that, if you don't specify a value later in your code, this will be used instead.

Here's a pretty non-standard example, provided in case you ever need to issue a batch of statements (this example works in MS SQL Server), declaring variables, etc.:

<query command="
     IF @StoreId = 0 SET @BatchDesc = '_NoStore_';
     ELSE SET @BatchDesc = CAST(@StoreId AS varchar(25)) + '-';
     SET @BatchDesc = @BatchDesc + CONVERT(char(10), GETDATE(), 101);"
   type='Text'>
   <param name='StoreId' type='int' />
   <param name='BatchDesc' type='varchar' length='255' direction='output' />
 </query>

This query would take one parameter ("StoreId") and yield an output parameter ("BatchDesc") that concatenates the current date from the database server with the input "StoreId" value (or "_NoStore_" if the "StoreId" is equal to 0) into a single string. Certainly this is an odd example, but the idea is that any query (or group of queries) that you can write yourself, including logic, can be encapsulated in this format.

I should mention that, because the query configuration string will be parsed as XML, the < and > characters may give you a little trouble; you can get around this by simply encoding them as lt; and gt; respectively (as you would in HTML). So, instead of writing this:

<query command='SELECT * FROM Customer WHERE Id < 0' type='Text' />

You would write:

<query command='SELECT * FROM Customer WHERE Id < 0' type='Text' />

Parsing the XML into an OleDbCommand Object

Given the XML format described above for the query definitions, all that's left is to write a parser that will transform this information into a fully-configured OleDbCommand object in your actual software. The parser that I describe below is written in C# (though it should be simple to convert the code to other .Net languages, like VB.NET) and returns an OleDbCommand object. If you're dealing primarily with MS SQL Server and want to use the SqlCommand object instead, keep reading -- I'll get to that in a moment. The input to the parser is an XML string, as described above, and the return value is an instantiated and configured OleDbCommand:

public static OleDbCommand cmdGetCommandFromXML(string sXmlFragment)

By declaring the function static, you'll be able to call it without actually instantiating an object of any kind. Inside the function, the first thing you need to do is declare an OleDbCommand variable to serve as the target of the configuration that you're about to create:

OleDbCommand cmdResult = new OleDbCommand();

Next, you need to open an XML reader for your query definition -- this will give you an easy way to cycle through all the attributes and parameters. The XmlDocument and XmlNodeReader classes (found in System.Xml) provide a simple way to handle this, although they require a slightly more well-formed XML string than did our initial query definition. Luckily, simply enclosing the original XML within any additional tag will suffice:

XmlDocument xmlDoc = new XmlDocument();
 xmlDoc.LoadXml("<commanddetail>" + sXmlFragment + "</commanddetail>");
 XmlNodeReader reader = new XmlNodeReader(xmlDoc);

The XmlNodeReader class provides a Read() method that, in conjunction with a simple "while" loop and a few "case" statements, will allow us to cycle through the nodes of our XML, namely the <query /> and <param /> information. The basic loop will look something like this:

while (reader.Read())
{
 switch (reader.Name.ToLower())
 {
   case "query":  
     break;
   case "param":
     break;
   default:
     break;
 }
}

Now you have a simple construct for parsing the entire XML string into the different <query /> and <param /> tags, but how do you process the actual information contained in each? The XmlNodeReader provides two properties/methods, AttributeCount and MoveToAttribute, which will help in this.

The AttributeCount property tells you how many attributes are in the current node; for example <query command='Q' type='SP' /> has two attributes: command and type. The MoveToAttribute will allow you to advance the XmlNodeReader along in the XML to that particular attribute, and get the value(s). For the <query /> tag, we know we have to process, at most, the command, type, and timeout tags, so that section of code becomes:

case "query":
 for (int i = 0; i < reader.AttributeCount; i++)
 {
   reader.MoveToAttribute(i);
   switch (reader.Name.ToLower())
   {
     case "command":
       cmdResult.CommandText = reader.Value;
       break;
     case "type":
       cmdResult.CommandType = (reader.Value.ToString().ToLower() == "text") ? CommandType.Text : CommandType.StoredProcedure;
       break;
     case "timeout":
       cmdResult.CommandTimeout = Int32.Parse(reader.Value.ToString());
       break;
   }
 }
 break;

Processing the <param /> information is a little more complex, mainly because there are more attributes to account for (name, type, length, default and direction). And for the type and direction attributes, the values in our incoming XML have to be mapped to actual constants in .NET (ex: a type of int must be converted to the value of the constant OleDbType.Integer, while a direction of return must be interpreted as ParameterDirection.ReturnValue). This next section of code shows the basics for handling this:

case "param":
 OleDbParameter curParm = new OleDbParameter();

 for (int i = 0; i < reader.AttributeCount; i++)
 {
   reader.MoveToAttribute(i);
   switch (reader.Name.ToLower())
   {
     case "name":
       curParm.ParameterName = "@" + reader.Value.ToString();
       break;  
     case "type":
       // Convert the string representation of this data type to
       // an actual OleDb type constant
       switch (reader.Value.ToLower())  
       {
         case "int":
           curParm.OleDbType = OleDbType.Integer;
           break;
         case "tinyint":
           curParm.OleDbType = OleDbType.TinyInt;
           break;
         case "varchar":
           curParm.OleDbType = OleDbType.VarChar;
           break;
         default:
           break;
       }
       break;
     case "length":
       curParm.Size = Int32.Parse(reader.Value.ToString());
       break;
     case "default":
       curParm.Value = reader.Value;
       break;  
     case "direction":
       // Convert the string representation of this value
       // into an actual ParameterDirection constant
       switch (reader.Value.ToLower())
       {
         case "return":
           curParm.Direction = ParameterDirection.ReturnValue;
           break;
         case "output":
           curParm.Direction = ParameterDirection.Output;
           break;
         case "inputoutput":
           curParm.Direction = ParameterDirection.InputOutput;
           break;
         default:
           break;
       }
       break;
     default:
       break;
   }
 }

 cmdResult.Parameters.Add(curParm);
 break;

To save space, I didn't specify each of the possible data types that can be handled by the OleDbCommand. But, using the examples above for int, tinyint, and varchar as patterns, you should be able to see how easy it would be to expand the code to handle the full suite of OleDbType values.

SqlCommand vs. OleDbCommand

If you happen to be working in an MS SQL Server environment, and want to get the best performance out of your ADO.NET database access, then you'd be better off to use the SqlCommand object, rather than the OleDbCommand. To take the parsing routine provided above, and to build an alternate version which will configure a SqlCommand object, is pretty easy. In fact, once you change the OleDbCommand types to SqlCommand, you can basically do a search-and-replace on the rest of the code to change Ole to Sql. After that, the only thing you need to do is rewrite the type switch for the <param /> node so that it uses actual SqlDbType values instead of OleDbType (e.g. SqlDbType.Int instead of OleDbType.Integer).

Example Usage

Well, this covers how to build the XML query definitions and how to parse them, but you may still be wondering how actually to put this into practice. As I mentioned previously, one option is to store the query definitions in a database and have one query hard-coded into your application to retrieve them at run-time. An alternate option is to use the web.config or app.config file for your .NET Web or desktop application to store this information. For instance, consider the following query definition:

<query command='SELECT * FROM Customer WHERE Id = @CustId' type='Text'>
   <param name='CustId' type='int' />
 </query>

Given this, you can make the following addition to the <appSettings> section in the *.config file to represent the query (after first choosing a name by which to refer to it, in this case, GetCustomer):

<?xml version="1.0" encoding="utf-8"?>
 <configuration>
   <appSettings>
     <add key="GetCustomer" value="
     <query command='SELECT * FROM Customer WHERE Id = @CustId' type='Text'>
       <param name='CustId' type='int' />
     </query>
     " />
   </appSettings>
 </configuration>

Note the conversion of all < and > to lt; and gt; respectively. The embedding of an XML string within what is itself an XML document can be a little tricky at times. In fact, if your query actually uses "less than" or "greater than" in the body of its text, you would have to encode it even further: < would become < and > would become >. Given this difficulty, if you decide to use the *.config file method, you may want to write a utility application to convert back and forth between the encoded XML and the standard format.

Now, to use this information from the *.config file at run-time, you can use the System.Configuration.AppSettingsReader object to first retrieve the XML configuration string, using the name that you originally chose to represent the query definition (GetCustomer). Then, make a call to the parsing class we built above to configure the Command object, like so:

// Retrieve the XML query configuration string
 System.Configuration.AppSettingsReader rdr = new
   System.Configuration.AppSettingsReader();
 string sQueryDef = (string) rdr.GetValue("GetCustomer",  
                       System.Type.GetType("System.String"));

 // Instantiate and configure the OleDbCommand object
 OleDbCommand cmdQuery = OleDbDataUtils.cmdGetCommandFromXML(sQueryDef);

 // Set the value of any parameter(s), for example:
 cmdQuery.Parameters["@CustId"].Value = 10;

 // Set the database configuration, and use the cmdQuery object to populate
 // a DataReader or DataSet, etc.

Conclusion

When it comes to database-driven applications, the way you build queries into your software can have a large impact on how quickly you can adapt to change. In compiled applications, this is especially true. Every time a flaw is discovered, a database change is made, or even just an improvement is found, the software must be adjusted, compiled and deployed. If you're lucky enough to use a database system that supports stored procedures then your problem is greatly reduced -- but not eliminated! In this case, the only time you need to compile and deploy software is when a stored procedure's name or parameter definition changes.

The flexibility and ease of maintenance of your application can be improved by decoupling your database queries from the rest of the code – and this can be achieved without greatly sacrificing performance or security. Whatever method you choose for storing your configuration information (whether in a config file, or a settings database), using a syntax and parser similar to the one I've described here means that many modifications will require only a small change to the configuration information, and possibly a restart of your application. That equates to easier maintenance and long-term time-savings -- something I doubt many developers would complain about!