(Tạo biểu đồ kiểu Pie lấy dữ liệu từ SQL Server) – Đối với những người quản trị, việc thống kê báo cáo là công việc thường xuyên phải thực hiện. Và để tăng tính trực quan trong việc so sánh đánh giá giữa các tiêu chí với nhau, họ thường sử dụng biểu đồ. Bài viết dưới đây sẽ hướng dẫn các bạn cách sử dụng ZedGraph để tạo biểu đồ, dữ liệu để tạo biểu đồ sẽ được lấy từ CSDL Northwind. Biểu đồ này sẽ cho người sử dụng biết được số lượng và tỷ lệ % của từng loại sản phẩm.
- B1: Download CSDL Northwind
- B2: Bổ xung thêm 2 store:
+ Pro_Categories_List: Liệt kê toàn bộ danh sách Category
+ Pro_ProductByCategory: Đếm số sản phẩm theo từng Category
CREATE PROCEDURE [dbo].[Pro_Categories_List]
@Keyword nvarchar(250)
AS
declare@strSQL nvarchar(1000)
declare @strWhere nvarchar(500)
declare @strOrder nvarchar (50)
set @strSQL= 'SELECT CategoryID,CategoryName from Categories'
set @strWhere =' Where 1=1 '
if @Keyword<>''
set @strWhere= @strWhere +' And (CategoryName like N''%' +@Keyword+'%'' )'
set @strOrder =' Order by CategoryName'
set @strSQL=@strSQL+@strWhere+@strOrder
print @strSQL
exec sp_executesql @strSQL
Go
CREATE PROCEDURE [dbo].[Pro_ProductByCategory]
AS
declare@strSQL nvarchar(1000)
declare @strWhere nvarchar(500)
declare @strOrder nvarchar (50)
set @strSQL= 'SELECT Products.CategoryID, Categories.CategoryName,COUNT(Products.ProductID) AS TotalNumber
FROM Products INNER JOIN
Categories ON Products.CategoryID = Categories.CategoryID
GROUP BY Products.CategoryID, Categories.CategoryName
ORDER BY dbo.Categories.CategoryName'
set @strSQL=@strSQL
print @strSQL
exec sp_executesql@strSQL- B3: Tạo Project trong Microsoft Visual Studio 2010
Trong Visual Studio tạo 1 Class có tên: Utility và nhập đoạn Code phía dưới cho Class này.
Imports System.Data.SqlClient
Imports System.Data
Namespace ZedGraphPieCharts
Public Class SqlDataProvider
#Region "Membres Prives"
Shared _IsError As Boolean = False
Private _connectionString AsString
#End Region
#Region "Constructeurs"
Public Sub New()
Try
_connectionString = ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString
_IsError = False
Catch ex As Exception
_IsError = True
End Try
End Sub
#End Region
#Region "Proprietes"
Public ReadOnly Property ConnectionString() AsString
Get
Return _connectionString
End Get
End Property
#End Region
#Region "Functions"
Public FunctionFillTable(ByVal sql AsString) As DataTable
Try
Dim tb AsNew DataTable
Dim adap AsNew SqlDataAdapter(sql, _connectionString)
adap.Fill(tb)
Return tb
Catch ex As Exception
Return Nothing
End Try
End Function
Public FunctionFillTable(ByVal ProcName As String, ByVal ParamArrayPara() As ObjectPara) As DataTable
Try
Dim tb AsNew DataTable
Dim adap AsNew SqlDataAdapter(ProcName, _connectionString)
adap.SelectCommand.CommandType = CommandType.StoredProcedure
If NotPara Is NothingThen
For Eachp As ObjectParaIn Para
adap.SelectCommand.Parameters.Add(New SqlParameter(p.Name, p.Value))
Next
End If
adap.Fill(tb)
Return tb
Catch ex As Exception
Return Nothing
End Try
End Function
#End Region
End Class
Public Class ObjectPara
Dim _name As String
Dim _Value As Object
Sub New(ByVal Pname As String, ByVal PValue As Object)
_name = Pname
_Value = PValue
End Sub
Public PropertyName() As String
Get
Return _name
End Get
Set(ByVal value As String)
_name = value
End Set
End Property
Public PropertyValue() As Object
Get
Return _Value
End Get
Set(ByVal value As Object)
_Value = value
End Set
End Property
End Class
End Namespace
Chú ý: Thuộc tính SiteSqlServer chính là chuỗi Connect với SQL Server trong file Web.Config
- B4: Download thư viện ZedGraph tại địa chỉ:
http://sourceforge.net/projects/zedgraph/files/zedgraph%20dll%20only/
- B5: Giải nén ZedGraph, và References ZedGraph.Web vào Project vừa tạo.
http://sourceforge.net/projects/zedgraph/files/zedgraph%20dll%20only/
- B5: Giải nén ZedGraph, và References ZedGraph.Web vào Project vừa tạo.
- B6: Mở file Default.aspxdưới dạng HTML và nhập mã HTML
<%@ PageTitle="Pie Charts in ASP.Net" Language="vb" MasterPageFile="~/Site.Master" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="ZedGraphPieCharts._Default" %>
<%@ Registerassembly="ZedGraph.Web"namespace="ZedGraph.Web"tagprefix="cc1"%>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<h1>
ZedGraph Pie Charts in ASP.Net
</h1>
<br />
<cc1:ZedGraphWeb ID="ZedGraphWeb1"width="600"Height="475"runat="server">
</cc1:ZedGraphWeb>
</asp:Content>- B7: Viết Code cho file Default.aspx
C# Code
//Visit http://www.laptrinhdotnet.com for more ASP.NET Tutorials
using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using ZedGraph;
using ZedGraph.Web;
using System.Drawing;
namespace ZedGraphPieCharts
{
public partial class _Default : System.Web.UI.Page
{
#region"Private Members"
private string[] Lables;
private int[] X;
#endregion
#region"Bind Data"
private DataTableBindCategories()
{
SqlDataProvider objSQL = newSqlDataProvider();
DataTable objBind = objSQL.FillTable("Pro_Categories_List", new ObjectPara("@Keyword", ""));
returnobjBind;
}
private DataTableBindProduct()
{
SqlDataProvider objSQL = newSqlDataProvider();
DataTable objBind = objSQL.FillTable("Pro_ProductByCategory");
return objBind;
}
#endregion
#region"Color"
public stringGetColor(int Index)
{
string[] colors = {
"Maroon","MidnightBlue","SaddleBrown","Teal","AliceBlue","AntiqueWhite","Aqua","Aquamarine","Azure","Beige","Bisque","Black","BlanchedAlmond","Blue","BlueViolet","Brown",
"Green","CadetBlue","Chartreuse","BurlyWood","Gray","GreenYellow","BurlyWood","HoneyDew","HotPink","IndianRed","Indigo","Ivory","Khaki","Lavender","LavenderBlush",
"LawnGreen","LemonChiffon","SlateBlue","SpringGreen","LightBlue","SteelBlue","Turquoise","Violet","YellowGreen","Silver","SeaGreen"
};
if (Index >= colors.Length)
{
Index = 0;
}
return colors[Index];
}
#endregion
#region"Event Handles"
protected voiducZedGraph_RenderGraph1(ZedGraph.Web.ZedGraphWebz, System.Drawing.Graphics g, ZedGraph.MasterPane masterPane)
{
DataTable objBind = newDataTable();
string ObjectName = "";
DataRow dRow = null;
int i = 0;
GraphPane myPane = masterPane[0];
myPane.Fill = new Fill(Color.White, Color.Goldenrod, 45f);
myPane.Chart.Fill.Type = FillType.None;
myPane.Legend.Position = LegendPos.BottomCenter;
myPane.Legend.FontSpec.Size = 11f;
myPane.Legend.IsHStack = false;
objBind = BindCategories();
if (objBind != null)
{
if(objBind.Rows.Count > 0)
{
Lables = new string[objBind.Rows.Count];
}
}
if (Lables != null)
{
for (i = 0; i <= objBind.Rows.Count-1; i++)
{
dRow = objBind.Rows[i];
ObjectName = dRow["CategoryName"].ToString();
if (!string.IsNullOrEmpty(ObjectName))
{
Lables[i] = ObjectName;
}
}
}
if (Lables != null)
{
objBind = BindProduct();
X = new int[Lables.Length];
i = 0;
if (objBind != null)
{
if (objBind.Rows.Count > 0)
{
foreach (DataRow row in objBind.Rows)
{
if (row != null)
{
X[i] =Convert.ToInt32(row["TotalNumber"]);
i = i + 1;
}
}
}
}
}
if (X != null)
{
for (intj = 0; j <= X.Length - 1; j++)
{
PieItem segment1 = myPane.AddPieSlice(X[j], Color.FromName(GetColor(j)), Color.FromName(GetColor(j)), 45f, 0, Lables[j] + " (" + X[j] + (")"));
segment1.LabelType = PieLabelType.Name_Percent;
}
}
masterPane.AxisChange(g);
}
#endregion
}
}VB.NET Code
Imports ZedGraph
Imports ZedGraph.Web
Imports System.Drawing
Namespace ZedGraphPieCharts
Public Class _Default
Inherits System.Web.UI.Page
#Region "Private Members"
Private Lables() As String
Private X() As Integer
#End Region
#Region "Bind Data"
Private FunctionBindCategories() As DataTable
Dim objSQL As New SqlDataProvider
Dim objBind As DataTable = objSQL.FillTable("Pro_Categories_List", New ObjectPara("@Keyword", ""))
Return objBind
End Function
Private FunctionBindProduct() As DataTable
Dim objSQL As New SqlDataProvider
Dim objBind As DataTable = objSQL.FillTable("Pro_ProductByCategory")
Return objBind
End Function
#End Region
#Region "Color"
Public FunctionGetColor(ByVal Index AsInteger) As String
Dim colors() As String = {"Maroon", "MidnightBlue", "SaddleBrown", "Teal", "AliceBlue", "AntiqueWhite", "Aqua", "Aquamarine", "Azure", "Beige", "Bisque", "Black", "BlanchedAlmond", "Blue", "BlueViolet", "Brown", "Green", "CadetBlue", "Chartreuse", "BurlyWood", "Gray", "GreenYellow", "BurlyWood", "HoneyDew", "HotPink", "IndianRed", "Indigo", "Ivory", "Khaki", "Lavender", "LavenderBlush", "LawnGreen", "LemonChiffon", "SlateBlue", "SpringGreen", "LightBlue", "SteelBlue", "Turquoise", "Violet", "YellowGreen", "Silver", "SeaGreen"}
If Index >= colors.Length Then
Index = 0
End If
Return colors(Index)
End Function
#End Region
#Region "Event Handles"
Protected SubPage_Load(ByVal sender AsObject, ByVal e As System.EventArgs) Handles Me.Load
Try
If Page.IsPostBack = False Then
End If
Catch ex As Exception
End Try
End Sub
Private SubZedGraphWeb1_RenderGraph(ByVal z As ZedGraph.Web.ZedGraphWeb, ByVal g AsSystem.Drawing.Graphics, ByVal masterPane AsZedGraph.MasterPane) Handles ZedGraphWeb1.RenderGraph
Dim myPane As GraphPane = masterPane(0)
myPane.Fill = New Fill(Color.White, Color.Goldenrod, 45.0F)
myPane.Chart.Fill.Type = FillType.None
myPane.Legend.Position = LegendPos.BottomCenter
myPane.Legend.FontSpec.Size = 11.0F
myPane.Legend.IsHStack = False
Dim objBind As New DataTable
Dim ObjectName As String = ""
Dim dRow As DataRow
Dim i As Integer = 0
objBind = BindCategories()
If Not objBind Is Nothing Then
If objBind.Rows.Count > 0 Then
ReDim Lables(objBind.Rows.Count - 1)
End If
End If
If Not Lables Is Nothing Then
For i = 0 ToUBound(Lables)
dRow = objBind.Rows(i)
ObjectName = dRow("CategoryName")
If ObjectName <> "" Then
Lables(i) = ObjectName
End If
Next
End If
If Not Lables Is Nothing Then
objBind = BindProduct()
ReDim X(Lables.Length - 1)
i = 0
If NotobjBind Is NothingThen
If objBind.Rows.Count > 0 Then
For Each row As DataRow In objBind.Rows
If Not row Is Nothing Then
X(i) = row("TotalNumber")
i = i + 1
End If
Next
End If
End If
End If
If Not X Is Nothing Then
For j AsInteger = 0 ToX.Length - 1
Dim segment1 AsPieItem = myPane.AddPieSlice(X(j), Color.FromName(GetColor(j)), Color.FromName(GetColor(j)), 45.0F, 0, Lables(j) & " (" & X(j) & (")"))
segment1.LabelType = PieLabelType.Name_Percent
Next
End If
masterPane.AxisChange(g)
End Sub
#End Region
End Class
End Namespace
Bây giờ chạy Project bạn sẽ có kết quả sẽ giống như hình dưới.
Chúc các bạn thành công!
Quang Bình
0 comments Blogger 0 Facebook
Post a Comment