Data binding in ASP.NET 4.0 Chart control from SQL Server database

Introduction
In this article I will explain how to display data in ASP.NET 4.0 Chart control from SQL Server database

Description
Chart control is a data control in ASP.NET 4.0. 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)

New Project_2013-09-07_00-11-52

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:

Data binding in Chart control from code behind - Google Chrome_2013-09-07_01-46-39

One thought on “Data binding in ASP.NET 4.0 Chart control from SQL Server database

Leave a Reply

Your email address will not be published. Required fields are marked *