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.
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
- {
-
- 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 6Create 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>
-
- <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);
- window.onload = function () {
- var mapOptions = {
- center: new google.maps.LatLng(markers[3].lat, markers[3].lng),
- zoom: 9,
- mapTypeId: google.maps.MapTypeId.ROADMAP
- };
- var infoWindow = new google.maps.InfoWindow();
- var map = new google.maps.Map(document.getElementById("SatyaMap"), mapOptions);
- for (i = 0; i < markers.length; i++) {
- var data = markers[i]
- var myLatlng = new google.maps.LatLng(data.lat, data.lng);
- var marker = new google.maps.Marker({
- position: myLatlng,
- map: map,
- title: data.title
- });
- (function (marker, data) {
- google.maps.event.addListener(marker, "click", function (e) {
- infoWindow.setContent(data.description);
- infoWindow.open(map, marker);
- });
- })(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>
NoteView 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 descriptionHere, 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.