Bing Maps with SQL Server Data

For technical warm-up exercises for a project I’m working on that involves mapping data, I decided I’d create a Web page that reads SQL data about a city (latitude and longitude in particular) and uses that data to construct a Bing Maps map that’s centered on the target city.


I was surprised that I couldn’t really find any useful information or examples. So I created my own demo. The main problem is that Bing Maps is based on client side JavaScript so I needed a way to read SQL data from a backend database, convert it to JSON data so the Web page could send it to the Bing Maps service to get a map.

I came up with two possible approaches. The one I describe here is to have a front end Web page that uses jQuery to send a getJSON request to an ASP.NET Web API service which then uses C# ADO.NET to send a select request to a SQL database.

Step 1 – Create a SQL database and table.

I created a SQL Server database named dbCityData. I used Azure because I’ve been working with that technology recently, but in most cases I would have used a local machine or a machine accessible on my network.

I created a table named tblCityData with information for three cities:

cityID cityName cityLat cityLon
1   Seattle  47.61, -122.33
2   New York 40.71, -74.01
3   Omaha    41.25, -96.00


One really nice thing about Azure databases is that I could easily get the SQL Server connection string which can be a real adventure (not in a good way).

Step 2 – Create an ASP.NET Web API Service

To create the Web service, I launched a new instance of Visual Studio and created a Web API project. Briefly, File | New | Project | Templates | Visual C# | Web | (.NET 4.5) | ASP.NET Web Application, name = CityLatLonService, then ASP.NET 4.5 Templates | Empty + Web API checked | OK.

In the Solution Explorer window, I right-clicked on the Models folder and did an Add | Class, Name = CityDataClass.cs | Add to create a class that holds a row of SQL data.

namespace CityLatLonService.Models
  public class CityDataClass
    public int cityID;
    public string cityName;
    public double cityLat;
    public double cityLon;

Next, I right-clicked on the Controllers folder and did Add | Controller | Web API 2 Controller – Empty | Add, name = CityDataController | Add to create the logic. Note that I wrote the code to allow more than one return result per city ID which isn’t possible here.

using System.Data.SqlClient;
namespace CityLatLonService.Controllers
  public class CityDataController : ApiController
    public HttpResponseMessage GetData(int cityID)
      SqlConnection sqlConn = null;
      Models.CityDataClass c = new Models.CityDataClass();
        string connString = 
        sqlConn = new SqlConnection(connString);
        SqlCommand sqlCmd = new SqlCommand();
        sqlCmd.CommandType = System.Data.CommandType.Text;
        sqlCmd.CommandText = "SELECT * from tblCityData WHERE
 cityID = " + cityID + ";";
        sqlCmd.Connection = sqlConn;

        SqlDataReader sqlReader = sqlCmd.ExecuteReader();
        while (sqlReader.Read())
          int id = sqlReader.GetInt32(0);
          string name = sqlReader.GetString(1);
          double lat = sqlReader.GetDouble(2);
          double lon = sqlReader.GetDouble(3);

          c.cityID = id;
          c.cityName = name;
          c.cityLat = lat;
          c.cityLon = lon;

        if (sqlConn.State == System.Data.ConnectionState.Open)

        return Request.CreateResponse(c);
      catch (Exception ex)
        c.cityID = 0;
        c.cityName = ex.Message;
        c.cityLat = 0.0;
        c.cityLon = 0.0;
        if (sqlConn.State == System.Data.ConnectionState.Open)
        return Request.CreateResponse(c);
    } // GetData
  } // class 
} // ns

In file App_Start WebApiConfig.cs I edited the template-generated code to change the parameter name from the default “id” to “cityID” to correspond the the logic in the Controller code:

namespace CityLatLonService
  public static class WebApiConfig
    public static void Register(HttpConfiguration config)

          name: "DefaultApi",
          routeTemplate: "api/{controller}/{cityID}",
          defaults: new { id = RouteParameter.Optional }

To test the Web API service, I hit the F5 key and Visual Studio complained but did tell me which port is being used (28853). Then I manually entered the URI:


in the Internet Explorer address bar and then IE asked me if I wanted to view the data or save it. I asked to view it and verified that I got the data for city 2:

{“cityID”:2,”cityName”:”New York”,”cityLat”:40.71,”cityLon”:-74.01}


Step 3 – Create a Preliminary Web Page

My next step was to create a preliminary Web page that accessed the city data from the Web API service (but without adding a map to the page). In Visual Studio I right-clicked on the CityLatLonService project name and did Add | New Item | Installed | Visual C# | Web | HTML Page, Name = testPage.html | Add. Here’s the code in an image so my blog software doesn’t go crazy:


It took a while to get rid of the bugs here but eventually I was satisfied my Web page was communicating with the Web service.


Step 4 -Add a Bing Maps Map to the Web Page

The last step was to parse out the lat-lon data from the Web service JSON result and feed the data to a Bing Maps map so that the generated map is centered on the lat-lon. Here’s the code, again in an image:


Well, it all worked eventually. There must be other approaches but the jQuery + Web API service + ADO.NET + SQL approach seems reasonable if you’re using the Microsoft trechnologies stack.

If I get motivated, I might try an alternative design where an ASP.NET Web page communicates directly to the SQL data using ADO.NET, and the return from the SQL SELECT statement is placed inside an HTML hidden control, which can then be read by JavaScript on the Web page and then passed to Bing Maps.

This entry was posted in Miscellaneous. Bookmark the permalink.