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)
{
	this.name = 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
    {
        get;
        set;
    }

    [DatabaseField("bar2", 2)]
    public string Bar2
    {
        get;
        set;
    }
}

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();
	else
		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
			if(DataAccessProvider.Database.SupportsNamedStoredProcedureParameters)
				parameters[attributes[0].Name] = value;
			else
				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.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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