Creating Silverlight Charts from SQL Data using ASP.Net and Visifire
vivekIntroduction
In this tutorial am going to show you how to pull SQL data and create a nice chart out of it using Visifire. It just takes few minutes!!
In order to create this sample, I chose Microsoft SQL Server Sample database Northwind.mdf. You can download it here.
Visifire is available in two flavors. One in the form of an assembly which you can use within Silverlight applications and another one packaged as .xap file which you can embed within any webpage. Am using the latter one.
Visifire requires the Data to be furnished in the form of XML through a JavaScript API (Visifire2.js). You can either pass an XML string or specify the URI where XML data is available. In this example am using the latter method. In order to understand the basic XML structure required by Visifire, you can refer the documentation.
Project Setup
Create an “ASP.Net Web Application” project and add a new folder “Visifire”. Download the latest Visifire binaries from here. Extract the Zip file and add two files named “SL.Visifire.Charts.xap” and “Visifire2.js” to the folder. Now, add NORTHWND.MDF file into App_Data folder in the solution.
Am using two aspx pages in this sample.
- Data.aspx – One which pull the Data from SQL and creates required XML structure out of it. Am using aspx page just to keep it simple.
- Default.aspx – Chart is embedded into this page.
Below are the steps that we are going to follow.
- Pull the SQL Data in Data.aspx.cs file.
- Construct the Data XML inside Data.aspx.cs and return.
- Embed the Chart into Default.aspx page and set the DataUri to Data.aspx.
Pulling the Data From SQL Server DataBase
Just to keep it simple, am going to pull the Data in an aspx page (Data.aspx.cs), build XML out of it and return.
Open Data.aspx file and remove all the html code except for Page directive. So the page should have only one line of code.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Data.aspx.cs" Inherits="Data" %>
Now open Data.aspx.cs file and add the following code to setup a SQL Server DataBase connection inside the Page_Load event handler as shown below.
// Set the NORTHWND.MDF file path
String path = Server.MapPath("App_Data/NORTHWND.MDF"); // Set the connection string
String connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=" + path + ";Integrated Security=True;User Instance=True"; // Query DataBase
String query = "SELECT CategoryName, SUM(ProductSales) AS CategorySales"
+ " FROM [Product Sales for 1997] "
+ "GROUP BY CategoryName"; // Initialize the Sql connection
SqlConnection con = new SqlConnection(connectionString); // Open connection
con.Open();
// Initialize DataTable class
DataTable dt = new DataTable(); // Initialize SqlDataAdapter class
SqlDataAdapter da = new SqlDataAdapter(); // Set the Sql command
da.SelectCommand = new SqlCommand(query, con); // Fill DataSet with rows
da.Fill(dt);
// Close connection
con.Close();
Creating the XML Data
Now am going to build the Data XML that needs to be passed over to Visifire Chart control. Sample XML Data Looks like below.
<vc:Chart xmlns:vc="clr-namespace:Visifire.Charts;assembly=SLVisifire.Charts" Width="500" Height="300" Theme="Theme1" > <vc:Chart.AxesY>
<vc:Axis Prefix="$"></vc:Axis>
</vc:Chart.AxesY>
<vc:Chart.Titles>
<vc:Title Text="Category wise Sales for the year 1997" FontSize="14"/>
</vc:Chart.Titles>
<vc:Chart.Series>
<vc:DataSeries RenderAs="Column">
<vc:DataSeries.DataPoints>
<vc:DataPoint AxisXLabel="Beverages" YValue="102074.3100"/>
<vc:DataPoint AxisXLabel="Condiments" YValue="55277.6000"/>
<vc:DataPoint AxisXLabel="Confections" YValue="80894.1400"/>
<vc:DataPoint AxisXLabel="Dairy Products" YValue="114749.7800"/>
<vc:DataPoint AxisXLabel="Grains/Cereals" YValue="55948.8200"/>
<vc:DataPoint AxisXLabel="Meat/Poultry" YValue="81338.0600"/>
<vc:DataPoint AxisXLabel="Produce" YValue="53019.9800"/>
<vc:DataPoint AxisXLabel="Seafood" YValue="65544.1800"/>
</vc:DataSeries.DataPoints>
</vc:DataSeries>
</vc:Chart.Series> </vc:Chart>
You can play around with the Chart Designer to get a better picture of Visifire’s basic features.
Below is the code which generates the required XML.
// Initialize StringBuilder class
StringBuilder chartXml = new StringBuilder(); // Append chart XML data
chartXml.Append("<vc:Chart xmlns:vc=\"clr-namespace:Visifire.Charts;assembly=SLVisifire.Charts\" Width=\"500\" Height=\"300\" Theme=\"Theme1\" >"); chartXml.Append("<vc:Chart.AxesY>");
chartXml.Append("<vc:Axis Prefix=\"$\"></vc:Axis>");
chartXml.Append("</vc:Chart.AxesY>"); chartXml.Append("<vc:Chart.Titles>");
chartXml.Append("<vc:Title Text=\"Category wise Sales for the year 1997\" FontSize=\"14\"/>");
chartXml.Append("</vc:Chart.Titles>"); chartXml.Append("<vc:Chart.Series>");
chartXml.Append("<vc:DataSeries RenderAs=\"Column\" >");
chartXml.Append("<vc:DataSeries.DataPoints>"); foreach (DataRow dataRow in dt.Rows)
chartXml.Append("<vc:DataPoint AxisXLabel=\"" + dataRow["CategoryName"].ToString() + "\" YValue=\"" + dataRow["CategorySales"].ToString() + "\"/>"); chartXml.Append("</vc:DataSeries.DataPoints>");
chartXml.Append("</vc:DataSeries>");
chartXml.Append("</vc:Chart.Series>");
chartXml.Append("</vc:Chart>"); // Write object to an HTTP response stream
Response.Write(chartXml);
Create Chart
Now open Default.aspx page and add the following code. First we need to add a reference to Visifire2.js file inside the Head section of Default.aspx page.
<head runat="server">
<script type="text/javascript" src="./Visifire/Visifire2.js"></script>
</head>
Then inside the body, create a div element and add the JavaScript code which renders the Chart.
<div id="VisifireChart0"> <script type="text/javascript"> // Create Visifire object
var vChart = new Visifire2('Visifire/SL.Visifire.Charts.xap', "MyChart", 500, 300); // Set Chart Data xml source
vChart.setDataUri("Data.aspx?reqtime=" + (new Date()).getTime()); // Render the chart
vChart.render("VisifireChart0");
</script>
</div>
Make sure that the path for Visifire2.js and SL.Visifire.Charts.xap files are correct. Set Default.aspx as Start Page and run the application.
Below is a snapshot of the chart.
You can download the complete solution here.
Comments(1)
