SQL Server 2008 spatial data with BLToolkit (cont’d)

Right after I published the previous post on the spatial data and BLToolkit components I decided to write a follow-up on that post. I wasn’t happy about the way I described the implementation of the new property and field attribute to support the SQL Server UdtTypeName property and spatial data types. I hope I will make it more clear how I accomplished the support for this here. Please read the previous post about this on, .

The goal

The goal in this article is to describe the changes made to add support for the following code to work, that will fail with an exception on lines 28, 40 and 69:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Types;
using ExBLToolkit.DBTypes;
using BLToolkit.DataAccess;
namespace ExBLToolkit
{
    class AntennasGeomTester
    {
        public void AddSomeAntennas()
        {
            // Create the query using the template constructor
            SqlQuery query = new SqlQuery();

            // Create an SqlGeometry object
            SqlGeometry antennaALocation = SqlGeometry.Parse("POINT(111.222 333.444)");

            // Create and initiate an antenna
            AntennaWithGeometry antennaA = new AntennaWithGeometry()
            {
                Name = "Antenna A",
                Location = antennaALocation
            };

            // Insert the antenna into the database
            query.Insert(antennaA);

            //
            // Do it all over again with another antenna
            //
            SqlGeometry antennaBLocation = SqlGeometry.Parse("POINT(222.111 444.333)");
            AntennaWithGeometry antennaB = new AntennaWithGeometry()
            {
                Name = "Antenna B",
                Location = antennaBLocation
            };

            query.Insert(antennaB);
        }

        public List GetAntennas()
        {
            // Create the query using the template constructor
            SqlQuery query = new SqlQuery();

            // Select all antennas in the database
            List antennas = query.SelectAll();

            return antennas;
        }

        public bool UpdateAntenna(int antennaID, SqlGeometry newLocation)
        {
            // Create the query using the template constructor
            SqlQuery query = new SqlQuery();

            // Get the antenna to be updated
            AntennaWithGeometry antenna = query.SelectByKey(new object[] { antennaID });

            if (antenna != null)
            {
                // Give the antenna a new location
                antenna.Location = newLocation;

                // Invoke the update query;
                // return true the antenna was updated
                return query.Update(antenna) > 0;
            }
            else
            {
                return false;
            }
        }
    }
}

The antenna class looks like this, including the attribute UdtTypeName to be implemented:

public class AntennaWithGeometry
    {
        [MapField("ID"), PrimaryKey, NonUpdatable]
        public int AntennaID { get; set; }

        public string Name;

        [MapField("Position"), UdtTypeName("geometry")]
        public SqlGeometry Location;
    }

The work performed is aimed to add an extension to the BLToolkit using the SQL Server as DBMS, but still adaptions and extensions to other DBMS’s should be fairly easy as long as I don’t cut to many corners.

Before we start, let’s recap the UdtTypeNameAttribute class, a little bit modified since the previous post on this subject:

using System;
namespace BLToolkit.Mapping
{
    [AttributeUsage(AttributeTargets.Field | AttributeTargets.Property)]
    public class UdtTypeNameAttribute : Attribute
    {
        private readonly string _udtTypeName;
        public string UdtTypeName
        {
                get { return _udtTypeName; }
        }

        public UdtTypeNameAttribute(string udtTypeName)
        {
            _udtTypeName = udtTypeName;
        }
    }
}

The process

To get enough knowledge about how the BLToolkit generates its queries I debugged the code generating the actual SQL query. I started by debugging the generation of the insert query and ended up in the GetParameters methods in the SqlQueryInfo class (SqlQueryInfo.cs):

		public IDbDataParameter[] GetParameters(DbManager db, object[] key)
		{
			if (_parameters.Count != key.Length)
				throw new DataAccessException("Parameter list does match key list.");
			IDbDataParameter[] parameters = new IDbDataParameter[_parameters.Count];

			for (int i = 0; i < _parameters.Count; i++)
			{
				SqlQueryParameterInfo info = (SqlQueryParameterInfo)_parameters[i];

				parameters[i] = db.Parameter(info.ParameterName, key[i]);
			}

			return parameters;
		}

		public IDbDataParameter[] GetParameters(DbManager db, object obj)
		{
			IDbDataParameter[] parameters = new IDbDataParameter[_parameters.Count];

			for (int i = 0; i < _parameters.Count; i++)
			{
				SqlQueryParameterInfo info = (SqlQueryParameterInfo)_parameters[i];

				parameters[i] = db.Parameter(info.ParameterName,
								info.MemberMapper.GetValue(obj));
			}

			return parameters;
		}

On line 10 a SqlQueryParameterInfo object is used from the class member array _parameters. The SqlQueryParameterInfo type contains the value of the MapField attribute among other things. This means that the attributes are already read, which means that I was “too late”. I backed up a little and found a call to a method in the SqlQuery<T> class, the Insert method. By stepping into this I found out where the MapField attribute was read, in the Init method in the ObjectMapper class (ObjectMapper.cs):

					MapMemberInfo mi = new MapMemberInfo();

					mi.MemberAccessor  = ma;
					mi.Type            = ma.Type;
					mi.MappingSchema   = mappingSchema;
					mi.MemberExtension = _extension[ma.Name];
					mi.Name            = GetFieldName   (ma);
					mi.MemberName      = ma.Name;
					mi.Trimmable       = GetTrimmable   (ma);
					mi.MapValues       = GetMapValues   (ma);
					mi.DefaultValue    = GetDefaultValue(ma);
					mi.Nullable        = GetNullable    (ma);
					mi.NullValue       = GetNullValue   (ma, mi.Nullable);

On line 7 the Name property is set on the MapMemberInfo object mi. I decided to add a property to the MapMemberInfo class to get the value of UdtTypeName attribute. Add the following property to the MapMemberInfo class (MapMemberInfo.cs):

        private string _udtTypeName;
        public string UdtTypeName
        {
            get { return _udtTypeName; }
            set { _udtTypeName = value; }
        }

To get value of the attribute I added a method to the ObjectMapper class to make it possible to add the following method call among the methods above, setting the mi object property:

mi.UdtTypeName = GetUdtTypeName(ma);

The GetUdtTypeName looks more or less exactly the same as the GetFieldName method:

protected virtual string GetUdtTypeName(MemberAccessor memberAccessor)
{
		bool isSet;
		return MetadataProvider.GetUdtTypeName(this,
						memberAccessor,
						out isSet);
}

This makes it necessary to add a GetUdtTypeName method to the MetadataProviderBase class (MetadataProviderBase.cs):

public virtual string GetUdtTypeName(ObjectMapper objectMapper,
						MemberAccessor memberAccessor,
						out bool isSet)
{
    throw new NotImplementedException();
}

By adding this method the solution will build successfully, but an exception is thrown. Let’s make things run and not just build. The MetadataProviderBase is inherited by a couple of classes. Looking at the implementation of the GetFieldName method, reading the MapField attribute, this method is overridden in the AttributeMetadataProvider and MetadataProviderList classes. I will do the same with the GetUdtTypeName, adding the following method to these classes:

public override string GetUdtTypeName(ObjectMapper mapper, MemberAccessor member, out bool isSet)
{
            UdtTypeNameAttribute a = member.GetAttribute<UdtTypeNameAttribute>();

            if (a != null)
            {
                isSet = true;
                return a.UdtTypeName;
            }

            return base.GetUdtTypeName(mapper, member, out isSet);
}

Of course the method in the base class is changed to set the parameter isSet to false and to return null. All changes above make way to use the UdtTypeName attribute when generating the queries to be executed. As mentioned above one step in this process is done in the methods GetParameters in the SqlQueryInfo class. Look at lines 10 and 24 in the GetParameters method code section above. This is where the extended MapMemberInfo will be available, via the info.MemberMapper.MapMemberInfo property. The next step is somewhat a shortcut. The handling of the new UdtTypeName attribute is to be used only when having SqlServer as dataprovider, but I will add the handling in the SqlQueryInfo class anyway. I will make a better solution by adding a method to the DbManagerBase class in the future, but I have to make a deeper analysis to implement this method correctly. Anyway, let’s take a look at the code, showing the modified methods GetParameters along with the new method handling the UdtTypeName attribute:

public IDbDataParameter[] GetParameters(DbManager db, object[] key)
{
	if (_parameters.Count != key.Length)
		throw new DataAccessException("Parameter list does match key list.");
	IDbDataParameter[] parameters = new IDbDataParameter[_parameters.Count];

	for (int i = 0; i < _parameters.Count; i++)
	{
		SqlQueryParameterInfo info = (SqlQueryParameterInfo)_parameters[i];

		parameters[i] = db.Parameter(info.ParameterName, key[i]);

		// Added call to a new method
		AddCustomParameterAttributes(ref parameters[i],
			info.MemberMapper.MapMemberInfo,
			db.DataProvider.Name);
	}

	return parameters;
}

public IDbDataParameter[] GetParameters(DbManager db, object obj)
{
	IDbDataParameter[] parameters = new IDbDataParameter[_parameters.Count];

	for (int i = 0; i < _parameters.Count; i++)
	{
		SqlQueryParameterInfo info = (SqlQueryParameterInfo)_parameters[i];

		parameters[i] = db.Parameter
			(info.ParameterName, info.MemberMapper.GetValue(obj));

		// Added call to a new method
		AddCustomParameterAttributes(ref parameters[i],
			info.MemberMapper.MapMemberInfo,
			db.DataProvider.Name);
	}

	return parameters;
}

private void AddCustomParameterAttributes(ref IDbDataParameter dbDataParameter,
				MapMemberInfo info,
				string dbProviderName)
{
	switch (dbProviderName)
	{
		case Data.DataProvider.SqlDataProvider.NameString:
			{
				((System.Data.SqlClient.SqlParameter)dbDataParameter).UdtTypeName =
					info.UdtTypeName;
			}
			break;

		case Data.DataProvider.OdbcDataProvider.NameString:
		case Data.DataProvider.OleDbDataProvider.NameString:
		case Data.DataProvider.OracleDataProvider.NameString:
		case Data.DataProvider.AccessDataProvider.NameString:
			{ }
			break;

		default:
			{ }
			break;
	}
}

Look at line 14 and 34, where I added a call to the new method implemented on line 42-66. I decided to treat the dataproviders in the toolkit using a switch-case construction. For the SqlServer I set the UdtTypeName property on the SqlParameter to the value of the UdtTypeName attribute. The changes described above will make the insert method calls on line 28 and 40 in the first block of code work nicely. The update on line 69 runs smoothly as well. Let’s make a short summary and create a ToDo list.

Summary

Let’s look at what is done:

• Add an attribute class; the UdtTypeNameAttribute class to the Mapping namespace.
• Identify where the SqlParameters are created; GetParameters methods in the SqlQueryInfo class.
• Identify where the attributes are read, e.g. the MapField attribute; Init method in ObjectMapper class.
• Add a property for the new attribute to the MapMemberInfo class, string UdtTypeName.
• Add a method to the ObjectMapper class, GetUdtTypeName, to read the UdtTypeName attribute and call the method in the Init method identified above.
  -The new method looks like the GetFieldName, calling a method in the MetadataProviderBase class.
• GetUdtTypeName in ObjectMapper class will call GetUdtTypeName in MetadataProviderBase class. Add this method to the class, make it virtual.
• Override GetUdtTypeName in the AttributeMetadataProvider and MetadataProviderList classes. These methods read the actual UdtTypeName attribute value if defined.
• Back up to the GetParameters methods, add a call to a new method (AddCustomParameterAttributes).
• Implement the newly added method. This method handles the different dataproviders, setting properties on the query parameters. In this case the UdtTypeName property will be set on the SqlParameter, when the dataprovider is SqlServer.

I hope this will make a couple of you interested in the BLToolKit. It is well worth the time to try it out!

ToDo

The above procedure is just a beginning. I like the toolkit a lot and therefore I will continue to extend it at learn about how it is implemented. I’m impressed by the work performed by the BLToolKit team!

There are things left to be done with the procedure described above:

• More testing must be performed to see if the above is supported when using custom queries and stored procedures among other things.
• I noticed some time ago that the UdtTypeName attribute wasn’t read if the MapField attribute was left out, e.g. if the SqlGeometry database column and the SqlGeometry property of the antenna class map by name. I have to solve this if it is an issue.
• Move the AddCustomParameterAttribute method “closer” to the dataprovider implementation, e.g. SqlServer specific stuff will be implemented in the SqlDataProvider or somewhere else making the SqlServer specific stuff located in SqlServer specific classes.