» » » Sử dụng ZedGraph để tạo biểu đồ kiểu Horizontal Bars trong Asp.net

(Tạo biểu đồ kiểu Horizontal Bars lấy dữ liệu từ SQL Server) – Bài viết trước thủ thuật lập trình đã hướng dẫn các bạn cách tạo biểu đồ kiểu Pie lấy dữ liệu từ SQL. Hôm nay thủ thuật tin học sẽ giới thiệu 1 cách tạo biểu đồ khác, đó là biểu đồ kiểu cột nằm ngang (Horizontal Bars). Với biểu đồ hình cột người sử dụng dễ dàng hơn trong việc so sánh và đánh giá giữa các tiêu chí với nhau.

Nghe những bài hát đỉnh nhất về Thấy cô giáo - Nghe trên Youtube



Code Example C#, Code Example VB.NET
Code Example C#, Code Example VB.NET



- 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 ZedGraphHorizontalBarsCharts

    Public Class SqlDataProvider

#Region "Membres Prives"

        Shared _IsError As Boolean = False
        Private _connectionString As String

#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() As String
            Get
                Return _connectionString
            End Get
        End Property

#End Region

#Region "Functions"

        Public Function FillTable(ByVal sql As String) As DataTable
            Try
                Dim tb As New DataTable
                Dim adap As New SqlDataAdapter(sql, _connectionString)
                adap.Fill(tb)
                Return tb
            Catch ex As Exception
                Return Nothing
            End Try
        End Function

        Public Function FillTable(ByVal ProcName As String, ByVal ParamArray Para() As ObjectPara) As DataTable
            Try
                Dim tb As New DataTable
                Dim adap As New SqlDataAdapter(ProcName, _connectionString)
                adap.SelectCommand.CommandType = CommandType.StoredProcedure
                If Not Para Is Nothing Then
                    For Each p As ObjectPara In 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 Property Name() As String
            Get
                Return _name
            End Get
            Set(ByVal value As String)
                _name = value
            End Set
        End Property

        Public Property Value() 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.

- B6: Mở file Default.aspxdưới dạng HTML và nhập mã HTML

<%@ PageTitle="ZedGraph Horizontal Bars Charts in ASP.Net" Language="vb" MasterPageFile="~/Site.Master" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="ZedGraphHorizontalBarsCharts._Default" %>
<%@ Registerassembly="ZedGraph.Web"namespace="ZedGraph.Web"tagprefix="cc1"%>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <h1>
        ZedGraph Horizontal Bars 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 ZedGraphHorizontalBarsCharts
{
    public partial class _Default : System.Web.UI.Page
    {

        #region"Private Members"

        private string[] Labels;
        private double[] XAxis;

        #endregion

        #region"Bind Data"

        private DataTableBindCategories()
        {
            SqlDataProvider objSQL = newSqlDataProvider();
            DataTable objBind = objSQL.FillTable("Pro_Categories_List", new ObjectPara("@Keyword", ""));
            return objBind;
        }

        private DataTableBindProduct()
        {
            SqlDataProvider objSQL = newSqlDataProvider();
            DataTable objBind = objSQL.FillTable("Pro_ProductByCategory");
            return objBind;
        }

        #endregion

        #region"Event Handles"

        protected voiducZedGraph_RenderGraph1(ZedGraph.Web.ZedGraphWebz, System.Drawing.Graphics g, ZedGraph.MasterPane masterPane)
        {
            GraphPane myPane = masterPane[0];
            BarItem myCurve = default(BarItem);

            DataTable objBind = newDataTable();
            string ObjectName = "";
            DataRow dRow = null;
            int i = 0;

            objBind = BindCategories();

            if (objBind != null)
            {
                if (objBind.Rows.Count > 0)
                {
                    Labels = new string[objBind.Rows.Count];
                    XAxis = new double[objBind.Rows.Count];
                    for(i = 0; i <= objBind.Rows.Count-1; i++)
                    {
                        dRow = objBind.Rows[i];
                        ObjectName = dRow["CategoryName"].ToString();
                        if (!string.IsNullOrEmpty(ObjectName))
                        {
                            Labels[i] = ObjectName;
                        }
                    }
                }
            }

            if (Labels != null)
            {
                objBind = BindProduct();
                i = 0;
                if (objBind.Rows.Count > 0)
                {
                    foreach (DataRowrow in objBind.Rows)
                    {
                        if (row != null)
                        {
                            XAxis[i] =Convert.ToDouble(row["TotalNumber"]);
                            i = i + 1;
                        }
                    }
                }
            }

            //Set the title Chart
            myPane.Title.Text = "Horizontal Bars Charts";
            myPane.Title.FontSpec.FontColor = Color.Blue;
            myPane.Title.FontSpec.Size = 14f;

            //Set the title and axis labels  
            //axis X
            myPane.XAxis.Title.Text = "Number";
            myPane.XAxis.Title.FontSpec.Size = 12f;
            //axis Y
            myPane.YAxis.Title.Text = "Category";
            myPane.YAxis.Title.FontSpec.Size = 12f;

            //axis X
            myCurve = myPane.AddBar("", XAxis, null, Color.Blue);

            // Fill the bar with a Blue-white-Blue color gradient for a 3d look
            myCurve.Bar.Fill = new Fill(Color.Green, Color.White, Color.Green, 90f);

            //axis X
            myPane.YAxis.Scale.TextLabels = Labels;
            myPane.YAxis.Scale.AlignH = AlignH.Right;
            // Set the XAxis to Text type
            myPane.YAxis.Type = AxisType.Text;

            // Make the bars Vertical by setting the BarBase to "X"
            myPane.BarSettings.Type = BarType.Cluster;
            myPane.BarSettings.Base = BarBase.Y;

            //Set Font Value for Axis
            myPane.YAxis.Scale.FontSpec.Size = 8f;
            myPane.XAxis.Scale.FontSpec.Size = 8f;

            // Fill the axis background with a color gradient
            myPane.Chart.Fill = new Fill(Color.White, Color.FromArgb(255, 255, 166), 45f);

            masterPane.AxisChange(g);
        }

        #endregion
    }

}

VB.NET Code
Imports ZedGraph
Imports ZedGraph.Web
Imports System.Drawing

Namespace ZedGraphHorizontalBarsCharts

    Public Class _Default
        Inherits System.Web.UI.Page

#Region "Private Members"

        Private Labels As String()
        Private XAxis As Double()

#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 "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 SubucZedGraph_RenderGraph1(ByVal z As ZedGraph.Web.ZedGraphWeb, ByVal g AsSystem.Drawing.Graphics, ByVal masterPane AsZedGraph.MasterPane) Handles ZedGraphWeb1.RenderGraph
            Dim myPane As GraphPane = masterPane(0)
            Dim myCurve As BarItem

            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 Labels(objBind.Rows.Count - 1)
                    ReDim XAxis(objBind.Rows.Count - 1)
                    For i = 0 ToUBound(Labels)
                        dRow = objBind.Rows(i)
                        ObjectName = dRow("CategoryName")
                        If ObjectName <> ""Then
                            Labels(i) = ObjectName
                        End If
                    Next
                End If
            End If

            If Not Labels Is Nothing Then
                objBind = BindProduct()
                i = 0
                If objBind.Rows.Count > 0 Then
                    For Eachrow As DataRowIn objBind.Rows
                        IfNot row Is Nothing Then
                            XAxis(i) = row("TotalNumber")
                            i = i + 1
                        End If
                    Next
                End If
            End If

            'Set the title Chart
            myPane.Title.Text = "Horizontal Bars Charts"
            myPane.Title.FontSpec.FontColor = Color.Blue
            myPane.Title.FontSpec.Size = 14.0F

            'Set the title and axis labels  
            'axis X
            myPane.XAxis.Title.Text = "Number"
            myPane.XAxis.Title.FontSpec.Size = 12.0F
            'axis Y
            myPane.YAxis.Title.Text = "Category"
            myPane.YAxis.Title.FontSpec.Size = 12.0F

            'axis X
            myCurve = myPane.AddBar("", XAxis, Nothing, Color.Blue)

            ' Fill the bar with a Blue-white-Blue color gradient for a 3d look
            myCurve.Bar.Fill = New Fill(Color.Green, Color.White, Color.Green, 90.0F)

            'axis X
            myPane.YAxis.Scale.TextLabels = Labels
            myPane.YAxis.Scale.AlignH = AlignH.Right
            ' Set the XAxis to Text type
            myPane.YAxis.Type = AxisType.Text

            ' Make the bars Vertical by setting the BarBase to "X"
            myPane.BarSettings.Type = BarType.Cluster
            myPane.BarSettings.Base = BarBase.Y

            'Set Font Value for Axis
            myPane.YAxis.Scale.FontSpec.Size = 8.0F
            myPane.XAxis.Scale.FontSpec.Size = 8.0F

            ' Fill the axis background with a color gradient
            myPane.Chart.Fill = New Fill(Color.White, Color.FromArgb(255, 255, 166), 45.0F)

            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.

Code Example C#, Code Example VB.NET
Code Example C#, Code Example VB.NET



Chúc các bạn thành công!

Quang Bình
2:26:00 AM
Gia Khang
,

0 comments Blogger 0 Facebook

Post a Comment

Subscribe to: Post Comments ( Atom )
 
lập trình đốt nét © 2013. All Rights Reserved. Powered by Blogger
Top