You can download the Source Code from this link Download Source Code 

Introduction

In this article we will see in detail about how to create a Dynamic MVC Dashboard with Chart and Data display using AngularJS and WEB API. Using this web application now you can write your own SQL query to bind dynamic Dashboard with Chart and Data. This program makes your work easy to display any Table/Columns details with your entered where Condition, Order BY and with Group By Option for the selected database on your home page with both data and Chart.

In our previous article https://code.msdn.microsoft.com/MVC-Dashboard-using-91748fc8  we have explained in detail about how to display any data on home page dashboard on our MVC web application. In this article we will see in detail about how to display data and Chart on dashboard in MVC Web application using AngularJs and Web API.

In this demo application we have drawn Pie Chart in our MVC Dashboard page. You can draw any chart as per your requirement In our previous article https://code.msdn.microsoft.com/Draw-MVC-Line-Chart-using-5c6c9f49  we have explained about how to draw Chart like,Line, Pie, Bar, Donut, Bubble and Line & Bar Chart in MVC application .We have used the same logic to draw chart on our MVC dashboard page.

Features in Shanu MVC Dashboard

1) Dynamic SQL Query:

2) Column Names:       

3) Table Names:

4) Where Condition:

5) Group By:

6) Order By:

7) Chart SQL Query

8) Chart Setting and Draw Chart 

Here we will see details of each part

Kindly refer our previous article https://code.msdn.microsoft.com/MVC-Dashboard-using-91748fc8  for sections from 1 to 6.We have explained in detail about each section with animated images.

This article has all same features with additional Chart Feature to be displayed on our MVC Dashboard.

7) Chart SQL Query: To display chart first we need write our select query to display both Chart Item and Value.

Here is the sample query to display Chart on our MVC dashboard page. Here for chart binding user can enter the complete select query to bind the result in the combobox.

Sample Select query to be used for our application: 

Select ItemName as Name,SUM(Price) as Value FROM ItemDetail GROUP BY  ItemName ORDER BY Value,Name.

For draw chart we have fixed the standard as always display 2 columns one is Name and another one is Value. Here name is the any name (Legend) to be displayed for chart and value is the actual value to draw the chart.In search button click we first bind the chart item reault to the combobox .We will be using this combobox result draw chart.  

8) Chart Setting and Draw Chart

User can add Chart Title, Watermark Text as per your requirement at runtime and click on “Click to Draw Chart) button to draw your chart on Dashboard.

Note: You can display any chart data from any table from the given database. All you need to do is write the select query for chart with Name and Value column.

Building the Sample

Visual Studio 2015: You can download it from here.

Description

Step 1:Create a sample database and Table 

 Create a sample database and Table to for testing this application. Here is a SQL script to create database and Table with insert query. Kindly run the below code in your SQL Server to create DB and Tables.
First we create Database and ItemDetail Table with sample insert query and select query.

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

Next we create a UserDetails table and insert some sample data to be selected for our Dashboard page.

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


Next create UserAddress Table and insert the sample records to be used for our dashboard records display.

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

Create Stored Procedure to run Dynamic Query:

This is our main Stored procedure used to run all our Dynamic SQL Select query and return the result to bind in our MVC page.

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

Step 2: Create your MVC Web Application in Visual Studio 2015 

After installing our Visual Studio 2015 click Start, then Programs and select Visual Studio 2015 - Click Visual Studio 2015. Click New, then Project, select Web and then select ASP.NET Web Application. Enter your project name and click OK.

Select MVC, WEB API and click OK.

Now we have created our MVC Application as a next step we add our connection string in our Web.Config file. Here we are not using Entity Frame work .Here we will directly get the data from our MVC Web API controller method using normal ADO.NET method. 

<add name="dashboard" connectionString="Data Source=SQLSERVERNAME;Initial Catalog=DashboardDB;Persist Security Info=True;User ID=UID;Password=PWD" providerName="System.Data.SqlClient" />

Kindly update with your SQL server connection. 

Step 3: Add web API Controller

Right click Controllers folder and click Add and click on Controller.

Here we will add a WEB API Controller to be used for our AngularJS.

Select Web API 2 Controller – Empty and click Add .next enter the controller name as DashboardAPIController

Get Method

Here use the Http Get method to get all our dynamic data from database using normal ADO.Net method.

[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]);
        }

Step 4: Creating AngularJs Controller

First create a folder inside the Script Folder and we give the folder name as “MyAngular”

Now add your Angular Controller inside the folder.

Right Click the MyAngular Folder and click Add and New Item > Select Web > Select AngularJs Controller and give name to Controller. We have given my AngularJs Controller as “Controller.js”

 

If the Angular JS package is missing then add the package to your project.

Right Click your MVC project and Click-> Manage NuGet Packages. Search for AngularJs and click Install.

Modules.js: Here we will add the reference to the AngularJS JavaScript and create an Angular Module named “AngularJs_Module”. 

// <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: In AngularJS Controller we have done all the business logic and returned the data from Web API to our MVC HTML page.

1. Variable declarations

Firstly, we declared all the local variables need to be used. 

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 Method:

This method we call on search button click. Here we check for all the validation of user entered data before passing all the parameter to our Web API method.In this method we have commented for each condition checking.

In this method we call the "searchbildChartData” method to bind the select result to the combo box.

//search Details
    $scope.searchDetails = function () {
         
       // 1. Check for Select Query -> In this fucntion we check for SQL injection in user entered select query if any key word from the array list is found then we give msg to user to entert 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 checkd 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 check 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 check 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 check 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 fucntion we check for SQL injection in user entered select query if any key word from the array list is found then we give msg to user to entert 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;
            }
    }

Main Search Method

Finally after validation we call our main bind method to pass all the parameter to our WEB API to get the dynamic data from the database.

// 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 occured while loading posts!";
   });
    }

Chart Data Bind Method

This method will be called from our main method to bind the result to combobox to draw our Pie chart. 

// 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 occured while loading posts!";
   });
    }

Step 5: Draw Pie Chart for our Dashboard.

We are using the jQuery to draw our Pie Chart.In Draw Chart button Click event we call the drawPieChart jQuery method to draw our chart .In this method we get chart value and name from the combobox and draw the chart on the Canvas tag which we placed on our MVC Dashboard main page.

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;
        });
    }



See also

Draw MVC Pie Chart using WEB API, AngularJS and JQuery
MVC Dashboard using AngularJS and Web API

Download

You can download the Source Code from this link 
Download Source Code