Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Dinesh's Blog
Browse by Tags · View All
brh 20
#SILVERLIGHT 19
#DOTNET 18
silverlight 18
#ASP.NET 15
Silverlight 4.0 9
bing 7
bing maps 6
wcf services 5
map 5

Archive · View All
August 2010 10
September 2010 3
July 2010 3
June 2010 3
October 2010 1

Bing Map Binding With SQL Server Database

Jul 13 2010 6:39PM by Dinesh Sodani   

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

Dataservice

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

Runing Data Service

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..

Add WCF Service Reference

This will open a Dialog Box.

Data Service Discover

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

  1. Source code
  2. Database
  3. SQL Script
  4. 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#.

Tags: bing maps, map, silverlight, bing, brh, xml, MapItemsControl, RenderTransform, Pushpin, Pushpin.RenderTransform, Resizing Pushpin, #DOTNET, #SILVERLIGHT, wcf data contracts, wcf, wcf services, wcf contracts, wcf service contracts, Windows Communication Foundation, bing map, bing map binding with sql server, sql server, #MAPS, #XML,


Dinesh Sodani
30 · 6% · 1807
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]