SHANU MVC Dashboard with Chart using AngularJS and WEB API
3.88/5 (22投票s)
如何创建带有图表和数据显示的动态 MVC Dashboard。
引言
在本文中，我们将详细介绍如何使用 AngularJS 和 WEB API 创建一个包含图表和数据显示的动态 MVC Dashboard。通过这个 Web 应用程序，您可以编写自己的 SQL 查询来绑定动态 Dashboard 的图表和数据。此程序可让您轻松地显示您输入的 `Condition`、`Order BY` 和 `Group By` 选项所选数据库的任何表的/列的详细信息，并在主页上同时显示数据和图表。
在我们 上一篇文章 中，我们详细介绍了如何在我们的 MVC Web 应用程序的主页 Dashboard 上显示任何数据。在本文中，我们将详细介绍如何使用 AngularJs 和 Web API 在 MVC Web 应用程序的 Dashboard 上显示数据和图表。
在此演示应用程序中，我们在 MVC Dashboard 页面上绘制了饼图。您可以根据自己的需求绘制任何图表。在我们 上一篇文章 中，我们解释了如何在 MVC 应用程序中绘制折线图、饼图、条形图、甜甜圈图、气泡图和折线图/条形图。我们使用相同的逻辑在 MVC Dashboard 页面上绘制图表。
Shanu MVC Dashboard 中的功能
- 动态 SQL 查询
- 列名
- 表名
- WHERE 条件
- Group By
- Order By
- 图表 SQL 查询
- 图表设置和绘制图表
在这里，我们将详细介绍每个部分。
请参考我们 上一篇文章 中的 1 到 6 部分。我们详细介绍了每个部分以及动画图像。
本文具有所有相同的功能，并增加了要在我们的 MVC Dashboard 上显示的图表功能。
7. 图表 SQL 查询
要显示图表，我们首先需要编写 `select` 查询来显示图表项和值。
这是在我们的 MVC dashboard 页面上显示图表的示例查询。在这里，对于图表绑定，用户可以输入完整的 `select` 查询来在 `combobox` 中绑定结果。
用于我们应用程序的示例 `Select` 查询
Select ItemName as Name, SUM(Price) as Value FROM ItemDetail _
GROUP BY ItemName ORDER BY Value, Name
为了绘制图表，我们始终将标准固定为显示 2 列，一列是 `Name`，另一列是 `Value`。在这里，Name 是要为图表显示的任何名称（`Legend`），Value 是绘制图表的实际值。在搜索按钮单击时，我们首先将图表项结果绑定到 `combobox`。我们将使用此 `combobox` 结果来绘制图表。
8. 图表设置和绘制图表
用户可以根据运行时需求添加图表标题、水印文本，然后单击“Click to Draw Chart”按钮在 Dashboard 上绘制图表。
注意：您可以显示给定数据库中任何表的任何图表数据。您所要做的就是使用 `Name` 和 `Value` 列编写图表的 `select` 查询。
必备组件
Visual Studio 2015：您可以 从此处 下载。
Using the Code
步骤 1：创建用于测试此应用程序的示例数据库和表
以下是创建数据库和表的 SQL 脚本，其中包含 `insert` 查询。请在您的 SQL Server 中运行以下代码来创建 DB 和表。
---- =============================================
---- Author : Shanu
---- Create date : 2016-05-12
---- Description : To Create Database,Table and Sample Insert Query
---- Latest
---- Modifier : Shanu
---- Modify date : 2016-05-12
---- =============================================
----Script to create DB,Table and sample Insert data
USE MASTER
GO
--1) Check for the Database Exists .If the database is exist then drop and create new DB
IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'DashboardDB' )
DROP DATABASE DashboardDB
GO
CREATE DATABASE DashboardDB
GO
USE DashboardDB
GO
-- 1) //////////// ItemDetails table
-- Create Table ItemDetails,This table will be used
-- to store the details like Item Information
IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'ItemDetail' )
DROP TABLE ItemDetail
GO
CREATE TABLE [dbo].[ItemDetail](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ItemNo] [varchar](100) NOT NULL ,
[ItemName] [varchar](100) NOT NULL,
[Comments] [varchar](100) NOT NULL,
[Price] INT NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Insert into ItemDetail(ItemNo,ItemName,Comments,Price) values
('101','NoteBook', 'HP Notebook 15 Inch', 24500)
Insert into ItemDetail(ItemNo,ItemName,Comments,Price) values
('102','MONITOR', 'SAMSNG', '8500')
Insert into ItemDetail(ItemNo,ItemName,Comments,Price) values
('103','MOBILE', 'SAMSUNG NOTE 5', 42500)
Insert into ItemDetail(ItemNo,ItemName,Comments,Price) values
('104','MOBILE', 'SAMSUNG S7 Edge', 56000)
Insert into ItemDetail(ItemNo,ItemName,Comments,Price) values
('105','MOUSE', 'ABKO', 780)
Insert into ItemDetail(ItemNo,ItemName,Comments,Price) values
('106','HDD' ,'LG', 3780)
select * from ItemDetail
select ItemName,SUM(convert(int,Price)) as totalCost
from ItemDetail
GROUP BY ItemName
-- 2) User table
IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'UserDetails' )
DROP TABLE UserDetails
GO
CREATE TABLE [dbo].UserDetails(
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](100) NOT NULL,
[UserType] [varchar](100) NOT NULL,
[Phone] [varchar](20) NOT NULL,
PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Insert into UserDetails(UserName,UserType,Phone) values
('SHANU','Admin','01039124503')
Insert into UserDetails(UserName,UserType,Phone) values
('Afraz','user','01039120984')
Insert into UserDetails(UserName,UserType,Phone) values
('Afreen','user','01039120005')
Insert into UserDetails(UserName,UserType,Phone) values
('Raj','Admin','01039120006')
Insert into UserDetails(UserName,UserType,Phone) values
('Mak','Manager','01039124567')
Insert into UserDetails(UserName,UserType,Phone) values
('Jack','Manager','01039120238')
Insert into UserDetails(UserName,UserType,Phone) values
('Pak','User','01039125409')
Insert into UserDetails(UserName,UserType,Phone) values
('Ninu','Accountant','01039126810')
Insert into UserDetails(UserName,UserType,Phone) values
('Nanu','Accountant','01039152011')
-- select * from Userdetails
-- 3 UserAddress
IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'UserAddress' )
DROP TABLE UserAddress
GO
CREATE TABLE [dbo].UserAddress(
[UserAddID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] ,
[Address] [varchar](200) NOT NULL,
[Email] [varchar](100) NOT NULL,
PRIMARY KEY CLUSTERED
(
[UserAddID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Insert into UserAddress(UserID,Address,Email) values
(1,'Madurai,Tamil Nadu, India','syedshanumcain@gmail.com')
Insert into UserAddress(UserID,Address,Email) values
(2,'Madurai,Tamil Nadu, India','afraz@afrazmail.com')
Insert into UserAddress(UserID,Address,Email) values
(3,'Seoul,South Korea','afreen@afrazmail.com')
select * from UserAddress
select A.UserName,A.UserType,A.Phone,B.Address,B.Email
From
Userdetails A Left Outer JOIN UserAddress B
on
A.UserID=B.UserID
创建存储过程以运行动态查询
这是我们用于运行所有动态 SQL Select 查询并将结果返回以绑定到 MVC 页面的主存储过程。
USE [DashboardDB]
GO
/****** Object: StoredProcedure [dbo].[USP_Dashboard_Select] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 1) select top 10 random kidsLearnerMaster records
-- Author : Shanu
-- Create date : 2016-05-14
-- Description :To run dymanic Query
-- Tables used : Dynamic Table
-- Modifier : Shanu
-- Modify date : 2016-05-14
-- =============================================
-- To Select all user roles
-- EXEC USP_Dashboard_Select @columnName = 'UserName,UserType,Phone' ,
-- @TableNames = 'UserDetails' ,@isCondition=0,@ConditionList='UserType=''ADMIN'' ',
-- @isGroupBY =1,@GroupBYList = 'UserName,UserType,Phone',
-- @isOrderBY =1,@OrderBYList = ' UserType '
-- EXEC USP_Dashboard_Select @columnName = 'ItemName,SUM(Price) as totalCost' ,
-- @TableNames = 'ItemDetail' ,@isCondition=0,@ConditionList='Price>''400'' ',
-- @isGroupBY =1,@GroupBYList = 'ItemName'
-- EXEC USP_Dashboard_Select @sqlQuery = 'Select * from ItemDetail'
-- EXEC USP_Dashboard_Select @sqlQuery = 'select ID,ItemNo ,
-- ItemName ,Comments ,Price from ItemDetail'
-- =============================================
ALTER PROCEDURE [dbo].[USP_Dashboard_Select]
(
@sqlQuery varchar(MAX)='',
@columnName varchar(MAX)='',
@TableNames varchar(MAX)='',
@isCondition INT=0,
@ConditionList varchar(MAX)='',
@isGroupBY INT=0,
@GroupBYList varchar(MAX)='',
@isOrderBY INT=0,
@OrderBYList varchar(MAX)=''
)
AS
BEGIN
BEGIN TRY
IF @sqlQuery =''
BEGIN
SET @sqlQuery = 'SELECT ' + @columnName + ' FROM ' + @TableNames
IF @isCondition=1
BEGIN
SET @sqlQuery = @sqlQuery+ ' WHERE ' + @ConditionList
END
IF @isGroupBY=1
BEGIN
SET @sqlQuery = @sqlQuery+ ' GROUP BY ' + @GroupBYList
END
IF @isOrderBY=1
BEGIN
SET @sqlQuery = @sqlQuery+ ' Order BY ' + @OrderBYList
END
EXEC (@sqlQuery)
END
ELSE
BEGIN
EXEC (@sqlQuery)
END
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
步骤 2：在 Visual Studio 2015 中创建您的 MVC Web 应用程序
安装 Visual Studio 2015 后，单击 **Start**，然后单击 **Programs**，选择 **Visual Studio 2015** - 单击 **Visual Studio 2015**。单击 **New**，然后单击 **Project**，选择 **Web**，然后选择 **ASP.NET Web Application**。输入您的项目名称，然后单击 **OK**。
选择 **MVC**、**WEB API**，然后单击 **OK**。
现在，我们已经创建了 MVC 应用程序。下一步，我们在 `Web.Config` 文件中添加连接字符串。这里，我们不使用 Entity Framework。这里，我们将直接通过正常的 ADO.NET 方法从我们的 MVC Web API 控制器方法获取数据。
<add name="dashboard" connectionString="Data Source=SQLSERVERNAME;
Initial Catalog=DashboardDB;Persist Security Info=True;User ID=UID;Password=PWD"
providerName="System.Data.SqlClient" />
请使用您的 SQL Server 连接进行更新。
步骤 3：添加 Web API 控制器
右键单击 `Controllers` 文件夹，然后单击 **Add**，再单击 **Controller**。
在这里，我们将添加一个用于 AngularJS 的 WEB API 控制器。
选择 **Web API 2 Controller – Empty**，然后单击 **Add**。接下来，将控制器名称输入为 `DashboardAPIController`。
Get 方法
在这里，我们使用 Http `Get` 方法通过正常的 ADO.NET 方法从数据库获取所有动态数据。
[HttpGet]
public string getDashboardDetails(string sqlQuery, string columnName,
string tableNames, Nullable<int> isCondition, string conditionList,
Nullable<int> isGroupBY, string groupBYList,
Nullable<int> isOrderBY, string orderBYList)
{
if (sqlQuery == null)
sqlQuery = "";
if (columnName == null)
columnName = "";
if (tableNames == null)
tableNames = "";
if (isCondition == null)
isCondition = 0;
if (conditionList == null)
conditionList = "";
if (isGroupBY == null)
isGroupBY = 0;
if (groupBYList == null)
groupBYList = "";
if (isOrderBY == null)
isOrderBY = 0;
if (orderBYList == null)
orderBYList = "";
string connectionString =
ConfigurationManager.ConnectionStrings["dashboard"].ToString();
DataSet ds = new DataSet();
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Create the SQL command and add Sp name
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "USP_Dashboard_Select";
command.CommandType = CommandType.StoredProcedure;
// Add parameter for Query.
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@sqlQuery";
parameter.SqlDbType = SqlDbType.NVarChar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = sqlQuery;
command.Parameters.Add(parameter);
// Add parameter for Column Names
SqlParameter parameter1 = new SqlParameter();
parameter1.ParameterName = "@columnName";
parameter1.SqlDbType = SqlDbType.NVarChar;
parameter1.Direction = ParameterDirection.Input;
parameter1.Value = columnName;
command.Parameters.Add(parameter1);
// Add parameter for Table names
SqlParameter parameter2 = new SqlParameter();
parameter2.ParameterName = "@tableNames";
parameter2.SqlDbType = SqlDbType.NVarChar;
parameter2.Direction = ParameterDirection.Input;
parameter2.Value = tableNames;
command.Parameters.Add(parameter2);
// Add parameter to check for Where condition
SqlParameter parameter3 = new SqlParameter();
parameter3.ParameterName = "@isCondition";
parameter3.SqlDbType = SqlDbType.NVarChar;
parameter3.Direction = ParameterDirection.Input;
parameter3.Value = isCondition;
command.Parameters.Add(parameter3);
// Add parameter for Where conditions
SqlParameter parameter4 = new SqlParameter();
parameter4.ParameterName = "@ConditionList";
parameter4.SqlDbType = SqlDbType.NVarChar;
parameter4.Direction = ParameterDirection.Input;
parameter4.Value = conditionList;
command.Parameters.Add(parameter4);
// Add parameter to check for Group By
SqlParameter parameter5 = new SqlParameter();
parameter5.ParameterName = "@isGroupBY";
parameter5.SqlDbType = SqlDbType.NVarChar;
parameter5.Direction = ParameterDirection.Input;
parameter5.Value = isGroupBY;
command.Parameters.Add(parameter5);
// Add parameter for Group By
SqlParameter parameter6 = new SqlParameter();
parameter6.ParameterName = "@groupBYList";
parameter6.SqlDbType = SqlDbType.NVarChar;
parameter6.Direction = ParameterDirection.Input;
parameter6.Value = groupBYList;
command.Parameters.Add(parameter6);
// Add parameter to check for Order By
SqlParameter parameter7 = new SqlParameter();
parameter7.ParameterName = "@isOrderBY";
parameter7.SqlDbType = SqlDbType.NVarChar;
parameter7.Direction = ParameterDirection.Input;
parameter7.Value = isOrderBY;
command.Parameters.Add(parameter7);
// Add parameter for OrderBY
SqlParameter parameter8 = new SqlParameter();
parameter8.ParameterName = "@orderBYList";
parameter8.SqlDbType = SqlDbType.NVarChar;
parameter8.Direction = ParameterDirection.Input;
parameter8.Value = orderBYList;
command.Parameters.Add(parameter8);
connection.Open();
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
da.Fill(ds);
connection.Close();
}
}
return DataTableToJSONWithJavaScriptSerializer(ds.Tables[0]);
}
步骤 4：创建 AngularJs 控制器
首先，在 `Script` 文件夹内创建一个文件夹，并将文件夹名称命名为“MyAngular”。
现在将您的 Angular Controller 添加到该文件夹中。
右键单击 `MyAngular` 文件夹，单击 **Add**，然后单击 **New Item** > 选择 **Web** > 选择 **AngularJs Controller**，然后为控制器命名。我们将 AngularJs 控制器命名为“Controller.js”。
如果 Angular JS 包丢失，请将该包添加到您的项目中。
右键单击您的 MVC 项目，然后单击 -> **Manage NuGet Packages**。搜索 AngularJs，然后单击 **Install**。
Modules.js：在这里，我们将添加 AngularJS JavaScript 的引用，并创建一个名为“AngularJs_Module”的 Angular 模块。
// <reference path="../angular.js" />
/// <reference path="../angular.min.js" />
/// <reference path="../angular-animate.js" />
/// <reference path="../angular-animate.min.js" />
var app;
(function () {
app = angular.module("dashbordModule", ['ngAnimate']);
})();
Controllers：在 AngularJS Controller 中，我们完成了所有业务逻辑，并将 Web API 的数据返回到我们的 MVC HTML 页面。
1. 变量声明
首先，我们声明了所有需要使用的本地变量。
app.controller("AngularJs_Controller",
function ($scope, $filter, $timeout, $rootScope, $window, $http) {
$scope.date = new Date();
$scope.MyName = "shanu";
$scope.isQuerys = false;
$scope.Querys = "";
$scope.ColumnNames = "UserName,UserType,Phone";
$scope.TableNames = "UserDetails";
$scope.isCondition = false;
$scope.whereCondition = 0;
$scope.Conditions = "";
$scope.isGroupBy = false;
$scope.GroupBy = 0;
$scope.GroupBys = "";
$scope.isOrderBy = false;
$scope.OrderBy = 0;
$scope.OrderBys = "";
// Array value to check for SQL Injection
$scope.sqlInjectionArray = ['create', 'drop', 'delete',
'insert', 'update', 'truncate',
'grant', 'print', 'sp_executesql',
'objects', 'declare',
'table', 'into', 'sqlcancel',
'sqlsetprop', 'sqlexec',
'sqlcommit', 'revoke',
'rollback', 'sqlrollback', 'values',
'sqldisconnect', 'sqlconnect',
'system_user', 'schema_name',
'schemata', 'information_schema',
'dbo', 'guest', 'db_owner',
'db_', 'table', '@@', 'Users',
'execute', 'sysname', 'sp_who',
'sysobjects', 'sp_', 'sysprocesses',
'master', 'sys', 'db_',
'is_', 'exec', 'end', 'xp_', '; --',
'alter', 'begin', 'cursor',
'kill', '--', 'tabname', 'sys'];
// Declaration for Chart
$scope.chartQuerys = "Select ItemName as Name,
SUM(Price) as Value FROM ItemDetail GROUP BY
ItemName ORDER BY Value,Name";
$scope.sItemName = "";
$scope.itemCount = 5;
$scope.selectedItem = "MOUSE";
$scope.chartTitle = "SHANU Item Sales Chart";
$scope.waterMark = "SHANU";
$scope.ItemValues = 0;
$scope.ItemNames = "";
$scope.minsnew = 0;
$scope.maxnew = 0;
Search 方法
我们在搜索按钮单击时调用此方法。在这里，我们在将所有参数传递给我们的 Web API 方法之前，会检查用户输入数据的所有验证。在此方法中，我们对每项条件检查进行了注释。
在此方法中，我们调用“searchbildChartData”方法将选择结果绑定到 `combobox`。
//search Details
$scope.searchDetails = function () {
// 1. Check for Select Query -> In this function we check for SQL injection
// in user entered select query if any keyword from the array list is found,
// then we give msg to user to enter he valid select query
if ($scope.isQuerys == true) {
if ($scope.Querys != "") {
$scope.whereCondition = 1;
for (var i = 0; i < $scope.sqlInjectionArray.length-1; i++) {
if ($filter('lowercase')($scope.Querys).match($scope.sqlInjectionArray[i])) {
alert("Sorry " + $scope.sqlInjectionArray[i] +
" keyword is not accepted in select query");
return;
}
}
searchTableDetails($scope.Querys, $scope.ColumnNames, $scope.TableNames,
$scope.whereCondition, $scope.Conditions, $scope.GroupBy,
$scope.GroupBys, $scope.OrderBy, $scope.OrderBys);
return;
}
else {
alert("Enter Your Select Query !");
return;
}
}
else
{
$scope.Querys = "";
}
// 2. Check for Column Names -> If user entered the valid column names,
// the details will be checked and binded in page
if ($scope.ColumnNames == "") {
alert("Enter the Column Details !");
return;
}
else
{
for (var i = 0; i < $scope.sqlInjectionArray.length - 1; i++) {
if ($filter('lowercase')($scope.ColumnNames).match
($scope.sqlInjectionArray[i])) {
alert("Sorry " + $scope.sqlInjectionArray[i] +
" keyword is not accepted in Column Names");
return;
}
}
}
// 3. Check for Table Names -> If user entered the valid Table names
// the details will be checkd and binded in page
if ($scope.TableNames == "") {
alert("Enter the Table Details !");
return;
}
else {
for (var i = 0; i < $scope.sqlInjectionArray.length - 1; i++) {
if ($filter('lowercase')($scope.TableNames).match
($scope.sqlInjectionArray[i])) {
alert("Sorry " + $scope.sqlInjectionArray[i] +
" keyword is not accepted in Table Names");
return;
}
}
}
// 4. Check for Where condition -> If user checks the Where condition check box,
// the user entered where condition will be added to the select query
if ($scope.isCondition == true) {
if ($scope.Conditions == "") {
alert("Enter the Where Condition !");
return;
}
else {
for (var i = 0; i < $scope.sqlInjectionArray.length - 1; i++) {
if ($filter('lowercase')($scope.Conditions).match
($scope.sqlInjectionArray[i])) {
alert("Sorry " + $scope.sqlInjectionArray[i] +
" keyword is not accepted in Where Condition");
return;
}
}
$scope.whereCondition = 1;
}
}
else {
$scope.whereCondition = 0;
}
// 5. Check for GroupBy condition -> If user checks the GroupBy condition check box,
// the user entered GroupBy condition will be added to the select query
if ($scope.isGroupBy == true) {
if ($scope.GroupBys == "") {
alert("Enter the Group By Details !");
return;
}
else {
for (var i = 0; i < $scope.sqlInjectionArray.length - 1; i++) {
if ($filter('lowercase')($scope.GroupBys).match
($scope.sqlInjectionArray[i])) {
alert("Sorry " + $scope.sqlInjectionArray[i] +
" keyword is not accepted in GroupBy");
return;
}
}
$scope.GroupBy = 1;
}
}
else {
$scope.GroupBy = 0;
}
// 6. Check for OrderBy condition -> If user checks the OrderBy condition check box,
// the user entered OrderBy condition will be added to the select query
if ($scope.isOrderBy == true) {
if ($scope.OrderBys == "") {
alert("Enter the Group By details !");
return;
}
else {
for (var i = 0; i < $scope.sqlInjectionArray.length - 1; i++) {
if ($filter('lowercase')($scope.OrderBys).match
($scope.sqlInjectionArray[i])) {
alert("Sorry " + $scope.sqlInjectionArray[i] +
" keyword is not accepted in OrderBy");
return;
}
}
$scope.OrderBy = 1;
}
}
else {
$scope.OrderBy = 0;
}
searchTableDetails($scope.Querys, $scope.ColumnNames, $scope.TableNames,
$scope.whereCondition, $scope.Conditions, $scope.GroupBy,
$scope.GroupBys, $scope.OrderBy, $scope.OrderBys);
// 7. Check for Chart Select Query -> In this function, we check for SQL injection
// in user entered select query if any keyword from the array list is found
// then we give msg to user to enter he valid select query
if ($scope.chartQuerys != "") {
$scope.whereCondition = 0;
for (var i = 0; i < $scope.sqlInjectionArray.length - 1; i++) {
if ($filter('lowercase')($scope.chartQuerys).match
($scope.sqlInjectionArray[i])) {
alert("Sorry " + $scope.sqlInjectionArray[i] +
" keyword is not accepted in select query");
return;
}
}
searchbildChartData($scope.chartQuerys,
$scope.ColumnNames, $scope.TableNames,
$scope.whereCondition, $scope.Conditions, $scope.GroupBy,
$scope.GroupBys, $scope.OrderBy, $scope.OrderBys);
return;
}
else {
alert("Enter Your Chart Select Query !");
return;
}
}
主搜索方法
最后，在验证完成后，我们调用我们的主绑定方法，将所有参数传递给我们的 WEB API，以从数据库获取动态数据。
//Main Select and Bind function
//All query details entered by user after validation,
//this method will be called to bind the result to the Dashboard page.
function searchTableDetails(sqlQuery, columnName, tableNames,
isCondition, conditionList, isGroupBY, groupBYList, isOrderBY, orderBYList) {
$http.get('/api/DashboardAPI/getDashboardDetails/', { params:
{ sqlQuery: sqlQuery, columnName: columnName, tableNames: tableNames,
isCondition: isCondition, conditionList: conditionList, isGroupBY: isGroupBY,
groupBYList: groupBYList, isOrderBY: isOrderBY,
orderBYList: orderBYList } }).success(function (data) {
$scope.dashBoadData = angular.fromJson(data);;
//alert($scope.dashBoadData.length);
//if ($scope.dashBoadData.length > 0) {
//}
})
.error(function () {
$scope.error = "An Error has occurred while loading posts!";
});
}
图表数据绑定方法
此方法将从我们的主方法调用，将结果绑定到 `combobox` 以绘制我们的饼图。
// For binding the Chart result to Listbox before bind result to Chart
function searchbildChartData(sqlQuery, columnName, tableNames,
isCondition, conditionList, isGroupBY, groupBYList, isOrderBY, orderBYList) {
$http.get('/api/DashboardAPI/getDashboardDetails/', { params:
{ sqlQuery: sqlQuery, columnName: columnName, tableNames: tableNames,
isCondition: isCondition, conditionList: conditionList, isGroupBY: isGroupBY,
groupBYList: groupBYList, isOrderBY: isOrderBY,
orderBYList: orderBYList } }).success(function (data) {
$scope.itemData = angular.fromJson(data);
$scope.itemCount = $scope.itemData.length;
$scope.selectedItem = $scope.itemData[0].Name;
$scope.minsnew = $scope.itemData[0].Value;
$scope.maxnew = $scope.itemData[$scope.itemData.length-1].Value;
})
.error(function () {
$scope.error = "An Error has occurred while loading posts!";
});
}
步骤 5：为我们的 Dashboard 绘制饼图
我们使用 Jquery 来绘制我们的饼图。在 Draw Chart 按钮的 Click 事件中，我们调用 `drawPieChart` jQuery 方法来绘制我们的图表。在此方法中，我们从 `combobox` 获取图表值和名称，并在我们放置在 MVC Dashboard 主页面上的 `Canvas` 标签上绘制图表。
function drawPieChart() {
var lastend = 0;
var XvalPosition = xSpace;
chartWidth = (canvas.width / 2) - xSpace;
chartHeight = (canvas.height / 2) - (xSpace / 2);
widthcalculation = parseInt(((parseInt(chartWidth) - 100) / noOfPlots));
//Draw Xaxis Line
//-- draw bar X-Axis and Y-Axis Line
var XLineStartPosition = xSpace;
var yLineStartPosition = xSpace;
var yLineHeight = chartHeight;
var xLineWidth = chartWidth;
colorval = 0;
var chartTotalResult = getChartTotal();
$('#DropDownList1 option').each(function () {
if (isNaN(parseInt($(this).val()))) {
}
else
{
ctx.fillStyle = pirChartColor[colorval];
ctx.beginPath();
ctx.moveTo(chartWidth, chartHeight);
//Here we draw the each Pic Chart arc with values and size.
ctx.arc(chartWidth, chartHeight + 6, chartHeight, lastend, lastend +
(Math.PI * 2 * (parseInt($(this).val()) / chartTotalResult)), false);
ctx.lineTo(chartWidth, chartHeight);
ctx.fill();
lastend += Math.PI * 2 * (parseInt($(this).val()) / chartTotalResult);
//END Draw Bar Graph **************==================********************
}
colorval = colorval + 1;
});
}
关注点
首先，运行 SQL 脚本来创建数据库和表，然后运行脚本来创建存储过程。使用您的本地 SQL Server 连接更新 `Web.Config` 连接字符串。运行应用程序，并使用提供的示例 select 脚本进行测试，以显示带有图表的动态数据。
历史
- 2016 年 7 月 1 日：初始版本