空间数据库学习笔记（六）：在silverlight BingMap地图中展现空间数据库

（一）新建空间数据库，并插入条数据，代码如下。

use DBDemo;
CREATE TABLE DrawnPolygons(
[ ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ Name ] [ varchar ] ( 50 ) NOT NULL ,
[ Polygon ] [ geography ] NOT NULL )
GO
insert into DrawnPolygons(Name,Polygon)
values ( ' 测试 ' ,geography::STGeomFromText( ' POLYGON((60 60 , 65 65,75 75,80 80, 60 60 )) ' , 4326 )

);

（二）新建WCF服务

因为Silverlight不支持Microsoft.SqlServer.Types.dll，所以要写成服务方法，通过客户端调用，代码如下

（1）数据契约DrawnPolygons.cs

[DataContract]
public class DrawnPolygons
{
[DataMember]
public int ID { get ; set ; }
[DataMember]
public string Name { get ; set ; }
[DataMember]
public string Xaml { get ; set ; }
}

(2)服务契约SpaceSQL.svc

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
// 添加命名空间
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Data.SqlTypes;
// 添加空间数据库命名空间
using Microsoft.SqlServer.Types;
// 添加命名空间
using System.ServiceModel.Activation;


namespace SLSpaceSQL.Web
{
// 注意: 使用“重构”菜单上的“重命名”命令，可以同时更改代码、svc 和配置文件中的类名“SpaceSQL”。
[ServiceContract(Namespace = "" )]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public class SpaceSQL
{
private DataTable Query( string sql)
{
string cstring = ConfigurationManager.ConnectionStrings[ " BingMapsDB " ].ConnectionString;
SqlConnection conn = new SqlConnection(cstring);
if (conn.State == ConnectionState.Closed) conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
adapter.Fill(ds);
return ds.Tables[ 0 ];
}

[OperationContract]
public List < DrawnPolygons > QueryPolygons()
{
var sql = " SELECT * FROM [DrawnPolygons] " ;

var result = Query(sql);
List < DrawnPolygons > areas = null ;
if (result != null && result.Rows.Count > 0 )
{
areas = new List < DrawnPolygons > ();
foreach (DataRow row in result.Rows)
{
areas.Add( new DrawnPolygons
{
ID = int .Parse(row[ " ID " ].ToString()),
Name = row[ " NAME " ].ToString(),
Xaml = ToXaml(row[ " Polygon " ], row[ " ID " ].ToString())
});
}
}
return areas;
}

private string ToXaml( object polygon, string id)
{
StringBuilder sb = new StringBuilder();
// 将数据库查询出的空间数据构造为SQL Server空间数据类型对象
var geo = SqlGeography.STGeomFromText(
new SqlChars(
new SqlString(polygon.ToString())), 4326 );
// 将空间数据构造为Bing Maps图形（多边形）对象的Xaml文本，以返回到客户端直接解析Xaml为Silverlight程序中的对象。
for ( int j = 1 ; j <= geo.NumRings(); j ++ )
{
if (geo.RingN(j).STNumPoints() > 1 )
{
sb.Append( " <m:MapPolygon xmlns:m=\"clr-namespace:Microsoft.Maps.MapControl;assembly=Microsoft.Maps.MapControl\" " );
sb.Append( " Fill=\"Red\" Locations=\" " );
for ( int k = 1 ; k <= geo.RingN(j).STNumPoints(); k ++ )
{
if (k > 1 ) sb.Append( " " );
sb.Append(String.Format( " {0:0.#####},{1:0.#####} " ,
( double )geo.RingN(j).STPointN(k).Lat,
( double )geo.RingN(j).STPointN(k).Long));
}
sb.AppendLine( " \"/> " );
}
}

return sb.ToString();
}

}
}

（3）web.config

<? xml version="1.0" encoding="utf-8" ?>

<!--
有关如何配置 ASP.NET 应用程序的详细消息，请访问
http://go.microsoft.com/fwlink/?LinkId=169433
-->

< configuration >
< system.web >
< compilation debug ="true" targetFramework ="4.0" />
</ system.web >
< system.serviceModel >
< behaviors >
< serviceBehaviors >
< behavior name ="" >
< serviceMetadata httpGetEnabled ="true" />
< serviceDebug includeExceptionDetailInFaults ="false" />
</ behavior >
</ serviceBehaviors >
</ behaviors >
< serviceHostingEnvironment multipleSiteBindingsEnabled ="true" />
</ system.serviceModel >
< connectionStrings >
< add name ="BingMapsDB" connectionString ="Data Source=WIN-VJ53NI8SPD0;Initial Catalog=DBDemo;User ID=sa;Password=123;" />
</ connectionStrings >
</ configuration >

 (三)silverlight客户端调用服务

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using SLSpaceSQL.ServiceReference1;
// 添加命名空间
using Microsoft.Maps;
using Microsoft.Maps.MapControl;
using System.Windows.Markup;

namespace SLSpaceSQL
{
public partial class MainPage : UserControl
{
public MainPage()
{
InitializeComponent();
this .Loaded += new RoutedEventHandler(MainPage_Loaded);
}

void MainPage_Loaded( object sender, RoutedEventArgs e)
{
SpaceSQLClient client = new SpaceSQLClient();
client.QueryPolygonsCompleted += new EventHandler < QueryPolygonsCompletedEventArgs > (client_QueryPolygonsCompleted);
client.QueryPolygonsAsync();
}

void client_QueryPolygonsCompleted( object sender, QueryPolygonsCompletedEventArgs e)
{
if (e.Error != null ) return ;
if (e.Result != null )
{
for ( int i = 0 ; i < e.Result.Count; i ++ )
{
// 将空间数据所构造的Xaml语言标记解析为Bing Maps Silverlight Control中的多边形（MapPolygon）对象。
MapPolygon polygon = (MapPolygon)XamlReader.Load(e.Result[i].Xaml);
polygon.Fill = new SolidColorBrush(Colors.Red);
// 将多边形对象添加到地图中呈现。
map.Children.Add(polygon);
}
}
}
}
}

（四）图形显示

源代码下载

参考资料：SQL Server 2008空间数据应用系列七：基于Bing Maps(Silverlight) 的空间数据展现