Introduction

In this article, we will learn MVC, using Angular wizard & datatable from the Server side with the stored procedure, using Visual Studio 2015

Why use Wizard?

Wizard means the process is moved by one-by-one step. It allows logically dividing the group of data.That way, the user can enter valuable data.

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

Create a table

I will create a new table that is based on the booking information.

  1. CREATE TABLE [dbo].[WizardMaster](
  2. [BookCode] [varchar](10) NULL,
  3. [BookName] [varchar](50) NULL,
  4. [BookDesc] [varchar](500) NULL,
  5. [BookAuthor] [varchar](50) NULL,
  6. [Email] [varchar](50) NULL,
  7. [Password] [varchar](10) NULL,
  8. [Name] [varchar](10) NULL,
  9. [PhoneNo] [varchar](10) NULL,
  10. [Addess] [varchar](500) NULL
  11. ) ON [PRIMARY]
  12. GO
After creating the table, add some data, as given 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 done “Select” and “Insert” operations.

Create MVC Application

Open Visual Studio 2015.

Go to Menu, click New->New project ->select Visual C# under templates-> ASP.NET Web Application.

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



HTML Design

Step 1

Here, getting email input & matching password using “ui-validate-watch” & “ui-validate”.

  1. “activeStep==1” class=“wizard-step”>
  2.  Create Account — Step 1

  3.     “form_step1” class=“form-validate”>
  4. Email:

    “email” placeholder=“mail@example.com” name=“email” ng-model=“Email” required=“required” class=“form-control” />

  5. class=“mt”>Password

  6. class=“row”>
  7. class=“col-xs-6”> “id-source” type=“password” placeholder=“Type your password” ng-model=“form.match1” class=“form-control” />
  8. class=“col-xs-6”> “password” placeholder=“Retype your password” name=“confirm_match” data-parsley-equalto=“#id-source” required=“” ng-model=“form.match2” ui-validate=“‘$value==form.match1′” ui-validate-watch=“‘validator.match1′” class=“form-control” />
  9.         </div>
  10.         <p ng-show=“form_step1.confirm_match.$error.validator” class=“text-danger”>Password do not match!</p>
  11. class=“mt”> “submit” ng-disabled=“form_step1.$invalid” ng-click=“activeStep=2” class=“btn btn-primary”>Next
  12.     </form>
  13. </div>

Output 1

setp 1 output given below

Step 2

Once you click “Next” button.you have completed step 1. it will move the step 2. In this step i have used ng-pattern=”/^[0-9]+$/” & required key word using for validation .in html form element.

  1. “activeStep==2” class=“wizard-step”>
  2.  Your Social Networks — Step 2

  3.     “form_step2” class=“form-validate”>
  4. class=“mt”>Name:

     “text” name=“name” placeholder=“Your fullname” ng-model=“name” required=“” class=“form-control” />

  5. class=“mt”>Phone:

     “text” name=“phone” ng-model=“phone” placeholder=“Only numbers” ng-pattern=“/^[0-9]+$/” class=“form-control” />

  6. class=“mt”>Address:

     “address” placeholder=“Your address” ng-model=“address” class=“form-control”>

  7. class=“mt”> “button” ng-click=“activeStep=1” class=“btn btn-default”>Prev “submit” ng-disabled=“form_step2.$invalid” ng-click=“activeStep=3” class=“btn btn-primary”>Next
  8.     </form>
  9. </div>

Output 2

 

if you need go back,click to “Prev” button.step 1 has hide,but our assigned values will follow in the state.

Step 3

After clicking the “Next” button in step 2.it will show as step 3.

  1. “activeStep==3” class=“wizard-step”>
  2.  Personal details — Step 3

  3.     “form_step3” class=“form-validate”>
  4. class=“form-group”>
  5. class=“mt”>Book Code:

     “text” class=“form-control” ng-model=“BookCode” placeholder=“Book Code” />

  • class=“form-group”>
  • class=“mt”>Book Name:

     “text” class=“form-control” ng-model=“BookName” placeholder=“Book Name” />

  • class=“form-group”>
  • class=“mt”>Book Desc:

     “text” class=“form-control” ng-model=“BookDesc” placeholder=“Book Desc” />

  • class=“form-group”>
  • class=“mt”>Book Author:

     “text” class=“form-control” ng-model=“BookAuthor” placeholder=“Book Author” />

  •     </form>
  • “stepsDone” class=“alert alert-success mv-lg”>
  • class=“text-bold m0”>Excellent ! You’ve completed all steps.

  • class=“mt”> “button” ng-click=“activeStep=2” class=“btn btn-default”>Prev “button” ng-disabled=“form_step3.$invalid” ng-click=“Save()” class=“btn btn-primary”>Finish
  • </div>  

Output 3

If you click “Finish” button,data should add in tables.

Angular Module

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> User Name </td>
  5.             <td> Email </td>
  6.             <td> Book Name </td>
  7.             <td> Book Description </td>
  8.             <td> Book Author Name </td>
  9.             <td> Phone No </td>
  10.             <td> Address </td>
  11.         </tr>
  12.     </thead>
  13.     <tbody>
  14.         <tr ng-repeat=“model in LoadData”>
  15.             <td>{{ model.Name }}</td>
  16.             <td>{{ model.Email }}</td>
  17.             <td>{{ model.BookName }}</td>
  18.             <td>{{ model.BookDesc }}</td>
  19.             <td>{{ model.BookAuthor }}</td>
  20.             <td>{{ model.Phone }}</td>
  21.             <td>{{ model.Address }}</td>
  22.         </tr>
  23.     </tbody>
  24. </table>

 

Code Behind

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

  1. public class BookModel {
  2.     public string Email {
  3.         get;
  4.         set;
  5.     }
  6.     public string Password {
  7.         get;
  8.         set;
  9.     }
  10.     public string Name {
  11.         get;
  12.         set;
  13.     }
  14.     public string Phone {
  15.         get;
  16.         set;
  17.     }
  18.     public string Address {
  19.         get;
  20.         set;
  21.     }
  22.     public string BookCode {
  23.         get;
  24.         set;
  25.     }
  26.     public string BookName {
  27.         get;
  28.         set;
  29.     }
  30.     public string BookDesc {
  31.         get;
  32.         set;
  33.     }
  34.     public string BookAuthor {
  35.         get;
  36.         set;
  37.     }
  38.     public string Mode {
  39.         get;
  40.         set;
  41.     }
  42. }

 

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

Angular Controller

  1. controller(‘WizardController’function($scope, WizardService) {
  2.     $scope.loadTable = function() {
  3.         var Param = {
  4.             Mode: ‘GET’
  5.         }
  6.         var ServiceData = WizardService.loadGrid(Param);
  7.         ServiceData.then(function(result) {
  8.             $scope.LoadData = result.data;
  9.         }, function() {});
  10.     }
  11.     $scope.loadTable();
  12.     $scope.Save = function() {
  13.         debugger;
  14.         var Param = {
  15.             Mode: ‘ADD’,
  16.             Email: $scope.Email,
  17.             Password: $scope.form.match1,
  18.             Name: $scope.name,
  19.             Phone: $scope.phone,
  20.             Address: $scope.address,
  21.             BookCode: $scope.BookCode,
  22.             BookName: $scope.BookName,
  23.             BookDesc: $scope.BookDesc,
  24.             BookAuthor: $scope.BookAuthor
  25.         }
  26.         var ServiceData = WizardService.EditData(Param);
  27.         ServiceData.then(function(result) {
  28.             $scope.loadTable();
  29.             $scope.stepsDone = true;
  30.         }, function() {});
  31.     }
  32. });

 

Angular 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. http://span
  3. http://span

My Files

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

Output 5

Here, I have done simple angular wizard method.

Conclusion

In this article, we have learned MVC, using Angular wizard. If you have any queries, please tell me through the comments section. Your comments are very valuable.

Happy Coding…..
Advertisements