Massoud Mazar

Sharing The Knowledge

NAVIGATION - SEARCH

Using Google GeoMap Visualization API in .net

This past week I tried to use Google GeoMap visualizations API to display some values on a map. The JavaScript part was easy, but I could not find good sample code for providing JSON data asynchronously from .net, so I thought I should build a sample here to save others some time.

Here is the HTML for the page which displays the map: (This page has no code behind)

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <script type="text/javascript" src="http://www.google.com/jsapi%22%3E%3C/script>
    <script type="text/javascript">
        google.load("visualization", "1", { packages: ["geomap"] });
        google.setOnLoadCallback(Initialize);
        
        function Initialize() {
            var query = new google.visualization.Query('<%= ResolveUrl("~/JsonData.aspx") %>');
            // Send the query with a callback function.
            query.send(handleQueryResponse);
        }
 
        function handleQueryResponse(response) {
            if (response.isError()) {
                alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
                return;
            }
            var data = response.getDataTable();
            var options = {};
            options['dataMode'] = 'regions';
            var container = document.getElementById('map_canvas');
            var geomap = new google.visualization.GeoMap(container);
            geomap.draw(data, options);
        }
    </script>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div id="map_canvas"></div>
    </form>
</body>
</html> 

Above HTML contains a JavaScript call to another page named JasonData.aspx. This page consist only of the Page directive, and all of the functionality is in the code behind:

public partial class JSONData : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // prepare the data (this table can be populated from database)
        System.Data.DataTable data = new System.Data.DataTable(); 

        data.Columns.Add("Country", typeof(string));
        data.Columns.Add("Value", typeof(int)); 

        System.Data.DataRow row; 

        row = data.NewRow();
        row["Country"] = "US";
        row["Value"] = 1000;
        data.Rows.Add(row); 

        row = data.NewRow();
        row["Country"] = "GB";
        row["Value"] = 500;
        data.Rows.Add(row); 

        row = data.NewRow();
        row["Country"] = "BR";
        row["Value"] = 300;
        data.Rows.Add(row); 

        row = data.NewRow();
        row["Country"] = "FR";
        row["Value"] = 100;
        data.Rows.Add(row); 

        row = null; 

        // Serialize the data to format Google API expects
        string JsonData = JSONHelper.SerializeDataTable(data);
        data.Dispose(); 

        System.Text.StringBuilder output = new System.Text.StringBuilder(); 

        // Set the Response Handler
        output.Append("google.visualization.Query.setResponse");
        output.Append("({"); 

        output.Append("version:'0.5',"); 

        // if client sends a reqId, same values must be sent back
        string tqx = Request["tqx"];
        if (!string.IsNullOrEmpty(tqx))
        {
            string[] parameters = tqx.Split(';');
            foreach (string parameter in parameters)
            {
                if (!string.IsNullOrEmpty(parameter) && parameter.StartsWith("reqId"))
                    output.Append(parameter + ",");
            }
        } 

        // Other attributes
        output.Append("status:'ok',");
        output.Append("sig:'" + JsonData.GetHashCode() + "',");
        output.Append(JsonData);
        output.Append("});"); 

        // This JSON string must be the only thing sent back
        Response.Clear();
        Response.ClearContent();
        Response.ClearHeaders();
        Response.ContentType = "text/plain";
        Response.Write(output.ToString());
    }
} 

Above code calls SerializeDataTable method of my JSONHelper class. Since I could not get the JSON serialization of .net create the format Google expects, I created my own serializer:

public static class JSONHelper
{ 

    public static string SerializeDataTable(DataTable table)
    {
        System.Text.StringBuilder Result = new System.Text.StringBuilder(); 

        Result.Append("table:{"); 

        if (table != null && table.Rows.Count > 0)
        {
            // Columns 
            Result.Append("cols:[");
            string Separator = "";
            foreach (DataColumn column in table.Columns)
            {
                Result.Append(Separator);
                Result.Append("{label:'");
                Result.Append(column.Caption);
                Result.Append("',type:'");
                Result.Append(GetJSONType(column.DataType));
                Result.Append("'}");
                Separator = ",";
            }
            Result.Append("]"); 

            // Rows
            Result.Append(",rows:[");
            string SeparatorC = "";
            foreach (DataRow row in table.Rows)
            {
                Result.Append(SeparatorC);
                Result.Append("{c:[");
                string SeparatorV = "";
                foreach (DataColumn column in table.Columns)
                {
                    Result.Append(SeparatorV);
                    Result.Append("{v:");
                    Result.Append(GetJSONValue(row[column].ToString(), column.DataType));
                    Result.Append("}");
                    SeparatorV = ",";
                }
                Result.Append("]}");
                SeparatorC = ",";
            }
            Result.Append("]"); 


        } 

        Result.Append("}"); 

        return Result.ToString();
    } 

    public static string GetJSONType(Type DataType)
    {
        string result = "string',pattern:'"; 

        switch (DataType.Name.ToLower())
        {
            case "int":
            case "int16":
            case "int32":
            case "int64":
                result = "number',pattern:'#,##1";
                break;
            case "decimal":
            case "float":
                result = "number',pattern:'#,##0.0#";
                break;
            case "DateTime":
                result = "date";
                break;
            default:
                break;
        } 

        return result;
    } 

    public static string GetJSONValue(object Value, Type DataType)
    {
        string result = ""; 

        switch (DataType.Name.ToLower())
        {
            case "string":
                result = "'" + Value.ToString().Replace("'", " ") + "'";
                break;
            case "int":
            case "int16":
            case "int32":
            case "int64":
                result = Convert.ToInt64(Value).ToString("#.0") + ",f:'" + Convert.ToInt64(Value).ToString("#,##0") + "'";
                break;
            case "decimal":
            case "float":
                result = Convert.ToDecimal(Value) + ",f:'" + Convert.ToDecimal(Value).ToString("#,##0.0#") + "'";
                break;
            default:
                result = Value.ToString();
                break;
        } 

        return result;
    } 

}

Add comment