Introduction

In this article, we will learn MVC, using AngularJS data binding to the datatable from the Server side with the store procedure using visual studio 2015

Why use Angular Datatable?

Datatable is the most powerful table. It is best for loading a large amount of data. The table was design to be extremely flexible and lightweight.

Some of main features are

  • 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.

  1. USE [master]
  2. GO
  3. /****** Object:  Database [test]    Script Date: 5/7/2017 8:09:18 AM ******/
  4. CREATE DATABASE [test]
  5.  CONTAINMENT = NONE
  6.  ON  PRIMARY
  7. NAME = N‘test’, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test.mdf’ , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ),
  8.  FILEGROUP [DocFiles] CONTAINS FILESTREAM  DEFAULT
  9. NAME = N‘FileStream’, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FileStream’ , MAXSIZE = UNLIMITED)
  10.  LOG ON
  11. 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 )
  12. GO
  13. ALTER DATABASE [test] SET COMPATIBILITY_LEVEL = 130
  14. GO
  15. IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
  16. begin
  17. EXEC [test].[dbo].[sp_fulltext_database] @action = ‘enable’
  18. end
  19. GO
  20. ALTER DATABASE [test] SET ANSI_NULL_DEFAULT OFF
  21. GO
  22. ALTER DATABASE [test] SET ANSI_NULLS OFF
  23. GO
  24. ALTER DATABASE [test] SET ANSI_PADDING OFF
  25. GO
  26. ALTER DATABASE [test] SET ANSI_WARNINGS OFF
  27. GO
  28. ALTER DATABASE [test] SET ARITHABORT OFF
  29. GO
  30. ALTER DATABASE [test] SET AUTO_CLOSE OFF
  31. GO
  32. ALTER DATABASE [test] SET AUTO_SHRINK OFF
  33. GO
  34. ALTER DATABASE [test] SET AUTO_UPDATE_STATISTICS ON
  35. GO
  36. ALTER DATABASE [test] SET CURSOR_CLOSE_ON_COMMIT OFF
  37. GO
  38. ALTER DATABASE [test] SET CURSOR_DEFAULT  GLOBAL
  39. GO
  40. ALTER DATABASE [test] SET CONCAT_NULL_YIELDS_NULL OFF
  41. GO
  42. ALTER DATABASE [test] SET NUMERIC_ROUNDABORT OFF
  43. GO
  44. ALTER DATABASE [test] SET QUOTED_IDENTIFIER OFF
  45. GO
  46. ALTER DATABASE [test] SET RECURSIVE_TRIGGERS OFF
  47. GO
  48. ALTER DATABASE [test] SET  DISABLE_BROKER
  49. GO
  50. ALTER DATABASE [test] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
  51. GO
  52. ALTER DATABASE [test] SET DATE_CORRELATION_OPTIMIZATION OFF
  53. GO
  54. ALTER DATABASE [test] SET TRUSTWORTHY OFF
  55. GO
  56. ALTER DATABASE [test] SET ALLOW_SNAPSHOT_ISOLATION OFF
  57. GO
  58. ALTER DATABASE [test] SET PARAMETERIZATION SIMPLE
  59. GO
  60. ALTER DATABASE [test] SET READ_COMMITTED_SNAPSHOT OFF
  61. GO
  62. ALTER DATABASE [test] SET HONOR_BROKER_PRIORITY OFF
  63. GO
  64. ALTER DATABASE [test] SET RECOVERY FULL
  65. GO
  66. ALTER DATABASE [test] SET  MULTI_USER
  67. GO
  68. ALTER DATABASE [test] SET PAGE_VERIFY CHECKSUM
  69. GO
  70. ALTER DATABASE [test] SET DB_CHAINING OFF
  71. GO
  72. ALTER DATABASE [test] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N‘DocFileDirctory’ )
  73. GO
  74. ALTER DATABASE [test] SET TARGET_RECOVERY_TIME = 60 SECONDS
  75. GO
  76. ALTER DATABASE [test] SET DELAYED_DURABILITY = DISABLED
  77. GO
  78. EXEC sys.sp_db_vardecimal_storage_format N‘test’, N‘ON’
  79. GO
  80. ALTER DATABASE [test] SET QUERY_STORE = OFF
  81. GO

Create a table

I will create a new table based on book information.

  1. CREATE TABLE [dbo].[BookMaster](
  2.     [BookCode] [varchar](10) NULL,
  3.     [BookName] [varchar](50) NULL,
  4.     [BookDesc] [varchar](500) NULL,
  5.     [BookAuthor] [varchar](50) NULL
  6. ON [PRIMARY]
  7. GO

After creating the table, add some data given below.

MVC

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.

  1. Create PROCEDURE [dbo].[BookMaster_SP]
  2. @BookCode   VARCHAR(10)=NULL,
  3. @BookName   VARCHAR(50)=NULL,
  4. @BookDesc   VARCHAR(500)=NUL,
  5. @BookAutor  VARCHAR(500)=NULL,
  6. @Mode       VARCHAR(50)
  7. AS
  8. BEGIN
  9.     SET NOCOUNT ON;
  10.     IF (@Mode=‘ADD’)
  11.     BEGIN
  12.         INSERT INTO BookMaster(BookCode,BookName,BookDesc,BookAuthor)
  13.             VALUES(@BookCode,@BookName,@BookDesc,@BookAutor)
  14.     END
  15.     IF (@Mode=‘EDIT’)
  16.     BEGIN
  17.         UPDATE BookMaster SET BookName=@BookName,BookDesc=@BookDesc,BookAuthor=@BookAutor WHERE BookCode=@BookCode
  18.     END
  19.     IF (@Mode=‘DELETE’)
  20.     BEGIN
  21.         DELETE FROM BookMaster WHERE BookCode=@BookCode
  22.     END
  23.     IF (@Mode=‘GET’)
  24.     BEGIN
  25.         SELECT BookCode,BookName,BookDesc,BookAuthor FROM BookMaster
  26.     END
  27.     IF (@Mode=‘GETID’)
  28.     BEGIN
  29.         SELECT BookCode,BookName,BookDesc,BookAuthor FROM BookMaster WHERE BookCode=@BookCode
  30.     END
  31.     SET NOCOUNT OFF;
  32. END

Create MVC Application

Open Visual Studio 2015, go to Menu New->New project ->select Visual C# under templates-> Choose ASP.NET Web Application.

MVC

Once you click OK button, one more window opens. Select MVC.

MVC

After creating project, click the link given below and you can download 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.

  1. <table datatable=“ng” class=“table-responsive table-bordered table-striped “>
  2.             <thead style=“background-color:#428bca;color:white” >
  3.                 <tr>
  4.                     <td >
  5.                         Book Code
  6.                     </td>
  7.                     <td >
  8.                         Book Name
  9.                     </td>
  10.                     <td >
  11.                         Book Description
  12.                     </td>
  13.                     <td>
  14.                         Book Author Name
  15.                     </td>
  16.                     <td>
  17.                         <span class=“glyphicon glyphicon-edit”></span>
  18.                     </td>
  19.                     <td>
  20.                         <span class=“glyphicon glyphicon-trash”></span>
  21.                     </td>
  22.                 </tr>
  23.             </thead>
  24.             <tbody>
  25.                 <tr ng-repeat=“model in LoadData”>
  26.                     <td >{{ model.BookCode }}</td>
  27.                     <td >{{ model.BookName }}</td>
  28.                     <td>{{ model.BookDesc }}</td>
  29.                     <td>{{ model.BookAuthor }}</td>
  30.                     <td><span class=“glyphicon glyphicon-edit” ng-click=“LoadById(model)”></span></td>
  31.                     <td><span class=“glyphicon glyphicon-trash” ng-click=“DaleteById(model)”></span></td>
  32.                 </tr>
  33.             </tbody>
  34.         </table>

Using Angular Datatable

Create Model folder in Solution Explorer & create new class in Model folder.

  1. public class BookModel
  2.     {
  3.         public string BookCode { get; set; }
  4.         public string BookName { get; set; }
  5.         public string BookDesc{ get; set; }
  6.         public string BookAuthor { get; set; }
  7.         public string Mode { get; set; }
  8.     }

Write the method given below in home controller. LoadData displays the data in the Datatable & another one is using data manipulation.

  1. [HttpPost]
  2.         #region LoadData
  3.         public JsonResult LoadData(BookModel Param)
  4.         {
  5.             List<BookModel> BookList = new List<BookModel>();
  6.             using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings[“DbSqlCon”].ConnectionString))
  7.             {
  8.                 var cmd = new SqlCommand(“BookMaster_SP”, con);
  9.                 cmd.CommandType = CommandType.StoredProcedure;
  10.                 cmd.Parameters.Add(new SqlParameter(“@Mode”, SqlDbType.VarChar)).Value = Param.Mode;
  11.                 cmd.Parameters.Add(new SqlParameter(“@BookCode”, SqlDbType.VarChar)).Value = Param.BookCode;
  12.                 try
  13.                 {
  14.                    con.Open();
  15.                     using (SqlDataReader DbReader = cmd.ExecuteReader())
  16.                         if (DbReader.HasRows)
  17.                         {
  18.                             while (DbReader.Read())
  19.                             {
  20.                                 BookModel Books = new BookModel();
  21.                                 Books.BookCode = DbReader.GetString(DbReader.GetOrdinal(“BookCode”));
  22.                                 Books.BookName = DbReader.GetString(DbReader.GetOrdinal(“BookName”));
  23.                                 Books.BookDesc = DbReader.GetString(DbReader.GetOrdinal(“BookDesc”));
  24.                                 Books.BookAuthor = DbReader.GetString(DbReader.GetOrdinal(“BookAuthor”));
  25.                                 BookList.Add(Books);
  26.                             }
  27.                         }
  28.                     return Json(BookList, JsonRequestBehavior.AllowGet);
  29.                 }
  30.                 finally
  31.                 {
  32.                         con.Close();
  33.                 }
  34.             }
  35.         }
  36.         #endregion
  37.         [HttpPost]
  38.         #region EditData
  39.         public string EditData(BookModel Param)
  40.         {
  41.             if (Param != null)
  42.             {
  43.                 using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings[“DbSqlCon”].ConnectionString))
  44.                 {
  45.                     var cmd = new SqlCommand(“BookMaster_SP”, con);
  46.                     cmd.CommandType = CommandType.StoredProcedure;
  47.                     cmd.Parameters.Add(new SqlParameter(“@Mode”, SqlDbType.VarChar)).Value = Param.Mode;
  48.                     cmd.Parameters.Add(new SqlParameter(“@BookCode”, SqlDbType.VarChar)).Value = Param.BookCode;
  49.                     cmd.Parameters.Add(new SqlParameter(“@BookName”, SqlDbType.VarChar)).Value = Param.BookName;
  50.                     cmd.Parameters.Add(new SqlParameter(“@BookDesc”, SqlDbType.VarChar)).Value = Param.BookDesc;
  51.                     cmd.Parameters.Add(new SqlParameter(“@BookAutor”, SqlDbType.VarChar)).Value = Param.BookAuthor;
  52.                     try
  53.                     {
  54.                         con.Open();
  55.                         cmd.ExecuteNonQuery();
  56.                         return “Success”;
  57.                     }
  58.                     catch (Exception ex)
  59.                     {
  60.                         return ex.ToString();
  61.                     }
  62.                     finally
  63.                     {
  64.                         if (con.State != ConnectionState.Closed)
  65.                             con.Close();
  66.                     }
  67.                 }
  68.             }
  69.             else
  70.             {
  71.                 return “Model Error”;
  72.             }
  73.         }
  74.         #endregion

Declare the connection string in Web.config file.

  1. <connectionStrings>
  2.     <add name=“DbSqlCon” connectionString=“Data Source=xxxx; Initial Catalog=test; User Id=sa; Password=XXX; connect timeout=0;” providerName=“System.Data.SqlClient;” />
  3.   </connectionStrings>

Create an Angular Controller & Service to get the data from the Server side.

Controller

  1. $scope.LoadById = function (model)
  2.     {
  3.         $scope.Ed = model;
  4.         $scope.Datamode = ‘Update’;
  5.         $scope.Enable = true;
  6.     }
  7.     $scope.Save = function ()
  8.     {
  9.         if ($scope.Datamode === ‘Update’)
  10.         {
  11.             var Param = {
  12.                 Mode: ‘EDIT’,
  13.                 BookCode: $scope.Ed.BookCode,
  14.                 BookName: $scope.Ed.BookName,
  15.                 BookDesc: $scope.Ed.BookDesc,
  16.                 BookAuthor: $scope.Ed.BookAuthor
  17.             }
  18.         }
  19.         else
  20.         {
  21.             var Param = {
  22.                 Mode: ‘ADD’,
  23.                 BookCode: $scope.Ed.BookCode,
  24.                 BookName: $scope.Ed.BookName,
  25.                 BookDesc: $scope.Ed.BookDesc,
  26.                 BookAuthor: $scope.Ed.BookAuthor
  27.             }
  28.         }
  29.         var ServiceData = BookService.EditData(Param);
  30.         ServiceData.then(function (result) {
  31.             $scope.loadTable()
  32.             $scope.Ed = ;
  33.             $scope.message = “Data Save Successfully”;
  34.         }, function () {
  35.         });
  36.     }
  37.     $scope.DaleteById = function (model) {
  38.         var Param = {
  39.             Mode: ‘DELETE’,
  40.             BookCode: model.BookCode,
  41.         }
  42.         var ServiceData = BookService.EditData(Param);
  43.         ServiceData.then(function (result) {
  44.             $scope.loadTable()
  45.             $scope.Ed = ;
  46.             $scope.message = “Data Delete Successfully”;
  47.         }, function () {
  48.         });

Service

  1. this.loadGrid = function (Param) {
  2.         var response = $http({
  3.             method: “post”,
  4.             url: “Home/LoadData”,
  5.             data: JSON.stringify(Param),
  6.             dataType: “json”
  7.         });
  8.         return response;
  9.     }
  10.     this.EditData = function (Param) {
  11.         var response = $http({
  12.             method: “post”,
  13.             url: “Home/EditData”,
  14.             data: JSON.stringify(Param),
  15.             dataType: “json”
  16.         });
  17.         return response;
  18.     }

Do not forget to refer the plug in files and JS file also.

Plug In

  1. <link href=“~/Plugin/datatables/media/css/jquery.dataTables.min.css” rel=“stylesheet” />
  2. “~/Plugin/datatables/media/js/jquery.dataTables.js”>
  3. “~/Plugin/angular-datatables/dist/angular-datatables.js”>

My Files

  1. “~/App/App.module.js”>
  2. “~/App/App.config.js”>
  3. “~/App/BookController.js”>

Once you are done with the process given above, your datatable is ready to load. Thus, run the Application.

Output

MVC

Here, I have done simple data manipulation method.

Conclusion

In this article, we have learned WPF CRUD operations, using VB.NET. If you have any queries, please tell me through the comments section.

Advertisements