Boris posted me some code that I used to play around with, in order to make XPO work with SQL Server 2008 spatial data. It is possible to make it work, quite easily actually, but there are a few things that need to be considered, and where more than one solution is possible. Let's see.
First, we need a class to store data in the client application. This is the one I'm using:
public class PolygonData: XPObject {
public PolygonData(Session session)
: base(session) { }
private string name;
public string Name {
get { return name; }
set { SetPropertyValue("Name", ref name, value); }
}
SqlGeography polygon;
[ValueConverter(typeof(GeographyConverter))]
[DbType("geography")]
public SqlGeography Polygon {
get { return polygon; }
set { SetPropertyValue("Polygon", ref polygon, value); }
}
}
This is already the first thing that probably needs some thought. In order to use the SqlGeography class as the property type, it is necessary to have a reference to the assembly Microsoft.SqlServer.Types. That assembly is not currently part of the .NET Framework, but it is installed together with SQL Server. As the SqlGeography class has several pieces of useful functionality (and there are other useful classes in the same assembly), it seems weird that they aren't available in the "standard client". Apparently Microsoft prepared these classes for server-side use, and obviously this makes some sense if you're going to use .NET on the server in some way, but still... there are a number of blog posts out there on the topic (try searching SqlGeography client side, for instance), and at least one of them points to a future solution that involves a separate redistributable which might become available at some point. For the time being, it would probably be possible to install the assembly in question on the client manually -- but please note that I haven't looked into legal issues involved with this, so be careful!
If, for whatever reasons, you don't want to go this way, you would have to create your own client-side class to represent the spatial information. The other approaches described in this post would still be quite similar though.
Now, as you can see, the property for the Polygon in my code is decorated with two attributes. The DbType attribute makes sure that the field gets created with the correct type "geography" in SQL Server. The ValueConverter attribute is used to convert the geography into the string format needed in SQL. Here it is:
public class GeographyConverter : ValueConverter {
public override object ConvertFromStorageType(object value) {
if (value is string)
return SqlGeography.Parse((string) value);
else return value;
}
public override object ConvertToStorageType(object value) {
return value == null ? null : ((SqlGeography) value).ToString( );
}
public override Type StorageType {
get { return typeof(string); }
}
}
The ConvertFromStorageType function implementation is a bit weird, since it simply skips the conversion if the object that's getting passed in is not a string. This is perhaps not the most secure way of implementing this, but it's good enough for the purpose of the demo. The reason I'm expecting objects that might not be strings is explained further down and it has to do with performance -- depending on the decision you make at that point, you might implement this function in slightly different ways.
To create a bit test data, I'm using the following helper function in my code (the SqlGeographyBuilder is another class from Microsoft.SqlServer.Types):
private static SqlGeography CreatePolygon( ) {
SqlGeographyBuilder builder = new SqlGeographyBuilder( );
builder.SetSrid(4326);
builder.BeginGeography(OpenGisGeographyType.Polygon);
builder.BeginFigure(55.36728, -2.74941);
builder.AddLine(55.40002, -2.68289);
builder.AddLine(55.39908, -2.74913);
builder.AddLine(55.36728, -2.74941);
builder.EndFigure( );
builder.EndGeography( );
return builder.ConstructedGeography;
}
With this in place, I can create and store some data:
using (UnitOfWork uow = new UnitOfWork( )) {
new PolygonData(uow) {
Name = "Test 1",
Polygon = CreatePolygon( )
}.Save( );
uow.CommitChanges( );
}
If you are following along and you've built your own sample with these code snippets, you will be able to execute the sample at this point. It will get a table created in SQL Server and the spatial data inserted. Wonderful!
Finally, of course we want to read data back from the database. In my sample, I'm using this simple piece of code to do it:
using (UnitOfWork uow = new UnitOfWork( )) {
var polygons = new XPCollection<PolygonData>( );
foreach (var polygon in polygons) {
Console.WriteLine(polygon.Name);
Console.WriteLine(polygon.Polygon);
}
}
If you try to run this, you will see an exception though. The reason for that is that there are types here which don't match up. The SQL Server client library, somewhat confusingly, returns an object that is actually of type SqlGeography, whereas our type converter defines a storage type of string ins StorageType property. As a result, a function called ReformatReadValue is called on the connection provider we're using (MSSqlConnectionProvider by default), and that function attempts to use the .NET Framework standard Convert.ChangeType function to convert a SqlGeography into a string. That function in turn expects the object to implement IConvertable, and throws an exception because it doesn't do that. Phew.
So what's the solution to this problem? Well, making the ReformatReadValue function do the conversion of SqlGeography into string, that's one solution. Let's derive a connection provider and override that function:
public class GISProvider : MSSqlConnectionProvider {
public GISProvider(IDbConnection connection, AutoCreateOption autoCreateOption)
: base(connection, autoCreateOption) {
}
protected override object ReformatReadValue(object value, ReformatReadValueArgs args) {
if (value != null) {
Type valueType = value.GetType( );
if (valueType == typeof(SqlGeography) || valueType == typeof(SqlGeometry))
return value.ToString( );
}
return base.ReformatReadValue(value, args);
}
}
Now we need to make sure this is the provider we're using, instead of the standard MSSqlConnectionProvider. We can do this with an initialization line like this:
XpoDefault.DataLayer =
new SimpleDataLayer(new GISProvider(
new SqlConnection("data source=.;integrated security=SSPI;initial catalog=XPOSql2008Spatial"),
AutoCreateOption.DatabaseAndSchema));
If you're following along, try running your application again, with the reading code in place, and you should see the geography information read back to the client and shown on the console.
There's one problem with this code, and I'm sure you have noticed already: we are getting back a SqlGeography instance from the database client code, and this gets passed in to the ReformatReadValue method in the XPO infrastructure. There it is converted into a string. Then it gets passed into the configured value converter for the Polygon property, and gets converted in a SqlGeography instance. Sounds suboptimal, doesn't it? Yeah...
There is a solution to this, but it's not entirely perfect. It is possible to simply ignore what ReformatReadValue wants us to do and just not convert the object (leave off the ToString() from the value the method returns). Then the SqlGeography type object will be passed in to the value converter, which needs to be implemented to ignore the fact that this is not really a string it receives. Remember, I already pointed this out above - if you want to go this "better performance" way, your value converter will have to be able to deal with the fact that it might receive objects to convert that have actually already been converted.
I'm only describing this solution instead of showing the code, because it's not really something I want to recommend. This solution neglects the contracts of the XPO infrastructure, and that is of course a bad thing when changes are made in the future. It might also be a problem if the data has to be serialized after being read from the database - having it in string format is what XPO normally assumes, and that works well with all types of serialization. I haven't made any real tests, so I can just say I don't know precisely what will happen if SqlGeography instances are sent across the wire using Remoting, XML Web Services, WCF or other frameworks. So - if you're interested in the performance gain, Remoting and so on are perhaps not a concern of yours (it seems unlikely anyway that Remoting and the perf gain from a few saved string conversions are *both* important to you), then it shouldn't be hard to use this approach I described. Please note though that you should take extra care to have relevant unit tests in place, so that changes in future XPO versions don't catch you out.
Overall it seems a bit weird the way this feature has been implemented. The client side seems to convert the spatial data into the original type automatically, pretty much whether you want it or not - at least that's how I understand it at this point. Since I push in string data through SQL in order to store the information, it would seem logical to be able to retrieve the string data only, and make my own decision about converting it back into object data, and specifically about the right point in time to do this. Perhaps it fits in somehow with Microsoft's decision not to make Microsoft.SqlServer.Types available in the client by default. Or perhaps there's something I missing right now in this regard :-)
Anyway, have fun! Here's the complete code of the sample I created: XPOSql2008Spatial.zip (4201 bytes)