How to bind data from database to chart control in asp.net

Suggested Videos
Part 1 - ASP.NET Chart Control
Part 2 - How to set asp.net chart control ChartType property dynamically
Part 3 - Creating asp.net chart data programmatically



In Part 3 of this video series, we discussed programatically creating data for asp.net chart control. But in that example, we have hard-coded the data in the code-behind file. In this video, we will discuss retrieving data from the database and then programatically adding that data to the chart control. We will be continuing with the example we worked with in Part 3.



Step 1 : Create Students table

Create Table Students

(

     ID int primary key identity,

     StudentName nvarchar(50),

     TotalMarks int

)

GO

Insert into Students values('Mark', 800)

Insert into Students values('Steve', 900)

Insert into Students values('John', 700)

Insert into Students values('Mary', 900)

Insert into Students values('Ben', 600)

GO

Step 2 : In web.config file, add the connection string to your database

<add name="CS"

         connectionString="server=.;database=Sample;integrated security=SSPI"/>

Step 3 : Modify the code in the code-behind file as shown below. The code is commented and self explanatory.

using System;

using System.Configuration;

using System.Data.SqlClient;

using System.Web.UI.DataVisualization.Charting;

using System.Web.UI.WebControls;

namespace ChartsDemo

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                // Call Get ChartData() method in the PageLoad event

                GetChartData();

                GetChartTypes();

            }

        }

        private void GetChartData()

        {

            string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;

            using (SqlConnection con = new SqlConnection(cs))

            {

                // Command to retrieve Students data from Students table

                SqlCommand cmd = new

                    SqlCommand("Select StudentName, TotalMarks from Students", con);

                con.Open();

                SqlDataReader rdr = cmd.ExecuteReader();

                // Retrieve the Series to which we want to add DataPoints

                Series series = Chart1.Series["Series1"];

                // Loop thru each Student record

                while (rdr.Read())

                {

                    // Add X and Y values using AddXY() method

                    series.Points.AddXY(rdr["StudentName"].ToString(),

                        rdr["TotalMarks"]);

                }

            }

        }

        private void GetChartTypes()

        {

            foreach (int chartType in Enum.GetValues(typeof(SeriesChartType)))

            {

                ListItem li = new ListItem(Enum.GetName(typeof(SeriesChartType),

                    chartType), chartType.ToString());

                DropDownList1.Items.Add(li);

            }

        }

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)

        {

            // Call Get ChartData() method when the user select a different chart type

            GetChartData();

            this.Chart1.Series["Series1"].ChartType = (SeriesChartType)Enum.Parse(

                typeof(SeriesChartType), DropDownList1.SelectedValue);

        }

    }

}