Charts and graphs always attract more users to the website. Displaying statistical data in the form of graphs gives more meaning to the data. There are tons of third party controls that can be used to create impressive charts but almost all of them come with a price. In this article I will introduce a very simple charting control, WebChart which allows the developer to create really cool looking graphs with only few lines of code. The best thing about this control is that it is totally free. The article will focus on creating the SmoothLineChart and the PieChart.
Introduction:
Charts and graphs always attract more users to the website. Displaying statistical data in the form of graphs gives more meaning to the data. There are tons of third party controls that can be used to create impressive charts but almost all of them come with a price. In this article I will introduce a very simple charting control, WebChart which allows the developer to create really cool looking graphs with only few lines of code. The best thing about this control is that it is totally free. The article will focus on creating the SmoothLineChart and the PieChart.
Downloading the WebChart Control:
The first task is to download the WebChart control. Simply go to this link and download the latest version of the WebChart control. Once, you have downloaded the control simply add the control in your toolbox. This will give you the feature of dragging and dropping the control on the Visual Studio.NET designer surface and use it instantly.
Populating the DropDownList with Students:
Before implementing the code to display SmoothLineChart let’s first create some dummy data. In this article the fiction less company name is “LadyBird School”. The LadyBird School allows the teachers to view the student exam scores in the form of a chart. A student can take an exam multiple numbers of times and his highest score in any of his attempt is considered his final score. The teachers are displayed a list of all the students from which they can select a student and see his progress.
So, our first task is to populate a DropDownList with all the students. Check out the code below:
private void BindData() { string connectionString = "Server=localhost;Database=School;Trusted_Connection=true"; SqlConnection myConnection = new SqlConnection(connectionString); SqlDataAdapter ad = new SqlDataAdapter("SELECT UserID,FirstName + ' ' + LastName AS [FullName] FROM Users", myConnection); DataSet ds = new DataSet(); ad.Fill(ds); ddlNames.DataSource = ds; ddlNames.DataTextField = "FullName"; ddlNames.DataValueField = "UserID"; ddlNames.DataBind(); } |
The above code simply gets all the students and displays them in the DropDownList. Check out the screen shot below:
Creating the SmoothLineChart:
Creating the report is the fun part. We want that when the user clicks the “Display Report” button a custom report is generated of the selected student. Let’s first check out the T-SQL query which returns the maximum score of the student in each of his exams.
|
SELECT ue.ExamID,MAX(ue.Score) AS Score,u.FirstName,u.LastName,e.Title FROM UserExams ue
JOIN Users u ON u.UserID = ue.UserID
JOIN Exams e ON e.ExamID = ue.ExamID
WHERE u.UserID = @UserID
GROUP BY ue.ExamID,u.FirstName,u.LastName,e.Title |
The query takes UserID as the parameter and returns the maximum score of the student in each of his exams. Let’s check out the “Display Report” click button event in detail.
protected void Button1_Click(object sender, EventArgs e) { int userID = Convert.ToInt32(ddlNames.SelectedValue); string connectionString = "Server=localhost;Database=School;Trusted_Connection=true"; SqlConnection myConnection = new SqlConnection(connectionString); string selectQuery = @"SELECT ue.ExamID,MAX(ue.Score) AS Score,u.FirstName,u.LastName,e.Title FROM UserExams ue JOIN Users u ON u.UserID = ue.UserID JOIN Exams e ON e.ExamID = ue.ExamID WHERE u.UserID = @UserID GROUP BY ue.ExamID,u.FirstName,u.LastName,e.Title"; SqlCommand myCommand = new SqlCommand(selectQuery, myConnection); myCommand.Parameters.AddWithValue("@UserID", userID); SqlDataAdapter ad = new SqlDataAdapter(myCommand); DataSet ds = new DataSet(); ad.Fill(ds); SmoothLineChart chart = new SmoothLineChart(); chart.DataXValueField = "Title"; chart.DataYValueField = "Score"; chart.DataLabels.Visible = true; chart.ShowLegend = true; chart.DataBind(); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { int a = Convert.ToInt32(ds.Tables[0].Rows[i]["Score"]); chart.Data.Add(new ChartPoint((string)ds.Tables[0].Rows[i]["Title"], a)); //chart.Data.Add(new Pie ChartControl1.Charts.Add(chart); ChartControl1.RedrawChart(); } } |
Now, that you have the complete code in front of you let’s break it up into small pieces and see what is going on. The first thing I did is to retrieve the UserID from the DropDownList and assign it to the userID variables. The UserID is the primary key in the database table. Next, I defined a connection string and the T-SQL query. I attached the UserID as a parameter to the SqlCommand object and used it with the SqlDataAdapter to fill the DataSet.
Once, the DataSet was filled I created a SmoothLineChart object by using the following line of code:
SmoothLineChart chart = new SmoothLineChart(); |
The SmoothLineChart object has bunch of properties but the most important ones are the following:
DataXValueField: Represents the field name to be used on the X-Axis.
DataYValueField: Represents the field name to be used on the Y-Axis.
The purpose of the for loop is to put the points on the chart. The ChartPoint constructor takes two argument which are X and Y axis respectively. Finally, the SmoothLineChart object, chart is added to the WebChart control’s collection and RedrawChart() method is called to bind the chart on the screen.
The above chart is plotted for the user “Mohammad Azam” which displays the test score of each of the exams.
Creating the Pie Chart Using WebChart Control:
Pie Chart offers a way to view the overall performance of the class. Let’s say that the teacher wants to see the number of students who scored in a particular range, meaning how many students scored between 0-59 and how many scored between 90-100? Let’s see how this can be done. First thing you will need is a T-SQL query that returns the “GroupName” and the number of students belonging in that group. The group name refers to different ranges like 0-59, 59-69, 69-79 and so on. Here is a T-SQL query that returns the GroupName and the students.
-- Create the Temp Table CREATE TABLE #UserExamGroups ( GroupName varchar(10) ) INSERT INTO #UserExamGroups(GroupName) SELECT 'Group' = CASE WHEN MAX(ue.Score) BETWEEN 0 AND 59 THEN '0-59' WHEN MAX(ue.Score) BETWEEN 59 AND 69 THEN '59-69' WHEN MAX(ue.Score) BETWEEN 69 AND 79 THEN '69-79' WHEN MAX(ue.Score) BETWEEN 79 AND 89 THEN '79-89' WHEN MAX(ue.Score) BETWEEN 89 AND 100 THEN '89-100' END FROM UserExams ue JOIN Users u ON ue.UserID = u.UserID JOIN Exams e ON ue.ExamID = e.ExamID GROUP BY u.FirstName,u.LastName,e.Title SELECT GroupName,COUNT(GroupName) AS [Total] FROM #UserExamGroups GROUP BY GroupName -- Drop the temp table DROP TABLE #UserExamGroups |
Now, let’s see the code which displays the Pie Chart.
private void DrawPieChart() { string connectionString = "Server=localhost;Database=School;Trusted_Connection=true"; SqlConnection myConnection = new SqlConnection(connectionString); string selectQuery = @" -- Create the Temp Table CREATE TABLE #UserExamGroups ( GroupName varchar(10) ) INSERT INTO #UserExamGroups(GroupName) SELECT 'Group' = CASE WHEN MAX(ue.Score) BETWEEN 0 AND 59 THEN '0-59' WHEN MAX(ue.Score) BETWEEN 59 AND 69 THEN '59-69' WHEN MAX(ue.Score) BETWEEN 69 AND 79 THEN '69-79' WHEN MAX(ue.Score) BETWEEN 79 AND 89 THEN '79-89' WHEN MAX(ue.Score) BETWEEN 89 AND 100 THEN '89-100' END FROM UserExams ue JOIN Users u ON ue.UserID = u.UserID JOIN Exams e ON ue.ExamID = e.ExamID GROUP BY u.FirstName,u.LastName,e.Title SELECT GroupName,COUNT(GroupName) AS [Total] FROM #UserExamGroups GROUP BY GroupName -- Drop the temp table DROP TABLE #UserExamGroups "; SqlCommand myCommand = new SqlCommand(selectQuery, myConnection); SqlDataAdapter ad = new SqlDataAdapter(myCommand); DataSet ds = new DataSet(); ad.Fill(ds); PieChart pie = new PieChart(); pie.DataSource = ds.Tables[0].DefaultView; pie.DataXValueField = "GroupName"; pie.DataYValueField = "Total"; pie.DataLabels.Visible = true; pie.DataLabels.ShowXTitle = true; pie.DataLabels.Separator = ": "; pie.Shadow.Visible = true; pie.DataBind(); pie.Explosion = 10; ChartControl1.Charts.Add(pie); ChartControl1.RedrawChart(); } |
I have made the important lines bold. First, I create an object of the PieChart class and assigned it to the DataTable which was populated by the T-SQL query defined above. Next, I assigned the XValueField and YValueField. Finally, I added the pie chart to the WebChart chart’s collection and used the RedrawChart() method to display it on the screen.
-
As, you can see in the image above the chart is divided into small sections and each section represents a different group. From looking at the chart we can calculate that there were 5 students who scored in the range of 89-100 and 3 students who scored in the range of 0-59.
Conclusion:
WebChart allows you to quickly create very impressive graphs and charts. It comes with many different types of graphs which suits all sorts of applications.
I hope you liked this article, happy programming!
