Post
Ask Question

MVC Dashboard Using AngularJS And Web API



Introduction 

In this article, we will see in detail how to create a dynamic MVC dashboard display using AngularJS and Web API. Using this web application, you can write your own SQL query to bind dynamic dashboard. 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. 

Features in Shanu MVC Dashboard


  1. Dynamic SQL Query:
  2. Column Names:
  3. Table Names:
  4. Where Condition:
  5. Group By:
  6. Order By:

Here, we will see details of each part,

1. Dynamic SQL Query

We can bind any data on our Dashboard page by entering our Select Query in this text box. We can also write our join query to display data from more than one table with where conditions. We need to first check the Is Query Checkbox for displaying our Dynamic SQL Query data on the dashboard page. If the check box is not checked then by default we will display the column and table details which we have given.



We can see from the above image, first we display the simple select query result in our dashboard page. Next, we display the combination of 2 table data using left outer join select query which will display UserName, UserType, Phone, Address, Email from userDetails and userAddress table. Here you can write your own SQL select query to display data with your where condition, Group By, Order By or Union or any other select query to be displayed. 

Note In this article, we can find the sample database and table creation query in code part. Before running this application kindly run the code part SQL script to create sample database and table with insert record for testing the output in our MVC dashboard page.

Sample Select query to be used for our application:
  1. select ID,ItemNo ,ItemName ,Comments ,Price from ItemDetail where Price>'1000'  
  2. select A.UserName,A.UserType,A.Phone,B.Address,B.Email  
  3. From  
  4. Userdetails A Left Outer JOIN UserAddress B  
  5. on  
  6.   
  7. A.UserID=B.UserID  
Here in this application we have option only to select and bind the data. If user entered any insert, drop, delete query, other than select query we will see the alert message as only select query can be used from this application. We have checked SQL Injection in each user input and display the error message to the user. We will see more in detail about this SQL injection checking in this article.

Note: Now we have fixed for only one database, we can select any table dada from the given Database name in our web config.

2. Column Names & 3) Table Name

Next we have another option to display the data by entering specific column or all columns to be displayed from the user entered tables .here user no need to write the complete Select SQL query. Here we can first write all our column names with comma and then write our table name for the given column details. We can also write our join query here by giving all the join column name details in the Column Names and join table name details with on Condition on the Table Name input. Here we can see a sample output for column and table details.



Sample Column Name and table details to be used for testing:
  1. Column Names: UserName, UserType, Phone

    Table Names: UserDetails

  2. Column Names: A.UserName, A.UserType, A.Phone, B.Address, B.Email

    Table Names: Userdetails A Left Outer JOIN UserAddress B on A.UserID=B.UserID
3. Where Condition

Now let’s see how to write our where condition for the given column and table details. If user need to add some condition using then they can add there condition same like we write in our SQL query to add more conditions we can use and key word. Here in the following image we can see the output of using where condition in our dashboard page.
 
 
 
Sample Column Name and table details with where condition to be used for testing:
  1. Column Names: ItemName, Price

    Table Names: ItemDetail

    Where Condition: Price>'4000'

  2. Column Names: ItemNo, ItemName, Comments, Price

    Table Names:ItemDetail

    Where Condition: ItemName like 'm%'

5. Group By

To use the Group By for our given column and table details user needs to check the Group By checkbox. In the Group By text box user can enter the column details for Group By. Here, we can see a sample output using Group By for the given column and Table details. Here, we can see as we have used both where condition and also unchecked the where condition to display the dynamic data with condition using Group By keyword.
 

Sample Column Name and table details with where condition and Group By to be used for testing:

  1. Column Names: ItemName,Price

    Table Names: ItemDetail

    Where Condition: Price>'4000'

    Group By: ItemName
6. Order By

To use the Order By for our given column and table details user need to check the Order By checkbox. In the Order By text box user can enter the column details for displaying the data in any order as ascending or in descending. Here, we can see a sample output using Order By for the given column and table details.
 


Sample Column Name and table details with Order By:
  1. Column Names: ID, ItemNo, ItemName, Comments, Price

    Table Names: ItemDetail

    Order By: Price desc

SQL Injection checking in user entered select query

 
We have also checked for the SQL Injection before executing user entered SQL Select query. We have created an array list to add all SQL injection string and we will be checking any of the array word matching with user entered select query. For example, we can see the above image as after select query we have entered drop query. But when we click on Search button, we display the message as drop is not accepted in select query.
 
Here is the list of SQL Injection string we have used in our application, You can add or remove as per your requirement. 
  1. // Array value to check for SQL Injection  
  2. $scope.sqlInjectionArray = ['create', 'drop', 'delete', 'insert', 'update', 'truncate',  
  3.                                 'grant', 'print', 'sp_executesql', 'objects', 'declare',  
  4.                                 'table', 'into', 'sqlcancel', 'sqlsetprop', 'sqlexec',  
  5.                                 'sqlcommit', 'revoke', 'rollback', 'sqlrollback', 'values',  
  6.                                 'sqldisconnect', 'sqlconnect', 'system_user', 'schema_name',  
  7.                                 'schemata', 'information_schema', 'dbo', 'guest', 'db_owner',  
  8.                                 'db_', 'table', '@@', 'Users', 'execute', 'sysname', 'sp_who',  
  9.                                 'sysobjects', 'sp_', 'sysprocesses', 'master', 'sys', 'db_',  
  10.                                 'is_', 'exec', 'end', 'xp_', '; --', 'alter', 'begin', 'cursor',  
  11.                                 'kill', '--', 'tabname', 'sys'];  
Prerequisites

Visual Studio 2015: You can download it from here.

Code Part

Step 1: Create a sample database and Table 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. 

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

  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.            
  65.     END  
  66. ELSE  
  67.         BEGIN  
  68.         EXEC (@sqlQuery)  
  69.           
  70.           
  71.         END  
  72. END TRY  
  73. BEGIN CATCH  
  74.     SELECT ERROR_NUMBER() AS ErrorNumber    
  75.      ,ERROR_MESSAGE() AS ErrorMessage;  
  76. END CATCH  
  77. 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. Next, we add our connection string in our Web.Config file. Here, we are not using Entity Framework. We will directly get the data from our MVC Web API controller method using 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 then Controller.

 

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

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

 

Get Method

Use the Http Get method to get all our dynamic data from 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

Firstly, create a folder inside the Script folder and we give the folder name “MyAngular”

Now add your Angular Controller inside the folder.

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

 
 
If the AngularJS 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.

1. Variable declarations

Firstly, we declared all the local variables need 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'];  
Search Method

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

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

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. 

  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/',  
  6.     {  
  7.         params:  
  8.         {  
  9.             sqlQuery: sqlQuery,  
  10.             columnName: columnName,  
  11.             tableNames: tableNames,  
  12.             isCondition: isCondition,  
  13.             conditionList: conditionList,  
  14.             isGroupBY: isGroupBY,  
  15.             groupBYList: groupBYList,  
  16.             isOrderBY: isOrderBY,  
  17.             orderBYList: orderBYList  
  18.         }  
  19.     }).success(function(data)  
  20.     {  
  21.         $scope.dashBoadData = angular.fromJson(data);;  
  22.         //alert($scope.dashBoadData.length);    
  23.         //if ($scope.dashBoadData.length > 0) {    
  24.         //}    
  25.     })  
  26.     .error(function()  
  27.     {  
  28.         $scope.error = "An Error has occured while loading posts!";  
  29.     });  
  30. }  

Step 5: HTML part to bind all our dynamic result for the Dashboard

Next in our MVC View page we design our dashboard page and bind all the dynamic data at runtime. 

  1. <html data-ng-app="dashbordModule">  
  2. @{ ViewBag.Title = "Shanu Dashboard"; }  
  3.   
  4. <body data-ng-controller="AngularJs_Controller">  
  5.   
  6.     <table style='width: 99%;table-layout:fixed;'>  
  7.         <tr>  
  8.             <td>  
  9.                 <table style="background-color:#FFFFFF;border: dashed 3px #6D7B8D; padding:10px;width: 99%;table-layout:fixed;" cellpadding="12" cellspacing="12">  
  10.                     <tr style="height: 30px; background-color:#f06a0a ; color:#FFFFFF ;border: solid 1px #659EC7;">  
  11.                         <td align="center">  
  12.                             <h3> SHANU MVC Dashboard using AngularJS </h3>  
  13.                         </td>  
  14.   
  15.                     </tr>  
  16.                     <tr style="background-color:#FFFFFF; border:dotted 3px #6D7B8D; padding: 6px;width:99%;">  
  17.                         <td>  
  18.                             You can write your own SQL query to bind dynamic Dashboard. 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.  
  19.                         </td>  
  20.                     </tr>  
  21.   
  22.                 </table>  
  23.   
  24.             </td>  
  25.         </tr>  
  26.         <tr>  
  27.             <td> </td>  
  28.         </tr>  
  29.   
  30.         <tr>  
  31.             <td>  
  32.   
  33.                 <table style="background-color:#FFFFFF; border: dashed 3px #6D7B8D; padding :5px;width :99%;table-layout:fixed;" cellpadding="2" cellspacing="2">  
  34.                     <tr style="height: 30px; background-color:#336699 ; color:#FFFFFF ;border: solid 1px #659EC7;">  
  35.                         <td>  
  36.                             <h3> Build Your Dashboard Query : </h3>  
  37.                         </td>  
  38.   
  39.                     </tr>  
  40.                     <tr>  
  41.                         <td>  
  42.                             <table style="width :99%;">  
  43.                                 <tr>  
  44.   
  45.                                     <td width="20"></td>  
  46.                                     <td valign="top">  
  47.   
  48.                                         <table style="color:#9F000F;font-size:large;width :99%;" cellpadding="4" cellspacing="6">  
  49.                                             <tr>  
  50.                                                 <td>  
  51.                                                     <b>Dynamic SQL Query : </b>  
  52.   
  53.                                                 </td>  
  54.                                                 <td>  
  55.                                                     <div style="float:left;width:80%;">  
  56.                                                         <textarea name="txtQuerys" ng-model="Querys" value="" style="width:100%"></textarea>  
  57.                                                     </div>  
  58.                                                 </td>  
  59.                                                 <td align="left">  
  60.                                                     <input type="checkbox" ng-model="isQuerys"><b> Is Query </b>  
  61.                                                 </td>  
  62.                                             </tr>  
  63.   
  64.                                             <tr>  
  65.                                                 <td>  
  66.                                                     <b>Column Names : </b>  
  67.                                                 </td>  
  68.                                                 <td>  
  69.   
  70.                                                     <input type="text" name="txtColumnNames" ng-model="ColumnNames" value="UserName,UserType,Phone" style="width: 350px;" maxlength=5 00 />  
  71.                                                 </td>  
  72.                                                 <td></td>  
  73.                                             </tr>  
  74.   
  75.                                             <tr>  
  76.                                                 <td>  
  77.                                                     <b>Table Names : </b>  
  78.                                                 </td>  
  79.                                                 <td>  
  80.   
  81.                                                     <input type="text" name="txtTableNames" ng-model="TableNames" value="UserDetails" size="120" />  
  82.                                                 </td>  
  83.                                                 <td></td>  
  84.                                             </tr>  
  85.   
  86.                                             <tr>  
  87.                                                 <td>  
  88.                                                     <b>Where Condition : </b>  
  89.                                                 </td>  
  90.                                                 <td>  
  91.   
  92.                                                     <input type="text" name="txtConditions" ng-model="Conditions" value="" size="120" />  
  93.                                                     <br />  
  94.                                                 </td>  
  95.                                                 <td align="left">  
  96.                                                     <input type="checkbox" ng-model="isCondition"> <b> Is Condition  </b>  
  97.   
  98.                                                 </td>  
  99.                                             </tr>  
  100.   
  101.                                             <tr>  
  102.                                                 <td>  
  103.                                                     <b>Group By : </b>  
  104.                                                 </td>  
  105.                                                 <td>  
  106.   
  107.                                                     <input type="text" name="txtGroupBys" ng-model="GroupBys" value="" size="120" />  
  108.                                                     <br />  
  109.                                                 </td>  
  110.                                                 <td align="left"> <input type="checkbox" ng-model="isGroupBy"><b> Is GroupBy  </b></td>  
  111.                                             </tr>  
  112.   
  113.                                             <tr>  
  114.                                                 <td>  
  115.                                                     <b>Order By : </b>  
  116.                                                 </td>  
  117.                                                 <td>  
  118.   
  119.                                                     <input type="text" name="txtOrderBys" ng-model="OrderBys" value="" size="120" />  
  120.                                                     <br />  
  121.                                                 </td>  
  122.                                                 <td align="left"><input type="checkbox" ng-model="isOrderBy"><b> Is OrderBy  </b> </td>  
  123.                                             </tr>  
  124.                                             <tr>  
  125.                                                 <td colspan="3" align="center">  
  126.                                                     <input type="submit" value="Search" style="background-color:#3f9835;color:#FFFFFF;border-color:crimson;border-style:dashed;height:40px;width:500px;" ng-click="searchDetails()" />  
  127.                                                 </td>  
  128.                                             </tr>  
  129.   
  130.                                         </table>  
  131.   
  132.                                     </td>  
  133.   
  134.                                 </tr>  
  135.   
  136.                             </table>  
  137.                         </td>  
  138.                     </tr>  
  139.   
  140.                 </table>  
  141.             </td>  
  142.         </tr>  
  143.         <tr>  
  144.             <td>  
  145.   
  146.             </td>  
  147.         </tr>  
  148.         <tr>  
  149.             <td>  
  150.                 <table style="background-color:#FFFFFF; border :dashed 3px #6D7B8D; padding:10px;width: 99%;table-layout:fixed;" cellpadding="12" cellspacing="12">  
  151.                     <tr style="height: 30px; background-color:#336699 ; color:#FFFFFF ;border: solid 1px #659EC7;">  
  152.                         <td align="center">  
  153.                             <h3> Dynamic Dashboard Details </h3>  
  154.                         </td>  
  155.   
  156.                     </tr>  
  157.                     <tr style="background-color:#FFFFFF; border:dotted 3px #6D7B8D; padding: 6px;width: 99%;">  
  158.                         <td>  
  159.                             <table style="width: 99%; background-color:#FFFFFF; border solid 2px #6D7B8D; padding 5px;width 99%;table-layout:fixed;" cellpadding="2" cellspacing="2">  
  160.   
  161.                                 <thead>  
  162.                                     <tr style="height: 30px; background-color:#336699 ; color:#FFFFFF ;border: solid 1px #659EC7;">  
  163.                                         <th ng-repeat="(header, value) in dashBoadData[0]" width="100" align="center">  
  164.                                             <strong>   {{header}} </strong>  
  165.                                         </th>  
  166.                                     </tr>  
  167.                                 </thead>  
  168.                                 <tbody>  
  169.                                     <tr ng-repeat="row in dashBoadData">  
  170.                                         <td ng-repeat="cell in row" style="border: solid 1px #659EC7; padding: 5px;table-layout:fixed;">  
  171.                                             <span style="color:#9F000F">    {{cell}} </span>  
  172.                                         </td>  
  173.                                     </tr>  
  174.                                 </tbody>  
  175.                             </table>  
  176.                         </td>  
  177.                     </tr>  
  178.                 </table>  
  179.   
  180.             </td>  
  181.         </tr>  
  182.     </table>  
  183. </body>  
  184.   
  185. </html>  
  186.   
  187. <script src="~/Scripts/angular.js"></script>  
  188. <script src="~/Scripts/angular.min.js"></script>  
  189. <script src="~/Scripts/angular-animate.js"></script>  
  190. <script src="~/Scripts/angular-animate.min.js"></script>  
  191. <script src="~/Scripts/MYAngular/controller.js"></script>  


Similar Articles