Introduction
In this article, we will learn how to use MVC, using Angular’s data binding functionality to create a datatable from the server side with the stored procedures.
Why Use Angular Datatable?
Datatable is great for loading a large amount of data. The table is designed to be extremely flexible and lightweight.
Some main features are given below:
- Column Reordering.
- Column Resizing.
- Handle Large Data.
- Client Side and Server Side Pagination/Sorting.
- Fixed and Fluid Height.
In this article, we are going to:
- Create a database.
- Create a stored procedure.
- Create an MVC Application.
- Use Angular Datatable.
Create a Database
Open SQL Server 2016. Click New Query Window and run the query given below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 |
USE [master] GO /****** Object: Database [test] Script Date: 5/7/2017 8:09:18 AM ******/ CREATE DATABASE [test] CONTAINMENT = NONE ON PRIMARY ( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ), FILEGROUP [DocFiles] CONTAINS FILESTREAM DEFAULT ( NAME = N'FileStream', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FileStream' , MAXSIZE = UNLIMITED) LOG ON ( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB ) GO ALTER DATABASE [test] SET COMPATIBILITY_LEVEL = 130 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [test].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [test] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [test] SET ANSI_NULLS OFF GO ALTER DATABASE [test] SET ANSI_PADDING OFF GO ALTER DATABASE [test] SET ANSI_WARNINGS OFF GO ALTER DATABASE [test] SET ARITHABORT OFF GO ALTER DATABASE [test] SET AUTO_CLOSE OFF GO ALTER DATABASE [test] SET AUTO_SHRINK OFF GO ALTER DATABASE [test] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [test] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [test] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [test] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [test] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [test] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [test] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [test] SET DISABLE_BROKER GO ALTER DATABASE [test] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [test] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [test] SET TRUSTWORTHY OFF GO ALTER DATABASE [test] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [test] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [test] SET READ_COMMITTED_SNAPSHOT OFF GO ALTER DATABASE [test] SET HONOR_BROKER_PRIORITY OFF GO ALTER DATABASE [test] SET RECOVERY FULL GO ALTER DATABASE [test] SET MULTI_USER GO ALTER DATABASE [test] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [test] SET DB_CHAINING OFF GO ALTER DATABASE [test] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'DocFileDirctory' ) GO ALTER DATABASE [test] SET TARGET_RECOVERY_TIME = 60 SECONDS GO ALTER DATABASE [test] SET DELAYED_DURABILITY = DISABLED GO EXEC sys.sp_db_vardecimal_storage_format N'test', N'ON' GO ALTER DATABASE [test] SET QUERY_STORE = OFF GO Create a Table I will create a new table, with the information given below: CREATE TABLE [dbo].[BookMaster]( [BookCode] [varchar](10) NULL, [BookName] [varchar](50) NULL, [BookDesc] [varchar](500) NULL, [BookAuthor] [varchar](50) NULL ) ON [PRIMARY] GO |
After creating the table, add some data, as shown below:
Create a Stored Procedure
I have written the stored procedure for my data operations, so run SP as given below. In this procedure, I have created Done Select, Insert, Update, and Delete functionalities.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
Create PROCEDURE [dbo].[BookMaster_SP] @BookCode VARCHAR(10)=NULL, @BookName VARCHAR(50)=NULL, @BookDesc VARCHAR(500)=NUL, @BookAutor VARCHAR(500)=NULL, @Mode VARCHAR(50) AS BEGIN SET NOCOUNT ON; IF (@Mode='ADD') BEGIN INSERT INTO BookMaster(BookCode,BookName,BookDesc,BookAuthor) VALUES(@BookCode,@BookName,@BookDesc,@BookAutor) END IF (@Mode='EDIT') BEGIN UPDATE BookMaster SET BookName=@BookName,BookDesc=@BookDesc,BookAuthor=@BookAutor WHERE BookCode=@BookCode END IF (@Mode='DELETE') BEGIN DELETE FROM BookMaster WHERE BookCode=@BookCode END IF (@Mode='GET') BEGIN SELECT BookCode,BookName,BookDesc,BookAuthor FROM BookMaster END IF (@Mode='GETID') BEGIN SELECT BookCode,BookName,BookDesc,BookAuthor FROM BookMaster WHERE BookCode=@BookCode END SET NOCOUNT OFF; END |
Create an MVC Application
Open Visual Studio 2015, go to Menu New->New project ->select Visual C# under templates-> Choose ASP.NET Web Application.
Once you click the OK button, one more Window opens. Select MVC.
After creating the project, click the link given below and download the plugin files.
Subsequently, inject the datatable keyword in the Angular module.
angular.module('uiroute',['ui.router', 'datatables']);
Create and design an HTML page with the table. Mention datatable=ng
. Now, bind the Server data.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
<table datatable="ng" class="table-responsive table-bordered table-striped "> <thead style="background-color:#428bca;color:white" > <tr> <td > Book Code </td> <td > Book Name </td> <td > Book Description </td> <td> Book Author Name </td> <td> <span class="glyphicon glyphicon-edit"></span> </td> <td> <span class="glyphicon glyphicon-trash"></span> </td> </tr> </thead> <tbody> <tr ng-repeat="model in LoadData"> <td >{{ model.BookCode }}</td> <td >{{ model.BookName }}</td> <td>{{ model.BookDesc }}</td> <td>{{ model.BookAuthor }}</td> <td><span class="glyphicon glyphicon-edit" ng-click="LoadById(model)"></span></td> <td><span class="glyphicon glyphicon-trash" ng-click="DaleteById(model)"></span></td> </tr> </tbody> </table> |
Using Angular Datatable
Create a Model folder in Solution Explorer and create a new class in the Model folder.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
public class BookModel { public string BookCode { get; set; } public string BookName { get; set; } public string BookDesc{ get; set; } public string BookAuthor { get; set; } public string Mode { get; set; } } |
Write the method given below in your home controller. LoadData displays the data in the Datatable, and another one is using data manipulation.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 |
[HttpPost] #region LoadData public JsonResult LoadData(BookModel Param) { List<BookModel> BookList = new List<BookModel>(); using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DbSqlCon"].ConnectionString)) { var cmd = new SqlCommand("BookMaster_SP", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@Mode", SqlDbType.VarChar)).Value = Param.Mode; cmd.Parameters.Add(new SqlParameter("@BookCode", SqlDbType.VarChar)).Value = Param.BookCode; try { con.Open(); using (SqlDataReader DbReader = cmd.ExecuteReader()) if (DbReader.HasRows) { while (DbReader.Read()) { BookModel Books = new BookModel(); Books.BookCode = DbReader.GetString(DbReader.GetOrdinal("BookCode")); Books.BookName = DbReader.GetString(DbReader.GetOrdinal("BookName")); Books.BookDesc = DbReader.GetString(DbReader.GetOrdinal("BookDesc")); Books.BookAuthor = DbReader.GetString(DbReader.GetOrdinal("BookAuthor")); BookList.Add(Books); } } return Json(BookList, JsonRequestBehavior.AllowGet); } finally { con.Close(); } } } #endregion [HttpPost] #region EditData public string EditData(BookModel Param) { if (Param != null) { using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DbSqlCon"].ConnectionString)) { var cmd = new SqlCommand("BookMaster_SP", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@Mode", SqlDbType.VarChar)).Value = Param.Mode; cmd.Parameters.Add(new SqlParameter("@BookCode", SqlDbType.VarChar)).Value = Param.BookCode; cmd.Parameters.Add(new SqlParameter("@BookName", SqlDbType.VarChar)).Value = Param.BookName; cmd.Parameters.Add(new SqlParameter("@BookDesc", SqlDbType.VarChar)).Value = Param.BookDesc; cmd.Parameters.Add(new SqlParameter("@BookAutor", SqlDbType.VarChar)).Value = Param.BookAuthor; try { con.Open(); cmd.ExecuteNonQuery(); return "Success"; } catch (Exception ex) { return ex.ToString(); } finally { if (con.State != ConnectionState.Closed) con.Close(); } } } else { return "Model Error"; } } #endregion |
Declare the connection string in your Web.config file.
1 2 3 |
<span class="cm-tag bracket"><</span><span class="cm-tag">connectionStrings</span><span class="cm-tag bracket">> </span><span class="cm-tag bracket"><</span><span class="cm-tag">add</span> <span class="cm-attribute">name</span>=<span class="cm-string">"DbSqlCon"</span> <span class="cm-attribute">connectionString</span>=<span class="cm-string">"Data Source=xxxx; Initial Catalog=test; User I </span><span class="cm-tag bracket"></</span><span class="cm-tag">connectionStrings</span><span class="cm-tag bracket">></span> |
Create an Angular Controller and Service to get the data from the Server side.
Controller
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
$scope.LoadById = function (model) { $scope.Ed = model; $scope.Datamode = 'Update'; $scope.Enable = true; } $scope.Save = function () { if ($scope.Datamode === 'Update') { var Param = { Mode: 'EDIT', BookCode: $scope.Ed.BookCode, BookName: $scope.Ed.BookName, BookDesc: $scope.Ed.BookDesc, BookAuthor: $scope.Ed.BookAuthor } } else { var Param = { Mode: 'ADD', BookCode: $scope.Ed.BookCode, BookName: $scope.Ed.BookName, BookDesc: $scope.Ed.BookDesc, BookAuthor: $scope.Ed.BookAuthor } } var ServiceData = BookService.EditData(Param); ServiceData.then(function (result) { $scope.loadTable() $scope.Ed = ''; $scope.message = "Data Save Successfully"; }, function () { }); } $scope.DaleteById = function (model) { var Param = { Mode: 'DELETE', BookCode: model.BookCode, } var ServiceData = BookService.EditData(Param); ServiceData.then(function (result) { $scope.loadTable() $scope.Ed = ''; $scope.message = "Data Delete Successfully"; }, function () { }); |
Service
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
this.loadGrid = function (Param) { var response = $http({ method: "post", url: "Home/LoadData", data: JSON.stringify(Param), dataType: "json" }); return response; } this.EditData = function (Param) { var response = $http({ method: "post", url: "Home/EditData", data: JSON.stringify(Param), dataType: "json" }); return response; } |
Do not forget to refer the plugin files and JS file also.
Plugin
1 2 3 |
<link href="~/Plugin/datatables/media/css/jquery.dataTables.min.css" rel="stylesheet" /> <script src="~/Plugin/datatables/media/js/jquery.dataTables.js"></script> <script src="~/Plugin/angular-datatables/dist/angular-datatables.js"></script> |
My Files
1 2 3 |
<script src="~/App/App.module.js"></script> <script src="~/App/App.config.js"></script> <script src="~/App/BookController.js"></script> |
Once you are done with the process given above, your datatable is ready to load. Thus, run the application.
Output
Conclusion
In this article, we have learned some MVC, using Angular Datatable. If you have any queries, please tell me through the comments section.
Happy Coding!