SQL Server 2008 spatial data with BLToolkit

In previous posts I have described my way to use the new spatial data types that come with SQL Server 2008. As shown in these posts ( and ) there is a little bit of work to make this run smoothly.

The other day I started to search for an alternative way to get rid of the dataset designer and the problems with UDTs (User Defined Types) and I bumped into the BLToolkit. This toolkit is a set of components to use when accessing data from a couple of providers, such as SQL Server. I looked at their tutorial and I liked what I saw. No dataset designer involved, just pure C# code and a couple of configuration settings and the work is done!

I will show how to use the spatial data types with BLToolkit, by presenting an extension of the toolkit to make the data access involving the new types a piece of cake. I have added a new attribute class to the toolkit making this possible.

Introduction to the BLToolkit

The BLToolkit consists of great number of classes to use when accessing data from different providers.

The tutorial showed how to use it so I started off by implementing the same functionality as in the previous posts about the spatial data types in SQL Server, but without the actual spatial data. Instead of the SqlGeometry object I used two doubles to represent the latitude and longitude coordinates.

BLToolkit has a nice way to map C# classes and properties to database tables and columns. The database table used in this introduction looks like this:

The name of the table is AntennasLatLon. The ID column is the primary key with Identity Specification = Yes.

All that is left to do to make this accessible from a C# application is to create a class to be mapped to this table, but before that we have to set up the Visual Studio solution to use. This is easy:

• Download and extract the BLToolkit.
• Create a Visual Studio solution containing a Windows Forms project, or any other project type.
• Right click the solution and Add an existing project, pointing out the BLToolkit project file (BLToolkit.2.csproj for .NET 2.0 or BLToolkit.3.csproj for .NET 3.5).
• Add a reference to the BLToolkit project to the Windows Forms project.

Add the following class to the Windows Forms project:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using BLToolkit.Mapping;
using BLToolkit.DataAccess;

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

        public string Name { get; set; }

        public double Latitude { get; set; }

        public double Longitude { get; set; }
    }
}

A few comments on the code above:

• The class name will map the class to the table AntennasLatLon in the database.
• The property AntennaID will be mapped to ID column, since the MapField attribute defines the mapping.
• The AntennaID property is marked with PrimaryKey attribute and NonUpdatable, since the column is primary key and specified to be an Identity column in the database.
• The properties Name, Latitude and Longitude are mapped to the right columns since the names are identical to the column names.

Now it’s time to insert, update, delete and show the data in the Windows Forms application. The BLToolkit will perform the database accessing. Look at the code below together with the comments. The code shows the event handlers for insert, select and update.

        private NumberFormatInfo _nfi =
                    new NumberFormatInfo()
                    {
                        NumberDecimalSeparator = "."
                    };

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            AntennasLatLon antenna = new AntennasLatLon()
            {
                Name = textBoxName.Text,
                Latitude = Convert.ToDouble(textBoxLatitude.Text, _nfi),
                Longitude = Convert.ToDouble(textBoxLongitude.Text, _nfi)
            };

            SqlQuery<AntennasLatLon> query = new SqlQuery<AntennasLatLon>();

            int numberOfInsertedAntennas =
                query.Insert(antenna);

            textBoxName.Text = string.Empty;
            textBoxLatitude.Text = string.Empty;
            textBoxLongitude.Text = string.Empty;

            buttonUpdate.Enabled = false;

            MessageBox.Show("Inserted " + numberOfInsertedAntennas + " antenna(s).");
        }

        private void buttonGet_Click(object sender, EventArgs e)
        {
            int antID = Convert.ToInt32(textBoxID.Text);

            SqlQuery<AntennasLatLon> query = new SqlQuery<AntennasLatLon>();
            AntennasLatLon antenna = query.SelectByKey(new object[] { antID });

            if (antenna != null)
            {
                textBoxName.Text = antenna.Name;
                textBoxLatitude.Text = antenna.Latitude.ToString();
                textBoxLongitude.Text = antenna.Longitude.ToString();

                buttonUpdate.Enabled = true;
            }
            else
            {
                textBoxName.Text = string.Empty;
                textBoxLatitude.Text = string.Empty;
                textBoxLongitude.Text = string.Empty;

                buttonUpdate.Enabled = false;

                MessageBox.Show("No antenna found");
            }
        }

        private void buttonUpdate_Click(object sender, EventArgs e)
        {
            AntennasLatLon antenna = new AntennasLatLon()
            {
                AntennaID = Convert.ToInt32(textBoxID.Text),
                Name = textBoxName.Text,
                Latitude = Convert.ToDouble(textBoxLatitude.Text, _nfi),
                Longitude = Convert.ToDouble(textBoxLongitude.Text, _nfi)
            };

            SqlQuery<AntennasLatLon> query = new SqlQuery<AntennasLatLon>();
            int numberOfUpdatedAntennas = query.Update(antenna);

            buttonUpdate.Enabled = false;

            MessageBox.Show("Updated " + numberOfUpdatedAntennas + " antenna(s).");
        }

• The UI consists of buttons for Add, Select (get) and Update along with textboxes for AntennaID, Name and Position (fields for Latitude and Longitude coordinates).
• The NumberFormatInfo object is used because of the Swedish decimal separator is by default the ‘,’ sign.
• The SqlQuery object uses a DbManager. This DbManager is initialized by referencing a connection string. In the example above the one and only connection string in the application is used automatically by the toolkit. This connection string is defined in the app.config file.
• The SqlQuery template object created on the lines 16, 34 and 67 makes the BLToolkit map the AntennasLatLon class to the database table and the columns.
• On line 18 and 19 the Insert method on the SqlQuery object is invoked. The toolkit creates an Insert query for the database.
• On line 35 the SelectByKey method is invoked, with an AntennaID as the only object in the array parameter.
• On line 68 the Update method is invoked, generating an Update query executed on the database.
• The methods Insert, SelectByKey and Update are generated automatically by BLToolkit.

The code above shows how to use the standard methods generated by BLToolkit. As seen the interaction with the database is really simple.

Why this article?

As seen above, in the introduction to BLToolkit, it is really easy to access the underlying database using strings, integers and other simple data types. But! As the title of this article says the spatial data is the key point here.

I then modified the antenna class so the location of the antenna was handled using an SqlGeometry object instead of two doubles. Selecting spatial data works nice, but adding and updating failed with almost the same error as the dataset designer in the previous posts on the subject. There is a need to specify the User Defined Type (UDT) when using the SqlGeometry.

As I noticed this error I came up with the idea to extend the toolkit by adding support for a new attribute for member variables and properties. I will explain how I did this below.

Adding spatial data support to BLToolkit

The goal of this article is to handle updating and adding of spatial data in the same easy way as simple data types described above. I would like to use the following class as parameters to the SqlQuery object:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using BLToolkit.Mapping;
using BLToolkit.DataAccess;
using Microsoft.SqlServer.Types;

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

        public string Name { get; set; }

        [MapField("Position")]
        public SqlGeometry Location { get; set; }
    }
}

The drawback with this class is that the generated SQL query will cause the application to throw an exception because of the null value for the User Defined Type name attribute for the SqlGeometry member field. This is not good, so let’s implement a new attribute like this: [UdtTypeName("geometry")].

The attribute class

There are a lot of namespaces/directories to put the attribute class in, but I decided to put it in the Mapping directory, along with the MapField attribute used above. The attribute class looks like this:

using System;

namespace BLToolkit.Mapping
{
    [AttributeUsage(AttributeTargets.Field | AttributeTargets.Property)]
    public class UdtTypeNameAttribute : Attribute
    {
        public readonly string UdtTypeName;

        public UdtTypeNameAttribute(string udtTypeName)
        {
            this.UdtTypeName = udtTypeName;
        }
    }
}

The above class will make it possible to modify the AntennasGeom class to look like this:

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

for the Location/Position property.

Integrate the attribute in BLToolkit

Just adding the attribute class will not make BLToolkit use the attribute. The following files are affected to handle the new attribute:

• Data/DataProvider/DataProviderBase.cs
• Data/DataProvider/SqlDataProvider.cs
• DataAccess/SqlQueryInfo.cs
• Mapping/MapMemberInfo.cs
• Mapping/MemberMapper.cs
• Mapping/ObjectMapper.cs
• Reflection/MetadataProvider/AttributeMetadataProvider.cs
• Reflection/MetadataProvider/MetadataProviderBase.cs
• Reflection/MetadataProvider/MetadataProviderList.cs

I will post an article about the changes describing them more in detail, but some comments on the changes in the files come here:

ObjectMapper.cs

This is the class holding the information for the toolkit to use when mapping the class to the database. In the Init method a MapMemberInfo object is created and initialized. The MapMemberInfo class is extended with a new property to hold the UdtTypeName value. The value of the attribute is read by an added method GetUdtTypeName returning a string containing the UdtTypeName value, if set.

MetadataProviderBase.cs

This class is the base class for all MetadataProviders, such as AttributeMetadataProvider and MetadataProviderList. These classes get the actual value of the UdtTypeName attribute if set, using a newly added method GetUdtTypeName.

AttributeMetadataProvider.cs/MetadataProviderList.cs

As mentioned above these classes inherit the MetadataProviderBase class, and override the method GetUdtTypeName to read the value of the attribute if set.

MemberMapper.cs

This class is similar to the ObjectMapper class above. In the Init method the UdtTypeName value is read via the MetadataProvider class.

MemberMapperInfo.cs

This class is extended with a property to hold the value of the UdtTypeName attribute. This class is used in the MemberMapper class and ObjectMapper class.

DataProviderBase.cs

I added a method to this class called ExtendParameter. This method is the one used by the classes that inherits this base class when adding data provider specific stuff to the generated query. In this case with SqlGeometry the SqlParameter will get UdtTypeName property set to the value of the UdtTypeName attribute for the property above, the Location in the AntennasGeom class.

SqlDataProvider.cs

This class overrides the ExtendParameter method in the DataProviderBase class, and sets the UdtTypeName in this particular case.

SqlQueryInfo.cs

This class is the one generating the query for the SQL Server provider, that will be executed. I added a call to the ExtendParameter method in the two GetParameters methods.

Conclusion

It may seem to be a lot of files affected by this little extension of the toolkit, but this is the very first time I have even used the BLToolkit, so there will for sure be modifications to this solution.

By adding this support for UdtTypeName it is possible to use SqlGeometry properties in the data model class and the toolkit will generate queries for inserting and updating records in the database, at least for the SQL Server as a provider.

I haven’t tested any User Defined Types other than the SqlGeometry type, but as far as I know it will work. One thing I noticed was that the BLToolkit will not generate any query parameters from the SqlGeometry property if the MapField attribute is left out. I will look into this later on and post the changes here.

Read more

Read more about the spatial data and SQL Server 2008 stuff:

Read more about the BLToolkit here.

I will make improvements and add more extensions to the toolkit in the future and I will post articles about it here. Maybe I will create a separate page for the BLToolkit and make it a little project to extend it over a longer period of time.