Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / Javascript
Tip/Trick

Simple Razor Page using SQL, JavaScript and Google-Maps

Rate me:
Please Sign up or sign in to vote.
4.83/5 (4 votes)
22 Mar 2019CPOL2 min read 7.8K   250   5   2
A simple web page that would allow the user to see all locations where their service could be found

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.

JavaScript
@{
    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.

JavaScript
<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:

JavaScript
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:

JavaScript
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:

JavaScript
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:

JavaScript
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

  • 21st March, 2019: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questiontypo Pin
Pete Lomax Member 1066450525-Mar-19 7:36
professionalPete Lomax Member 1066450525-Mar-19 7:36 
QuestionHTML got in the way Pin
brian_eckert22-Mar-19 1:31
brian_eckert22-Mar-19 1:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.