As promised, I have uploaded the data provider code I mentioned earlier to the SharpMap site. It’s written in C#. I took the existing Oracle provider, stripped out the Oracle SQL and put in T-SQL. There are a couple of things I’m still trying to chase down but it’s basically done at this point. I know they’re busy trying to finish up SharpMap 2.0 so I don’t know how long it’ll take to become available. Just in case, here’s the code:
// Copyright 2008 - William Dollins// SQL Server 2008 by William Dollins (dollins.bill@gmail.com)// Based on Oracle provider by Humberto Ferreira (humbertojdf@hotmail.com)//// Date 2007-11-28//// This file is part of SharpMap.// SharpMap is free software; you can redistribute it and/or modify// it under the terms of the GNU Lesser General Public License as published by// the Free Software Foundation; either version 2 of the License, or// (at your option) any later version.// // SharpMap is distributed in the hope that it will be useful,// but WITHOUT ANY WARRANTY; without even the implied warranty of// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the// GNU Lesser General Public License for more details.// You should have received a copy of the GNU Lesser General Public License// along with SharpMap; if not, write to the Free Software// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA using System;using System.Collections.Generic;using System.Collections.ObjectModel;using System.Text;using System.Data.SqlClient;namespace SharpMap.Data.Providers{ /// <summary> /// SQL Server 2008 data provider /// </summary> /// <remarks> /// <para>This provider was developed against the SQL Server 2008 November CTP. The platform may change significantly before release.</para> /// <example> /// Adding a datasource to a layer: /// <code lang="C#"> /// SharpMap.Layers.VectorLayer myLayer = new SharpMap.Layers.VectorLayer("My layer"); /// string ConnStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=myDB;Data Source=myServer\myInstance"; /// myLayer.DataSource = new SharpMap.Data.Providers.Katmai(ConnStr, "myTable", "GeomColumn", "OidColumn"); /// </code> /// </example> /// <para>SharpMap SQL Server 2008 provider by Bill Dollins (dollins.bill@gmail.com). Based on the Oracle provider written by Humberto Ferreira.</para> /// </remarks> [Serializable] public class Katmai : SharpMap.Data.Providers.IProvider, IDisposable { /// <summary> /// Initializes a new connection to SQL Server /// </summary> /// <param name="ConnectionStr">Connectionstring</param> /// <param name="tablename">Name of data table</param> /// <param name="geometryColumnName">Name of geometry column</param> /// /// <param name="OID_ColumnName">Name of column with unique identifier</param> public Katmai(string ConnectionStr, string tablename, string geometryColumnName, string OID_ColumnName) { //Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ztTest;Data Source=<server>\<instance> this.ConnectionString = ConnectionStr; this.Table = tablename; this.GeometryColumn = geometryColumnName; this.ObjectIdColumn = OID_ColumnName; } /// <summary> /// Initializes a new connection to SQL Server /// </summary> /// <param name="ConnectionStr">Connectionstring</param> /// <param name="tablename">Name of data table</param> /// <param name="OID_ColumnName">Name of column with unique identifier</param> public Katmai(string ConnectionStr, string tablename, string OID_ColumnName) : this(ConnectionStr,tablename,"",OID_ColumnName) { this.GeometryColumn = "shape"; } private bool _IsOpen; /// <summary> /// Returns true if the datasource is currently open /// </summary> public bool IsOpen { get { return _IsOpen; } } /// <summary> /// Opens the datasource /// </summary> public void Open() { //Don't really do anything. _IsOpen = true; } /// <summary> /// Closes the datasource /// </summary> public void Close() { //Don't really do anything. _IsOpen = false; } #region Disposers and finalizers private bool disposed = false; /// <summary> /// Disposes the object /// </summary> public void Dispose() { Dispose(true); GC.SuppressFinalize(this); } internal void Dispose(bool disposing) { if (!disposed) { if (disposing) { //Close(); } disposed = true; } } /// <summary> /// Finalizer /// </summary> ~Katmai() { Dispose(); } #endregion private string _ConnectionString; /// <summary> /// Connectionstring /// </summary> public string ConnectionString { get { return _ConnectionString; } set { _ConnectionString = value; } } private string _Table; /// <summary> /// Data table name /// </summary> public string Table { get { return _Table; } set { _Table = value; } } private string _GeometryColumn; /// <summary> /// Name of geometry column /// </summary> public string GeometryColumn { get { return _GeometryColumn; } set { _GeometryColumn = value; } } private string _ObjectIdColumn; /// <summary> /// Name of column that contains the Object ID /// </summary> public string ObjectIdColumn { get { return _ObjectIdColumn; } set { _ObjectIdColumn = value; } } /// <summary> /// Returns geometries within the specified bounding box /// </summary> /// <param name="bbox"></param> /// <returns></returns> public Collection<Geometries.Geometry> GetGeometriesInView(SharpMap.Geometries.BoundingBox bbox) { Collection<Geometries.Geometry> features = new Collection<SharpMap.Geometries.Geometry>(); using (SqlConnection conn = new SqlConnection(_ConnectionString)) { //Get bounding box string string strBbox = GetBoxFilterStr(bbox); string strSQL = "SELECT g." + this.GeometryColumn +".STAsBinary() "; strSQL += " FROM " + this.Table + " g WHERE "; if (!String.IsNullOrEmpty(_defintionQuery)) strSQL += this.DefinitionQuery + " AND "; strSQL += strBbox; using (SqlCommand command = new SqlCommand(strSQL, conn)) { conn.Open(); using (SqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { if (dr[0] != DBNull.Value) { SharpMap.Geometries.Geometry geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]); if(geom!=null) features.Add(geom); } } } conn.Close(); } } return features; } /// <summary> /// Returns the geometry corresponding to the Object ID /// </summary> /// <param name="oid">Object ID</param> /// <returns>geometry</returns> public SharpMap.Geometries.Geometry GetGeometryByID(uint oid) { SharpMap.Geometries.Geometry geom = null; using (SqlConnection conn = new SqlConnection(_ConnectionString)) { string strSQL = "SELECT g." + this.GeometryColumn + ".STAsBinary() FROM " + this.Table + " g WHERE " + this.ObjectIdColumn + "='" + oid.ToString() + "'"; conn.Open(); using (SqlCommand command = new SqlCommand(strSQL, conn)) { using (SqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { if (dr[0] != DBNull.Value) geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]); } } } conn.Close(); } return geom; } /// <summary> /// Returns geometry Object IDs whose bounding box intersects 'bbox' /// </summary> /// <param name="bbox"></param> /// <returns></returns> public Collection<uint> GetObjectIDsInView(SharpMap.Geometries.BoundingBox bbox) { Collection<uint> objectlist = new Collection<uint>(); using (SqlConnection conn = new SqlConnection(_ConnectionString)) { //Get bounding box string string strBbox = GetBoxFilterStr(bbox); string strSQL = "SELECT g." + this.ObjectIdColumn + " "; strSQL += "FROM " + this.Table + " g WHERE "; if (!String.IsNullOrEmpty(_defintionQuery)) strSQL += this.DefinitionQuery + " AND "; strSQL += strBbox; using (SqlCommand command = new SqlCommand(strSQL, conn)) { conn.Open(); using (SqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { if (dr[0] != DBNull.Value) { uint ID = (uint)(decimal)dr[0]; objectlist.Add(ID); } } } conn.Close(); } } return objectlist; } /// <summary> /// Returns the box filter string needed in SQL query /// </summary> /// <param name="bbox"></param> /// <returns></returns> private string GetBoxFilterStr(SharpMap.Geometries.BoundingBox bbox) { //geography::STGeomFromText('LINESTRING(47.656 -122.360, 47.656 -122.343)', 4326); SharpMap.Geometries.LinearRing lr = new SharpMap.Geometries.LinearRing(); lr.Vertices.Add(new SharpMap.Geometries.Point(bbox.Left, bbox.Bottom)); lr.Vertices.Add(new SharpMap.Geometries.Point(bbox.Right, bbox.Bottom)); lr.Vertices.Add(new SharpMap.Geometries.Point(bbox.Right, bbox.Top)); lr.Vertices.Add(new SharpMap.Geometries.Point(bbox.Left, bbox.Top)); lr.Vertices.Add(new SharpMap.Geometries.Point(bbox.Left, bbox.Bottom)); SharpMap.Geometries.Polygon p = new SharpMap.Geometries.Polygon(lr); string bboxText = SharpMap.Converters.WellKnownText.GeometryToWKT.Write((SharpMap.Geometries.IGeometry)p); // ""; string whereClause = this.GeometryColumn + ".STIntersects(geometry::STGeomFromText('" + bboxText + "', " + this.SRID.ToString() + ")) = 1"; return whereClause; // strBbox; } /// <summary> /// Returns the features that intersects with 'geom' /// </summary> /// <param name="geom"></param> /// <param name="ds">FeatureDataSet to fill data into</param> public void ExecuteIntersectionQuery(SharpMap.Geometries.Geometry geom, FeatureDataSet ds) { List<Geometries.Geometry> features = new List<SharpMap.Geometries.Geometry>(); using (SqlConnection conn = new SqlConnection(_ConnectionString)) { //TODO: Convert to SQL Server string strGeom = "geography::STGeomFromText('" + geom.AsText() + "', #SRID#)"; if (this.SRID > 0) { strGeom = strGeom.Replace("#SRID#", this.SRID.ToString()); } else { strGeom = strGeom.Replace("#SRID#", "0"); } strGeom = this.GeometryColumn + ".STIntersects(" + strGeom + ") = 1"; string strSQL = "SELECT g.* , g." + this.GeometryColumn + ").STAsBinary() As sharpmap_tempgeometry FROM " + this.Table + " g WHERE "; if (!String.IsNullOrEmpty(_defintionQuery)) strSQL += this.DefinitionQuery + " AND "; strSQL += strGeom; using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, conn)) { conn.Open(); adapter.Fill(ds); conn.Close(); if (ds.Tables.Count > 0) { FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]); foreach (System.Data.DataColumn col in ds.Tables[0].Columns) if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry") fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression); foreach (System.Data.DataRow dr in ds.Tables[0].Rows) { SharpMap.Data.FeatureDataRow fdr = fdt.NewRow(); foreach (System.Data.DataColumn col in ds.Tables[0].Columns) if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry") fdr[col.ColumnName] = dr[col]; fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]); fdt.AddRow(fdr); } ds.Tables.Add(fdt); } } } } /// <summary> /// Convert WellKnownText to linestrings /// </summary> /// <param name="WKT"></param> /// <returns></returns> private SharpMap.Geometries.LineString WktToLineString(string WKT) { SharpMap.Geometries.LineString line = new SharpMap.Geometries.LineString(); WKT = WKT.Substring(WKT.LastIndexOf('(') + 1).Split(')')[0]; string[] strPoints = WKT.Split(','); foreach (string strPoint in strPoints) { string[] coord = strPoint.Split(' '); line.Vertices.Add(new SharpMap.Geometries.Point(double.Parse(coord[0], SharpMap.Map.numberFormat_EnUS), double.Parse(coord[1], SharpMap.Map.numberFormat_EnUS))); } return line; } /// <summary> /// Returns the number of features in the dataset /// </summary> /// <returns>number of features</returns> public int GetFeatureCount() { int count = 0; using (SqlConnection conn = new SqlConnection(_ConnectionString)) { string strSQL = "SELECT COUNT(*) FROM " + this.Table; if (!String.IsNullOrEmpty(_defintionQuery)) strSQL += " WHERE " + this.DefinitionQuery; using (SqlCommand command = new SqlCommand(strSQL, conn)) { conn.Open(); count = (int)command.ExecuteScalar(); conn.Close(); } } return count; } #region IProvider Members private string _defintionQuery; /// <summary> /// Definition query used for limiting dataset /// </summary> public string DefinitionQuery { get { return _defintionQuery; } set { _defintionQuery = value; } } /// <summary> /// Gets a collection of columns in the dataset /// </summary> public System.Data.DataColumnCollection Columns { get { throw new NotImplementedException(); } } private int _srid = 0; /// <summary> /// Spacial Reference ID /// </summary> public int SRID { get { return _srid; } set { _srid = value; } } /// <summary> /// Returns a datarow based on a RowID /// </summary> /// <param name="RowID"></param> /// <returns>datarow</returns> public SharpMap.Data.FeatureDataRow GetFeature(uint RowID) { using (SqlConnection conn = new SqlConnection(_ConnectionString)) { string strSQL = "select g.* , g." + this.GeometryColumn + ".STAsBinary() As sharpmap_tempgeometry from " + this.Table + " g WHERE " + this.ObjectIdColumn + "=" + RowID.ToString() + ""; using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, conn)) { FeatureDataSet ds = new FeatureDataSet(); conn.Open(); adapter.Fill(ds); conn.Close(); if (ds.Tables.Count > 0) { FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]); foreach (System.Data.DataColumn col in ds.Tables[0].Columns) if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry") fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression); if(ds.Tables[0].Rows.Count>0) { System.Data.DataRow dr = ds.Tables[0].Rows[0]; SharpMap.Data.FeatureDataRow fdr = fdt.NewRow(); foreach (System.Data.DataColumn col in ds.Tables[0].Columns) if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry") fdr[col.ColumnName] = dr[col]; fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]); return fdr; } else return null; } else return null; } } } /// <summary> /// Boundingbox of dataset /// </summary> /// <returns>boundingbox</returns> public SharpMap.Geometries.BoundingBox GetExtents() { using (SqlConnection conn = new SqlConnection(_ConnectionString)) { string strSQL = "SELECT g." + this.GeometryColumn + ".STEnvelope().STAsText() FROM " + this.Table + " g "; if (!String.IsNullOrEmpty(_defintionQuery)) strSQL += " WHERE " + this.DefinitionQuery; using (SqlCommand command = new SqlCommand(strSQL, conn)) { conn.Open(); //SharpMap.Geometries.Geometry geom = null; SharpMap.Geometries.BoundingBox bx = null; SqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { string wkt = dr.GetString(0); //[this.GeometryColumn]; SharpMap.Geometries.Geometry g = SharpMap.Converters.WellKnownText.GeometryFromWKT.Parse(wkt); SharpMap.Geometries.BoundingBox bb = g.GetBoundingBox(); if (bx == null) { bx = bb; } else { bx = bx.Join(bb); } } dr.Close(); conn.Close(); return bx; } } } /// <summary> /// Gets the connection ID of the datasource /// </summary> public string ConnectionID { get { return _ConnectionString; } } #endregion #region IProvider Members /// <summary> /// Returns all features with the view box /// </summary> /// <param name="bbox">view box</param> /// <param name="ds">FeatureDataSet to fill data into</param> public void ExecuteIntersectionQuery(SharpMap.Geometries.BoundingBox bbox, SharpMap.Data.FeatureDataSet ds) { List<Geometries.Geometry> features = new List<SharpMap.Geometries.Geometry>(); using (SqlConnection conn = new SqlConnection(_ConnectionString)) { //Get bounding box string string strBbox = GetBoxFilterStr(bbox); string strSQL = "SELECT g.*, g." + this.GeometryColumn + ".Get_WKB() AS sharpmap_tempgeometry "; strSQL += "FROM " + this.Table + " g WHERE "; if (!String.IsNullOrEmpty(_defintionQuery)) strSQL += this.DefinitionQuery + " AND "; strSQL += strBbox; using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, conn)) { conn.Open(); System.Data.DataSet ds2 = new System.Data.DataSet(); adapter.Fill(ds2); conn.Close(); if (ds2.Tables.Count > 0) { FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]); foreach (System.Data.DataColumn col in ds2.Tables[0].Columns) if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry") fdt.Columns.Add(col.ColumnName,col.DataType,col.Expression); foreach (System.Data.DataRow dr in ds2.Tables[0].Rows) { SharpMap.Data.FeatureDataRow fdr = fdt.NewRow(); foreach(System.Data.DataColumn col in ds2.Tables[0].Columns) if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry") fdr[col.ColumnName] = dr[col]; fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]); fdt.AddRow(fdr); } ds.Tables.Add(fdt); } } } } #endregion }}