Introduction
Creating Line Charts from Database Data
This article shows you how to create a line chart for web forms using VB.NET. I think there are several third party components available for creating line chart and graphs. It wouldn't get you a knowledge on how internally it works. Hence I have created a line chart using GDI+ in VB.NET. Besides this, you will be able to create a line chart in a quick manner
Using the code
Below are the code for functions used to create the line chart
Now that you know how to create a simple image from an ASP.NET Web page, you can create more complex (and useful) images. For the remainder of this article I'll look at how to use the .NET Framework drawing classes to create a Line chart from database information. I'll build all of this functionality into a set of functions in an ASP.NET Web page that will end up streaming the dynamically created Line chart's binary content to the Response object's OutputStream.
While creating a set of page-level functions to display a line chart will accomplish the task at hand, a more reusable solution would be to encapsulate this functionality into a custom-defined ASP.NET Web control or compiled custom control. One disadvantage of such an approach, though, would be that the custom-defined ASP.NET Web control or compiled custom control would have to save the image's file to the Web server's file system and then render it from an appropriate img tag. While this isn't difficult to accomplish, you'll have to deal with the disadvantages I mentioned earlier, including the fact that each time a chart is generated you'll keep adding to the list of images on the Web server's file system.
1) Create the another vb Class file for it That will be Creating the Line Graph according to user Sessions :===
The code for the class file is over and now we need to create an instance of
this file in a Web Form which needs line graph, and set appropriate properties.
Here I'm using Score Comparison report as an example (date Vs. Attrition & Absentisum) to
This Class file will be generate the Line Chart According to your Fatched Data Values from database
draw a line graph to compare the runs scored by both teams using line graph
LineChart Class vb class file name Like (LineChartAbs.vb) code
� Imports These Namespaces
Imports System.Data
Imports System.Drawing
Imports System.Drawing.Drawing2D
Imports System.Drawing.Imaging
Imports System.Web.UI
Public Class LineChartAbs
Public b As Bitmap
Public Title As String = "Default Title"
Public chartValues As ArrayList = New ArrayList
Public Xorigin As Single = 0
Public Yorigin As Single = 0
Public ScaleX As Single
Public ScaleY As Single
Public Xdivs As Single = 2
Public Ydivs As Single = 2
Private Width As Integer
Private Height As Integer
Private g As Graphics
Private p As Page
Private btn As Button
Structure datapoint
Public x As Single
Public y As Single
Public xCaption As String
Public yCaption As String
Public ATTRAVERAGE As String
Public valid As Boolean
End Structure
Public Sub New(ByVal myWidth As Integer, ByVal myHeight As Integer, ByVal myPage As Page)
Width = myWidth
Height = myHeight
ScaleX = myWidth
ScaleY = myHeight
b = New Bitmap(myWidth, myHeight)
g = Graphics.FromImage(b)
p = myPage
End Sub
Public Sub AddValue(ByVal x As Integer, ByVal y As Double, ByVal xCaption As String, ByVal yCaption As String)
Dim myPoint As datapoint
myPoint.x = x
myPoint.y = y
myPoint.xCaption = xCaption
myPoint.yCaption = yCaption
'myPoint.ATTRAVERAGE = ATTRAVERAGE
myPoint.valid = True
chartValues.Add(myPoint)
End Sub
Public Sub AddValueN(ByVal x As Integer, ByVal y As Double, ByVal xCaption As String, ByVal ATTRAVERAGE As String)
Dim myPoint As datapoint
myPoint.x = x
myPoint.y = y
myPoint.xCaption = xCaption
'myPoint.yCaption = yCaption
myPoint.ATTRAVERAGE = ATTRAVERAGE
myPoint.valid = True
chartValues.Add(myPoint)
End Sub
Public Sub AddValue(ByVal x As Integer, ByVal y As Double)
AddValue(x, y, x.ToString, y.ToString())
End Sub
Public Sub DisplayValues()
Dim i As Integer
Dim Labelvalue As String
Dim blackBrushN As Brush = New SolidBrush(Color.Black)
Dim axesFontN As Font = New Font("arial", 10)
Dim ChartInset As Integer = 500
Dim ChartWidth As Integer = Width - (2 * ChartInset)
Dim ChartHeight As Integer = Height - (2 * ChartInset)
For i = 0 To chartValues.Count
If i >= chartValues.Count Then
Labelvalue = ""
Else
Labelvalue = CType(chartValues(i), datapoint).xCaption
Labelvalue = Labelvalue & "-" & CType(chartValues(i), datapoint).yCaption
g.DrawString(Labelvalue, axesFontN, blackBrushN, Width + 1, Height)
End If
Next
End Sub
'Public Sub Draw(ByVal filename As String)
Public Sub Draw()
Dim i As Integer
Dim x As Single
Dim y As Single
Dim y1, y2 As Single
Dim x0 As Single
Dim y0 As Single
Dim PrintAonDot As Integer
Dim myLabel As String
Dim d1 As String
Dim m1, m2, m3 As String
Dim blackPen As Pen = New Pen(Color.Black, 1)
Dim RedPen As Pen = New Pen(Color.FromArgb(186, 5, 5), 1)
Dim bluePen As Pen = New Pen(Color.Blue, 1)
Dim blueBrush As Brush = New SolidBrush(Color.Blue)
Dim RedBrush As Brush = New SolidBrush(Color.FromArgb(109, 44, 0))
Dim MarunBrush As Brush = New SolidBrush(Color.FromArgb(109, 44, 0))
Dim blackBrush As Brush = New SolidBrush(Color.Black)
Dim axesFont As Font = New Font("verdana", 8, FontStyle.Bold)
p.Response.ContentType = "image/jpeg"
'g.FillRectangle(New SolidBrush(Color.LightYellow), 0, 0, Width, Height)
g.FillRectangle(New SolidBrush(Color.FromArgb(255, 216, 189)), 0, 0, Width, Height)
'Dim ChartInset As Integer = 50 old given for test change
Dim ChartInset As Integer = 50
Dim ChartWidth As Integer = Width - (2 * ChartInset)
Dim ChartHeight As Integer = Height - (2 * ChartInset)
g.DrawRectangle(New Pen(Color.Blue, 1), ChartInset, ChartInset, ChartWidth, ChartHeight)
btn = New Button
btn.ID = "btnBack"
btn.Text = "deepchand"
g.DrawString(Title, New Font("verdana", 8, FontStyle.Bold), blueBrush, Width / 6, 5)
i = 0
i = 0
While i <= Xdivs
x = ChartInset + (i * ChartWidth) / Xdivs
y = ChartHeight + ChartInset
'myLabel = (Xorigin + (ScaleX * i / Xdivs)).ToString
' This code Add for Print The X-axis values in Formate
If i >= chartValues.Count Then
'If i > chartValues.Count Then
myLabel = "Days->" & vbCrLf & " "
g.DrawString(myLabel, axesFont, blueBrush, x - 15, y + 10)
Else
'myLabel = CType(chartValues(i - 1), datapoint).xCaption change
myLabel = CType(chartValues(i), datapoint).xCaption
d1 = myLabel.Substring(0, 2).ToString
m1 = myLabel.Substring(3, 3).ToString
myLabel = d1 & vbCrLf & m1
g.DrawString(myLabel, axesFont, blackBrush, x - 10, y + 10)
End If
'g.DrawString(myLabel, axesFont, blackBrush, x - 8, y + 10)
g.DrawLine(blackPen, x, y + 2, x, y - 2)
If i < chartValues.Count Then
'' To Print the Attrition% on the dots
myLabel = CType(chartValues(i), datapoint).yCaption
x = (ChartWidth * (CType(chartValues(i), datapoint).x - Xorigin) / ScaleX) + ChartInset
y = ChartHeight - (ChartHeight * (CType(chartValues(i), datapoint).y - Yorigin) / ScaleY) + ChartInset - 15
g.DrawString(myLabel, axesFont, blackBrush, x - 10, y - 5)
''End To Print the Attrition% on the dots
End If
System.Math.Min(System.Threading.Interlocked.Increment(i), i - 1)
End While
i = 0
i = 0
While i <= Ydivs
x = ChartInset
y = ChartHeight + ChartInset - (i * ChartHeight / Ydivs)
myLabel = (Yorigin + (ScaleY * i / Ydivs)).ToString & "%"
If i = Ydivs Then
Dim s1 As StringFormat = New StringFormat(StringFormatFlags.DirectionVertical)
myLabel = "<-Absenteeism"
g.DrawString(myLabel, axesFont, blueBrush, 15, y - 45, s1)
ElseIf (i Mod 5) = 0 Then
g.DrawString(myLabel, axesFont, blackBrush, 20, y - 6)
Else
myLabel = ""
g.DrawString(myLabel, axesFont, blackBrush, 20, y - 6)
End If
g.DrawLine(blackPen, x + 2, y, x - 2, y)
System.Math.Min(System.Threading.Interlocked.Increment(i), i - 1)
End While
g.RotateTransform(180)
g.TranslateTransform(0, -Height)
g.TranslateTransform(-ChartInset, ChartInset)
g.ScaleTransform(-1, 1)
Dim prevPoint As datapoint = New datapoint
prevPoint.valid = False
i = 0
For Each myPoint As datapoint In chartValues
If prevPoint.valid = True Then
x0 = ChartWidth * (prevPoint.x - Xorigin) / ScaleX
y0 = ChartHeight * (prevPoint.y - Yorigin) / ScaleY
'y1 = ChartHeight * (prevPoint.ATTRAVERAGE - Yorigin) / ScaleY
x = ChartWidth * (myPoint.x - Xorigin) / ScaleX
y = ChartHeight * (myPoint.y - Yorigin) / ScaleY
'y2 = ChartHeight * (myPoint.ATTRAVERAGE - Yorigin) / ScaleY
g.DrawLine(bluePen, x0, y0, x, y)
g.FillEllipse(blueBrush, x0 - 2, y0 - 2, 6, 6)
g.FillEllipse(blueBrush, x - 2, y - 2, 6, 6)
End If
prevPoint = myPoint
i = i + 1
Next
b.Save(p.Response.OutputStream, ImageFormat.Jpeg)
b.Dispose()
� you this code un commete when pass file name from aspx page 18-nov-05
'b.Save("d:\Graph\Absent.gif", ImageFormat.Gif)
''''Start This code line save output as an image in specified Location
'Try
' b.Save(filename, ImageFormat.Gif)
' b.Dispose()
'Catch ex As Exception
' 'Response.Write("error:" & ex.Message.ToString)
'End Try
''''End This code line save output as an image in specified Location
End Sub
Public Sub DrawAverage()
Dim i As Integer
Dim x As Single
Dim y As Single
Dim x0 As Single
Dim y0 As Single
Dim PrintAonDot As Integer
Dim myLabel As String
Dim d1 As String
Dim m1, m2, m3 As String
Dim blackPen As Pen = New Pen(Color.Black, 1)
Dim bluePen As Pen = New Pen(Color.Blue, 1)
Dim blueBrush As Brush = New SolidBrush(Color.Blue)
Dim blackBrush As Brush = New SolidBrush(Color.Black)
Dim axesFont As Font = New Font("verdana", 8, FontStyle.Bold)
p.Response.ContentType = "image/jpeg"
'g.FillRectangle(New SolidBrush(Color.LightYellow), 0, 0, Width, Height)
g.FillRectangle(New SolidBrush(Color.FromArgb(255, 216, 189)), 0, 0, Width, Height)
'Dim ChartInset As Integer = 50 old given for test change
Dim ChartInset As Integer = 50
Dim ChartWidth As Integer = Width - (2 * ChartInset)
Dim ChartHeight As Integer = Height - (2 * ChartInset)
g.DrawRectangle(New Pen(Color.Blue, 1), ChartInset, ChartInset, ChartWidth, ChartHeight)
'g.DrawString(Title, New Font("arial", 14), blackBrush, Width / 3, 10)
g.DrawString(Title, New Font("verdana", 8, FontStyle.Bold), blueBrush, Width / 6, 5)
i = 0
i = 0
While i <= Xdivs
x = ChartInset + (i * ChartWidth) / Xdivs
y = ChartHeight + ChartInset
' This code Add for Print The X-axis values in Formate
If i >= chartValues.Count Then
myLabel = "Weeks->" & vbCrLf & " "
'g.DrawString(myLabel, axesFont, blueBrush, x - 17, y + 10)
Else
'myLabel = CType(chartValues(i - 1), datapoint).xCaption change
myLabel = CType(chartValues(i), datapoint).xCaption
d1 = myLabel.Substring(0, 2).ToString
m1 = myLabel.Substring(3, 3).ToString
myLabel = d1 & vbCrLf & m1
End If
g.DrawString(myLabel, axesFont, blackBrush, x - 8, y + 10)
g.DrawLine(blackPen, x, y + 2, x, y - 2)
If i < chartValues.Count And i >= 3 Then
'' To Print the Average Attrition% on the dots
myLabel = CType(chartValues(i), datapoint).ATTRAVERAGE
x = (ChartWidth * (CType(chartValues(i), datapoint).x - Xorigin) / ScaleX) + ChartInset
y = ChartHeight - (ChartHeight * (CType(chartValues(i), datapoint).y - Yorigin) / ScaleY) + ChartInset - 15
g.DrawString(myLabel, axesFont, blackBrush, x, y)
''End To Print the Average Attrition% on the dots
End If
System.Math.Min(System.Threading.Interlocked.Increment(i), i - 1)
End While
i = 0
i = 0
While i <= Ydivs
x = ChartInset
y = ChartHeight + ChartInset - (i * ChartHeight / Ydivs)
myLabel = (Yorigin + (ScaleY * i / Ydivs)).ToString & "%"
'g.DrawString(myLabel, axesFont, blackBrush, 5, y - 6) old given for test change
g.DrawString(myLabel, axesFont, blackBrush, 25, y - 6)
g.DrawLine(blackPen, x + 2, y, x - 2, y)
System.Math.Min(System.Threading.Interlocked.Increment(i), i - 1)
End While
g.RotateTransform(180)
g.TranslateTransform(0, -Height)
g.TranslateTransform(-ChartInset, ChartInset)
g.ScaleTransform(-1, 1)
Dim prevPoint As datapoint = New datapoint
prevPoint.valid = False
i = 0
For Each myPoint As datapoint In chartValues
If prevPoint.valid = True Then
x0 = ChartWidth * (prevPoint.x - Xorigin) / ScaleX
y0 = ChartHeight * (prevPoint.y - Yorigin) / ScaleY
x = ChartWidth * (myPoint.x - Xorigin) / ScaleX
y = ChartHeight * (myPoint.y - Yorigin) / ScaleY
'If CType(chartValues(i), datapoint).ATTRAVERAGE.Trim > 0 Then
'g.DrawLine(bluePen, x0, y0, x, y)
'End If
If i >= 3 Then
g.DrawLine(bluePen, x0, y0, x, y)
g.FillEllipse(blueBrush, x0 - 2, y0 - 2, 6, 6)
g.FillEllipse(blueBrush, x - 2, y - 2, 6, 6)
End If
i = i + 1
End If
'myLabel = myPoint.yCaption
'g.DrawString(myLabel, axesFont, blackBrush, x0 + 1, y0 + 1)
prevPoint = myPoint
Next
b.Save(p.Response.OutputStream, ImageFormat.Jpeg)
End Sub
Protected Overrides Sub Finalize()
g.Dispose()
b.Dispose()
End Sub
End Class
*************************************************************************************************
2) The CreateLineChart Function for That create a one ASPX Page. Name is Display.aspx
*********************************************************************
� Imports These Namespaces
Imports System.Data
Imports System.Data.OracleClient
� Variable Declarations
Dim orclCommand As OracleCommand
Dim rdr As OracleDataReader
Private OraCn As New OracleConnection(ConfigurationSettings.AppSettings("connString")) 'OracleConnection()
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Dim Itm As ListItem
Dim Sqlstr As String
� On page_load Event of that Page
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
Session("numSelIndex") = 0
Sqlstr = " SQL-Query For Get the dates from and to�
OraCn.Open()
orclCommand = New OracleCommand(Sqlstr, OraCn)
rdr = orclCommand.ExecuteReader
Itm = New ListItem
Itm.Text = "---Select---"
Itm.Value = ""
Itm.Selected = True
CmbFrWeek.Items.Add(Itm)
CmbToWeek.Items.Add(Itm)
While rdr.Read
Itm = New ListItem
Itm.Text = rdr.GetValue(0)
Itm.Value = rdr.GetValue(0)
CmbFrWeek.Items.Add(Itm)
CmbToWeek.Items.Add(Itm)
End While
rdr.Close()
OraCn.Close()
End If
End Sub
Private Sub BtnReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnReport.Click
Dim NoOfWeek As Integer
Dim Enddate, M As String
Dim StartDate, Enddate1 As String
Dim TotalPer, RowNo As Integer
Dim DynWdith As Integer
Dim XFromDate As String
Dim XFromDate1 As Integer
Dim YattritionPer, YattritionPerAvg As Double
Dim Grdflag As Boolean = True
Try
If CmbToWeek.SelectedIndex < 1 And CmbFrWeek.SelectedIndex < 1 Then
Label9.Text = "Select a Proper Dates"
Label9.Visible = True
flgFormSubmit.Value = 0
Exit Sub
ElseIf (CmbToWeek.SelectedIndex - CmbFrWeek.SelectedIndex) < 4 Then
Label9.Text = "Date's are selected should be more than 5"
flgFormSubmit.Value = 0
Label9.Visible = True
Exit Sub
ElseIf CmbToWeek.SelectedIndex <= CmbFrWeek.SelectedIndex Then
Label9.Text = "From Date must be less than To Date"
flgFormSubmit.Value = 0
Label9.Visible = True
Exit Sub
ElseIf (CmbToWeek.SelectedIndex - CmbFrWeek.SelectedIndex) > 31 Then
Label9.Text = "Date's can not select more than one months"
Label9.Visible = True
flgFormSubmit.Value = 0
Exit Sub
Else
Label9.Text = ""
End If
Label9.Text = ""
If CmbFrWeek.SelectedIndex = 1 Then
Enddate = "24-May-2003"
Else
Enddate = CmbFrWeek.Items(CmbFrWeek.SelectedIndex - 1).Value
End If
NoOfWeek = (CmbToWeek.SelectedIndex - CmbFrWeek.SelectedIndex) + 2
' This code for Excute Proc Pass the parameter
OraCn.Open()
� Create a one Glogal Temprary Table That will be Store the Main Graphical data
� That will display in the Line Graph From This Table
Dim cmd As New OracleCommand("Proc_Absent_New", OraCn)
cmd.CommandType = CommandType.StoredProcedure
Dim FromWeek As OracleParameter = cmd.Parameters.Add("frmDay", OracleType.DateTime)
FromWeek.Direction = ParameterDirection.Input
FromWeek.Value = UCase(Trim(Enddate.ToString()))
Dim NoOfWeeks As OracleParameter = cmd.Parameters.Add("NoOfDays", OracleType.Int32, 20)
NoOfWeeks.Direction = ParameterDirection.Input
NoOfWeeks.Value = UCase(Trim(NoOfWeek.ToString()))
cmd.ExecuteNonQuery()
cmd.Dispose()
� Create a one Glogal Temprary Table That will be Store the Main Graphical data
� That will display in the Line Graph From This Table
Sqlstr = "SELECT COUNT(*)RowNo,ROUND(MAX(absentper)+1) FROM ABSENT_TEMP "
'OraCn.Open()
orclCommand = New OracleCommand(Sqlstr, OraCn)
rdr = orclCommand.ExecuteReader
While rdr.Read()
TotalPer = rdr.GetValue(1)
DynWdith = rdr.GetValue(1)
RowNo = rdr.GetValue(0)
End While
rdr.Close()
orclCommand.Dispose()
'****This code for Draw graph call class declare Object
'****Select Values from the Tempprary Table
Sqlstr = "select Fromday,ABSENTPER, rownum,to_char(Fromday, 'dd-Mon') fdate from ABSENT_TEMP "
orclCommand = New OracleCommand(Sqlstr, OraCn)
rdr = orclCommand.ExecuteReader
StartDate = CmbToWeek.Items(CmbToWeek.SelectedIndex).Value
Enddate1 = CmbFrWeek.Items(CmbFrWeek.SelectedIndex).Value
� Create the Object of Line chart class that we have created before
Dim c1 As LineChartAbs = New LineChartAbs(RowNo * 50, 550, Page)
c1.Title = " Absenteeism Graph " & vbCrLf & " " & Convert.ToDateTime(Enddate1).ToString("dd/MM/yyyy") & "-" & Convert.ToDateTime(StartDate).ToString("dd/MM/yyyy")
c1.Xorigin = 0
c1.ScaleX = NoOfWeek - 0
c1.Xdivs = NoOfWeek - 0
c1.Yorigin = 0
If TotalPer > 12 Then
c1.ScaleY = Math.Ceiling(TotalPer / 10) * 10
c1.Ydivs = CInt((Math.Ceiling(TotalPer / 10) * 10))
Else
c1.ScaleY = TotalPer 'if Absent% is Less then 10% then implement this scale
c1.Ydivs = CInt(TotalPer) * 5
End If
'c1.ScaleY = 100
'c1.Ydivs = 50
While rdr.Read
XFromDate = rdr.GetValue(2) - 1
XFromDate1 = CType(XFromDate, Integer) * 1
YattritionPer = rdr.GetValue(1)
c1.AddValue(XFromDate1, YattritionPer, rdr.GetValue(3), rdr.GetValue(1))
End While
rdr.Close()
c1.Draw()
''''Start This code line save output as an image in specified Location
Dim strFilePath, strUser As String
Dim strPath As String
Session("login") = "Absenteesimg"
strUser = Session("login")
Try
strFilePath = Server.MapPath(".") & "/Graphs/" & strUser & ".gif"
Catch ex As Exception
Response.Write("Error:" & ex.Message.ToString)
End Try
c1.Draw(strFilePath)
Response.Redirect("Graph.aspx")
'End here that dispaly proper way
Catch ex As Exception
Response.Write("Error Occured" & ex.StackTrace)
Finally
OraCn.Close()
'Label9.Text = ""
'Session("numSelIndex") = CmbToWeek.SelectedIndex
'Session("formsubmit") = 0
End Try
End Sub
End Class
******************************************************************************
3) Create the One frm Graph.aspx file Write The Below line of code
In this page Call a Generate Graph that created by previous Display ASPX PAGE
'**********************************************************************
Take The one Images control in that Call the Saved Graph.
IMG TAGE set the path of that Image.
src="Graphs\Absenteesimg.gif"
Advantages :=
1) Multiple user can Access at same time.
2) Generate the Images(Graph) according to user session.
3) Export the Gif images with data in the Excel file as report point of view.
4) Generate the Dynamic Graph for each user request.
Graphical Represention of the data so that user easier way understand the output of the system data.
*****************************************************************************
Possible Enhancements
The CreatePieChart function lacks the bang and pizzazz that a third-party graphing component may provide, but this function was created in less than 15 minutes, costs nothing (except my time), and, best of all, has the source code readily available for any future changes or enhancements you may be interested in making.
One possible enhancement for the CreatePieChart function would be to add the ability to pass in a SQL string as opposed to a database table name. In its current state, the CreatePieChart graph can only create pie charts for databases that have very simple data models. Being able to specify a SQL string means you could create graphs where the data came from multiple tables, or graph only certain rows from a database table by specifying a WHERE clause.
Conclusion
Because ASP.NET allows you to use the classes from the .NET Framework, with a little bit of code you can create your own dynamic images from a Web page. These images can either be saved to the Web server's file system or streamed directly to the browser. All of the image-generation routines you will ever need are included in the .NET Framework. The charts and graphs you can create are limited only by your imagination.
