In my previous post I described how to add multiple pushpins on Bing Map by fetching data from XML data source.In this article I will step through the process to add multiple locations on the Bing Map by fetching the data from SQL Server Database.
We cannot access SQL Server directly from Silverlight. There needs to be a layer in between Silverlight and the SQL Server. You can use WCF Service for this with some custom written ADO.NET Code to access the SQL Server database or you could make use of ADO.NET Entity Services to access the SQL Server database.
Here we will use WCF Service to access the SQL Server Database.
Setting up the Database
Let’s create a database called “Bing Map”.
Create a Customer table as following:
CREATE TABLE [dbo].[tblCustomer](
[CustomerId] [int] NOT NULL,
[CustomerName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Latitude] [decimal](18, 13) NULL,
[Longitude] [decimal](18, 13) NULL,
CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED
(
[CustomerId] ASC
)) ON [PRIMARY]
In my previous post we saw we fetched data from a XML file. So I think it will be a good idea to populate data in table tblCustomer from that XML file.
Let’s say the whole XML is assigned in variable @CustomerXML. Now you can use following query to populate the table:
DECLARE @CustomerXML AS XML
SET @CustomerXML='<Customer>
<Cust CustID="10001" CustomerName="Aaron" Latitude="43.778926" Longitude="-73.958068" />
<Cust CustID="10002" CustomerName="Gabriel" Latitude="41.003764" Longitude="-75.494192" />
<Cust CustID="10003" CustomerName="Jack" Latitude="40.941352" Longitude="-73.871310" />
<Cust CustID="10004" CustomerName="Dale" Latitude="40.930429" Longitude="-73.900575" />
<Cust CustID="10005" CustomerName="Maddox" Latitude="40.923250" Longitude="-73.794536" />
<Cust CustID="10006" CustomerName="Paul" Latitude="40.886874" Longitude="-73.857243" />
<Cust CustID="10008" CustomerName="Samson" Latitude="40.885397" Longitude="-73.849536" />
<Cust CustID="10009" CustomerName="Vance" Latitude="40.883922" Longitude="-73.863246" />
<Cust CustID="10010" CustomerName="Abbott" Latitude="40.882452" Longitude="-73.896702" />
<Cust CustID="10011" CustomerName="Dalton" Latitude="40.876592" Longitude="-73.879747" />
</Customer>'
INSERT INTO [tblCustomer]
SELECT x.ext.value('@CustID[1]', 'int') AS CustID
,x.ext.value('@CustomerName[1]', 'varchar(50)') as CustomerName
,x.ext.value('@Latitude[1]', 'decimal(18,13)') as Latitude
,x.ext.value('@Longitude[1]', 'decimal(18,13)') as Longitude
FROM @CustomerXML.nodes('//Customer/Cust') x(ext)
Now let’s create a store procedure to get the customer data.
CREATE PROCEDURE [dbo].[GetCustomers]
AS
SET NOCOUNT ON
SELECT [CustomerId]
,[CustomerName]
,[Latitude]
,[Longitude]
FROM [tblCustomer] WITH(NOLOCK)
Creating Silverlight application
Create a Silverlight project to show Bing Map. Name the prjoect as DemoSqlServer.
Refer at my first post of this series to create a basic Silverlight application with Bing Map.
Creating WCF Service
Have a look at my “Introduction to WCF Services” article to understand basics of WCF services.
Right click On DemoSqlServer.Web project > Add > New Item
Select Silverlight in Categories and Silverlight-enabled WCF Service from Templates List:
Give Name of the WCF Service DataService.svc
Add Reference of System.Runtime.Serialization & System.ServiceModel
Let’s add a class file called Customer.cs and create a Data Contract for Customer in DemoSqlServer.web project.
using System;
using System.Runtime.Serialization;
namespace DemoSqlServer.Web
{
[DataContract]
public class Customer
{
[DataMember]
public String CustomerName { get; set; }
[DataMember]
public Int32 CustomerId { get; set; }
[DataMember]
public double Latitude { get; set; }
[DataMember]
public double Longitude { get; set; }
}
}
Let’s create an inteface for Service Contract in DemoSqlServer.web project.
using System.Collections.Generic;
using System.ServiceModel;
namespace DemoSqlServer.Web
{
[ServiceContract]
public interface IDataService
{
[OperationContract]
List GetCustomers();
}
}
We will call GetCustomers method to get customer data on the map.
Then change the Dataservice to Impletment IDataService Interface
using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DemoSqlServer.Web
{
[AspNetCompatibilityRequirements(RequirementsMode =
AspNetCompatibilityRequirementsMode.Allowed)]
public class DataService : IDataService
{
static string connectionString =
ConfigurationManager.ConnectionStrings["BingMap"].ConnectionString;
public List GetCustomers()
{
SqlConnection sqlConnection = new SqlConnection(connectionString);
DataSet objSet = new DataSet();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "GetCustomers";
sqlCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = sqlCommand;
sqlDataAdapter.Fill(objSet);
List lstResult = new List();
Customer objCustomer;
if (objSet.Tables.Count > 0)
{
foreach (DataRow dr in objSet.Tables[0].Rows)
{
objCustomer = new Customer();
objCustomer.CustomerName = dr["CustomerName"].ToString();
objCustomer.CustomerId = Convert.ToInt32(dr["CustomerId"]);
objCustomer.Latitude = Convert.ToDouble(dr["Latitude"]);
objCustomer.Longitude = Convert.ToDouble(dr["Longitude"]);
lstResult.Add(objCustomer);
}
}
return lstResult;
}
// Add more operations here and mark them with [OperationContract]
}
}
Here “BingMap” is the name of the connection string for database we created previously.
When Dataservice.svc files is added following is added in web.config file of DemoSqlServer.web project.
<system.serviceModel>
<behaviors>
<serviceBehaviors>
<behavior name="DemoSqlServer.Web.DataServiceBehavior">
<serviceMetadata httpGetEnabled="true" />
<serviceDebug includeExceptionDetailInFaults="false" />
</behavior>
</serviceBehaviors>
</behaviors>
<bindings>
<customBinding>
<binding name="customBinding0">
<binaryMessageEncoding />
<httpTransport />
</binding>
</customBinding>
</bindings>
<serviceHostingEnvironment aspNetCompatibilityEnabled="true" />
<services>
<service behaviorConfiguration="DemoSqlServer.Web.DataServiceBehavior"
name="DemoSqlServer.Web.DataService">
<endpoint address="" binding="customBinding"
bindingConfiguration="customBinding0"
contract="DemoSqlServer.Web.DataService" />
<endpoint address="mex" binding="mexHttpBinding"
contract="IMetadataExchange" />
</service>
</services>
</system.serviceModel>
As we created interface for service contract so we need to change the contract name in web config file
Change the contract name from DemoSqlServer.Web.DataService to DemoSqlServer.Web.IDataService. in Line 24.
To make sure Service is working correctly, view the DataService.svc in browser by Right click > View Browser
You will see the following
Adding Reference of WCF Service
To call the WCF Service in Silverlight project we need add reference of service. To Add reference Right click on the Silverlight project DemoSqlServer and choose “Add Service Reference..”
This will open a Dialog Box.
Click on Discover button which will show all the Services in local project. Select DataService.svc and give a name in Namespace textbox let’s say DataService.
This will add a new file called “ServiceReferences.ClientConfig” as following:
<configuration>
<system.serviceModel>
<bindings>
<customBinding>
<binding name="CustomBinding_IDataService">
<binaryMessageEncoding />
<httpTransport maxReceivedMessageSize="2147483647"
maxBufferSize="2147483647" />
</binding>
</customBinding>
</bindings>
<client>
<endpoint address="http://localhost:4849/DataService.svc"
binding="customBinding"
bindingConfiguration="CustomBinding_IDataService"
contract="DataService.IDataService"
name="CustomBinding_IDataService" />
</client>
</system.serviceModel>
</configuration>
Look at client section have endpoint. That end point specify the address of WCF Service.
That address can be changed to call the WCF service from different location/server.
Consuming the WCF Service
In the previous article we created LocationDataCollection class to load the pushpins on the map. Let’s change the Load method to call WCF Service instead of fetching data from a XML File as following:
public void Load()
{
DemoSqlServer.DataService.DataServiceClient objCust =
new DemoSqlServer.DataService.DataServiceClient();
objCust.GetCustomersCompleted += new EventHandler(GetCustomersCompleted);
objCust.GetCustomersAsync();
}
void GetCustomersCompleted(object sender,
DemoSqlServer.DataService.GetCustomersCompletedEventArgs e)
{
LocationDataCollection locationList = new LocationDataCollection();
foreach (DemoSqlServer.DataService.Customer C in e.Result)
{
LocationData T = new LocationData();
T.Location.Latitude = C.Latitude;
T.Location.Longitude = C.Longitude;
T.CustomerName = C.CustomerName;
T.CustomerId = C.CustomerId;
locationList.Add(T);
}
IEnumerator ppEnum = locationList.GetEnumerator();
while (ppEnum.MoveNext())
{
this.Add((LocationData)ppEnum.Current);
}
}
Download code
- Source code
- Database
- SQL Script
- XML File
Hope you enjoy the article !!!
Till now we saw how to add multiple pushpins on the Bing Map, resizing the pushpin, formatting of tooltip on pushpin. But we used xaml file to add pushpins which is not sufficient when we need to do something complex like change the color of pushpin based on certain condition and many more…..
In the next article I will describe how to add multiple pushpin in code behind in c#.