FusionMaps Documentation

FusionMaps with ASP.NET (C#) > Plotting Data from Database > USMap

In this page we will see the code used to achieve the Goal 1. Below is the important part of the code.

Before moving further with the process of plotting data from database, we recommend to go through "Using dataXML Method" and "Using dataXML Method" to know the basics about FusionMap generation procedure.

The code discussed here is present in Download Package > Code > CS_NET > DrillDown folder.

The code to create the US map and plot data from database contained in Default.aspx is listed as under :

<%@ Page Language="C#" AutoEventWireup="false" CodeFile="Default.aspx.cs" Inherits="FusionMapsDBExample_DrillDown" %>

<html>
<head>
       <title>FusionMaps v3 - Database Example</title>
     <script language="Javascript" src="../JSClass/FusionMaps.js"></script>
    </head>
    <body>
     <form id='form1' name='form1' method='post' runat="server">

            <% // Show USA Map %>
            <asp:Literal ID="USMap" runat="server" />

        </form>
   </body>
</html>

In the above code, we first include FusionMaps.js file to enable us embed the map using JavaScript. The USA map loads in Literal USMap through the following code behind script (Default.aspx.cs).

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using InfoSoftGlobal;

public partial class FusionMapsDBExample_DrillDown : System.Web.UI.Page
{
   protected void Page_Load(object sender, EventArgs e)
   {
      // Define dataURL variable
      // URLencode dataURL
      string dataURL = Server.UrlEncode("DataGen.aspx?op=GetUSMapDetails");

      // Create the Map with data contained in dataURL
      // and embed the chart rendered as HTML into Literal - USMap
      // We use FusionMaps class of InfoSoftGlobal namespace (FusionMaps.dll in BIN folder)
      // RenderMap() generates the necessary HTML needed to render the map
      string mapHTML=FusionMaps.RenderMap("../Maps/FCMap_USA.swf", dataURL, "", "mapid", "600", "400", false, false);

      ' embed the chart rendered as HTML into Literal - USMap
      USMap.Text = mapHTML;

   }
}

In the above code we do the following :

We create a dataURL to fetch map XML from DataGen.aspx (whose code behind page is DataGen.aspx.cs).
We call renderMap() function from FusionMaps of InfosoftGlobal assembly class which renders map of USA after passing the dataURL to it.

Let's now go thorough the code of DataGen.aspx.cs that fetches data and forms map XML.

using System;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
// contains the connection parameters to connect to database.
using DataConnection;

public partial class DrillDown_dataGen : System.Web.UI.Page
{
   ///<summary>
   /// This program calls diffarent fucntions according to the value of op
   /// op is passed as Request
   /// The functions generate XML and relay to map/chart using dataURL method
   ///</summary>
   protected void Page_Load(object sender, EventArgs e)
   {
      string op;
      op = Request["op"];
      //Depending on op we call function
      switch (op)
      {
         case "GetUSMapDetails":
            GetUSMapDetails(); // Call GetUSMapDetails
            break;
         case "GetStateDetails":
            GetStateDetails(); //Call GetStateDetails
            break;
         case "getChartEmpStat":
            getChartEmpStat(); //Call getChartEmpStat
            break;
      }
   }

   /// <summary>
   /// This program creates XML for USA Map to show polulation % of each state
   /// </summary>
   public void GetUSMapDetails()
   {

      /*
         In this example, we show how to connect FusionMaps to a database.
         You can connect to any database. Here, we've shown MSSQL/Access.
      */
      //strXML will be used to store the entire XML document generated
      StringBuilder strXML = new StringBuilder();

      //Variable to store SQL Queries
      string strQuery;
      //Variable to store total Population
      double sumdata;

      //Generate the map element
      //Create the opening <map> element and add the attributes that we need.
      strXML.Append("<map borderColor='FFFFFF' fillAlpha='80' showBevel='0' numberSuffix='% of total US population' legendBorderColor='F1f1f1' hoverColor='FFFFFF' legendPosition='bottom'>");

      //Define color ranges
      strXML.Append("<colorRange>");
      strXML.Append("<color minValue='0' maxValue='0.50' displayValue='0% to 0.50% of total' color='D64646' />");
      strXML.Append("<color minValue='0.50' maxValue='1' displayValue='0.50% to 1% of total' color='F6BD0F' />");
      strXML.Append("<color minValue='1' maxValue='3' displayValue='1% to 3% of total' color='8BBA00' />");
      strXML.Append("<color minValue='3' maxValue='10' displayValue='3% or above of total' color='AFD8F8' />");
      strXML.Append("</colorRange>");

      //store the sql query
      //create the datareader object to connect to table
      strQuery = "select sum(data) as datap from fcmap_distribution";
      DbConn Rs = new DbConn(strQuery);

      //Initialize sum container
      sumdata = 0;
      if (Rs.ReadData.HasRows == true)
      {
         // read first record
         Rs.ReadData.Read();
         // Store sum
         sumdata = Convert.ToDouble(Rs.ReadData["datap"]);
      }
      //close the reader
      Rs.ReadData.Close();

//Fetch all Internal id and data sum
strQuery = "select Internal_Id, (sum(data) / " + sumdata + ")*100 as datap from fcmap_distribution group by Internal_Id";

DbConn Rs1 = new DbConn(strQuery);

      // Add map data element
      strXML.Append("<data>");

      //Check if we've records to show
      if (Rs1.ReadData.HasRows == true)
      {

         //Iterate throuth the database
         while (Rs1.ReadData.Read())
         {
            // Create query string
            strQuery = "select map_swf from fcmap_master where Internal_Id='" + Rs1.ReadData["Internal_Id"].ToString() + "'";
            // Open fcmap_master table to get map swf names
            DbConn Rs2 = new DbConn(strQuery);

            // Read first record
            Rs2.ReadData.Read();

            // The link will in format StateDetails.aspx?Internal_Id=Int_Id&map=map_swf.swf - we'll need to URL Encode this link to convert & to %26 (or manually add it as %26 instead of &)
            string LinkURL = Server.UrlEncode("StateDetails.aspx?Internal_Id=" + Rs1.ReadData["Internal_Id"].ToString() + "&map=" + Rs2.ReadData["map_swf"].ToString());

            // Generate <entity id=".." value=".." /> and also add link to it
            strXML.AppendFormat("<entity id='{0}' value='{1}' link='{2}' />", Rs1.ReadData["Internal_Id"].ToString(), Math.Round(Convert.ToDouble(Rs1.ReadData["datap"]), 2), LinkURL);

            Rs2.ReadData.Close();
         }
      }

// Close reader
Rs1.ReadData.Close();

// Finally, close <map> element and add
strXML.Append("</data>");

      // If needed, you can append additional XML tags here - like STYLE or MARKERS
      strXML.Append("<styles><definition><style type='animation' name='animX' param='_xscale' start='0' duration='1' /><style type='animation' name='animY' param='_yscale' start='0' duration='1' /><style type='animation' name='animAlpha' param='_alpha' start='0' duration='1' /><style type='shadow' name='myShadow' color='FFFFFF' distance='1' /></definition>");

      strXML.Append("<application><apply toObject='PLOT' styles='animX,animY' /><apply toObject='LABELS' styles='myShadow,animAlpha' /></application></styles>");

      // Close Map element
      strXML.Append("</map>");

      // Set Proper output content-type
      Response.ContentType = "text/xml";
      // Just write out the XML data
      // NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER
      Response.Write(strXML.ToString());

}

...

}

Lets discuss the steps involded in the above code :

The first part of the code is executed when the page is loaded :

protected void Page_Load(object sender, EventArgs e)
{
   string op;
   op = Request["op"];
   //Depending on op we call function
   switch (op)
   {
      case "GetUSMapDetails":
         GetUSMapDetails(); // Call GetUSMapDetails
         break;
      case "GetStateDetails":
         GetStateDetails(); //Call GetStateDetails
         break;
      case "getChartEmpStat":
         getChartEmpStat(); //Call getChartEmpStat
         break;
   }
}

The file getDetails.aspx is passed a querystring - ?op=GetUSMapDetails. The above code retrieves this querystring value in variable -op. Depending on the value of op the code calls related functions using switch case statement. Hence, when op's value is GetUSMapDetails, it calls GetUSMapDetails() function, which in turn builds and returns the US map XML as dataURL.

Now, we will dive deep into the GetUSMapDetails() function.

We create a StringBuilder object - strXML (to store XML data) and sumdata to store sum of values from database. We also opened the root map element providing the map properties.

strXML.Append("<map borderColor='FFFFFF' fillAlpha='80' showBevel='0' numberSuffix='% of total US population' legendBorderColor='F1f1f1' hoverColor='FFFFFF' legendPosition='bottom'>");

Next, we define color range for the main US Map.

//Define color ranges

      strXML.Append("<colorRange>");

      strXML.Append("<color minValue='0' maxValue='0.50' displayValue='0% to 0.50% of total' color='D64646' />");

      strXML.Append("<color minValue='0.50' maxValue='1' displayValue='0.50% to 1% of total' color='F6BD0F' />");

      strXML.Append("<color minValue='1' maxValue='3' displayValue='1% to 3% of total' color='8BBA00' />");

      strXML.Append("<color minValue='3' maxValue='10' displayValue='3% or above of total' color='AFD8F8' />");

    strXML.Append("</colorRange>");

Providing data

To connect to database we have creatred a namespace DataConnection (file DbHelper.vb in App_Code folder). This contains a class DbConn, which we would using to connect to database in this example and subsequent example(s).

We had included DataConnection namespace in the header of the code page. DataConnection contains the connection parameters to connect to database.

Here comes the database mining part where we start fetching data.

First we initialize sumdata to store sum total of all data found in the table - fcmap_distribution. That represents the total US population (ficticious of-course). We did this using SQL Query : "select sum(data) as datap from fcmap_distribution".
Next, we fetch internal ID of each entity/state of US map and get the percentage of population of each entity/state against total US population. This is stored in data reader aReader1 using the SQL query - "select Internal_Id, (sum(data) / " + sumdata + ")*100 as datap from fcmap_distribution group by Internal_Id". We again used fcmap_distribution table here.
We get total population of each state and divide it by sum total US population and multiply it with 100 to get the percentage value.

Now we iterate through each record in the data reader and create entity element for each state.

//store the sql query
//create the datareader object to connect to table
strQuery = "select sum(data) as datap from fcmap_distribution";
DbConn Rs = new DbConn(strQuery);

//Initialize sum container
sumdata = 0;
if (Rs.ReadData.HasRows == true)
{
   // read first record
   Rs.ReadData.Read();
   // Store sum
   sumdata = Convert.ToDouble(Rs.ReadData["datap"]);
}

//close the reader
Rs.ReadData.Close();

//Fetch all Internal id and data sum
strQuery = "select Internal_Id, (sum(data) / " + sumdata + ")*100 as datap from fcmap_distribution group by Internal_Id";

DbConn Rs1 = new DbConn(strQuery);

// Add map data element
strXML.Append("<data>");

//Check if we've records to show
if (Rs1.ReadData.HasRows == true)
{
   //Iterate throuth the database
   while (Rs1.ReadData.Read())
   {
      // Create query string
      strQuery = "select map_swf from fcmap_master where Internal_Id='" + Rs1.ReadData["Internal_Id"].ToString() + "'";
      // Open fcmap_master table to get map swf names
      DbConn Rs2 = new DbConn(strQuery);

      // Read first record
      Rs2.ReadData.Read();

      // The link will in format StateDetails.aspx?Internal_Id=Int_Id&map=map_swf.swf - we'll need to URL Encode this link to convert & to %26 (or manually add it as %26 instead of &)
      string LinkURL = Server.UrlEncode("StateDetails.aspx?Internal_Id=" + Rs1.ReadData["Internal_Id"].ToString() + "&map=" + Rs2.ReadData["map_swf"].ToString());

// Generate <entity id=".." value=".." /> and also add link to it
      strXML.AppendFormat("<entity id='{0}' value='{1}' link='{2}' />", Rs1.ReadData["Internal_Id"].ToString(), Math.Round(Convert.ToDouble(Rs1.ReadData["datap"]), 2), LinkURL);

Rs2.ReadData.Close();
}
}

// Close reader
Rs1.ReadData.Close();

// Finally, close <map> element and add
strXML.Append("</data>");

Here we extract internalID from Rs1.ReadData["Internal_Id"] and set to id attribute.
We get percentage of population of the state against total US population from Math.Round(Convert.ToDouble(Rs1.ReadData["datap"]), 2) and set to value attribute. We set the dacimal limit of the value to 2 places using ASP.NET function Math.Round().

Setting up the Links

We also set up drill-down link to each entity. Each enity on click will drill down to another aspx file. This we did in the entity entry itself as shown above using link attribute. Let's see the how the link is structured.

...string LinkURL = Server.UrlEncode("StateDetails.aspx?Internal_Id=" + Rs1.ReadData["Internal_Id"].ToString() + "&map=" + Rs2.ReadData["map_swf"].ToString());

All states/entities will drill-down to StateDetails.aspx page.
Each entity will pass a query string variable - Internal_Id whose value will be the internal id of that state. This is extracted out from the"Internal_id" field of the iterated record.
Each entity will also pass the map file name to Detailed.asp page using querystirng variable -map. Note that another data reader Rs2 is created for each iteration which stores the map file name of the releted Internal_Id in "map_swf" field. Data reader Rs2 is created from table fcmap_master using SQL query - "select map_swf from fcmap_master where Internal_Id='" + Rs1.ReadData["Internal_Id"].ToString() + "'".

Thus all entities are created.

Next we add styles like XScale, YScale, alpha & shadow styles to the map using <styles> element.
Finally we return the XML as dataURL to renderMap() function in Default.aspx.cs..

// Set Proper output content-type

Response.ContentType = "text/xml";

// Just write out the XML data

// NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER

Response.Write(strXML.ToString());

The above mentioned renderMap() function (in Default.aspx.cs) renders the map retrieving the XML and set it to literal control USMap.

string mapHTML=FusionMaps.RenderMap("../Maps/FCMap_USA.swf", dataURL, "", "mapid", "600", "400", false, false);

' embed the chart rendered as HTML into Literal - USMap
USMap.Text = mapHTML;

Here is the screenshot of the US map that we just created extracting data from database.

We have used DataConnection Namespace in the above code and in all subsequent Database example(s). Using this class we establish connection to the MS Access database with ADO.NET component. You can always change your connection to any other database server. Let's go through the lines of code inside this class:

using System;
using System.Data;
using System.Data.Odbc;
using System.Web;
using System.Configuration;

namespace DataConnection
{
    /// <summary>
    /// Summary description for DbHelper.
    /// </summary>
    public class DbConn
    {

// Create a database Connection. using here Access Database
// Return type object of OdbcConnection

        public OdbcConnection connection;
        public OdbcDataReader ReadData;
        public OdbcCommand aCommand;
        public OdbcDataAdapter DataAdapter;
        public DataSet DataSet;

public DbConn()
{

string connectionName = "MSAccessConnection";
// connectionName = "sqlServerConnection";

            string ConnectionString = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
            try
            {
                connection = new OdbcConnection();
                connection.ConnectionString = ConnectionString;
                connection.Open();
            }
            catch (Exception e)
            {
                HttpContext.Current.Response.Write(e.Message.ToString());
            }
            DataSet = new DataSet();

}

public DbConn(string strQuery)
{

            string connectionName = "MSAccessConnection";
           // connectionName = "sqlServerConnection";

           string ConnectionString = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
            try
            {
                connection = new OdbcConnection();
                connection.ConnectionString = ConnectionString;
                connection.Open();
                GetReader(strQuery);
            }
            catch (Exception e)
            {
                    HttpContext.Current.Response.Write(e.Message.ToString());
            }
        }

        // Create an instance dataReader
        // Return type object of OdbcDataReader
        public void GetReader(string strQuery)
        {
            // Create a Command object
            aCommand = new OdbcCommand(strQuery, connection);
            // Create data reader object using strQuery string

ReadData = aCommand.ExecuteReader(CommandBehavior.CloseConnection);

}

        public void GetDataSet(string strQuery, string TableName)
        {
            // Create a Command object
            aCommand = new OdbcCommand(strQuery, connection);
            // Create Data Adapter
            DataAdapter = new OdbcDataAdapter(aCommand);
            // Fill dataset with record

            DataAdapter.Fill(DataSet, TableName);
        }
    }
}

What it does:

Set up Connection as per the connection string defined in web.config file.

<connectionStrings>
<add name="MSAccessConnection" providerName="System.Data.Odbc" connectionString="Driver={Microsoft Access Driver (*.mdb)};Dbq=|DataDirectory|\FactoryDB.mdb"/>
</connectionStrings>

To change your connection to any other database server, you only need to setup web.config file.
The class DBConn accepts SQL Query, executes it and returns the result as ASP.NET DataReader object -ReadData.

Example:

using DataConnection

...

   //store the sql query
   string strQuery = "select colName1, colName2 from yourTable";
   //Create datareader object
   DbConn Rs = new DbConn(strQuery);

   //Create a sample accumulator
   int accumulator= 0;

   //Check if we've records to show
   if(Rs.ReadData.HasRows)
   {
      //Iterate through table
      while(Rs1.ReadData.Read())
      {
        //do something using the columns
        sumdata += Convert.ToInt32(Rs.ReadData["colName1"])+Convert.ToInt32(Rs.ReadData["colName2"]);
      }
   }

   ' Close the reader
   Rs.ReadData.Close();