In this article I will explain with an example, how to populate Bar and Column Charts from database using ASP.Net Chart control in C# and VB.Net.
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
HTML Markup
The following HTML Markup consists of an ASP.Net DropDownList, RadioButtonList and Chart control. The DropDownList is populated with countries and when a country is selected, the Chart control is populated with the statistics of orders of different cities in the selected country.
The RadioButtonList is used to toggle the Chart types i.e. Bar and Column charts in the Chart control.
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<asp:DropDownList ID="ddlCountries" runat="server">
</asp:DropDownList>
</td>
<td>
<asp:RadioButtonList ID="rblChartType" runat="server" RepeatDirection="Horizontal">
<asp:ListItem Text="Bar" Value="7" Selected="True" />
<asp:ListItem Text="Column" Value="10" />
</asp:RadioButtonList>
</td>
</tr>
</table>
<br />
<asp:Button Text="Populate" runat="server" OnClick="PopulateChart" />
<hr />
<asp:Chart ID="Chart1" runat="server" Height="300px" Width="400px" Visible="false">
<Titles>
<asp:Title ShadowOffset="3" Name="Items" />
</Titles>
<Legends>
<asp:Legend Alignment="Center" Docking="Bottom" IsTextAutoFit="False" Name="Default"
LegendStyle="Row" />
</Legends>
<Series>
<asp:Series Name="Default" />
</Series>
<ChartAreas>
<asp:ChartArea Name="ChartArea1" BorderWidth="0" />
</ChartAreas>
</asp:Chart>
Namespaces
You will need to import the following Namespaces.
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.UI.DataVisualization.Charting;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.UI.DataVisualization.Charting
Populating the DropDownList
Inside the Page Load event, the DropDownList is populated with Countries from the Orders table of the Northwind database.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
string query = "SELECT DISTINCT ShipCountry FROM Orders";
DataTable dt = GetData(query);
ddlCountries.DataSource = dt;
ddlCountries.DataTextField = "ShipCountry";
ddlCountries.DataValueField = "ShipCountry";
ddlCountries.DataBind();
ddlCountries.Items.Insert(0, new ListItem("Select Country", ""));
}
}
private static DataTable GetData(string query)
{
string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
DataTable dt = new DataTable();
using (SqlDataAdapter sda = new SqlDataAdapter(query, con))
{
sda.Fill(dt);
}
return dt;
}
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim query As String = "SELECT DISTINCT ShipCountry FROM Orders"
Dim dt As DataTable = GetData(query)
ddlCountries.DataSource = dt
ddlCountries.DataTextField = "ShipCountry"
ddlCountries.DataValueField = "ShipCountry"
ddlCountries.DataBind()
ddlCountries.Items.Insert(0, New ListItem("Select Country", ""))
End If
End Sub
Private Shared Function GetData(ByVal query As String) As DataTable
Dim constr As String = ConfigurationManager.ConnectionStrings("ConString").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(query)
Dim dt As DataTable = New DataTable()
Using sda As SqlDataAdapter = New SqlDataAdapter(query, con)
sda.Fill(dt)
End Using
Return dt
End Using
End Using
End Function
Populating the Bar and Column charts using DataTable
When a Country is selected in the DropDownList, the statistical records of Ship Cities and their Total Orders are fetched from the Orders table.
The populated DataTable has been assigned to the Chart Control’s DataSource property.
Then the ShipCity field has been set as the XValueMember while the Total field has been set as the YValueMembers.
Finally the DataBind function is called, which populates the Chart.
C#
protected void PopulateChart(object sender, EventArgs e)
{
Chart1.Visible = ddlCountries.SelectedValue != "";
string query = string.Format("SELECT ShipCity, COUNT(OrderId) [Total] FROM Orders WHERE ShipCountry = '{0}' GROUP BY ShipCity", ddlCountries.SelectedValue);
DataTable dt = GetData(query);
Chart1.DataSource = dt;
Chart1.Series[0].ChartType = (SeriesChartType)int.Parse(rblChartType.SelectedItem.Value);
Chart1.Legends[0].Enabled = true;
Chart1.Series[0].XValueMember = "ShipCity";
Chart1.Series[0].YValueMembers = "Total";
Chart1.DataBind();
}
VB.Net
Protected Sub PopulateChart(ByVal sender As Object, ByVal e As EventArgs)
Chart1.Visible = ddlCountries.SelectedValue <> ""
Dim query As String = String.Format("SELECT ShipCity, COUNT(OrderId) [Total] FROM Orders WHERE ShipCountry = '{0}' GROUP BY ShipCity", ddlCountries.SelectedValue)
Dim dt As DataTable = GetData(query)
Chart1.DataSource = dt
Chart1.Series(0).ChartType = CType(Integer.Parse(rblChartType.SelectedItem.Value), SeriesChartType)
Chart1.Legends(0).Enabled = True
Chart1.Series(0).XValueMember = "ShipCity"
Chart1.Series(0).YValueMembers = "Total"
Chart1.DataBind()
End Sub
Web.Config Modifications
You will need to modify the Web.Config file as following shown in YELLOW in order to use the ASP.Net 4.0 Chart control.
<configuration>
<appSettings>
<add key="ChartImageHandler" value="storage=file;timeout=20;" />
</appSettings>
<connectionStrings>
<add name="conString"
connectionString="Data Source=.\SQL2005;database=Northwind;Integrated Security=true"/>
</connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.0">
<assemblies>
<add assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
</assemblies>
</compilation>
<httpHandlers>
<add path="ChartImg.axd" verb="GET,HEAD,POST" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false"/>
</httpHandlers>
<pages>
<controls>
<add tagPrefix="asp" namespace="System.Web.UI.DataVisualization.Charting" assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
</controls>
</pages>
</system.web>
<system.webServer>
<handlers>
<remove name="ChartImageHandler"/>
<add name="ChartImageHandler" preCondition="integratedMode" verb="GET,HEAD,POST" path="ChartImg.axd" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
</handlers>
</system.webServer>
</configuration>
Screenshot
![Populate Bar and Column Charts from database using ASP.Net Chart control]()
Demo
Downloads
