Using Infragistics Map control extensions with SQL Server 2008 via WCF

Often you may want to use the Map components Infragstics LightSwitch for a spatial data type, different from shapefiles.

In this case the NetAdvantage for Visual Studio LightSwitch offers convenient option of using WCF as a source of spatial data.
Thus you can use different data, such as implementation-reading of the data depends solely on you. This article will be shown examples of spatial data from SQL Server 2008.

Will be shown examples of different implementation of WCF – as a WCF Service Application, WCF Service in the ASP.Net Application, Silverlight-enabled WCF Service.
The reason I talk about different types of WCF is in need of different options for implementation. Client part of the program is clean LightSwitch Silverlight, which has some features using WCF. Referencing the data is hidden in the Map control extensions. The only thing you need to make user Visua Studio LightSwitch is set to the appropriate endpoint Uri WCF service.

Requirements for implementation of WCF for Infragistics Map control extensions.

Each as far as WCF service must have:
public class SqlDbService or public interface ISqlDbService with ServiceContract attribute. When we have in Interface ISqlDbService SqlDbService class should inherit ISqlDbService.

You should have in this class a method, named GetSpatialData. Declaration of the method should have OperationContract attribute.

The implementation below is used in all cases except Silverlight-enabled WCF Service

Interface ISqlDbService:

   1: [ServiceContract(Namespace = "")]

   2:  public interface ISqlDbService

   3:  {

   4:      [OperationContract]

   5:      IEnumerable<Dictionary<string, string>> GetSpatialData();

   6:  }

 

SqlDbService class:

   1:  

   2:     [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]

   3:     public class SqlDbService : ISqlDbService

   4:     {

   5:  

   6:  

   7:         #region GetSpatialData

   8:  

   9:         public IEnumerable<Dictionary<string, string>> GetSpatialData()

  10:         {

  11:  

  12:             return GetCountries();

  13:         }

  14:         #endregion //GetSpatialData

  15:  

  16:         private IEnumerable<Dictionary<string, string>> GetCountries()

  17:         {

  18:             string connectionString =

  19:             System.Configuration.ConfigurationManager.AppSettings["connectionString"];

  20:             List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();

  21:  

  22:             SqlConnection sqlConnection = new SqlConnection();

  23:             sqlConnection.ConnectionString = connectionString;

  24:             SqlCommand sqlCommand = new SqlCommand();

  25:             sqlCommand.Connection = sqlConnection;

  26:             sqlCommand.CommandText = "SELECT geom, CNTRY_NAME, POP_CNTRY FROM world";

  27:             //sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

  28:             //sqlCommand.CommandText = "get_WorldData";

  29:  

  30:             sqlConnection.Open();

  31:             SqlDataReader reader = sqlCommand.ExecuteReader();

  32:  

  33:             if (reader != null)

  34:                 while (reader.Read())

  35:                 {

  36:                     Dictionary<string, string> valueDictionary = new Dictionary<string, string>();

  37:  

  38:                     for (int i = 0; i < reader.FieldCount; i++)

  39:                     {

  40:                         valueDictionary.Add(reader.GetName(i), reader.GetValue(i).ToString());

  41:                     }

  42:  

  43:                     list.Add(valueDictionary);

  44:                 }

  45:  

  46:             sqlConnection.Close();

  47:             sqlConnection.Dispose();

  48:  

  49:             return list;

  50:         }

  51:  

  52:     }

 

Implementation for Silverlight-enabled WCF Service :

   1: [ServiceContract(Namespace = "")]

   2: [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]

   3: public class SqlDbService

   4: {

   5:  

   6:     #region GetSpatialData

   7:     [OperationContract]

   8:     public IEnumerable<Dictionary<string, string>> GetSpatialData()

   9:     {

  10:         string connectionString =

  11:                 System.Configuration.ConfigurationManager.AppSettings["connectionString"];

  12:         List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();

  13:  

  14:         SqlConnection sqlConnection = new SqlConnection();

  15:         sqlConnection.ConnectionString = connectionString;

  16:         SqlCommand sqlCommand = new SqlCommand();

  17:         sqlCommand.Connection = sqlConnection;

  18:         sqlCommand.CommandText = "SELECT geom, CNTRY_NAME, POP_CNTRY FROM world";

  19:  

  20:         sqlConnection.Open();

  21:         SqlDataReader reader = sqlCommand.ExecuteReader();

  22:  

  23:         if (reader != null)

  24:             while (reader.Read())

  25:             {

  26:                 Dictionary<string, string> valueDictionary = new Dictionary<string, string>();

  27:  

  28:                 for (int i = 0; i < reader.FieldCount; i++)

  29:                 {

  30:                     valueDictionary.Add(reader.GetName(i), reader.GetValue(i).ToString());

  31:                 }

  32:  

  33:                 list.Add(valueDictionary);

  34:             }

  35:  

  36:         sqlConnection.Close();

  37:         sqlConnection.Dispose();

  38:  

  39:         return list;

  40:     }

  41:     #endregion //GetSpatialData

  42: }

In all cases you should have class with the exact name SqlDbService and method with name GetSpatialData.

Declaration of the class / interface should have ServiceContract attribute. Declaration of the method should have OperationContract attribute (general requirements for WCF).

 

It is also possible to use any kind of data in your method implementation. Just the return data should be a nested collection from type:

   1: IEnumerable<Dictionary<string, string>>

 

Next snippet shows implementation of the GetSpatialData method, using stored procedure:

Stored procedure code:

   1: USE [SqlSpatialDemo]

   2: GO

   3:  

   4: /****** Object:  StoredProcedure [dbo].[get_WorldData]    Script Date: 08/23/2011 10:46:56 ******/

   5: SET ANSI_NULLS ON

   6: GO

   7:  

   8: SET QUOTED_IDENTIFIER ON

   9: GO

  10:  

  11: -- =============================================

  12: -- Author:        <Author,,MIHAIL MATEEV>

  13: -- Create date: <Create Date,,3/8/2010>

  14: -- Description:    <Description,,GetWorldLayer>

  15: -- =============================================

  16: CREATE PROCEDURE [dbo].[get_WorldData]

  17:     -- Add the parameters for the stored procedure here

  18:     --<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 

  19:     --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>

  20: AS

  21: BEGIN

  22:     -- SET NOCOUNT ON added to prevent extra result sets from

  23:     -- interfering with SELECT statements.

  24:     SET NOCOUNT ON;

  25:  

  26:     -- Insert statements for procedure here

  27:     SELECT geom, CNTRY_NAME, POP_CNTRY FROM world

  28: END

  29:  

  30: GO

 

GetSpatialData implementation:

   1: [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]

   2: public class SqlDbService : ISqlDbService

   3: {

   4:  

   5:  

   6:     #region GetSpatialData

   7:  

   8:     public IEnumerable<Dictionary<string, string>> GetSpatialData()

   9:     {

  10:  

  11:         return GetCountries();

  12:     }

  13:     #endregion //GetSpatialData

  14:  

  15:     private IEnumerable<Dictionary<string, string>> GetCountries()

  16:     {

  17:         string connectionString =

  18:         System.Configuration.ConfigurationManager.AppSettings["connectionString"];

  19:         List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();

  20:  

  21:         SqlConnection sqlConnection = new SqlConnection();

  22:         sqlConnection.ConnectionString = connectionString;

  23:         SqlCommand sqlCommand = new SqlCommand();

  24:         sqlCommand.Connection = sqlConnection;

  25:         sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

  26:         sqlCommand.CommandText = "get_WorldData";

  27:  

  28:         sqlConnection.Open();

  29:         SqlDataReader reader = sqlCommand.ExecuteReader();

  30:  

  31:         if (reader != null)

  32:             while (reader.Read())

  33:             {

  34:                 Dictionary<string, string> valueDictionary = new Dictionary<string, string>();

  35:  

  36:                 for (int i = 0; i < reader.FieldCount; i++)

  37:                 {

  38:                     valueDictionary.Add(reader.GetName(i), reader.GetValue(i).ToString());

  39:                 }

  40:  

  41:                 list.Add(valueDictionary);

  42:             }

  43:  

  44:         sqlConnection.Close();

  45:         sqlConnection.Dispose();

  46:  

  47:         return list;

  48:     }

  49:  

  50: }

 

Demo Application

Sample application is based on the application, used in t the article “Create Thematic Maps in Visual Studio LightSwitch with Infragistics Geospatial Map“.

You could use any of the sample WCF implementations.

Screens below show variant with WCF Service in the ASP.Net Application.

Run the web application.

LsMap_WCF_Pic01

In the Visual Studio LightSwitch application –> Screens –> Customers Map –>Country (Infragistics Map Control)  change the settings in accordance with the screen below:

LsMap_WCF_Pic02

Settings are the same for both” Infragistics Map Control and Infragistics Geospatial Map.

Run the application and select the screen, named Customers Map.

LsMap_WCF_Pic03

Enjoy the spatial data from SQL Server 2008!

LsMap_WCF_Pic04

LsMap_WCF_Pic05

Sample application  is available here:

SqlSpatialDemo database is available here:

WCF Service in the ASP.Net Application implementation is available here:

WCF Service Application implementation is available here:

Silverlight-enabled WCF Service implementation is available here:

About Mihail Mateev

am a Microsoft Regional Director currently living in Sofia, Bulgaria. My interests range from technology to entrepreneurship. I am also interested in programming, web development, and education. Technical Consultant, Community enthusiast, PASS Regional Mentor for Central Eastern Europe, chapter lead, Microsoft MVP – Microsoft Azure. Organizer of SQLSaturday, Azure Bootcamp, IoT and JavaScript conferences. My experience is in various areas related to Microsoft technologies, including Windows Platform, ASP.Net MVC, MS SQL Server and Microsoft Azure. I have a PhD in cloud computing and am a university lecturer on Smart Homes and Smart Energy IoT Solutions
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s