Introduction
In this article I will explain how to add Pushpins to a Bing map from locations (Latitude/Longitude) stored in a SQL Server database table
Description
We will use “Bing Maps AJAX Control 7.0 ISDK” to create Bing map. You have to create a key to use Bing map in your applications. You can refer to my article “How to integrate Bing map in your website” to create an application key.
First we will create a table with some sample data in SQL Server database to store latitudes and longitudes, then we will use these lat longs to plot Pushpins to the map. We will add click event to these pushpins that will zoom the map with clicked pushpin as its center
Pushpin
A pushpin is created using Microsoft.Maps.Pushpin class whose constructor takes Location object as parameter. It is added to the map using map.entities.push method which takes Pushpin object as parameter. Events can be added to a pushpin using Microsoft.Maps.Events.addHandler method that takes pushpin, event type and callback function name as parameters
Example:
var pushpin = new Microsoft.Maps.Pushpin(new Microsoft.Maps.Location(19.890723, 84.624022), null);
map.entities.push(pushpin);
Microsoft.Maps.Events.addHandler(pushpin, 'mouseup', ZoomIn);
function ZoomIn(e){}
Step:1 Create a table in SQL Server named “Locations” with two columns, Latitude and Longitude
Step:2 Create a new ASP.NET Application and add a DIV tag with an id “myMap” to hold the map and a Literal control to output script at runtime
<body onload="GetMap();">
<form id="form1" runat="server">
<div id="myMap"
style="position:relative; width:600px; height:600px;">
</div>
<asp:Literal ID="Literal1" runat="server">
</asp:Literal>
</form>
</body>
Step:3 Write a method in the code behind file to generate pushpin objects. We have also added event handler to the pushpins to zoom map after clicking on the pushpins
private string GetLocations()
{
string Locations = "";
using (SqlConnection con =
new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("SELECT Latitude, Longitude FROM Locations", con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Locations += "var pushpin = new Microsoft.Maps.Pushpin(new Microsoft.Maps.Location("+
reader["Latitude"].ToString()+", "+reader["Longitude"].ToString()+
"), null);Microsoft.Maps.Events.addHandler(pushpin, 'mouseup', ZoomIn);map.entities.push(pushpin);";
}
}
return Locations;
}
It will generate pushpin objects for all the rows in the table like following:
var pushpin = new Microsoft.Maps.Pushpin(new Microsoft.Maps.Location(26.273714, 84.902344), null); Microsoft.Maps.Events.addHandler(pushpin, 'mouseup', ZoomIn);map.entities.push(pushpin); var pushpin = new Microsoft.Maps.Pushpin(new Microsoft.Maps.Location(19.890723, 84.624022), null); Microsoft.Maps.Events.addHandler(pushpin, 'mouseup', ZoomIn);map.entities.push(pushpin);
Step:4 Write following in the page load event. It adds pushpins to the script by appending pushpin objects returned using GetLocations method. GetMap function is called from BODY onLoad event. Another function ZoomIn is called after clicking on the pushpin
protected void Page_Load(object sender, EventArgs e)
{
string Locations = GetLocations();
Literal1.Text= @"
<script type='text/javascript' src='http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=7.0'>
</script>
<script type='text/javascript'>
var map = new Microsoft.Maps.Map(document.getElementById('myMap'), {
credentials: 'Your Bing Map Key'
});
function GetMap() {
map.entities.clear();
" + Locations+ @"
function ZoomIn(e){
if (e.targetType == 'pushpin'){
var location = e.target.getLocation();
map.setView({
zoom:5,
center: location
});
}
}
}
</script>";
}
May 10, 2013 at 03:19
Does this method work also if i am using MVC
May 14, 2013 at 14:07
Yes, it will work with MVC
April 11, 2013 at 13:29
Thanks Deepak
it is really helpful
March 24, 2013 at 06:35
First thank you for yout example i need to add infobox with the pins i tried many solutions but none of them work’s can you help me
February 13, 2013 at 04:51
I tried you example. I can see that it is pulling from the database but nothing other than the Map is displaying. What did I miss?
February 13, 2013 at 10:27
Hi Brian,
You did not miss anything. This example is only for displaying a map with pushpins in a portion of a website. As you can see here we have only a div to display map inside the body tag so it is only displaying the map. If you put this code in a complete website with a container like div with id given in the script, it will display map with all your content in the site.
February 13, 2013 at 19:35
Thanks Deepak. I don’t know what was different with my project and the one you posted in Mark’s comment. I downloaded yours, unziped and recompiled. Added My BingMap key to it, recompiled. Web form displayed however it said it did not recognize the BingMaps key. I pulled it from a silverlight project that I have, and know that it works.
Regardless of that, you example made it very clear what to do and pull PushPins from an SQL Data base. Excellent example.
February 13, 2013 at 19:38
One other nice to know How To: would be how to change the color of the PushPin based on a value in the database.
for example, Yellow for Offices, and Orange for Field sites.
Any suggestion would be appreciated.
February 14, 2013 at 09:40
Thanks Brian for suggesting this how to
I will try that
February 14, 2013 at 19:21
Thanks! I am good at suggesting the ideas. Little rough on executing them.
February 10, 2013 at 07:52
There appears to be very little written about mapping from Sql databases and as a new programmer I’d love to see the complete working page code.
thanks
February 13, 2013 at 11:03
Hi Mark,
You are right, this is a simple example to integrate SQL databases with Bing map. You can download complete working code of this example from here
You can also refer to Bing map SDK to do more with Bing map