Google Map Using SQL Server Stored Procedure In ASP.NET MVC 5

Satyaprakash Samantaray
May 23, 2017
11.9k
0
1
Article

Introduction

Google Maps is the most spectacular app through which our day-to-day life becomes easier.

The database records hold the latitude and longitude information. It will be used to populate Google Maps with multiple markers in ASP.NET MVC Razor.

Description

In this article , I will show you how to show a marker on places like country, state, continent etc., using stored procedure.

To know more details about Google Map, go through my blogs and articles related to Google Map in MVC.

http://www.c-sharpcorner.com/members/satyaprakash-samantaray

Steps to be followed are given below.

Step1

Create a table named MyPlaces.

Table Script

CREATE TABLE [dbo].[MyPlaces](
[CityName] [varchar](50) NOT NULL,
[CityLatitude] [numeric](18, 6) NOT NULL,
[CityLongitude] [numeric](18, 6) NOT NULL,
[CityDescription] [varchar](300) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[CityName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Add your place name, longitude and latitude of the corresponding places with the description, as shown below.

Step 2

Create a stored procedure named Sp_GeoLoc.

Stored procedure script

Create Procedure Sp_GeoLoc
As
Begin
SELECT * FROM MyPlaces
End

Execute the stored procedure.

exec Sp_GeoLoc

Step 3

Create a MVC 5 Application named SatyaMVCGoogleMap.

Step 4

Put the connection string in Web.Config file.

Code ref

<connectionStrings>
<add name="ConString" connectionString="Put Your Connection String Here...."/>
</connectionStrings>

Code description

This adds name ConString, which is important to add in controller class file to access the table and the stored procedure.

Step 5

Add a controller class file named HomeController.cs.

Code ref

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Configuration;
using System.Data.SqlClient;
namespace SatyaMVCGoogleMap
{
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
string markers = "[";
string conString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
SqlCommand cmd = new SqlCommand("Sp_GeoLoc");
using (SqlConnection con = new SqlConnection(conString))
{
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
markers += "{";
markers += string.Format("'title': '{0}',", sdr["CityName"]);
markers += string.Format("'lat': '{0}',", sdr["CityLatitude"]);
markers += string.Format("'lng': '{0}',", sdr["CityLongitude"]);
markers += string.Format("'description': '{0}'", sdr["CityDescription"]);
markers += "},";
}
}
con.Close();
}
markers += "];";
ViewBag.Markers = markers;
return View();
}
}
}

Code description

You will need to import the namespaces given below.

using System.Configuration;
using System.Data.SqlClient;

Controller consists of the Index Action method. Inside this Action method, the records are fetched from the MyPlaces Table, using Sp_GeoLoc Stored Procedure .

Google Map needs an array of markers, which consists of CityName, CityLatitude, CityLongitude and CityDescription and hence a JavaScript Array is built from the fetched location records with the help of the string concatenation.

The generated string is assigned to a ViewBag object.

Using ADO.NET concept, I added my connection string reference add name and the stored procedure.

string conString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
SqlCommand cmd = new SqlCommand("Sp_GeoLoc");

With the help of string concatenation, all the column values are put in markers variable.

The array of markers which consists of CityName, CityLatitude, CityLongitude and CityDescription and hence a JavaScript array is built from the fetched location records with the help of string concatenation.

string markers = "[";
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
markers += "{";
markers += string.Format("'title': '{0}',", sdr["CityName"]);
markers += string.Format("'lat': '{0}',", sdr["CityLatitude"]);
markers += string.Format("'lng': '{0}',", sdr["CityLongitude"]);
markers += string.Format("'description': '{0}'", sdr["CityDescription"]);
markers += "},";
}
}
markers += "];";

The generated string is assigned to a ViewBag object.

ViewBag.Markers = markers;

Step 6

Create a view named “Index.cshtml”.

Code ref & code description

In this section, I added the code description with Green commented line besides the code.

@{
ViewBag.Title = "Satyaprakash Metro City Google Map";
}
<title>@ViewBag.Title</title> //The Title will show Here.
<h2 style="background-color: Yellow;color: Blue; text-align: center; font-style: oblique">Satyaprakash Google Map Using Asp.net MVC</h2>
<fieldset>
<legend style="font-family: Arial Black; color: blue; font-size: large;">Check Metro City Using Google Map</legend>
<div id="SatyaMap" style="width: 1240px; height: 400px;">
@*set google map size .*@
</div>
<script type="text/javascript" src="https://maps.googleapis.com/maps/api/js?key=AIzaSyCkVZYQFe4YYva_g5ulymGDt9EBoVjjZJ8"></script> @*api key after sign in your google account.*@
<script type="text/javascript">
var markers = @Html.Raw(ViewBag.Markers); //all string value of column properties to viewbag.
window.onload = function () {
var mapOptions = {
center: new google.maps.LatLng(markers[3].lat, markers[3].lng), //which city will be shown.
zoom: 9, //google map page zoom
mapTypeId: google.maps.MapTypeId.ROADMAP //type of view.
};
var infoWindow = new google.maps.InfoWindow();
var map = new google.maps.Map(document.getElementById("SatyaMap"), mapOptions);//pass div id and google map load values.
for (i = 0; i < markers.length; i++) { //here load all city map name
var data = markers[i]
var myLatlng = new google.maps.LatLng(data.lat, data.lng); //here i assigned lat and long.
var marker = new google.maps.Marker({
position: myLatlng, //lat and long value
map: map, //div id
title: data.title //city name
});
(function (marker, data) { //here city map description after click on it.
google.maps.event.addListener(marker, "click", function (e) {
infoWindow.setContent(data.description); //city description value
infoWindow.open(map, marker); //then pop will show the description
});
})(marker, data);
}
}
</script>
</fieldset>
<footer>
<p style="background-color: Yellow; font-weight: bold; color:blue; text-align: center; font-style: oblique">© @DateTime.Now.ToLocalTime()</p> @*Add Date Time*@
</footer>

Note

View consists of an HTML DIV element, which will be used to load and display Google Map.

The markers array built inside Controller are assigned to the markers JavaScript variable. Inside the JavaScript Window, OnLoad event handler, first, Google Maps are displayed inside HTML DIV element and then using a loop, marker is plotted on the map for each city present in the array.

Step 7

Set start page in “RouteConfig.cs”.

Code ref

routes.MapRoute(
name: "Default",
url: "{controller}/{action}/{id}",
defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }
);

Code description

Here, the name of the controller is Home.

Here, name of the view or controller action method is Index.

Output

URL is http://localhost:57131/Home/Index

At first, load the page and the city is shown, as I mentioned.

center: new google.maps.LatLng(markers[3].lat, markers[3].lng)

All the cities with Google Markers Icon are shown, using SQL Server stored procedure.

We need to check for city decsription of Bengaluru, Bhubaneswar, Hyderabad, Khurdha.

GIF image for better understanding

Summary

Google map marker placements, using stored procedure.
City description details.
Instead of mentioning longitude and latitude in view section manually, you can get those from the database.
Implement ADO.NET in ASP.NET MVC.