Firebird Stored Procedure parameters order: implementing in the DAL

So the fun continues. In my previous post I wrote that Firebird Stored Procedure (SP) parameters must be passed in the correct order, as they’re defined in the procedure itself. However, the example I gave with a class that uses attributes to map class properties to SP parameters was not quite complete. As I started to explore this issue further, I came across MSDN documentation of Type.GetProperties(BindingFlags) method. What’s important is:

The GetProperties method does not return properties in a particular order, such as alphabetical or declaration order. Your code must not depend on the order in which properties are returned, because that order varies.

Hmm, nice. I use that method to loop through all properties of a type and put them into a dictionary, which is later used to build the SP parameters. However, since Firebird doesn’t support named SP parameters, I had to implement some way to ensure that SP parameters are built in the correct order. So here’s what I did.

First, the DatabaseFieldAttribute, that I’ve shown in the previous post, was extended with a new property “Position”, which is an integer, and with a new constructor:

/// <summary>
/// Gets the position at which property value should be passed to Stored Procedure.
/// Used for databases that don't support named Stored Procedure parameters.
/// </summary>
public int Position
	get { return position; }

public DatabaseFieldAttribute(string name, int position)
{ = name;
	this.position = position;

The class I used before would now look like this:

public class Foo
    // this attribute tells that property should be passed to stored procedure with name "bar1"
    [DatabaseField("bar1", 1)]
    public int Bar1

    [DatabaseField("bar2", 2)]
    public string Bar2

I have a Data Access Layer, which I want to remain as database-independent as possible. There’s an abstract class that must be implemented for different database types (Firebird, MsSql, MySql, etc.). It was extended with a new property “SupportsNamedStoredProcedureParameters”:

Class Diagram

Now to create a dictionary of parameters, I use that new property to determine how should the list be built:

public static IDictionary CreateParameters(object source)
	IDictionary parameters = null;

	if (DataAccessProvider.Database.SupportsNamedStoredProcedureParameters)
		parameters = new Dictionary();
		parameters = new SortedDictionary();

	foreach (PropertyInfo property in source.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.GetProperty))
		// if more than one attribute is declared, use the first only
		DatabaseFieldAttribute[] attributes = (DatabaseFieldAttribute[])property.GetCustomAttributes(typeof(DatabaseFieldAttribute), true);

		if(attributes.Length &gt; 0)
			object value = property.GetValue(source, null);

			if (value == null)
				value = DBNull.Value;

			// if database supports named stored procedure parameters, user attribute's name
			// else - use attribute's position
				parameters[attributes[0].Name] = value;
				parameters[attributes[0].Position] = value;

	return parameters;

So, if database supports named SP parameters – parameter names are used for dictionary key and it doesn’t matter in which order you pass them. Otherwise, the newly created Position property of DatabaseFieldAttribute is used as a key for a SortedDictionary, so that parameters are sorted by the position. The dictionary key is always used as a parameter name when building DbParameter parameters for SP in DAL, but since database doesn’t support named parameters – the name doesn’t matter. So the Data Access Layer got extended without actually making it dependent on any specific type of database 🙂

The only downside of this solution is that you must manually fill the Position property values in attribute declaration.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s