Post
Ask Question

MVC Dashboard With Chart Using AngularJS And WEB API



Introduction

In this article, we will see in detail about how to create a Dynamic MVC dashboard with the chart and data display, using AngularJS and WEB API. Using this Web Application, you can write your own SQL query to bind the dynamic dashboard with the chart and data. This program makes your work easy in displaying any table/columns details with your entered where condition, order by, and group by options for the selected database on your home page with both the data and chart.



In our previous article, 

We explained in detail about how to display any data on the home page dashboard on our MVC Web Application. In this article, we will see in detail about how to display the data and chart on the dashboard in MVC Web Application, using AngularJs and Web API.

In this demo Application, we have drawn a pie chart in our MVC dashboard page. You can draw any chart as per your requirement. In our previous article, we have explained about how to draw a chart such as  Line, Pie, Bar, Donut, Bubble and Line and Bar Chart in MVC Application .
We have used the same logic to draw the 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 the details of each part.

    Kindly refer to our previous article MVC Dashboard Using AngularJS And Web API     for the sections from 1 to 6. We have explained in detail about each section with the animated images.

    This article has all the same features with the additional chart feature, to be displayed on our MVC dashboard.

  9. Chart SQL Query: To display the chart first, we need to write our Select query to display both the chart item and the value.



    Here, the sample query is used to display the chart in our MVC dashboard page. Here, for chart binding; the user can enter the complete Select query to bind the result in the Combo box.

    Sample Select query to be used for our Application is given below:
    1. Select ItemName as Name,SUM(Price) as Value FROM ItemDetail GROUP BY ItemName ORDER BY Value,Name  
    To draw a chart, we have fixed the standard --  as always display two columns, where one is the name and another one is the value. Here, the name is any name (Legend) to be displayed for a chart and a value is the actual value to draw the chart. In search button click, we first bind the chart item to the Combo box. We will be using this Combo box result to draw the chart.

  10. Chart Setting and Draw Chart

    A user can add Chart Title and Watermark text, as per his requirement at the run time and click “Click to Draw Chart) button to draw your chart on the 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 the chart with the name and value column.

Prerequisites

Visual Studio 2015: You can download it from here.

Code Part

Step 1:

Create a sample database and table to test this Application. Here is a SQL Script to create the database and the table with Insert query. Kindly run the code, given below, in your SQL Server to create DB and the tables. 

  1. USE MASTER  
  2. GO  
  3.   
  4.  --1) Check for the Database Exists .If the database is exist then drop and create new DB  
  5.   
  6. IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'DashboardDB' )  
  7. DROP DATABASE DashboardDB  
  8.   
  9. GO  
  10.   
  11. CREATE DATABASE DashboardDB  
  12. GO  
  13.   
  14. USE DashboardDB  
  15. GO  
  16.   
  17.   
  18. -- 1) //////////// ItemDetails table  
  19.   
  20. -- Create Table ItemDetails,This table will be used to store the details like Item Information  
  21. IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'ItemDetail' )  
  22. DROP TABLE ItemDetail  
  23. GO  
  24.   
  25. CREATE TABLE [dbo].[ItemDetail](  
  26.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  27.     [ItemNo] [varchar](100) NOT NULL ,  
  28.     [ItemName] [varchar](100) NOT NULL,  
  29.     [Comments] [varchar](100) NOT NULL,  
  30.     [Price] INT NOT NULL,  
  31. PRIMARY KEY CLUSTERED   
  32. (  
  33.     [ID] ASC  
  34. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  35. ) ON [PRIMARY]  
  36.   
  37. GO  
  38.   
  39.   
  40. Insert into ItemDetail(ItemNo,ItemName,Comments,Price) values  
  41. ('101','NoteBook',  'HP Notebook 15 Inch',  24500)  
  42.   
  43.   
  44. Insert into ItemDetail(ItemNo,ItemName,Comments,Price) values  
  45. ('102','MONITOR',   'SAMSNG',   '8500')  
  46.   
  47. Insert into ItemDetail(ItemNo,ItemName,Comments,Price) values  
  48. ('103','MOBILE',    'SAMSUNG NOTE 5',   42500)  
  49.   
  50. Insert into ItemDetail(ItemNo,ItemName,Comments,Price) values  
  51. ('104','MOBILE',    'SAMSUNG S7 Edge',  56000)  
  52.   
  53. Insert into ItemDetail(ItemNo,ItemName,Comments,Price) values  
  54. ('105','MOUSE', 'ABKO', 780)  
  55.   
  56. Insert into ItemDetail(ItemNo,ItemName,Comments,Price) values  
  57. ('106','HDD'    ,'LG',  3780)  
  58.   
  59. select * from ItemDetail  
  60.   
  61.   
  62. select ItemName,SUM(convert(int,Price)) as totalCost   
  63. from ItemDetail  
  64. GROUP BY ItemName  
  65.   
  66.   
  67.   
  68. -- 2) User table   
  69.   
  70. IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'UserDetails' )  
  71. DROP TABLE UserDetails  
  72. GO  
  73.   
  74. CREATE TABLE [dbo].UserDetails(  
  75.     [UserID] [int] IDENTITY(1,1) NOT NULL,  
  76.     [UserName] [varchar](100) NOT NULL,  
  77.     [UserType] [varchar](100) NOT NULL,  
  78.     [Phone] [varchar](20) NOT NULL,   
  79. PRIMARY KEY CLUSTERED   
  80. (  
  81.     [UserID] ASC  
  82. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  83. ) ON [PRIMARY]  
  84.   
  85. Insert into UserDetails(UserName,UserType,Phone) values  
  86. ('SHANU','Admin','01039124503')  
  87.   
  88. Insert into UserDetails(UserName,UserType,Phone) values  
  89. ('Afraz','user','01039120984')  
  90.   
  91. Insert into UserDetails(UserName,UserType,Phone) values  
  92. ('Afreen','user','01039120005')  
  93.   
  94. Insert into UserDetails(UserName,UserType,Phone) values  
  95. ('Raj','Admin','01039120006')  
  96.   
  97. Insert into UserDetails(UserName,UserType,Phone) values  
  98. ('Mak','Manager','01039124567')  
  99.   
  100. Insert into UserDetails(UserName,UserType,Phone) values  
  101. ('Jack','Manager','01039120238')  
  102.   
  103. Insert into UserDetails(UserName,UserType,Phone) values  
  104. ('Pak','User','01039125409')  
  105.   
  106. Insert into UserDetails(UserName,UserType,Phone) values  
  107. ('Ninu','Accountant','01039126810')  
  108.   
  109. Insert into UserDetails(UserName,UserType,Phone) values  
  110. ('Nanu','Accountant','01039152011')  
  111.   
  112. -- select * from Userdetails  
  113. -- 3 UserAddress  
  114.   
  115. IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'UserAddress' )  
  116. DROP TABLE UserAddress  
  117. GO  
  118.   
  119. CREATE TABLE [dbo].UserAddress(  
  120.     [UserAddID] [int] IDENTITY(1,1) NOT NULL,  
  121.     [UserID] [int] ,  
  122.     [Address] [varchar](200) NOT NULL,  
  123.     [Email] [varchar](100) NOT NULL,      
  124. PRIMARY KEY CLUSTERED   
  125. (  
  126.     [UserAddID] ASC  
  127. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  128. ) ON [PRIMARY]  
  129.   
  130. Insert into UserAddress(UserID,Address,Email) values  
  131. (1,'Madurai,Tamil Nadu, India','[email protected]')  
  132. Insert into UserAddress(UserID,Address,Email) values  
  133. (2,'Madurai,Tamil Nadu, India','[email protected]')  
  134. Insert into UserAddress(UserID,Address,Email) values  
  135. (3,'Seoul,South Korea','[email protected]')  
  136.   
  137. select * from UserAddress  
  138.   
  139. select A.UserName,A.UserType,A.Phone,B.Address,B.Email  
  140. From  
  141. Userdetails A Left Outer JOIN UserAddress B  
  142. on  
  143. 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 queries and return the result to bind in our MVC page. 
  1. USE [DashboardDB]  
  2. GO  
  3. /****** Object:  StoredProcedure [dbo].[USP_Dashboard_Select]    ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8.   
  9. -- 1) select top 10 random kidsLearnerMaster records       
  10.   
  11. -- Author      : Shanu                                                                  
  12. -- Create date :  2016-05-14                                                                 
  13. -- Description :To run dymanic Query                                              
  14. -- Tables used :  Dynamic Table                                                              
  15. -- Modifier    : Shanu                                                                  
  16. -- Modify date : 2016-05-14                                                                  
  17. -- =============================================    
  18. -- To Select all user roles   
  19. -- 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 '  
  20.   
  21. -- EXEC USP_Dashboard_Select @columnName = 'ItemName,SUM(Price) as totalCost' ,@TableNames = 'ItemDetail' ,@isCondition=0,@ConditionList='Price>''400'' ',@isGroupBY =1,@GroupBYList = 'ItemName'  
  22. -- EXEC USP_Dashboard_Select @sqlQuery = 'Select * from ItemDetail'  
  23. -- EXEC USP_Dashboard_Select @sqlQuery = 'select ID,ItemNo ,ItemName ,Comments ,Price from ItemDetail'  
  24.   
  25. -- =============================================    
  26. ALTER PROCEDURE [dbo].[USP_Dashboard_Select]     
  27. (    
  28.      @sqlQuery varchar(MAX)='',  
  29.      @columnName varchar(MAX)='',  
  30.      @TableNames varchar(MAX)='',  
  31.      @isCondition INT=0,  
  32.      @ConditionList varchar(MAX)='',      
  33.      @isGroupBY INT=0,  
  34.      @GroupBYList varchar(MAX)='',  
  35.      @isOrderBY INT=0,  
  36.      @OrderBYList varchar(MAX)=''     
  37.   )         
  38. AS                                                                  
  39. BEGIN     
  40.   
  41. BEGIN TRY  
  42. IF @sqlQuery =''   
  43.     BEGIN  
  44.            SET @sqlQuery = 'SELECT ' + @columnName + ' FROM ' + @TableNames   
  45.   
  46.                 IF @isCondition=1  
  47.                 BEGIN  
  48.                 SET @sqlQuery = @sqlQuery+ ' WHERE ' + @ConditionList   
  49.                 END  
  50.   
  51.                 IF @isGroupBY=1  
  52.                 BEGIN  
  53.                 SET @sqlQuery = @sqlQuery+ ' GROUP BY ' + @GroupBYList   
  54.                 END  
  55.   
  56.   
  57.                 IF @isOrderBY=1  
  58.                 BEGIN  
  59.                 SET @sqlQuery = @sqlQuery+ ' Order BY ' + @OrderBYList   
  60.                 END  
  61.   
  62.             EXEC (@sqlQuery)          
  63.            
  64.     END  
  65. ELSE  
  66.         BEGIN  
  67.         EXEC (@sqlQuery)  
  68.           
  69.           
  70.         END  
  71. END TRY  
  72. BEGIN CATCH  
  73.     SELECT ERROR_NUMBER() AS ErrorNumber    
  74.      ,ERROR_MESSAGE() AS ErrorMessage;  
  75. END CATCH  
  76. END  
Step 2: Create your MVC Web Application in Visual Studio 2015

After installing our Visual Studio 2015; click Start, followed by Programs, and select Visual Studio 2015. Click Visual Studio 2015. Click New, followed by 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 framework. Here, we will directly get the data from our MVC Web API controller method, using the normal ADO.NET method. 
  1. <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, click Add and click 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 the database, using normal ADO.NET method. 

  1. [HttpGet]  
  2.         public string getDashboardDetails(string sqlQuery, string columnName, string tableNames, Nullable<int> isCondition, string conditionList, Nullable<int> isGroupBY, string groupBYList, Nullable<int> isOrderBY, string orderBYList)  
  3.         {  
  4.             if (sqlQuery == null)  
  5.                 sqlQuery = "";  
  6.   
  7.             if (columnName == null)  
  8.                 columnName = "";  
  9.   
  10.             if (tableNames == null)  
  11.                 tableNames = "";  
  12.   
  13.             if (isCondition == null)  
  14.                 isCondition = 0;  
  15.   
  16.             if (conditionList == null)  
  17.                 conditionList = "";  
  18.   
  19.             if (isGroupBY == null)  
  20.                 isGroupBY = 0;  
  21.   
  22.             if (groupBYList == null)  
  23.                 groupBYList = "";  
  24.   
  25.             if (isOrderBY == null)  
  26.                 isOrderBY = 0;  
  27.   
  28.             if (orderBYList == null)  
  29.                 orderBYList = "";  
  30.   
  31.   
  32.             string connectionString = ConfigurationManager.ConnectionStrings["dashboard"].ToString();  
  33.             DataSet ds = new DataSet();  
  34.             using (SqlConnection connection = new SqlConnection(connectionString))  
  35.             {  
  36.                 // Create the SQL command and add Sp name  
  37.                 SqlCommand command = new SqlCommand();  
  38.                 command.Connection = connection;  
  39.                 command.CommandText = "USP_Dashboard_Select";  
  40.                 command.CommandType = CommandType.StoredProcedure;  
  41.   
  42.                 // Add parameter for Query.  
  43.                 SqlParameter parameter = new SqlParameter();  
  44.                 parameter.ParameterName = "@sqlQuery";  
  45.                 parameter.SqlDbType = SqlDbType.NVarChar;  
  46.                 parameter.Direction = ParameterDirection.Input;  
  47.                 parameter.Value = sqlQuery;  
  48.   
  49.                 command.Parameters.Add(parameter);  
  50.   
  51.                 // Add parameter for Column Names  
  52.                 SqlParameter parameter1 = new SqlParameter();  
  53.                 parameter1.ParameterName = "@columnName";  
  54.                 parameter1.SqlDbType = SqlDbType.NVarChar;  
  55.                 parameter1.Direction = ParameterDirection.Input;  
  56.                 parameter1.Value = columnName;  
  57.                    
  58.                 command.Parameters.Add(parameter1);  
  59.   
  60.                 // Add parameter for Table names  
  61.                 SqlParameter parameter2 = new SqlParameter();  
  62.                 parameter2.ParameterName = "@tableNames";  
  63.                 parameter2.SqlDbType = SqlDbType.NVarChar;  
  64.                 parameter2.Direction = ParameterDirection.Input;  
  65.                 parameter2.Value = tableNames;  
  66.                    
  67.                 command.Parameters.Add(parameter2);  
  68.   
  69.                 // Add parameter to check for  Where condition  
  70.                 SqlParameter parameter3 = new SqlParameter();  
  71.                 parameter3.ParameterName = "@isCondition";  
  72.                 parameter3.SqlDbType = SqlDbType.NVarChar;  
  73.                 parameter3.Direction = ParameterDirection.Input;  
  74.                 parameter3.Value = isCondition;  
  75.                    
  76.                 command.Parameters.Add(parameter3);  
  77.   
  78.                 // Add parameter for Where conditions  
  79.                 SqlParameter parameter4 = new SqlParameter();  
  80.                 parameter4.ParameterName = "@ConditionList";  
  81.                 parameter4.SqlDbType = SqlDbType.NVarChar;  
  82.                 parameter4.Direction = ParameterDirection.Input;  
  83.                 parameter4.Value = conditionList;  
  84.                    
  85.                 command.Parameters.Add(parameter4);  
  86.   
  87.                 // Add parameter to check for  Group By   
  88.                 SqlParameter parameter5 = new SqlParameter();  
  89.                 parameter5.ParameterName = "@isGroupBY";  
  90.                 parameter5.SqlDbType = SqlDbType.NVarChar;  
  91.                 parameter5.Direction = ParameterDirection.Input;  
  92.                 parameter5.Value = isGroupBY;  
  93.    
  94.                 command.Parameters.Add(parameter5);  
  95.                  
  96.                 // Add parameter for Group By  
  97.                 SqlParameter parameter6 = new SqlParameter();  
  98.                 parameter6.ParameterName = "@groupBYList";  
  99.                 parameter6.SqlDbType = SqlDbType.NVarChar;  
  100.                 parameter6.Direction = ParameterDirection.Input;  
  101.                 parameter6.Value = groupBYList;  
  102.                    
  103.                 command.Parameters.Add(parameter6);  
  104.   
  105.                 // Add parameter to check for Order By  
  106.                 SqlParameter parameter7 = new SqlParameter();  
  107.                 parameter7.ParameterName = "@isOrderBY";  
  108.                 parameter7.SqlDbType = SqlDbType.NVarChar;  
  109.                 parameter7.Direction = ParameterDirection.Input;  
  110.                 parameter7.Value = isOrderBY;  
  111.                    
  112.                 command.Parameters.Add(parameter7);  
  113.   
  114.                 // Add parameter  for OrderBY  
  115.                 SqlParameter parameter8 = new SqlParameter();  
  116.                 parameter8.ParameterName = "@orderBYList";  
  117.                 parameter8.SqlDbType = SqlDbType.NVarChar;  
  118.                 parameter8.Direction = ParameterDirection.Input;  
  119.                 parameter8.Value = orderBYList;  
  120.                    
  121.                 command.Parameters.Add(parameter8);  
  122.                   
  123.                 connection.Open();  
  124.   
  125.                 using (SqlDataAdapter da = new SqlDataAdapter(command))  
  126.                 {                    
  127.                     da.Fill(ds);  
  128.                     connection.Close();  
  129.                 }  
  130.   
  131.             }  
  132.             return DataTableToJSONWithJavaScriptSerializer(ds.Tables[0]);  
  133.         }  
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 the 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”. 
  1. // <reference path="../angular.js" />    
  2. /// <reference path="../angular.min.js" />     
  3. /// <reference path="../angular-animate.js" />     
  4. /// <reference path="../angular-animate.min.js" />     
  5. var app;  
  6. (function () {  
  7.     app = angular.module("dashbordModule", ['ngAnimate']);  
  8. })();  
Controllers: In AngularJS Controller, we have done all the business logic and returned the data from Web API to our MVC HTML page.

Variable declarations

First, we declare all the local variables required to be used. 

  1. app.controller("AngularJs_Controller", function ($scope, $filter, $timeout, $rootScope, $window, $http) {  
  2.     $scope.date = new Date();  
  3.     $scope.MyName = "shanu";  
  4.   
  5.     $scope.isQuerys = false;  
  6.     $scope.Querys = "";  
  7.     $scope.ColumnNames = "UserName,UserType,Phone";  
  8.     $scope.TableNames = "UserDetails";  
  9.   
  10.     $scope.isCondition = false;  
  11.     $scope.whereCondition = 0;  
  12.     $scope.Conditions = "";  
  13.   
  14.     $scope.isGroupBy = false;  
  15.     $scope.GroupBy = 0;  
  16.     $scope.GroupBys = "";  
  17.   
  18.     $scope.isOrderBy = false;  
  19.     $scope.OrderBy = 0;  
  20.     $scope.OrderBys = "";  
  21.     // Array value to check for SQL Injection  
  22.     $scope.sqlInjectionArray = ['create', 'drop', 'delete', 'insert', 'update', 'truncate',  
  23.                                 'grant', 'print', 'sp_executesql', 'objects', 'declare',  
  24.                                 'table', 'into', 'sqlcancel', 'sqlsetprop', 'sqlexec',  
  25.                                 'sqlcommit', 'revoke', 'rollback', 'sqlrollback', 'values',  
  26.                                 'sqldisconnect', 'sqlconnect', 'system_user', 'schema_name',  
  27.                                 'schemata', 'information_schema', 'dbo', 'guest', 'db_owner',  
  28.                                 'db_', 'table', '@@', 'Users', 'execute', 'sysname', 'sp_who',  
  29.                                 'sysobjects', 'sp_', 'sysprocesses', 'master', 'sys', 'db_',  
  30.                                 'is_', 'exec', 'end', 'xp_', '; --', 'alter', 'begin', 'cursor',  
  31.                                 'kill', '--', 'tabname', 'sys'];  
  32.   
  33. // Declaration for Chart  
  34.      $scope.chartQuerys = "Select ItemName as Name,SUM(Price) as Value FROM ItemDetail GROUP BY  ItemName ORDER BY Value,Name";  
  35.   
  36.     $scope.sItemName = "";  
  37.     $scope.itemCount = 5;  
  38.     $scope.selectedItem = "MOUSE";  
  39.     $scope.chartTitle = "SHANU Item Sales Chart";  
  40.     $scope.waterMark = "SHANU";  
  41.     $scope.ItemValues = 0;  
  42.     $scope.ItemNames = "";  
  43.     $scope.minsnew = 0;  
  44.     $scope.maxnew = 0;  
Search Method

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

In this method, we call the searchbildChartData method to bind the select result to the Combo box. 

  1. //search Details  
  2.     $scope.searchDetails = function () {  
  3.          
  4.        // 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  
  5.         if ($scope.isQuerys == true) {  
  6.         if ($scope.Querys != "") {  
  7.             $scope.whereCondition = 1;  
  8.             for (var i = 0; i < $scope.sqlInjectionArray.length-1; i++) {  
  9.                 if ($filter('lowercase')($scope.Querys).match($scope.sqlInjectionArray[i])) {  
  10.                     alert("Sorry " + $scope.sqlInjectionArray[i] + " keyword is not accepted in select query");  
  11.                     return;  
  12.                 }  
  13.             }  
  14.             searchTableDetails($scope.Querys, $scope.ColumnNames, $scope.TableNames, $scope.whereCondition, $scope.Conditions, $scope.GroupBy, $scope.GroupBys, $scope.OrderBy, $scope.OrderBys);  
  15.   
  16.             return;  
  17.         }  
  18.         else {  
  19.             alert("Enter Your Select Query !");  
  20.             return;  
  21.         }  
  22.         }  
  23.         else  
  24.         {  
  25.             $scope.Querys = "";  
  26.         }  
  27.   
  28.         // 2. Check for Column Names -> If user entered the valid column names the details will be checkd and binded in page  
  29.         if ($scope.ColumnNames == "") {  
  30.             alert("Enter the Column Details !");  
  31.             return;  
  32.         }  
  33.         else  
  34.         {  
  35.             for (var i = 0; i < $scope.sqlInjectionArray.length - 1; i++) {  
  36.                 if ($filter('lowercase')($scope.ColumnNames).match($scope.sqlInjectionArray[i])) {  
  37.                     alert("Sorry " + $scope.sqlInjectionArray[i] + " keyword is not accepted in Column Names");  
  38.                     return;  
  39.                 }  
  40.             }  
  41.         }  
  42.   
  43.         // 3. Check for Table Names -> If user entered the valid Table names the details will be checkd and binded in page  
  44.         if ($scope.TableNames == "") {  
  45.             alert("Enter the Table Details !");  
  46.             return;  
  47.         }  
  48.         else {  
  49.             for (var i = 0; i < $scope.sqlInjectionArray.length - 1; i++) {  
  50.                 if ($filter('lowercase')($scope.TableNames).match($scope.sqlInjectionArray[i])) {  
  51.                     alert("Sorry " + $scope.sqlInjectionArray[i] + " keyword is not accepted in Table Names");  
  52.                     return;  
  53.                 }  
  54.             }  
  55.         }  
  56.   
  57.          
  58.         // 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   
  59.         if ($scope.isCondition == true) {  
  60.             if ($scope.Conditions == "") {  
  61.                 alert("Enter the Where Condition !");  
  62.                 return;  
  63.             }  
  64.             else {  
  65.                 for (var i = 0; i < $scope.sqlInjectionArray.length - 1; i++) {  
  66.                     if ($filter('lowercase')($scope.Conditions).match($scope.sqlInjectionArray[i])) {  
  67.                         alert("Sorry " + $scope.sqlInjectionArray[i] + " keyword is not accepted in Where Condition");  
  68.                         return;  
  69.                     }  
  70.                 }  
  71.                 $scope.whereCondition = 1;  
  72.             }  
  73.                   
  74.             }  
  75.             else {  
  76.                 $scope.whereCondition = 0;  
  77.             }  
  78.   
  79.         // 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   
  80.         if ($scope.isGroupBy == true) {  
  81.   
  82.             if ($scope.GroupBys == "") {  
  83.                 alert("Enter the Group By Details !");  
  84.                 return;  
  85.             }  
  86.             else {  
  87.                 for (var i = 0; i < $scope.sqlInjectionArray.length - 1; i++) {  
  88.                     if ($filter('lowercase')($scope.GroupBys).match($scope.sqlInjectionArray[i])) {  
  89.                         alert("Sorry " + $scope.sqlInjectionArray[i] + " keyword is not accepted in GroupBy");  
  90.                         return;  
  91.                     }  
  92.                 }  
  93.                 $scope.GroupBy = 1;  
  94.             }  
  95.                  
  96.             }  
  97.             else {  
  98.                 $scope.GroupBy = 0;  
  99.             }  
  100.   
  101.         // 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   
  102.         if ($scope.isOrderBy == true) {  
  103.   
  104.             if ($scope.OrderBys == "") {  
  105.                 alert("Enter the Group By details !");  
  106.                 return;  
  107.             }  
  108.             else {  
  109.                 for (var i = 0; i < $scope.sqlInjectionArray.length - 1; i++) {  
  110.                     if ($filter('lowercase')($scope.OrderBys).match($scope.sqlInjectionArray[i])) {  
  111.                         alert("Sorry " + $scope.sqlInjectionArray[i] + " keyword is not accepted in OrderBy");  
  112.                         return;  
  113.                     }  
  114.                 }  
  115.                 $scope.OrderBy = 1;  
  116.             }  
  117.                  
  118.             }  
  119.             else {  
  120.                 $scope.OrderBy = 0;  
  121.             }  
  122.   
  123.             searchTableDetails($scope.Querys, $scope.ColumnNames, $scope.TableNames, $scope.whereCondition, $scope.Conditions, $scope.GroupBy, $scope.GroupBys, $scope.OrderBy, $scope.OrderBys);  
  124.              
  125.   
  126.         // 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  
  127.   
  128.             if ($scope.chartQuerys != "") {  
  129.                 $scope.whereCondition = 0;  
  130.                 for (var i = 0; i < $scope.sqlInjectionArray.length - 1; i++) {  
  131.                     if ($filter('lowercase')($scope.chartQuerys).match($scope.sqlInjectionArray[i])) {  
  132.                         alert("Sorry " + $scope.sqlInjectionArray[i] + " keyword is not accepted in select query");  
  133.                         return;  
  134.                     }  
  135.                 }  
  136.                 searchbildChartData($scope.chartQuerys, $scope.ColumnNames, $scope.TableNames, $scope.whereCondition, $scope.Conditions, $scope.GroupBy, $scope.GroupBys, $scope.OrderBy, $scope.OrderBys);  
  137.   
  138.                 return;  
  139.             }  
  140.             else {  
  141.                 alert("Enter Your Chart Select Query !");  
  142.                 return;  
  143.             }  
  144.     }  
Main Search Method

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

  1. // Main Select and Bind function  
  2.     //All query details entered by user after validation this method will be called to bind the result to the Dashboard page.  
  3.     function searchTableDetails(sqlQuery, columnName, tableNames, isCondition, conditionList, isGroupBY, groupBYList, isOrderBY, orderBYList) {  
  4.          
  5.         $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) {  
  6.   
  7.             $scope.dashBoadData = angular.fromJson(data);;  
  8.             //alert($scope.dashBoadData.length);  
  9.   
  10.             //if ($scope.dashBoadData.length > 0) {  
  11.   
  12.             //}  
  13.         })  
  14.    .error(function () {  
  15.        $scope.error = "An Error has occured while loading posts!";  
  16.    });  
  17.     }  
Chart Data Bind Method

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

  1. // For binding the Chart result to Listbox before bind result to Chart  
  2.     function searchbildChartData(sqlQuery, columnName, tableNames, isCondition, conditionList, isGroupBY, groupBYList, isOrderBY, orderBYList) {  
  3.    
  4.         $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) {  
  5.   
  6.             $scope.itemData = angular.fromJson(data);             
  7.             $scope.itemCount = $scope.itemData.length;            
  8.             $scope.selectedItem = $scope.itemData[0].Name;             
  9.             $scope.minsnew = $scope.itemData[0].Value;              
  10.             $scope.maxnew = $scope.itemData[$scope.itemData.length-1].Value;      
  11.         })  
  12.    .error(function () {  
  13.        $scope.error = "An Error has occured while loading posts!";  
  14.    });  
  15.     }  
Step 5: Draw Pie Chart for our Dashboard.

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

  1. function drawPieChart() {  
  2.        
  3.         var lastend = 0;  
  4.         var XvalPosition = xSpace;  
  5.   
  6.         chartWidth = (canvas.width / 2) - xSpace;  
  7.         chartHeight = (canvas.height / 2) - (xSpace / 2);  
  8.   
  9.         widthcalculation = parseInt(((parseInt(chartWidth) - 100) / noOfPlots));  
  10.   
  11.         //Draw Xaxis Line  
  12.         //-- draw bar X-Axis and Y-Axis Line  
  13.         var XLineStartPosition = xSpace;  
  14.         var yLineStartPosition = xSpace;  
  15.         var yLineHeight = chartHeight;  
  16.         var xLineWidth = chartWidth;  
  17.   
  18.         colorval = 0;  
  19.         var chartTotalResult = getChartTotal();  
  20.   
  21.         $('#DropDownList1 option').each(function () {  
  22.               
  23.             if (isNaN(parseInt($(this).val()))) {  
  24.                   
  25.             }  
  26.             else  
  27.                 {  
  28.           
  29.             ctx.fillStyle = pirChartColor[colorval];  
  30.             ctx.beginPath();  
  31.             ctx.moveTo(chartWidth, chartHeight);  
  32.             //Here we draw the each Pic Chart arc with values and size.  
  33.             ctx.arc(chartWidth, chartHeight + 6, chartHeight, lastend, lastend +  
  34.               (Math.PI * 2 * (parseInt($(this).val()) / chartTotalResult)), false);  
  35.             
  36.             ctx.lineTo(chartWidth, chartHeight);  
  37.   
  38.            ctx.fill();  
  39.             lastend += Math.PI * 2 * (parseInt($(this).val()) / chartTotalResult);  
  40.   
  41.             //END Draw Bar Graph  **************==================********************  
  42.              
  43.             }  
  44.             colorval = colorval + 1;  
  45.         });   
    }


Similar Articles