Before You Begin

In order to load geo-spatial data stored in a database, first you will have to convert the Shapefile to SQL database compliant format using third party utilities. Please refer to the About Shape to SQL Database Conversion Utilities topic for more information. In addition you need to have access to a remote SQL database server that contains the converted geo-spatial data.

What You Will Accomplish

Using a xamWebMap™ control’s MapLayer.Reader property and SqlShapeReader object, you will load geo-spatial data that is stored in a remote SQL Server 2008 database.

Follow These Steps

1. Create a Microsoft® Silverlight® project with hosting in a new Web site.
2. Go to the Host Web site project (the project name ending with .Web)
3. Add a New Silverlight -enabled WCF Service and name it SqlDbService.svc
   
4. Add the following namespaces in the SqlDbService class:

   In Visual Basic:

   Imports System.Data.SqlClient
   Imports System.Collections.Generic
   Imports System.Text
    

   In C#:

   using System.Collections.Generic;
   using System.Data.SqlClient;            
   using System.Text;
    

5. Add the following method in the SqlDbService class

   In Visual Basic:

   <OperationContract()> _
   Public Function GetData(ByVal connectionString As String, ByVal commandText As String) As IEnumerable(Of Dictionary(Of String, String))
       Dim list As New List(Of Dictionary(Of String, String))()
   
       Dim sqlConnection As New SqlConnection()
       sqlConnection.ConnectionString = connectionString
       Dim sqlCommand As New SqlCommand()
       sqlCommand.Connection = sqlConnection
       sqlCommand.CommandType = System.Data.CommandType.Text
       sqlCommand.CommandText = commandText
   
       sqlConnection.Open()
       Dim reader As SqlDataReader = sqlCommand.ExecuteReader()
   
       While reader.Read()
           Dim valueDictionary As New Dictionary(Of String, String)()
   
           For i As Integer = 0 To reader.FieldCount - 1
               valueDictionary.Add(reader.GetName(i), reader.GetValue(i).ToString())
           Next
   
           list.Add(valueDictionary)
       End While
   
       sqlConnection.Close()
       sqlConnection.Dispose()
   
       Return list
   End Function
   

   In C#:

   [OperationContract]
   public IEnumerable<Dictionary<string, string>> GetData(string connectionString, string commandText)
   {
       List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
   
       SqlConnection sqlConnection = new SqlConnection();
       sqlConnection.ConnectionString = connectionString;
       SqlCommand sqlCommand = new SqlCommand();
       sqlCommand.Connection = sqlConnection;
       sqlCommand.CommandType = System.Data.CommandType.Text;
       sqlCommand.CommandText = commandText;
   
       sqlConnection.Open();
       SqlDataReader reader = sqlCommand.ExecuteReader();
   
       while (reader.Read())
       {
           Dictionary<string, string> valueDictionary = new Dictionary<string, string>();
   
           for (int i = 0; i < reader.FieldCount; i++)
           {
               valueDictionary.Add(reader.GetName(i), reader.GetValue(i).ToString());
           }
   
           list.Add(valueDictionary);
       }
   
       sqlConnection.Close();
       sqlConnection.Dispose();
   
       return list;
   }
   

6. Save the SqlDbService file. In the Project Explorer, right click on SqlService.svc, and select the View it in Browser menu item to ensure that the service works.
7. Right click on the Silverlight Application project (the project name NOT ending with “.Web”) And Select the Add Service Reference menu item.
8. Click the Discover button and wait until the SqlDbService is discovered
   
9. Change the entry in Namespace textbox to "SqlDbServiceReference" and click the OK button
10. In the Solution Explorer, add the following reference to the Silverlight Application project:
    • Infragistics.Silverlight.DataVisualization.v9.2.dll
    • Infragistics.Silverlight.DataVisualization.Controls.v9.2.dll
    • Infragistics.Silverlight.DataVisualization.Map.v9.2.dll
11. Add the following namespace declarations for xamWebMap in the MainPage.xaml

    In XAML:

    xmlns:igMap="clr-namespace:Infragistics.Silverlight.Map;assembly=Infragistics.Silverlight.DataVisualization.Map.v9.2" 
    xmlns:igCtl="clr-namespace:Infragistics.Silverlight.Controls;assembly=Infragistics.Silverlight.DataVisualization.Controls.v9.2" 
    
    

12. Add the xamWebMap control to the page.

    In XAML:

    <igMap:XamWebMap x:Name="xamMap">
       <!-- TODO: Add Map Layer -->
    </igMap:XamWebMap>
    

13. Create two MapLayer objects to display the country and road data.

    In XAML:

    <igMap:XamWebMap.Layers>
    	<!-- TODO: Add Map Country Layer -->
    	<igMap:MapLayer LayerName="CountryLayer" Fill="Gray" 
    	FillMode="None" >
    	    <igMap:MapLayer.Reader>
    		<!-- NOTE: Replace SpatialDataColumn with your data column name -->
    		<igMap:SqlShapeReader DataMapping="Data=SpatialDataColumn"/>
    	    </igMap:MapLayer.Reader>
    	</igMap:MapLayer>
    	<!-- TODO: Add Map Road Layer -->
    	<igMap:MapLayer LayerName="RoadLayer" VisibleFromScale="0" 
    	Fill="Yellow" FillMode="None">
    	    <igMap:MapLayer.Reader>
    		<!-- NOTE: Replace SpatialDataColumn with your data column name -->
    		<igMap:SqlShapeReader DataMapping="Data=SpatialColumn"/>
    	    </igMap:MapLayer.Reader>
    	</igMap:MapLayer>
    </igMap:XamWebMap.Layers>
    

14. In the MainPage.xaml.cs file, add the following namespace declarations

    In Visual Basic:

    Imports Infragistics.Silverlight.Map
    Imports Infragistics.Silverlight.Controls
    

    In C#:

    using Infragistics.Silverlight.Map;        
    using Infragistics.Silverlight.Controls;    
    

15. Add the following code to the constructor.

    In Visual Basic:

    Public Sub New()
        InitializeComponent()
    
        Dim connString As String = "Data Source=ServerName;Initial Catalog=DbName;Integrated Security=True"
        ' - ServerName is name of server that contains Geospatial Database 
        ' - DbName is name of server that contains Geospatial Data
        Dim countryData As New SqlDbServiceReference.SqlDbServiceClient()
        AddHandler countryData.GetDataCompleted, AddressOf countryData_GetDataCompleted
        countryData.GetDataAsync(connString, "select SpatialDataColumn from countryTable")
        ' - SpatialDataColumn is name of column with Spatial Data
        ' - CountryTable is name of table containg columns with Country Spatial Data
        Dim roadData As New SqlDbServiceReference.SqlDbServiceClient()
        AddHandler roadData.GetDataCompleted, AddressOf roadData_GetDataCompleted
        roadData.GetDataAsync(connString, "select SpatialDataColumn from roadTable")
        ' - SpatialDataColumn is name of column with Spatial Data
        ' - RoadTable is name of table containg columns with Road Spatial Data
    End Sub
     

    In C#:

    public MainPage()
    {
        InitializeComponent();
    
        string connString = "Data Source=ServerName;Initial Catalog=DbName;Integrated Security=True";
        // - ServerName is name of server that contains Spatial Database 
        // - DatabaseName is name of server that contains Spatial Data
        SqlDbServiceReference.SqlDbServiceClient countryData = new SqlDbServiceReference.SqlDbServiceClient();
        countryData.GetDataCompleted += countryData_GetDataCompleted;
        countryData.GetDataAsync(connString, "SELECT SpatialDataColumn FROM CountryTable");
        // - SpatialDataColumn is name of column with Spatial Data
        // - CountryTable is name of table containg columns with Country Spatial Data
        SqlDbServiceReference.SqlDbServiceClient roadData = new SqlDbServiceReference.SqlDbServiceClient();
        roadData.GetDataCompleted += roadData_GetDataCompleted;
        roadData.GetDataAsync(connString, "SELECT SpatialDataColumn FROM RoadTable");
        // - SpatialDataColumn is name of column with Spatial Data
        // - RoadTable is name of table containg columns with Road Spatial Data
    } 

16. Add the following two methods to handle the SqlServiceClient object’s GetDataCompleted events

    In Visual Basic:

    Private Sub roadData_GetDataCompleted(ByVal sender As Object, ByVal e As SqlDbServiceReference.GetDataCompletedEventArgs)
        Dim sqlReader As SqlShapeReader = TryCast(xamMap.Layers("RoadLayer").Reader, SqlShapeReader)
        If sqlReader IsNot Nothing Then
            sqlReader.DataSource = e.Result
            xamMap.Layers("RoadLayer").ImportAsync()
        End If
    End Sub
    
    Private Sub countryData_GetDataCompleted(ByVal sender As Object, ByVal e As SqlDbServiceReference.GetDataCompletedEventArgs)
        Dim sqlReader As SqlShapeReader = TryCast(xamMap.Layers("CountryLayer").Reader, SqlShapeReader)
        If sqlReader IsNot Nothing Then
            sqlReader.DataSource = e.Result
            xamMap.Layers("CountryLayer").ImportAsync()
        End If
    End Sub
    
    

    In C#:

    void roadData_GetDataCompleted(object sender, SqlDbServiceReference.GetDataCompletedEventArgs e)
    {
        SqlShapeReader sqlReader = xamMap.Layers["RoadLayer"].Reader as SqlShapeReader;
        if (sqlReader != null)
        {
            sqlReader.DataSource = e.Result;
            xamMap.Layers["RoadLayer"].ImportAsync();
        }
    }
    void countryData_GetDataCompleted(object sender, SqlDbServiceReference.GetDataCompletedEventArgs e)
    {
        SqlShapeReader sqlReader = xamMap.Layers["CountryLayer"].Reader as SqlShapeReader;
        if (sqlReader != null)
        {
            sqlReader.DataSource = e.Result;
            xamMap.Layers["CountryLayer"].ImportAsync();
        }
    }
     

17. Run the application. The xamWebMap control will load geo-spatial data stored in a remote database located on SQL Server 2008.

About Shape to SQL Database Conversion Utilities