In this article I will explain with an example, how to create Multi-Series (Multi-Column) Column Chart with database using ASP.Net Chart Control with C# and VB.Net.
The Multi-Series (Multi-Column) Column Chart is different from Column Chart as compared to Column Chart which simply displays only one series, the Multi-Series (Multi-Column) Column Chart displays multiple series of Column Chart representing the statistical information.
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 Chart control.
<asp:Chart ID="Chart1" runat="server" Height="300px" Width="400px">
<Titles>
<asp:Title ShadowOffset="3" Name="Items" />
</Titles>
<Legends>
<asp:Legend Alignment="Center" Docking="Bottom" IsTextAutoFit="False" Name="Default"
LegendStyle="Row" />
</Legends>
<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 Multi-Series (Multi-Column) Column Chart from database in ASP.Net
Inside the Page Load event of the page, the Multi-Series (Multi-Column) Column Chart is populated from database.
The SQL Query gets the Country-wise Order distribution for three years. The records from database are populated into a DataTable.
The populated DataTable will contain statistical data for multiple countries in following format. Basically it is the statistical data of Orders of each Country for each Year. Example, Total Orders of Brazil for Year 1996, 1997 and 1998.
A loop is executed over the Countries present in DataTable and one by one series are added to the ASP.Net Chart control.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
//Fetch the Statistical data from database.
string query = "SELECT ShipCountry, DATEPART(Year, OrderDate) [Year], COUNT(OrderId) [Total]";
query += " FROM Orders WHERE ShipCountry IN ('France', 'Germany', 'Brazil')";
query += " GROUP BY ShipCountry, DATEPART(Year, OrderDate)";
DataTable dt = GetData(query);
//Get the DISTINCT Countries.
List<string> countries = (from p in dt.AsEnumerable()
select p.Field<string>("ShipCountry")).Distinct().ToList();
//Loop through the Countries.
foreach (string country in countries)
{
//Get the Year for each Country.
int[] x = (from p in dt.AsEnumerable()
where p.Field<string>("ShipCountry") == country
orderby p.Field<int>("Year") ascending
select p.Field<int>("Year")).ToArray();
//Get the Total of Orders for each Country.
int[] y = (from p in dt.AsEnumerable()
where p.Field<string>("ShipCountry") == country
orderby p.Field<int>("Year") ascending
select p.Field<int>("Total")).ToArray();
//Add Series to the Chart.
Chart1.Series.Add(new Series(country));
Chart1.Series[country].IsValueShownAsLabel = true;
Chart1.Series[country].ChartType = SeriesChartType.Column;
Chart1.Series[country].Points.DataBindXY(x, y);
}
Chart1.Legends[0].Enabled = true;
}
}
private static DataTable GetData(string query)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlDataAdapter sda = new SqlDataAdapter(query, con))
{
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
'Fetch the Statistical data from database.
Dim query As String = "SELECT ShipCountry, DATEPART(Year, OrderDate) [Year], COUNT(OrderId) [Total]"
query += " FROM Orders WHERE ShipCountry IN ('France', 'Germany', 'Brazil')"
query += " GROUP BY ShipCountry, DATEPART(Year, OrderDate)"
Dim dt As DataTable = GetData(query)
'Get the DISTINCT Countries.
Dim countries As List(Of String) = (From p In dt.AsEnumerable() _
Select p.Field(Of String)("ShipCountry")).Distinct().ToList()
'Loop through the Countries.
For Each country As String In countries
'Get the Year for each Country.
Dim x As Integer() = (From p In dt.AsEnumerable() _
Where p.Field(Of String)("ShipCountry") = country _
Order By p.Field(Of Integer)("Year") _
Select p.Field(Of Integer)("Year")).ToArray()
'Get the Total of Orders for each Country.
Dim y As Integer() = (From p In dt.AsEnumerable() _
Where p.Field(Of String)("ShipCountry") = country _
Order By p.Field(Of Integer)("Year") _
Select p.Field(Of Integer)("Total")).ToArray()
'Add Series to the Chart.
Chart1.Series.Add(New Series(country))
Chart1.Series(country).IsValueShownAsLabel = True
Chart1.Series(country).ChartType = SeriesChartType.Column
Chart1.Series(country).Points.DataBindXY(x, y)
Next
Chart1.Legends(0).Enabled = True
End If
End Sub
Private Shared Function GetData(ByVal query As String) As DataTable
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using sda As SqlDataAdapter = New SqlDataAdapter(query, con)
Dim dt As DataTable = New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Function
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="constr"
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
![Create Multi-Series (Multi-Column) Column Chart using ASP.Net Chart control with C# and VB.Net]()
Demo
Downloads
