In this article, you will learn how to create a Dynamic MVC dashboard with chart and data display, using AngularJS and WEB API.
Introduction
In this article, we will see in detail how to create a Dynamic MVC Dashboard with Chart and Data display using AngularJS and WEB API. Using this web application now, you can write your own SQL query to bind dynamic Dashboard with Chart and Data. This program makes your work easy to display any Table/Columns details with your entered where Condition
, Order BY
and with Group By
Option for the selected database on your home page with both data and chart.
In our previous article, we explained in detail how to display any data on home page dashboard on our MVC web application. In this article, we will see in detail about how to display data and Chart on dashboard in MVC Web application using AngularJs and Web API.
In this demo application, we have drawn Pie Chart in our MVC Dashboard page. You can draw any chart as per your requirement. In our previous article, we explained how to draw Chart like, Line, Pie, Bar, Donut, Bubble and Line & Bar Chart in MVC application. We used the same logic to draw chart on our MVC dashboard page.
Features in Shanu MVC Dashboard
- Dynamic SQL Query
- Column Names
- Table Names
- Where Condition
- Group By
- Order By
- Chart SQL Query
- Chart Setting and Draw Chart
Here, we will see details of each part.
Kindly refer to our previous article for sections from 1 to 6. We explained in detail about each section with animated images.
This article has all the same features with additional Chart Feature to be displayed on our MVC Dashboard.
7. Chart SQL Query
To display chart, first we need to write our select
query to display both Chart Item and Value.
Here is the sample query to display Chart on our MVC dashboard page. Here, for chart binding, user can enter the complete select
query to bind the result in the combobox
.
Sample Select
query to be used for our application:
Select ItemName as Name, SUM(Price) as Value FROM ItemDetail _
GROUP BY ItemName ORDER BY Value, Name
For draw chart, we fixed the standard as always display 2 columns, one is Name
and another one is Value
. Here, name is the any name (Legend
) to be displayed for chart and value is the actual value to draw the chart. In search button click, we first bind the chart item result to the combobox
. We will be using this combobox
result draw chart.
8. Chart Setting and Draw Chart
User can add Chart Title, Watermark Text as per your requirement at runtime and click on “Click to Draw Chart" button to draw your chart on Dashboard.
Note: You can display any chart data from any table from the given database. All you need to do is write the select
query for chart with Name
and Value
column.
Prerequisites
Visual Studio 2015: You can download it from here.
Using the Code
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.
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 Framework. Here, we will directly get the data from our MVC Web API controller method using normal ADO.NET method.
<add name="dashboard" connectionString="Data Source=SQLSERVERNAME;
Initial Catalog=DashboardDB;Persist Security Info=True;User ID=UID;Password=PWD"
providerName="System.Data.SqlClient" />
Kindly update with your SQL server connection.
Step 3: Add web API Controller
Right click Controllers folder and click Add and click on Controller.
Here, we will add a WEB API Controller to be used for our AngularJS.
Select Web API 2 Controller – Empty and click Add. Next, enter the controller name as DashboardAPIController
.
Get Method
Here, use the Http Get
method to get all our dynamic data from database using normal ADO.NET method.
[HttpGet]
public string getDashboardDetails(string sqlQuery, string columnName,
string tableNames, Nullable<int> isCondition, string conditionList,
Nullable<int> isGroupBY, string groupBYList,
Nullable<int> isOrderBY, string orderBYList)
{
if (sqlQuery == null)
sqlQuery = "";
if (columnName == null)
columnName = "";
if (tableNames == null)
tableNames = "";
if (isCondition == null)
isCondition = 0;
if (conditionList == null)
conditionList = "";
if (isGroupBY == null)
isGroupBY = 0;
if (groupBYList == null)
groupBYList = "";
if (isOrderBY == null)
isOrderBY = 0;
if (orderBYList == null)
orderBYList = "";
string connectionString =
ConfigurationManager.ConnectionStrings["dashboard"].ToString();
DataSet ds = new DataSet();
using (SqlConnection connection = new SqlConnection(connectionString))
{
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 that 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'];
$scope.chartQuerys = "Select ItemName as Name,
SUM(Price) as Value FROM ItemDetail GROUP BY
ItemName ORDER BY Value,Name";
$scope.sItemName = "";
$scope.itemCount = 5;
$scope.selectedItem = "MOUSE";
$scope.chartTitle = "SHANU Item Sales Chart";
$scope.waterMark = "SHANU";
$scope.ItemValues = 0;
$scope.ItemNames = "";
$scope.minsnew = 0;
$scope.maxnew = 0;
Search Method
We call this method on search button click. Here, we check for all the validation of user entered data before passing all the parameter to our Web API method. In this method, we have commented for each condition checking.
In this method, we call the ‘searchbildChartData
” method to bind the select result to the combobox
.
$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);
if ($scope.chartQuerys != "") {
$scope.whereCondition = 0;
for (var i = 0; i < $scope.sqlInjectionArray.length - 1; i++) {
if ($filter('lowercase')($scope.chartQuerys).match
($scope.sqlInjectionArray[i])) {
alert("Sorry " + $scope.sqlInjectionArray[i] +
" keyword is not accepted in select query");
return;
}
}
searchbildChartData($scope.chartQuerys,
$scope.ColumnNames, $scope.TableNames,
$scope.whereCondition, $scope.Conditions, $scope.GroupBy,
$scope.GroupBys, $scope.OrderBy, $scope.OrderBys);
return;
}
else {
alert("Enter Your Chart Select Query !");
return;
}
}
Main Search Method
Finally, after validation, we call our main bind method to pass all the parameter to our WEB API to get the dynamic data from the database.
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 occurred while loading posts!";
});
}
Chart Data Bind Method
This method will be called from our main method to bind the result to combobox
to draw our Pie chart.
function searchbildChartData(sqlQuery, columnName, tableNames,
isCondition, conditionList, isGroupBY, groupBYList, isOrderBY, orderBYList) {
$http.get('/api/DashboardAPI/getDashboardDetails/', { params:
{ sqlQuery: sqlQuery, columnName: columnName, tableNames: tableNames,
isCondition: isCondition, conditionList: conditionList, isGroupBY: isGroupBY,
groupBYList: groupBYList, isOrderBY: isOrderBY,
orderBYList: orderBYList } }).success(function (data) {
$scope.itemData = angular.fromJson(data);
$scope.itemCount = $scope.itemData.length;
$scope.selectedItem = $scope.itemData[0].Name;
$scope.minsnew = $scope.itemData[0].Value;
$scope.maxnew = $scope.itemData[$scope.itemData.length-1].Value;
})
.error(function () {
$scope.error = "An Error has occurred while loading posts!";
});
}
Step 5: Draw Pie Chart for our Dashboard
We are using the Jquery to draw our Pie Chart. In Draw Chart button Click event, we call the drawPieChart
jQuery method to draw our chart. In this method, we get chart value and name from the combobox
and draw the chart on the Canvas
tag which we placed on our MVC Dashboard main page.
function drawPieChart() {
var lastend = 0;
var XvalPosition = xSpace;
chartWidth = (canvas.width / 2) - xSpace;
chartHeight = (canvas.height / 2) - (xSpace / 2);
widthcalculation = parseInt(((parseInt(chartWidth) - 100) / noOfPlots));
var XLineStartPosition = xSpace;
var yLineStartPosition = xSpace;
var yLineHeight = chartHeight;
var xLineWidth = chartWidth;
colorval = 0;
var chartTotalResult = getChartTotal();
$('#DropDownList1 option').each(function () {
if (isNaN(parseInt($(this).val()))) {
}
else
{
ctx.fillStyle = pirChartColor[colorval];
ctx.beginPath();
ctx.moveTo(chartWidth, chartHeight);
ctx.arc(chartWidth, chartHeight + 6, chartHeight, lastend, lastend +
(Math.PI * 2 * (parseInt($(this).val()) / chartTotalResult)), false);
ctx.lineTo(chartWidth, chartHeight);
ctx.fill();
lastend += Math.PI * 2 * (parseInt($(this).val()) / chartTotalResult);
}
colorval = colorval + 1;
});
}
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. Run the application and test with provided sample select scripts to display the dynamic data with chart.
History
- 1st July, 2016: Initial version