SQL Server 2008空间数据应用系列六：基于SQLCRL的空间数据可编程性

友情提示，您阅读本篇博文的先决条件如下：

本文示例基于Microsoft SQL Server 2008 R2调测。
具备 Transact-SQL 编程经验和使用 SQL Server Management Studio 的经验。
具有使用 Microsoft Visual Studio 进行 Microsoft .NET Framework开发的经验。
熟悉或了解Microsoft SQL Server 2008中的空间数据类型。
具备相应（比如OGC）的GIS专业理论知识。

Microsoft .NET应用通常都是宿主在操作系统平台的.NET Framework之上，如果想在SQL Server 2008中使用.NET进行托管代码，SQL Server 2008将运行一个属于自己的.NET Framewrok平台运行环境（SQLOS），SQLOS和.NET CLR将共享数据库引擎进程空间，因此基于SQL Server 2008的CLR也被称为的SQLCLR。基于SQL Server 2008的核心组件SQLCRL提供了友好的的可编程性支持，SQL Server数据库引擎将使用由CLR提供的功能无缝地提供了多种功能，其中包括使用空间数据类型goegraphy和goemetry的数据的可编程性支持。

一、SQLCLR & .NET CLR互编程性

可以在SQL Server 2008的安装目录下找到SQLCLR与.NET CLR共享的空间数据类型共享库组件（Microsoft.SqlServer.Types.dll），该组件除了支持基于SQL Server 2008的空间数据类型数据库编程，同时也支持基于.NET Framework的面向对象编程，包括对VB.NET、C#的多语言的支持，该组件位于如下安装目录中：

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dll

通过下面这个示例程序可以演示基于Microsoft.SqlServer.Types.dll在SQLCLR和.NET CLR之间的双向互支持性。

SQLCLR：定义一个地理坐标点  
declare @geom geometry;  
set @geom = geometry::Point(107.04352,28.870554,4326);  
select @geom;  
------------------------------------------------------  
.NET CLR：定义一个地理坐标点  
static void Main(string[] args)  
{  
    var point = SqlGeometry.Point(107.04352, 28.870554, 4326);  
    Console.WriteLine(point.STX);  
    Console.WriteLine(point.STY);  
    Console.WriteLine(point.ToString());  
}

在.NET CLR中可以使用所有SQLCLR编程中所提供的编程接口，也就是说在.NET CLR中变成不仅仅只是局限于再空间对象的定义上，还包括对象的相关属性、方法的支持，如下代码块演示了SQLCLR和.NET CLR编程实现地理测距函数的对比。

declare @geom geometry;  
set @geom = geometry::Point(107.04352,28.870554,4326);  
declare @end geometry;  
set @end = geometry::Point(103.84041, 29.170240,4326);  
select @geom.STDistance(@end);  
----------------------------------------------------------------  
var pointStart = SqlGeometry.Point(107.04352, 28.870554, 4326);  
var pointEnd = SqlGeometry.Point(103.84041, 29.170240, 4326);  
var result = pointStart.STDistance(pointEnd);  
Console.WriteLine("地理距离：" + result + "(米)");

二、创建空间对象到数据库

Microsoft.SqlServer.Types.dll提供友好的.NET CLR编程性，同样我们可以将在.NET CLR中创建的对象插入到空间数据库中进行查询，以便于做空间分析、计算。下面演示通过.NET CLR构造一个多边形空间对象并插入到SQL Server 2008数据库中。

static void Main(string[] args)  
{  
    //定义一个多边形  
    var polygon = SqlGeography.STGeomFromText(  
        new SqlChars(  
        new SqlString("POLYGON ((-114.01611328125 42.0003251483162, -114.0380859375 42.0003251483162," 
            + "-113.994140625 37.0200982013681, -109.05029296875 37.0200982013681, -109.09423828125 41.0130657870063, " 
            + "-111.07177734375 41.0462168145206, -111.07177734375 42.0003251483162, -114.01611328125 42.0003251483162))",  
            111)),  
            4326);  
    var sql = "insert Cities (CityName,CityLocation) values ('test','" + polygon.ToString() + "')";  
    InsertToDB(sql);  
}  
 
private static void InsertToDB(string sql)  
{  
    using (var conn = new SqlConnection(ConfigurationManager.AppSettings["SQL2008"]))  
    {  
        if (conn.State == ConnectionState.Closed) conn.Open();  
        using (var cmd = new SqlCommand(sql, conn))  
        {  
            int row = cmd.ExecuteNonQuery();  
        }  
    }  
}

三、查询数据库空间数据

首先来看看基于SQL Server Management Studio 查询刚刚入库的记录，通过空间结果可以直接预览查询结果。

declare @city geometry;  
select @city = CityLocation from Cities where ID=5;  
select @city;  
select @city.STArea() as 面积;--求面积 
 
0x000000000104080000000000000008815CC035B18AA70A0045400000000070825CC035B18AA70A00454000000000
A07F5CC0CECAEE93928242400000000038435BC0CECAEE93928242400000000008465BC0BCF1C323AC814440000000
0098C45BC01572BD6EEA8544400000000098C45BC035B18AA70A0045400000000008
(1 行受影响)
面积
22.6802255629445
(1 行受影响)

同样可以使用.NET CLR对其进行编程实现，这其实和查询普通数据是没有区别的，只是将查询结果转为为的是空间数据类型。可以通过如下代码实现查询空间数据到应用程序中。

static void Main(string[] args)  
{  
    var sql = "select CityLocation from Cities where ID = 5";  
    var result = QueryDB(sql);  
    var polygon = SqlGeography.STGeomFromText(  
        new SqlChars(  
        new SqlString(result)), 4326);  
    Console.WriteLine(polygon.ToString());   
}  
 
private static string QueryDB(string sql)  
{  
    using (var conn = new SqlConnection(ConfigurationManager.AppSettings["SQL2008"]))  
    {  
        if (conn.State == ConnectionState.Closed) conn.Open();  
        using (var cmd = new SqlCommand(sql, conn))  
        {  
            return cmd.ExecuteScalar().ToString();  
        }  
    }  
}