Introduction
In this article I will explain how to display data in ASP.NET Chart control from SQL Server database
Description
Chart control is also a data control in ASP.NET. So we can easily bind data in Chart control from the database.
In following section I will explain how to bind data to a Chart control using HTML source as well as from code behind.
Step 1: Create a new ASP.NET application using Visual Studio 2010(any edition)
Step 2: Add a connection string named “ConString” in the Web.config file
<add name="ConString" connectionString="data source=.\SQLEXPRESS;user id=sa;password=******;initial catalog=northwind;" />
Step 3: Drag a Chart control and a SQLDataSource control on the ASPX page from the Toolbox and write following in the HTML source of the page
<asp:Chart ID="Chart1" runat="server" DataSourceID="SqlDataSource1" Width="800" Height="500">
<Series>
<asp:Series Name="Series1" XValueMember="ProductName"
YValueMembers="UnitsInStock">
</asp:Series>
</Series>
<ChartAreas>
<asp:ChartArea Name="ChartArea1" Area3DStyle-Enable3D="true">
</asp:ChartArea>
</ChartAreas>
</asp:Chart>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConString %>"
SelectCommand="SELECT TOP 10 ProductName, UnitsInStock FROM Products WHERE UnitsInStock >0 ORDER BY ProductName DESC">
</asp:SqlDataSource>
Complete HTML:
<!DOCTYPE html>
<html>
<head runat="server">
<title>Data binding in Chart control</title>
</head>
<body>
<form id="form1" runat="server">
<div style="width:800px; margin:auto; display:table;">
<asp:Chart ID="Chart1" runat="server" DataSourceID="SqlDataSource1" Width="800" Height="500">
<Series>
<asp:Series Name="Series1" XValueMember="ProductName"
YValueMembers="UnitsInStock">
</asp:Series>
</Series>
<ChartAreas>
<asp:ChartArea Name="ChartArea1" Area3DStyle-Enable3D="true">
</asp:ChartArea>
</ChartAreas>
</asp:Chart>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConString %>"
SelectCommand="SELECT TOP 10 ProductName, UnitsInStock FROM Products WHERE UnitsInStock >0 ORDER BY ProductName DESC">
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
To bind data in a Chart control from code behind follow these steps:
Step 1: Follow Step 1 and 2 from above
Step 2: Drag a Chart control on the ASPX page from the Toolbox
<asp:Chart ID="Chart1" runat="server" Width="800" Height="500">
<Series>
<asp:Series Name="Series1">
</asp:Series>
</Series>
<ChartAreas>
<asp:ChartArea Name="ChartArea1" Area3DStyle-Enable3D="true">
</asp:ChartArea>
</ChartAreas>
</asp:Chart>
Step 3: Import following namespaces in the code behind (.cs) file
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
Step 4: Write following in the code behind file
C#
protected void Page_Load(object sender, EventArgs e)
{
BindData();
}
private void BindData()
{
using(SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString))
{
string CmdString = "SELECT TOP 10 ProductName, UnitsInStock FROM Products WHERE UnitsInStock >0 ORDER BY ProductName DESC";
SqlDataAdapter sda = new SqlDataAdapter(CmdString, con);
DataSet ds = new DataSet();
sda.Fill(ds);
Chart1.DataSource = ds;
Chart1.Series["Series1"].XValueMember = "ProductName";
Chart1.Series["Series1"].YValueMembers = "UnitsInStock";
Chart1.DataBind();
}
}
VB.NET
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
BindData()
End Sub
Public Sub BindData()
Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("ConString").ConnectionString)
Dim CmdString As String="SELECT TOP 10 ProductName, UnitsInStock FROM Products WHERE UnitsInStock >0 ORDER BY ProductName DESC"
Dim sda As New SqlDataAdapter(CmdString, con)
Dim ds As New DataSet()
sda.Fill(ds)
Chart1.DataSource = ds
Chart1.Series("Series1").XValueMember = "ProductName"
Chart1.Series("Series1").YValueMembers = "UnitsInStock"
Chart1.DataBind()
End Using
End Sub
Here, first we retrieved data in a DataSet from the database and then we set DataSource property of the Chart control to the DataSet. Then we set XValueMember and YValueMembers property to the column names of our data source.
Complete HTML:
<!DOCTYPE html">
<html>
<head runat="server">
<title>Data binding in Chart control from code behind</title>
</head>
<body>
<form id="form1" runat="server">
<div style="width:800px; margin:auto; display:table;">
<asp:Chart ID="Chart1" runat="server" Width="800" Height="500">
<Series>
<asp:Series Name="Series1">
</asp:Series>
</Series>
<ChartAreas>
<asp:ChartArea Name="ChartArea1" Area3DStyle-Enable3D="true">
</asp:ChartArea>
</ChartAreas>
</asp:Chart>
</div>
</form>
</body>
</html>
Complete Code:
C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.UI.DataVisualization.Charting;
namespace ChartExample
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
BindData();
}
private void BindData()
{
using(SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString))
{
string CmdString = "SELECT TOP 10 ProductName, UnitsInStock FROM Products WHERE UnitsInStock >0 ORDER BY ProductName DESC";
SqlDataAdapter sda = new SqlDataAdapter(CmdString, con);
DataSet ds = new DataSet();
sda.Fill(ds);
Chart1.DataSource = ds;
Chart1.Series["Series1"].XValueMember = "ProductName";
Chart1.Series["Series1"].YValueMembers = "UnitsInStock";
Chart1.DataBind();
}
}
}
}
VB.NET
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.UI.DataVisualization.Charting
Public Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
BindData()
End Sub
Public Sub BindData()
Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("ConString").ConnectionString)
Dim CmdString As String = "SELECT TOP 10 ProductName, UnitsInStock FROM Products WHERE UnitsInStock >0 ORDER BY ProductName DESC"
Dim sda As New SqlDataAdapter(CmdString, con)
Dim ds As New DataSet()
sda.Fill(ds)
Chart1.DataSource = ds
Chart1.Series("Series1").XValueMember = "ProductName"
Chart1.Series("Series1").YValueMembers = "UnitsInStock"
Chart1.DataBind()
End Using
End Sub
End Class
Output:
