Writing own libs for SisoDb – SisoDb.Spatials

I’m in the process of finalizing a v16.0.0 of SisoDb and there’s a first pre-release out on NuGet (SQLCE4, SQL2005, SQL2008, SQL2012). I’m will soon get a pre-release 2 out which have one simple change in code but it allows you to write third party libs to SisoDb. Almost anything can allready be swapped out for your implementations. But what if you just want to provide some new functions via a NuGet? Lets say I would like to create SisoDb.Spatial and provide some simple helpers for spatial data. You should then extend the ISession and make use of the Session.ExecutionContext. It exposes all you need via the Session, but more importantly, exposes two Try methods that you should wrap your methods within. Why? Well SisoDb uses Auto-commit and rollback on exception, but to be sure states are updated correctly, the exception should be caught by that specific try methods.

Lets look at an example. First I extend ISession and pass along the ExecutionContext (available from v16.0.0-pre2)

public static class SessionExtensions
{
    public static ISisoSpatials Spatials(this ISession session)
    {
        return new SqlServerSisoSpatials(session.ExecutionContext);
    }
}

Lets define some operations

public interface ISisoSpatials 
{
    void UpsertFor<T>() where T : class;
    void DropFor<T>() where T : class;
    bool PointExistsInPolygonFor<T>(object id, Tuple<double, double> coords, int srid = SpatialReferenceId.Wsg84) where T : class;
    void DeleteGeoIn<T>(object id) where T : class;
    void InsertPolygonTo<T>(object id, Tuple<double, double>[] coords, int srid = SpatialReferenceId.Wsg84) where T : class;
    void UpdatePolygonIn<T>(object id, Tuple<double, double>[] coords, int srid = SpatialReferenceId.Wsg84) where T : class;
    void SetPolygonIn<T>(object id, Tuple<double, double>[] coords, int srid = SpatialReferenceId.Wsg84) where T : class;
}

Now just implement it and make use of the API exposed by the ExecutionContext.

public class SqlServerSisoSpatials : ISisoSpatials
{
    protected readonly ISessionExecutionContext ExecutionContext;
    protected ISession Session { get { return ExecutionContext.Session; } }
    protected readonly ISqlStatements SqlStatements;

    protected internal SqlServerSisoSpatials(ISessionExecutionContext executionContext)
    {
        ExecutionContext = executionContext;
        SqlStatements = SpatialSqlStatements.Instance;
    }

    public virtual void SetPolygonIn<T>(object id, Tuple<double, double>[] coords, int srid = SpatialReferenceId.Wsg84) where T : class
    {
        ExecutionContext.Try(() =>
        {
            var schema = Session.GetStructureSchema<T>();
            var sidParam = CreateStructureIdParam<T>(id);
            var geoParam = CreatePolygonParam(coords, srid);
            var sql = SqlStatements.GetSql("SetGeo").Inject(GenerateTableName(schema));
            Session.DbClient.ExecuteNonQuery(sql, sidParam, geoParam);
        });
    }

    //...
    //Some stuffe left out.
    //...

    protected virtual string GenerateTableName(IStructureSchema schema)
    {
        return string.Concat(DbSchemaNamingPolicy.GenerateFor(schema.Name), "Spatials");
    }

    protected virtual DacParameter CreateStructureIdParam<T>(object id) where T : class
    {
        return new DacParameter("id", id);
    }

    protected virtual GeographyDacParameter CreatePointParam(Tuple<double, double> coords, int srid)
    {
        return new GeographyDacParameter("geo", SqlGeography.Point(coords.Item1, coords.Item2, srid));
    }
}

That’s pretty much it.

//Daniel

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