In this article I will explain with an example how to dynamically populate ASP.Net AJAX Control Toolkit Animated Line Chart control with Multiple Series from SQL Server Database.
![ASP.Net AJAX Control Toolkit Line Chart Mutiple Series Example to populate from database]()
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here
Using the ASP.Net AJAX Line Chart control
1. Drag an ASP.Net AJAX ToolScriptManager on the page.
2. Register the AJAX Control Toolkit Library after adding reference to your project
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>
HTML Markup
The HTML Markup is simple, it contains of an ASP.Net DropDownList, ASP.Net AJAX Control Toolkit Line Chart control and a Button control.
<cc1:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
</cc1:ToolkitScriptManager>
<asp:DropDownList ID="ddlCountry1" runat="server">
</asp:DropDownList>
<asp:DropDownList ID="ddlCountry2" runat="server">
</asp:DropDownList>
<asp:Button ID="btnCompare" runat="server" Text="Compare" OnClick="Compare" Height="26px" />
<hr />
<cc1:LineChart ID="LineChart1" runat="server" ChartHeight="300" ChartWidth="450"
ChartType="Basic" ChartTitleColor="#0E426C" Visible="false" CategoryAxisLineColor="#D08AD9"
ValueAxisLineColor="#D08AD9" BaseLineColor="#A156AB">
</cc1:LineChart>
On the click of the Button based on the selection of the two DropDownLists the Line Chart with Multiple series will be dynamically populated from database.
Namespaces
You will need to import the following Namespaces
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
Populating the DropDownList and the ASP.Net AJAX Control Toolkit Line Chart with Multiple series
In the Page Load event of the ASP.Net Page I have populated two DropDownList with list of Countries from the Northwind database where the Orders have been shipped.
Then on the OnClick event of the Button I have populated the statistical information i.e. in which Cities of that selected countries how many orders were sent in a particular year.
This statistical information is presented as Line Chart where Axis are the Years the orders were shipped and the series are the total amount of Orders shipped for each Year for the city.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string query = "select distinct shipcountry from orders";
DataTable dt = GetData(query);
ddlCountry1.DataSource = dt;
ddlCountry1.DataTextField = "shipcountry";
ddlCountry1.DataValueField = "shipcountry";
ddlCountry1.DataBind();
ddlCountry2.DataSource = dt;
ddlCountry2.DataTextField = "shipcountry";
ddlCountry2.DataValueField = "shipcountry";
ddlCountry2.DataBind();
ddlCountry2.Items[1].Selected = true;
}
}
protected void Compare(object sender, EventArgs e)
{
string query = string.Format("select datepart(year, orderdate) Year, count(datepart(year, orderdate)) TotalOrders from orders where shipcountry = '{0}' group by datepart(year, orderdate)", ddlCountry1.SelectedItem.Value);
DataTable dt = GetData(query);
string[] x = new string[dt.Rows.Count];
decimal[] y = new decimal[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
x[i] = dt.Rows[i][0].ToString();
y[i] = Convert.ToInt32(dt.Rows[i][1]);
}
LineChart1.Series.Add(new AjaxControlToolkit.LineChartSeries { Name = ddlCountry1.SelectedItem.Value, Data = y });
query = string.Format("select datepart(year, orderdate) Year, count(datepart(year, orderdate)) TotalOrders from orders where shipcountry = '{0}' group by datepart(year, orderdate)", ddlCountry2.SelectedItem.Value);
dt = GetData(query);
y = new decimal[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
x[i] = dt.Rows[i][0].ToString();
y[i] = Convert.ToInt32(dt.Rows[i][1]);
}
LineChart1.Series.Add(new AjaxControlToolkit.LineChartSeries { Name = ddlCountry2.SelectedItem.Value, Data = y });
LineChart1.CategoriesAxis = string.Join(",", x);
LineChart1.ChartTitle = string.Format("{0} and {1} Order Distribution", ddlCountry1.SelectedItem.Value, ddlCountry2.SelectedItem.Value);
LineChart1.Visible = true;
}
private static DataTable GetData(string query)
{
DataTable dt = new DataTable();
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
sda.SelectCommand = cmd;
sda.Fill(dt);
}
}
return dt;
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
Dim query As String = "select distinct shipcountry from orders"
Dim dt As DataTable = GetData(query)
ddlCountry1.DataSource = dt
ddlCountry1.DataTextField = "shipcountry"
ddlCountry1.DataValueField = "shipcountry"
ddlCountry1.DataBind()
ddlCountry2.DataSource = dt
ddlCountry2.DataTextField = "shipcountry"
ddlCountry2.DataValueField = "shipcountry"
ddlCountry2.DataBind()
ddlCountry2.Items(1).Selected = True
End If
End Sub
Protected Sub Compare(sender As Object, e As EventArgs)
Dim query As String = String.Format("select datepart(year, orderdate) Year, count(datepart(year, orderdate)) TotalOrders from orders where shipcountry = '{0}' group by datepart(year, orderdate)", ddlCountry1.SelectedItem.Value)
Dim dt As DataTable = GetData(query)
Dim x As String() = New String(dt.Rows.Count - 1) {}
Dim y As Decimal() = New Decimal(dt.Rows.Count - 1) {}
For i As Integer = 0 To dt.Rows.Count - 1
x(i) = dt.Rows(i)(0).ToString()
y(i) = Convert.ToInt32(dt.Rows(i)(1))
Next
LineChart1.Series.Add(New AjaxControlToolkit.LineChartSeries() With { _
.Name = ddlCountry1.SelectedItem.Value, _
.Data = y _
})
query = String.Format("select datepart(year, orderdate) Year, count(datepart(year, orderdate)) TotalOrders from orders where shipcountry = '{0}' group by datepart(year, orderdate)", ddlCountry2.SelectedItem.Value)
dt = GetData(query)
y = New Decimal(dt.Rows.Count - 1) {}
For i As Integer = 0 To dt.Rows.Count - 1
x(i) = dt.Rows(i)(0).ToString()
y(i) = Convert.ToInt32(dt.Rows(i)(1))
Next
LineChart1.Series.Add(New AjaxControlToolkit.LineChartSeries() With { _
.Name = ddlCountry2.SelectedItem.Value, _
.Data = y _
})
LineChart1.CategoriesAxis = String.Join(",", x)
LineChart1.ChartTitle = String.Format("{0} and {1} Order Distribution", ddlCountry1.SelectedItem.Value, ddlCountry2.SelectedItem.Value)
LineChart1.Visible = True
End Sub
Private Shared Function GetData(query As String) As DataTable
Dim dt As New DataTable()
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand(query)
Using sda As New SqlDataAdapter()
cmd.CommandType = CommandType.Text
cmd.Connection = con
sda.SelectCommand = cmd
sda.Fill(dt)
End Using
End Using
Return dt
End Using
End Function
Demo
Downloads
