Simple Razor Page using SQL, JavaScript and Google-Maps

• Download source - 1.6 KB

Introduction

The client wanted to display a simple web page that would allow the user to see all locations where their service could be found.

Background

I believe that simple problems should have simple solutions. In this case, I was asked to use ASP. I elected to avoid the Entity-Framework and Model-View-Controller approach. Instead, I hoped to find a way to do all the work in a Razor Page. To do this, I would need:

1. Google-Maps key
2. A way to outline the state/area
3. A way to send C# DataTable results to JavaScript

Stepping Through The Code

This is the Razor logic to query the database. Note that SQL returns the data in a DataTable object. To get the data from the table, the SerializeObject method is used.

@{
    DataTable table = new DataTable();
    table.Columns.Add("name", typeof(string));
    table.Columns.Add("lat", typeof(double));
    table.Columns.Add("lng", typeof(double));

    SqlConnection conn = new SqlConnection(@"Server=YOUR_SERVER\SQL_INSTANCE; 
    Database=YOUR_DB; Trusted_Connection=True; MultipleActiveResultSets=true");
    conn.Open();

    SqlCommand sql = new SqlCommand("select top 10 [name], lat, 
    lng from Sites where (lng is not null) and (lng is not null); ", conn);
    SqlDataReader rows = sql.ExecuteReader();

    while (rows.Read())
    {
        table.Rows.Add(rows.GetValue(0), rows.GetValue(1), rows.GetValue(2));
        @* <text>@rows.GetValue(1), </text> *@
        @* <text> @table.Rows[0][0] </text> *@
    }
    string j_string = @JsonConvert.SerializeObject(table); 
}

The next section of code links to the Google maps and creates a map object. Note that you will have to use/generate your own key. Be sure to enable it for the actions you will perform, e.g., placing markers on the map, getting directions, etc.

<script src="https://maps.googleapis.com/maps/api/js?key=YOUR_MAP_KEY	"></script>
        var mapProp =
        {
            center: new google.maps.LatLng(30.9843, -91.9623),
            zoom: 7,
            mapTypeId: "roadmap",
            disableDefaultUI: true, // a way to quickly hide all controls
            scaleControl: true,
            zoomControl: true
        };
        var map = new google.maps.Map(document.getElementById("googleMap"), mapProp);

I wanted to add a border around the state in order to draw emphasis to do it. While the good people at Google did provide a tool to create a “poly line”, you will need to provide the data points on your own. The USGS provides this data but I could not find a site that provided what I needed from them. Happily, I was not the first one with this problem. Someone else solved it and graciously provided the answer at their web site: http://eric.clst.org/tech/usgeojson/.

(Thanks Eric!)

Because of the large number of data points, I created a separate file to hold the “outline” array. It looks like this:

var outline = [
    { lat: 29.787028166876066, lng: -93.923677017015592 },
    { lat: 29.791539871809256, lng: -93.927774155213186 },
    { lat: 29.799059423530494, lng: -93.930078295865385 },

This is the code that draws the outline:

var stateOutline = new google.maps.Polyline(
{
    path: outline,
    geodesic: true,
    strokeColor: '#0000FF',
    strokeOpacity: 0.5,
    strokeWeight: 4
});
stateOutline.setMap(map);

Now comes the fun part.

This line of code will start the process of getting the SQL data into a form that JavaScript could use. Note that the variable “j_string” from the Razor code above is preceded with an at-sign and is in quotation marks.

However, instead of being in pure JSON format, the quotation marks are presented in HTML language, e.g., “"”. So we need to replace all occurrences with actual quotation marks:

j_string = j_string.replace(/"/g, '"');

We can now parse the string into a JSON object, select each element of the object and pass it to the Google maps market logic:

var j_object = JSON.parse(j_string);
var x, y, marker;
//console.log(j_object);
for (var i = 0; i < j_object.length; i++)
{
    name = j_object[i].name;
    x    = j_object[i].lat;
    y    = j_object[i].lng;
    //console.log(name, x, y);
    marker = new google.maps.Marker(
    {
        title: name,
        position: { lat: x, lng: y },
        map: map
    });
}

And the map displays:

I hope this is of some use to anyone working on a similar problem.

History

• 21^st March, 2019: Initial version