|
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">
<% %>
<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)
{
string dataURL = Server.UrlEncode("DataGen.aspx?op=GetUSMapDetails");
string mapHTML=FusionMaps.RenderMap("../Maps/FCMap_USA.swf", dataURL, "", "mapid", "600", "400", false, false);
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;
using DataConnection;
public partial class DrillDown_dataGen : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string op;
op = Request["op"];
switch (op)
{
case "GetUSMapDetails":
GetUSMapDetails();
break;
case "GetStateDetails":
GetStateDetails();
break;
case "getChartEmpStat":
getChartEmpStat();
break;
}
}
public void GetUSMapDetails()
{
StringBuilder strXML = new StringBuilder();
string strQuery;
double sumdata;
strXML.Append("<map borderColor='FFFFFF' fillAlpha='80' showBevel='0' numberSuffix='% of total US population' legendBorderColor='F1f1f1' hoverColor='FFFFFF' legendPosition='bottom'>");
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>");
strQuery = "select sum(data) as datap from fcmap_distribution";
DbConn Rs = new DbConn(strQuery);
sumdata = 0;
if (Rs.ReadData.HasRows == true)
{
Rs.ReadData.Read();
sumdata = Convert.ToDouble(Rs.ReadData["datap"]);
}
Rs.ReadData.Close();
strQuery = "select Internal_Id, (sum(data) / " + sumdata + ")*100 as datap from fcmap_distribution group by Internal_Id";
DbConn Rs1 = new DbConn(strQuery);
strXML.Append("<data>");
if (Rs1.ReadData.HasRows == true)
{
while (Rs1.ReadData.Read())
{
strQuery = "select map_swf from fcmap_master where Internal_Id='" + Rs1.ReadData["Internal_Id"].ToString() + "'";
DbConn Rs2 = new DbConn(strQuery);
Rs2.ReadData.Read();
string LinkURL = Server.UrlEncode("StateDetails.aspx?Internal_Id=" + Rs1.ReadData["Internal_Id"].ToString() + "&map=" + Rs2.ReadData["map_swf"].ToString());
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();
}
}
Rs1.ReadData.Close();
strXML.Append("</data>");
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>");
strXML.Append("</map>");
Response.ContentType = "text/xml";
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"];
switch (op)
{
case "GetUSMapDetails":
GetUSMapDetails();
break;
case "GetStateDetails":
GetStateDetails();
break;
case "getChartEmpStat":
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.
|
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.
|
strQuery = "select sum(data) as datap from fcmap_distribution";
DbConn Rs = new DbConn(strQuery);
sumdata = 0;
if (Rs.ReadData.HasRows == true)
{
Rs.ReadData.Read();
sumdata = Convert.ToDouble(Rs.ReadData["datap"]);
}
Rs.ReadData.Close();
strQuery = "select Internal_Id, (sum(data) / " + sumdata + ")*100 as datap from fcmap_distribution group by Internal_Id";
DbConn Rs1 = new DbConn(strQuery);
strXML.Append("<data>");
if (Rs1.ReadData.HasRows == true)
{
while (Rs1.ReadData.Read())
{
strQuery = "select map_swf from fcmap_master where Internal_Id='" + Rs1.ReadData["Internal_Id"].ToString() + "'";
DbConn Rs2 = new DbConn(strQuery);
Rs2.ReadData.Read();
string LinkURL = Server.UrlEncode("StateDetails.aspx?Internal_Id=" + Rs1.ReadData["Internal_Id"].ToString() + "&map=" + Rs2.ReadData["map_swf"].ToString());
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();
}
}
Rs1.ReadData.Close();
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..
|
Response.ContentType = "text/xml";
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);
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
{
public class DbConn
{
public OdbcConnection connection;
public OdbcDataReader ReadData;
public OdbcCommand aCommand;
public OdbcDataAdapter DataAdapter;
public DataSet DataSet;
public DbConn()
{
string connectionName = "MSAccessConnection";
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";
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());
}
}
public void GetReader(string strQuery)
{
aCommand = new OdbcCommand(strQuery, connection);
ReadData = aCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
public void GetDataSet(string strQuery, string TableName)
{
aCommand = new OdbcCommand(strQuery, connection);
DataAdapter = new OdbcDataAdapter(aCommand);
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
...
string strQuery = "select colName1, colName2 from yourTable";
DbConn Rs = new DbConn(strQuery);
int accumulator= 0;
if(Rs.ReadData.HasRows)
{
while(Rs1.ReadData.Read())
{
sumdata += Convert.ToInt32(Rs.ReadData["colName1"])+Convert.ToInt32(Rs.ReadData["colName2"]);
}
}
Rs.ReadData.Close();
|
|