ASP.NET

Querying an SQL server with Web Service vice WCF Data Service RSS

1 reply

Last post Feb 28, 2013 05:42 AM by Song-Tian - MSFT

‹ Previous Thread|Next Thread ›

solutionsvil...

Member

48 Points

73 Posts

Querying an SQL server with Web Service vice WCF Data Service

Feb 27, 2013 04:09 PM|LINK

I have a WCFDataService that pulls a number of points from an SQL database and displays it in Silverlight. I built one that pulls from the database via ASP.NET, and am posting here.

I am tyring to move to the new AJAX map control and away from Silverlight.

Building and displayign the map is not an issue, it is the displaying the Pushpins from the database and specifiging colors to them.

I have found one example with Javascript that displays a group of random points in different layers.

I need to combine the two projects in Ajax, and have hit the brick wall trying to figure this out in Javascript. I have not done a lot of Javascript so my understanding is limited.

Here is the working code for the pull from the database;

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication1.Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
    <body onload="GetMap()">
    <form id="form1" runat="server">
        <table class="MapTable">
            <tr style="vertical-align: top">
                <td>
                    <div id="myMap" style="position:relative; width:1600px; height:1200px;"></div>
                    <asp:Literal ID="Literal1" runat="server"></asp:Literal>
                </td>
            </tr>
    </table>
    </form>
    </body>
</html>

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

namespace WebApplication1
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string Locations = GetLocations();
            Literal1.Text = @"
                <script type='text/javascript' src='http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=7.0'></script>
                <script type='text/javascript'>
                    var  map = new Microsoft.Maps.Map(document.getElementById('myMap'), {credentials: 'InsertKeyHere', center: new Microsoft.Maps.Location(38.5, -104.000), zoom: 5, mapTypeId: Microsoft.Maps.MapTypeId.aerial});
                        function GetMap() 
                        {
                            //Define Map Entities
                            map.entities.clear();
                            " + Locations + @"

                        function ZoomIn(e){
                            if (e.targetType == 'pushpin')
                            {  
                                var location = e.target.getLocation();                        
                                map.setView({zoom:12, center: location});
                            }
                        }
                    }
                </script>";
        }

        private string GetLocations()
        {
            string Locations = "";
            using (SqlConnection con =
                new SqlConnection(ConfigurationManager.ConnectionStrings["PTC"].ConnectionString)) // Get connection string defined in web.config file
            {
                SqlCommand cmd = new SqlCommand("SELECT Latitude, Longitude FROM MapLocations", con);
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Locations += "var pushpin = new Microsoft.Maps.Pushpin(new Microsoft.Maps.Location(" +
                        reader["Latitude"].ToString() + ", " + reader["Longitude"].ToString() + 
                        "), null);Microsoft.Maps.Events.addHandler(pushpin, 'click', ZoomIn);map.entities.push(pushpin);";
                }
            }
            return Locations;
        }
    }
}

Here is the working code for the Random in Layers;

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="WebApplication1.WebForm2" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
    <script charset="UTF-8" type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=7.0"></script>
    <script>
        var layer1 = new Microsoft.Maps.EntityCollection(); //Define Map Layer 1
        var layer2 = new Microsoft.Maps.EntityCollection(); //Define Map Layer 2
        var pinInfoBox;  //Define the Popup InfoBox
        var infoboxLayer = new Microsoft.Maps.EntityCollection(); //Define the InfoBox Layer
        var pinLayer = new Microsoft.Maps.EntityCollection(); //Define the Pin Layer
        var apiKey = "InsertKeyHere"; //Credentials Key

        function GetMap()
        {
            //Initialize the Map
            map = new Microsoft.Maps.Map(document.getElementById("myMap"), { credentials: apiKey });
            getpushpins(10, layer1, "images/BluePin.gif");
            getpushpins(10, layer2, "images/GreenPin.gif");

            //Define Map Entities
            map.entities.push(layer1);
            map.entities.push(layer2);
            map.entities.push(pinLayer);
            map.entities.push(infoboxLayer);

            //Create the info box for the pushpin
            pinInfobox = new Microsoft.Maps.Infobox(new Microsoft.Maps.Location(0, 0), { visible: false });
            infoboxLayer.push(pinInfobox);
        }

        function toggleLayer1()
        {
            layer1.setOptions({ visible: !layer1.getVisible() });
        }

        function toggleLayer2()
        {
            layer2.setOptions({ visible: !layer2.getVisible() });
        }

        function displayInfobox(e)
        {
            pinInfobox.setOptions({ title: e.target.Title, description: e.target.Description, visible: true, offset: new Microsoft.Maps.Point(0, 25) });
            pinInfobox.setLocation(e.target.getLocation());
        }

        function hideInfobox(e)
        {
            pinInfobox.setOptions({ visible: false });
        }

        function getpushpins(pinCount, layer, pinIcon)
        {
            var view = map.getBounds();
            var topLeft = view.getNorthwest();
            var bottomRight = view.getSoutheast();
            var latSpan = bottomRight.latitude - topLeft.latitude;
            var longSpan = bottomRight.longitude - topLeft.longitude;

            for (i = 1; i <= pinCount; i++)
            {
                var pinLatLong = new Microsoft.Maps.Location(topLeft.latitude + latSpan * Math.random(), topLeft.longitude + longSpan * Math.random());
                var pushpin = new Microsoft.Maps.Pushpin(pinLatLong, { text: i.toString(), icon: pinIcon });
                pushpin.Title = name; //usually title of the infobox
                pushpin.Description = "blahblahblah, " + i; //information you want to display in the infobox
                layer.push(pushpin);
                Microsoft.Maps.Events.addHandler(pushpin, 'click', displayInfobox);
            }
        }
    </script>
</head>
<body onload="GetMap()">
    <form id="form1" runat="server">
<table class="MapTable">
    <tr style="vertical-align: top">
        <td>
            <div id="myMap" style="position:relative; width:800px; height:600px;"></div>  
        </td>
    </tr>
    <tr>
    <td style="text-align:center">
        <input type="button" value="Toggle Layer 1" onclick="toggleLayer1();" />
        <input type="button" value="Toggle Layer 2" onclick="toggleLayer2();" />
    </td>
    </tr>
</table>
    </form>
</body>
</html>

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

namespace WebApplication1
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
    }
}

Edited by solutionsville on Feb 27, 2013 04:11 PM

Song-Tian - ...

All-Star

43715 Points

4304 Posts

Microsoft

Re: Querying an SQL server with Web Service vice WCF Data Service

Feb 28, 2013 05:42 AM|LINK

Hi,

Please refer to the example as follow:

$("#btnAdd").bind("click", function (event) {
                event.preventDefault();
                var name = $("#txtName").val();
                var data = JSON.stringify({ 'name': 'name' });
                $.ajax({
                    type: "POST",
                    url: "WebService.asmx/Save",
                    data: data,
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (response) {
                        alert(response.d);
                    },
                    error: function (response) {
                        alert(response.statusText);
                    }
                });

            }
    );

Please mark the replies as answers if they help or unmark if not.
Feedback to us

Develop and promote your apps in Windows Store

Edited by Song-Tian - MSFT on Feb 28, 2013 05:42 AM
Marked as answer by Song-Tian - MSFT on Mar 05, 2013 06:08 AM