First, before we begin this blog post we wanted to let you know that we have re-enabled the commenting feature on our blog. Secondly, EntitySpaces 2009 already supports these SQL Server2008 data types but it returns them as type “string”. EntitySpaces 2010 allows you to handle them natively.

EntitySpaces has very powerful provider independence support so we have to be careful when implementing “extended” types for a given database engine. For instance, our NUnit test suite is a single binary that runs against all of our supported databases. The only thing we change when targeting a different database engine is the connection string, we don’t have to recompile or regenerate the code for each database because the databases all have essentially the same schema. So the question is, how do we handle non standard types offered by various database vendors? By non standard types we mean types that do not map to a .NET System Type such as System.Int32. We have the solution all worked out and EntitySpaces 2010 will allow us to support non-standard types offered by various database vendors. For instance, Microsoft SQL Server 2008 supports these types which require a separate assembly to work with.

The .NET types such as SqlGeography are contained in the Microsoft.SqlServer.Type assembly. Obviously we cannot link this library into or core EntitySpaces assemblies as this would require all EntitySpaces customers to have this assembly present even if they were only using Oracle. The good news is we have a solution for this. Here are the language mappings from the ES2010 esLanguages.xml file for the new SQL Server 2008 data types (currently, in ES2009 these map to type “string”, but now we map them to the real extended type).

<Type From="geography" To="SqlGeography" NonSystemType="true"/>
<Type From="geometry" To="SqlGeometry" NonSystemType="true"/>
<Type From="hierarchyid" To="SqlHierarchyId" NonSystemType="true"/>       

Notice that these are marked as NonSystemTypes = “true”, that is to say they cannot be mapped to a standard .NET type such as a “System.Int32” and so on …

During the code generation process we use this flag to generate the properties using the GetSystemObject/SetSystemObject methods as shown below.

virtual public SqlGeography TheGeography
{
    get
    {
        return (SqlGeography)base.GetSystemObject(MasterTypesMetadata.ColumnNames.TheGeography);
    }
    set
    {
        if(base.SetSystemObject(MasterTypesMetadata.ColumnNames.TheGeography, value));
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(MasterTypesMetadata.PropertyNames.TheGeography));
            }
        }
    }
}   

Notice how we simply use GetSystemObject/SetSystemObject and then cast to the specific type. This is done in your generated classes and not in our core assemblies. The property shown below is a standard column that maps to a system type (an integer column) and thus uses GetSystemInt32/SetSystemInt32 and requires no cast.

virtual public System.Int32? TheInt
{
    get
    {
        return base.GetSystemInt32(MasterTypesMetadata.ColumnNames.TheInt);
    }
    set
    {
        if(base.SetSystemInt32(MasterTypesMetadata.ColumnNames.TheInt, value))
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(MasterTypesMetadata.PropertyNames.TheInt));
            }
        }
    }
}   

Since we cannot have a GetSystemSqlGeometry() in our core assemblies without linking to special database provider assembly this works out nicely. It doesn’t effect non Microsoft folks at all.

An Example

Take a look at this rather sophisticated example. Here we create a complex shape and store it to the database. We have created a table in SQL Server 2008 called “MasterTypes” that we are using for testing all possible types.

SqlGeometryBuilder gb = new SqlGeometryBuilder();

// Set the Spatial Reference ID to 1
gb.SetSrid(1);
// Start the collection
gb.BeginGeometry(OpenGisGeometryType.MultiPolygon);
// Start the first element in this collection
gb.BeginGeometry(OpenGisGeometryType.Polygon);
// Define the first element (figure)
gb.BeginFigure(-77.054700,38.872957);
gb.AddLine(-77.057962, 38.872620);
gb.AddLine(-77.058547, 38.870079);
gb.AddLine(-77.055592, 38.868840);
gb.AddLine(-77.053217, 38.870656);
gb.AddLine(-77.054700, 38.872957);
// End the first element (figure)
gb.EndFigure();
// Define the second figure
gb.BeginFigure(-77.056972, 38.870639);
gb.AddLine(-77.055851, 38.870219);
gb.AddLine(-77.054875, 38.870864);
gb.AddLine(-77.055452, 38.871804);
gb.AddLine(-77.056784, 38.871655);
gb.AddLine(-77.056972, 38.870639);
gb.EndFigure();
// End the first polygon
gb.EndGeometry();

// Define the second polygon
gb.BeginGeometry(OpenGisGeometryType.Polygon);
gb.BeginFigure(-77.056408, 38.875290);
gb.AddLine(-77.056947, 38.875224);
gb.AddLine(-77.057466, 38.873598);
gb.AddLine(-77.057273, 38.872737);
gb.AddLine(-77.055335, 38.873020);
gb.AddLine(-77.055499, 38.874058);
gb.AddLine(-77.056408, 38.875290);
gb.EndFigure();
gb.EndGeometry();
// End (close) the collection

gb.EndGeometry();

MasterTypes m = new MasterTypes();
m.TheGeometry = gb.ConstructedGeometry;
m.Save();

This code works prefectly and saves complex shape to the database. Of course, you can read it back and access the "TheGeometry” property (we named the database column ‘TheGeometry’).

The screen shot below is what it looks like in the debugger when we inspect our “TheGeometry” property after reading it back from the database.

One thing we have decided on is whether we should always make these extended types as nullable types, the answer will probably be yes.

We are also looking at supporting the SqlFileStream functionality but that is a little more complex. This approach should allow us to support non-standard data types for other database systems as well.

From Mobile Devices to large scale enterprise solutions in need of serious transaction support, EntitySpaces can meet your needs. Whether you’re writing an ASP.NET application with Medium Trust requirements, a Silverlight/WCF application, a Mono application, or a Windows.Forms application, the EntitySpaces architecture is there for you. EntitySpaces is provider independent, which means that you can run the same binary code against any of the supported databases. EntitySpaces is available in both C# and VB.NET. EntitySpaces uses no reflection, no XML files, and sports a tiny foot print of less than 200k. Pound for pound, EntitySpaces is one tough, dependable .NET architecture.

EntitySpaces LLC
Persistence Layer and Business Objects for Microsoft .NET
http://www.entityspaces.net