SQLCLR & .NET CLR互编程性

（1）可以在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

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

 

using

System;

using

System.Collections.Generic;

using

System.Linq;

using

System.Text;

//

添加命名空间

using

Microsoft.SqlServer.Types;

using

System.Data.SqlClient;

using

System.Data.SqlTypes;

using

System.Configuration;

using

System.Data;

namespace

SpatialDBDemo

{

class

Program

{

static

void

Main(

string

[] args)

{

//

产生一个点类型的空间数据

var point

=

SqlGeometry.Point(

107.04353

,

28.870554

,

4326

);

var pointStart

=

SqlGeometry.Point(

107.04352

,

28.870554

,

4326

);

var pointEnd

=

SqlGeometry.Point(

103.84041

,

29.170240

,

4326

);

Console.WriteLine(point.STX);

Console.WriteLine(point.STY);

Console.WriteLine(point.ToString());

//

求两点间的距离

var result

=

pointStart.STDistance(pointEnd);

Console.WriteLine(result);

//

创建空间对象到数据库

CreateSpatialToSQL();

Console.ReadKey();

}

public

static

void

CreateSpatialToSQL()

{

//

定义一个多边形

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(ConfigurationSettings.AppSettings[

"

SQL2008

"

]))

{

if

(conn.State

==

ConnectionState.Closed) conn.Open();

using

(var cmd

=

new

SqlCommand(sql, conn))

{

int

row

=

cmd.ExecuteNonQuery();

}

}

}

}

}