Introduction
In this article, we will learn MVC, using AngularJS data binding to the datatable from the Server side with the stored procedure, using Visual Studio 2015
Why use Angular Datatable?
Datatable is the most powerful table. It is best to load 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 & Server Side Pagination/Sorting.
- Fixed And Fluid Height.
In this article, we are going to
- Create a database.
- Create a stored procedure.
- Create MVC Application.
- Using Angular Datatable.
Create a database
Open SQL Server 2016. Click New Query Window & run the query given below.
- 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, which is based on the book information.
- 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 given below.
Create a stored procedure
I have written the stored procedure for my data operations, so run SP given below. In this procedure, I have done Select, Insert, Update & Delete.
- 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 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 OK button, one more Window opens. Select MVC.
After creating the project, click the link given below and you can download the plug in files.
Subsequently, inject datatable key word in Angular modular.
angular.module('uiroute',['ui.router', 'datatables']);
Create & design HTML page with the table. Mention datatable=ng. Now, bind the Server data.
- <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 Model folder in Solution Explorer & create new class in Model folder.
- 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 home controller. LoadData displays the data in the Datatable & another one is using data manipulation.
- [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 Web.config file.
- <connectionStrings>
- <add name="DbSqlCon" connectionString="Data Source=xxxx; Initial Catalog=test; User Id=sa; Password=XXX; connect timeout=0;" providerName="System.Data.SqlClient;" />
- </connectionStrings>
Create an Angular Controller & Service to get the data from the Server side.
Controller
- $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
- 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 plug in files and JS file also.
Plug In
- <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
- <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
Here, I have done simple data manipulation method.
Kindly refer my AngularJS articles
Conclusion
In this article, we have learnt MVC, using Angular datatable. If you have any queries, please tell me through the comments section.