Blog

Using Infragistics Map control extensions with SQL Server 2008 via WCF

[Infragistics] Mihail Mateev / Tuesday, August 23, 2011

Often you may want to use the Map components Infragistics 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 Visual 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.

In the Visual Studio LightSwitch application –> Screens –> Customers Map –>Country (Infragistics Map Control)  change the settings.

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

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

Enjoy the spatial data from SQL Server 2008!