Populate ASPNet 40 Chart Control Pie Chart from SQL Server Database

In this article I will explain with an example, how you can display charts in ASP.Net using new Chart Control in ASP.Net 4.0.

Database

Here I am making use of Microsoft’s Northwind Database. You can download it from here.

Download Northwind Database

HTML Markup

Below is the HTML Markup of the page. It has an ASP.Net DropDownList and an ASP.Net Chart Control. The DropDownList is populated with countries and when a country is selected, the chart is populated with the statistics of orders of different cities in the selected country.

<asp:DropDownList ID="ddlCountries" runat="server" OnSelectedIndexChanged="ddlCountries_SelectedIndexChanged"

AutoPostBack = "true">

</asp:DropDownList><hr />

<asp:Chart ID="Chart1" runat="server" Height="300px" Width="400px" Visible = "false">

<asp:Title ShadowOffset="3" Name="Items" />

</Titles>

<asp:Legend Alignment="Center" Docking="Bottom" IsTextAutoFit="False" Name="Default" LegendStyle="Row" />

</Legends>

<asp:Series Name="Default" />

</Series>

<asp:ChartArea Name="ChartArea1" BorderWidth="0" />

</ChartAreas>

</asp:Chart>

Namespaces

You will need to import the following Namespaces.

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

VB.Net

Imports System.Data

Imports System.Data.SqlClient

Imports System.Web.UI.DataVisualization.Charting

Populating the DropDownList and Chart

Inside the Page Load event, the DropDownList is populated with Countries from the Orders table of the Northwind database.

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 Ship City values are assigned to the X point values of the Chart while the Total Orders value for the Ship Cities are assigned to the Y point values of the Chart.

Finally using these values the Chart is populated and displayed.

protected void Page_Load(object sender, EventArgs e)

{

if (!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", ""));

}

protected void ddlCountries_SelectedIndexChanged(object sender, EventArgs e)

{

Chart1.Visible = ddlCountries.SelectedValue != "";

string query = string.Format("select shipcity, count(orderid) from orders where shipcountry = '{0}' group by shipcity", ddlCountries.SelectedValue);

DataTable dt = GetData(query);

string[] x = new string[dt.Rows.Count];

int[] y = new int[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]);

}

Chart1.Series[0].Points.DataBindXY(x, y);

Chart1.Series[0].ChartType = SeriesChartType.Pie;

Chart1.ChartAreas["ChartArea1"].Area3DStyle.Enable3D = true;

Chart1.Legends[0].Enabled = true;

}

private static DataTable GetData(string query)

{

DataTable dt = new DataTable();

SqlCommand cmd = new SqlCommand(query);

String constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;

SqlConnection con = new SqlConnection(constr);

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)

ddlCountries.DataSource = dt

ddlCountries.DataTextField = "shipcountry"

ddlCountries.DataValueField = "shipcountry"

ddlCountries.DataBind()

ddlCountries.Items.Insert(0, New ListItem("Select", ""))

End If

End Sub

Protected Sub ddlCountries_SelectedIndexChanged(sender As Object, e As EventArgs)

Chart1.Visible = ddlCountries.SelectedValue <> ""

Dim query As String = String.Format("select shipcity, count(orderid) from orders where shipcountry = '{0}' group by shipcity", ddlCountries.SelectedValue)

Dim dt As DataTable = GetData(query)

Dim x As String() = New String(dt.Rows.Count - 1) {}

Dim y As Integer() = New Integer(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))

Chart1.Series(0).Points.DataBindXY(x, y)

Chart1.Series(0).ChartType = SeriesChartType.Pie

Chart1.ChartAreas("ChartArea1").Area3DStyle.Enable3D = True

Chart1.Legends(0).Enabled = True

End Sub

Private Shared Function GetData(query As String) As DataTable

Dim dt As New DataTable()

Dim cmd As New SqlCommand(query)

Dim constr As [String] = ConfigurationManager.ConnectionStrings("ConString").ConnectionString

Dim con As New SqlConnection(constr)

Dim sda As New SqlDataAdapter()

cmd.CommandType = CommandType.Text

cmd.Connection = con

sda.SelectCommand = cmd

sda.Fill(dt)

Return dt

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.

</appSettings>

<add name="conString"

connectionString="Data Source=.\SQL2005;database=Northwind;Integrated Security=true"/>

</connectionStrings>

<system.web>

</assemblies>

</compilation>

</httpHandlers>

<pages>

</controls>

</pages>

</system.web>

<system.webServer>

</handlers>

</system.webServer>

</configuration>

Screenshot

Populate ASP.Net 4.0 Chart Control Pie Chart from SQL Server Database

Demo

View Demo

Downloads

Download Code

Populate ASP.Net 4.0 Chart Control Pie Chart from SQL Server Database

Related

Ask Question

Company

Explore

Follow us

Contact Us