MVC Dashboard Using AngularJS And Web API

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

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

Here is the list of SQL Injection string we have used in our application, You can add or remove as per your requirement.

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.

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.

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.

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