Introduction
In this article we will see in detail about how to create a Dynamic MVC Dashboard display using AngularJS and WEB API. Using this web application now 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
- Dynamic SQL Query
- Column Names
- Table Names
- Where Condition
- Group By
- 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 as 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 records for testing the output in our MVC dashboard page.
Sample Select query to be used for our application:
select ID,ItemNo ,ItemName ,Comments ,Price from ItemDetail where Price>'1000'
select A.UserName,A.UserType,A.Phone,B.Address,B.Email
FROM
Userdetails A Left Outer JOIN UserAddress B
Here in this application we have option only to select and bind the data .If user entered any Insert, drop, delete other than select query we will 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 webConfig.
Here in this application we have option only to select and bind the data .If user entered any Insert, drop, delete other than select query we will 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 webConfig.
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 :
- Column Names:
UserName, UserType, Phone
Table Names: UserDetails
- 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
4) 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 below 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 :
- Column Names: I
temName,Price
Table Names: ItemDetail
Where Condition: Price>'4000'
- 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 need 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 Key Word
Sample Column Name and table details with where condition with Group By to be used for testing :
- 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
- 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 checking 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 is any of the array word is matching with user entered select query. For example we can see the above image as after select query we have enter 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.
$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'];
Prerequisites
Visual Studio 2015: You can download it from here.
Using the Code
Step 1: 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.
USE MASTER
GO
IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'DashboardDB' )
DROP DATABASE DashboardDB
GO
CREATE DATABASE DashboardDB
GO
USE DashboardDB
GO
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
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')
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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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 HttpGet 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))
{
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "USP_Dashboard_Select";
command.CommandType = CommandType.StoredProcedure;
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@sqlQuery";
parameter.SqlDbType = SqlDbType.NVarChar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = sqlQuery;
command.Parameters.Add(parameter);
SqlParameter parameter1 = new SqlParameter();
parameter1.ParameterName = "@columnName";
parameter1.SqlDbType = SqlDbType.NVarChar;
parameter1.Direction = ParameterDirection.Input;
parameter1.Value = columnName;
command.Parameters.Add(parameter1);
SqlParameter parameter2 = new SqlParameter();
parameter2.ParameterName = "@tableNames";
parameter2.SqlDbType = SqlDbType.NVarChar;
parameter2.Direction = ParameterDirection.Input;
parameter2.Value = tableNames;
command.Parameters.Add(parameter2);
SqlParameter parameter3 = new SqlParameter();
parameter3.ParameterName = "@isCondition";
parameter3.SqlDbType = SqlDbType.NVarChar;
parameter3.Direction = ParameterDirection.Input;
parameter3.Value = isCondition;
command.Parameters.Add(parameter3);
SqlParameter parameter4 = new SqlParameter();
parameter4.ParameterName = "@ConditionList";
parameter4.SqlDbType = SqlDbType.NVarChar;
parameter4.Direction = ParameterDirection.Input;
parameter4.Value = conditionList;
command.Parameters.Add(parameter4);
SqlParameter parameter5 = new SqlParameter();
parameter5.ParameterName = "@isGroupBY";
parameter5.SqlDbType = SqlDbType.NVarChar;
parameter5.Direction = ParameterDirection.Input;
parameter5.Value = isGroupBY;
command.Parameters.Add(parameter5);
SqlParameter parameter6 = new SqlParameter();
parameter6.ParameterName = "@groupBYList";
parameter6.SqlDbType = SqlDbType.NVarChar;
parameter6.Direction = ParameterDirection.Input;
parameter6.Value = groupBYList;
command.Parameters.Add(parameter6);
SqlParameter parameter7 = new SqlParameter();
parameter7.ParameterName = "@isOrderBY";
parameter7.SqlDbType = SqlDbType.NVarChar;
parameter7.Direction = ParameterDirection.Input;
parameter7.Value = isOrderBY;
command.Parameters.Add(parameter7);
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”.
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 = "";
$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'];
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.
$scope.searchDetails = function () {
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 = "";
}
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;
}
}
}
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;
}
}
}
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;
}
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;
}
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);
}
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.
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);;
})
.error(function () {
$scope.error = "An Error has occured while loading posts!";
});
}
Points of Interest
First run the SQL Script to create Database and Tables and then run the script to create Stored Procedure.Update the Web.Config Connection string with your local SQL Server Connection. Rn the application and test with provided sample select scripts to display the dynamic data at your dashboard page.
History
2016-06-01 -> shanuMVCDashboardPart1.zip
