空间数据库学习笔记（六）：在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) 的空间数据展现