How to load GMap Direction from database in ASP.NET

Tuesday, February 25, 2014 Sourav Mondal ASP.NET | Google Map

Introduction

In this post I am explain how to load GMap Direction from database in ASP.NET.

Here i am writing this article to explain how to load GMap Direction from database in ASP.NET.
Previous post about GMap is How to Show Markers(Location) in Google Map dynamically form database in ASP.NET.

Steps :

Step - 1 : Create New Project.

Go to File > New > Project > Select asp.net web forms application > Entry Application Name > Click OK.

Step-2: Add a Database.

Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.

Step-3: Create table for fetch data.

Open Database > Right Click on Table > Add New Table > Add Columns > Save > Enter table name > Ok.

In this example, I have used one tables as below

Step-4: Create a Stored Procedure in Sql Server for retrieve Direction.

Open Database > Right Click on Stored Procedure > Add New Stored Procedure > Write below Sql Code > Save.

            CREATE PROCEDURE dbo.GetDirection
             @LocFrom int,
             @LocTo int
            AS
             Select
              (Select
               LatLong
              From
               Locations
              Where
               LocID = @LocFrom) as FromLoc,
              (Select
               LatLong
              from
               Locations
              Where
               LocID = @LocTo) as ToLoc
            RETURN

Step-5: Add a Webpage and Design for Show GMap Direction

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select web form/ web form using master page under Web > Enter page name > Add.

HTML Code

            <h3>Get GMap direction from database using ASP.NET</h3>
            <br />
            <table>
                <tr>
                    <td>From : </td>
                    <td>
                        <asp:DropDownList ID="ddFrom" runat="server"></asp:DropDownList>
                    </td>
                    <td>To : </td>
                    <td>
                        <asp:DropDownList ID="ddTo" runat="server"></asp:DropDownList>
                    </td>
                    <td>
                        <input type="button" value="Get Direction" id="btnGetDirection" />
                    </td>
                </tr>
            </table>
            <br />
            <div id="map" style="width:50%; height:300px; border:solid 1px black;float:left"></div>
            <div id="route" style="width:30%; height:300px; border:solid 1px black; float:left; overflow:auto"></div>

JS Code

Write this below js Code for load GMap Direction from database.

       <script src="//maps.google.com/maps?file=api&amp;v=2&amp;sensor=false&amp;key=ABQIAAAAuPsJpk3MBtDpJ4G8cqBnjRRaGTYH6UMl8mADNa0YKuWNNa8VNxQCzVBXTx2DYyXGsTOxpWhvIG7Djw" type="text/javascript"></script>
    <script language="javascript">
            var Gmap;
            var directionPanel;
            var direction;
 
            function initialize() {
                Gmap = new GMap2(document.getElementById("map"));
                Gmap.setCenter(new GLatLng(22.573438, 88.36293), 15); // here I have set kolkata as Center and 15 zoom level
                directionPanel = document.getElementById("route");
                direction = new GDirections(Gmap, directionPanel);
            }
            $(document).ready(function () {
                initialize();
                $('#btnGetDirection').click(function () {
                    //here populate data from database and get to GMap direction
                    PopulateDirection();
                });
            });
 
            function PopulateDirection() {
                var from = $('#<%=ddFrom.ClientID%>').val();
                var to = $('#<%=ddTo.ClientID%>').val();
                $.ajax({
                    url: "Default.aspx/GetDirectioin",// Here url for code behind function
                    type: "POST",
                    data: "{from: '"+from+"', to: '"+to+"'}",
                    dataType: "json",
                    contentType: "application/json; charset=utf-8",
                    success: function (data) {
                        direction.load(data.d.toString());
                    },
                    error: function () {
                        alert("Error!");
                    }
                });
            }
        </script>

Step-6: Write following code in Page_Load event for load Location.

             protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    PopulateLocation();
                }
            }

Here is the function...

            private void PopulateLocation()
            {
                // Populate location Here
                DataTable dt = new DataTable();
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString))
                {
                    string query = "Select * from Locations";
                    SqlCommand cmd = new SqlCommand(query, con);
                
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    dt.Load(cmd.ExecuteReader());
                }
 
                ddFrom.DataSource = dt;
                ddFrom.DataTextField = "LocName";
                ddFrom.DataValueField = "LocID";
                ddFrom.DataBind();
 
                ddTo.DataSource = dt;
                ddTo.DataTextField = "LocName";
                ddTo.DataValueField = "LocID";
                ddTo.DataBind();
            }

Step-7: Write this function into your page code behind for called from Jquery Code.

             // this is the function is for called from jquery
            [WebMethod]
            public static string GetDirectioin(string from, string to)
            {
                string returnVal = "";
                DataTable dt = new DataTable();
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand("GetDirection", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@LocFrom", from);
                    cmd.Parameters.AddWithValue("@LocTo", to);
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    dt.Load(cmd.ExecuteReader());
                }
                returnVal = "from: " + dt.Rows[0]["FromLoc"].ToString() + " to: " + dt.Rows[0]["ToLoc"].ToString();
                return returnVal;
            }