From Part 1: Basic Chart and Part 2: Customize Chart, you see how to
create a basic chart using GUI (no coding). But the chart is static,
it’s lack flexibility which means that you cannot change properties or
data of the chart while the application is running. Therefore, I will
show how to create a chart by coding and bind data from a SQL Server’s
database to the Chart control.On this example, I will create a
connection to “
Northwind” database on this SQL Server “
BKKSQL001\INSTANCE01” and query product name and units in stock from “
Products” table. Then, I create a chart and bind the query’s result to the chart.
Step-by-step to implement data binding chart to database
- Create a new Windows Application project on VB.NET and type name as “SampleDataBindChart“.
- On Form1, open code window and import these libraries. The first two libraries are used for SQL. The last one is used for Chart.
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms.DataVisualization.Charting
|
- Type code below on Form1_Load().
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
| Dim strConn As String = "Data Source=BKKSQL001\INSTANCE01;" & _
"Initial Catalog=Northwind;Integrated Security=True"
Dim conn As New SqlConnection(strConn)
Dim sqlProducts As String = "SELECT Top 8 ProductName, UnitsInStock FROM Products"
Dim da As New SqlDataAdapter(sqlProducts, conn)
Dim ds As New DataSet()
da.Fill(ds, "Products")
Dim ChartArea1 As ChartArea = New ChartArea()
Dim Legend1 As Legend = New Legend()
Dim Series1 As Series = New Series()
Dim Chart1 = New Chart()
Me.Controls.Add(Chart1)
ChartArea1.Name = "ChartArea1"
Chart1.ChartAreas.Add(ChartArea1)
Legend1.Name = "Legend1"
Chart1.Legends.Add(Legend1)
Chart1.Location = New System.Drawing.Point(13, 13)
Chart1.Name = "Chart1"
Series1.ChartArea = "ChartArea1"
Series1.Legend = "Legend1"
Series1.Name = "Series1"
Chart1.Series.Add(Series1)
Chart1.Size = New System.Drawing.Size(800, 400)
Chart1.TabIndex = 0
Chart1.Text = "Chart1"
Chart1.Series("Series1").XValueMember = "ProductName"
Chart1.Series("Series1").YValueMembers = "UnitsInStock"
Chart1.DataSource = ds.Tables("Products")
|
Code Explanation:
- Line 1-2: Define a connection string to connect to a database on SQL Server.
- Data Source is a SQL Server name.
- Initial Catalog is a database name.
- Set Integrated Security=True to use the current user as identity to access the SQL Server database.
- Line 4: Create a SqlConnection’s object.
- Line 6: Define SQL query string.
- Line 7-9: Execute the query and populate result to DataSet’s object.
- Line 11-14: Create Chart’s objects.
- Line 15: Add Chart’s object to the form.
- Line 17-29: Set Chart’s properties (ChartArea, Legend and Series).
- Line 31-32: Bind column “ProductName” to X-axis and column “UnitsInStock” to Y-axis on the “Series1” Chart.
- Line 34: Set Chart’s data source to the DataTable in the DataSet’s object.
- Run the project. You see a chart displaying “Product Name” on X-axis and “Unit in Stock” on Y-axis which data is gathered from Northwind database on SQL Server.
Post a Comment