CRUD Operation Using ASP.NET MVC, JSON, ADO.NET, Stored Procedure

Our first step is to create and design a SQL database. In the following steps we have the demonstration of creating a database table and basic CRUD Stored Procedure.

Let’s Create a Sample Database

Let’s create a sample database named ‘SampleDB’ with SQL Management Studio.

Using the ‘SampleDB’ now create a Table name ‘tblCustomer’.

Script:

  1. CREATE TABLE [dbo].[tblCustomer](  
  2.     [CustID] [bigintNOT NULL,  
  3.     [CustName] [nvarchar](50) NULL,  
  4.     [CustEmail] [nvarchar](50) NOT NULL,  
  5.     [CustAddress] [nvarchar](256) NULL,  
  6.     [CustContact] [nvarchar](50) NULL,  
  7.  CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED   
  8. (  
  9.     [CustID] ASC,  
  10.     [CustEmail] ASC  
  11. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  12. ON [PRIMARY]  
  13.   
  14. GO  
Stored Procedure

Now in this step we will go through to perform CRUD operations with stored procedure: 
  1. First we will create a stored procedure (SP) to RETRIVE record from Customer table.
  2. Now we will create a stored procedure( SP) to INSERT record into Customer table.
  3. Now we will create another procedure (SP) to UPDATE existing data in our Customer table.
  4. The last step we will create a stored procedure to DELETE existing record from customer table.

Stored Procedure to RETRIVE Record:

  1. ALTER PROCEDURE [dbo].[CREATE_CUSTOMER]  
  2.     -- Add the parameters for the stored procedure here  
  3. (  
  4.      @CustName NVarchar(50)  
  5.     ,@CustEmail NVarchar(50)  
  6.     ,@CustAddress NVarchar(256)  
  7.     ,@CustContact  NVarchar(50)  
  8. )  
  9. AS  
  10. BEGIN  
  11.     ---- SET NOCOUNT ON added to prevent extra result sets from  
  12.     SET NOCOUNT ON;  
  13.   
  14.     ---- Try Catch--  
  15.     BEGIN TRY  
  16.         BEGIN TRANSACTION  
  17.   
  18.         DECLARE @CustID Bigint  
  19.             SET @CustID = isnull(((SELECT max(CustID) FROM [dbo].[tblCustomer])+1),'1')  
  20.   
  21.         -- Insert statements for procedure here  
  22.         INSERT INTO [dbo].[tblCustomer] ([CustID],[CustName],[CustEmail],[CustAddress],[CustContact])  
  23.         VALUES(@CustID,@CustName,@CustEmail,@CustAddress,@CustContact)  
  24.         SELECT 1  
  25.         COMMIT TRANSACTION  
  26.     END TRY  
  27.     BEGIN CATCH  
  28.             DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;  
  29.             SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();  
  30.             RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);  
  31.         ROLLBACK TRANSACTION  
  32.     END CATCH  
  33.   
  34. END  
Stored Procedure to INSERT Record:
  1. ALTER PROCEDURE [dbo].[CREATE_CUSTOMER]  
  2.     -- Add the parameters for the stored procedure here  
  3. (  
  4.      @CustName NVarchar(50)  
  5.     ,@CustEmail NVarchar(50)  
  6.     ,@CustAddress NVarchar(256)  
  7.     ,@CustContact  NVarchar(50)  
  8. )  
  9. AS  
  10. BEGIN  
  11.     ---- SET NOCOUNT ON added to prevent extra result sets from  
  12.     SET NOCOUNT ON;  
  13.   
  14.     ---- Try Catch--  
  15.     BEGIN TRY  
  16.         BEGIN TRANSACTION  
  17.   
  18.         DECLARE @CustID Bigint  
  19.             SET @CustID = isnull(((SELECT max(CustID) FROM [dbo].[tblCustomer])+1),'1')  
  20.   
  21.         -- Insert statements for procedure here  
  22.         INSERT INTO [dbo].[tblCustomer] ([CustID],[CustName],[CustEmail],[CustAddress],[CustContact])  
  23.         VALUES(@CustID,@CustName,@CustEmail,@CustAddress,@CustContact)  
  24.         SELECT 1  
  25.         COMMIT TRANSACTION  
  26.     END TRY  
  27.     BEGIN CATCH  
  28.             DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;  
  29.             SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();  
  30.             RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);  
  31.         ROLLBACK TRANSACTION  
  32.     END CATCH  
  33.   
  34. END  
Stored Procedure to UPDATE Record:
  1. ALTER PROCEDURE [dbo].[UPDATE_CUSTOMER]  
  2.     -- Add the parameters for the stored procedure here  
  3.      @CustID BIGINT  
  4.     ,@CustName NVarchar(50)  
  5.     ,@CustEmail NVarchar(50)  
  6.     ,@CustAddress NVarchar(256)  
  7.     ,@CustContact  NVarchar(50)  
  8. AS  
  9. BEGIN  
  10.     ---- SET NOCOUNT ON added to prevent extra result sets from  
  11.     SET NOCOUNT ON;  
  12.   
  13.     ---- Try Catch--  
  14.     BEGIN TRY  
  15.         BEGIN TRANSACTION  
  16.   
  17.         -- Update statements for procedure here  
  18.         UPDATE [dbo].[tblCustomer]  
  19.         SET [CustName] = @CustName,  
  20.             [CustAddress] = @CustAddress,  
  21.             [CustContact] = @CustContact  
  22.         WHERE [CustID] = @CustID AND [CustEmail] = @CustEmail  
  23.         SELECT 1  
  24.         COMMIT TRANSACTION  
  25.     END TRY  
  26.     BEGIN CATCH  
  27.             DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;  
  28.             SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();  
  29.             RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);  
  30.         ROLLBACK TRANSACTION  
  31.     END CATCH  
  32.   
  33. END  
Stored Procedure to DELETE Record:
  1. ALTER PROCEDURE [dbo].[DELETE_CUSTOMER]  
  2.     -- Add the parameters for the stored procedure here  
  3.       @CustID BIGINT  
  4. AS  
  5. BEGIN  
  6.     ---- SET NOCOUNT ON added to prevent extra result sets from  
  7.     SET NOCOUNT ON;  
  8.   
  9.     ---- Try Catch--  
  10.     BEGIN TRY  
  11.         BEGIN TRANSACTION  
  12.   
  13.         -- Delete statements for procedure here  
  14.         DELETE [dbo].[tblCustomer]  
  15.         WHERE [CustID] = @CustID   
  16.         SELECT 1  
  17.         COMMIT TRANSACTION  
  18.     END TRY  
  19.     BEGIN CATCH  
  20.             DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;  
  21.             SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();  
  22.             RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);  
  23.         ROLLBACK TRANSACTION  
  24.     END CATCH  
  25.   
  26. END  
Stored Procedure to VIEW Single Record Details:
  1. ALTER PROCEDURE [dbo].[VIEW_CUSTOMER]  
  2.     -- Add the parameters for the stored procedure here  
  3.     @CustID BIGINT  
  4. AS  
  5. BEGIN  
  6.     -- SET NOCOUNT ON added to prevent extra result sets from  
  7.     SET NOCOUNT ON;  
  8.     -- Select statements for procedure here  
  9.   
  10.     SELECT * FROM [dbo].[tblCustomer]  
  11.     WHERE [CustID] = @CustID   
  12. END  
Let’s Start:

Open Visual Studio 2015, Click File, New, then Project. In this window give a name to the project and solution.

asp.net web application

Click ok and another window will appear with project template, choose MVC:

MVC

Click ok and visual studio will create and load a new ASP.NET application template. In this case we are using ASP.NET MVC 5. The new window will look like the following:

ASP.NET application template

Our next step is to create a new model. Now right click on model folder and add new item, choose data from the left side menu and select ADO.NET Entity Data Model. Follow the process and click next.

choose model content

Next step is to connect to database to generate .edmx file:

select table

Let’s Start with Design (UI)

Here are the .cshtml file of Customer CRUD operations. In the index view we have used a partial view to render the Customer list with Infinite scroll.

Index.cshtml 
  1. @{  
  2.     ViewBag.Title = "Index";  
  3. }  
  4.   
  5. <h2>Index</h2>  
  6. <p>  
  7.     @Html.ActionLink("Create New""Create")  
  8. </p>  
  9.   
  10. <!-- list -->  
  11. <div id="resultCust"></div>  
  12. <div id="loader" style="text-align: center; display: none; margin-bottom: 20px;">  
  13.     <img alt="Loading" src="@Url.Content("~/Content/loader.gif")" /> Loading...  
  14. </div>  
  15. <!-- list -->  
  16.   
  17. <script src="~/Scripts/jquery-1.10.2.min.js"></script>  
  18. <script type="text/javascript">  
  19.   
  20.     var inProgress = false;  
  21.     $(document).ready(function () {  
  22.   
  23.         //======================Default Load================//  
  24.         inProgress = true;  
  25.         $("#loader").show();  
  26.         $.get("@Url.Action("GetCustomer", "Customer")", { "RowCountPerPage": 5 },  
  27.                    function (data) {  
  28.                        $("#resultCust").append(data);  
  29.                        $("#loader").hide();  
  30.                        inProgress = false;  
  31.                    });  
  32.   
  33.         //======================UL SCroll Load================//  
  34.         var page = 1;  
  35.         var _inCallback = false;  
  36.   
  37.         $(window).scroll(function () {  
  38.   
  39.             if ($(window).scrollTop() == $(document).height() - $(window).height()) {  
  40.                 if (page > -1 && !_inCallback) {  
  41.                     _inCallback = true;  
  42.                     page++;  
  43.                     $("#loader").show();  
  44.                     $.get("@Url.Action("GetCustomer_Scroll", "Customer")", { "PageNo": page, "RowCountPerPage": 5 },  
  45.                      function (data) {  
  46.                          if (data != '') {  
  47.                              $("#resultCust").append(data);  
  48.                          }  
  49.                          else {  
  50.                              page = -1;  
  51.                          }  
  52.                          $("#loader").hide();  
  53.                          _inCallback = false;  
  54.                      });  
  55.                 }  
  56.             }  
  57.         });  
  58.     });  
  59.   
  60. </script>  
Partial view to view Customers:
  1. @model IEnumerable<CRUD_MVC5.Models.tblCustomer>  
  2.   
  3.   
  4. @foreach (var item in Model)  
  5. {  
  6.     <div>  
  7.         <strong>Name: @Html.DisplayFor(modelItem => item.CustName)</strong>  
  8.     </div>  
  9.     <div>  
  10.         Adddress: @Html.DisplayFor(modelItem => item.CustAddress)  
  11.     </div>  
  12.     <div>  
  13.         @Html.DisplayFor(modelItem => item.CustEmail)  
  14.     </div>  
  15.     <div>  
  16.         @Html.DisplayFor(modelItem => item.CustContact)  
  17.     </div>  
  18.     <div>  
  19.         @Html.ActionLink("Edit""Edit"new { id = item.CustID }) |  
  20.         @Html.ActionLink("Delete""Delete"new { id = item.CustID }, new { @class = "delete", @id = item.CustID })  
  21.     </div>  
  22.     <hr />  
  23. }  
  24.   
  25. <script src="~/Scripts/jquery-1.10.2.min.js"></script>  
  26. <script src="~/Scripts/jquery.validate.min.js"></script>  
  27.   
  28. <script type="text/javascript">  
  29.     $(document).ready(function () {  
  30.   
  31.         $('a.delete').click(function (e) {  
  32.             e.preventDefault();  
  33.   
  34.             var CustomerId = e.target.id;  
  35.             var flag = confirm('You are about to delete Customer ID ' + CustomerId + ' permanently.Are you sure you want to delete this record?');  
  36.             if (flag) {  
  37.                 $.ajax({  
  38.                     url: '/Customer/Delete',  
  39.                     async: false,  
  40.                     type: "POST",  
  41.                     data: { id: CustomerId },  
  42.                     dataType: "json",  
  43.                     success: function (data, textStatus, jqXHR) {  
  44.                         if (data.Message == 1) {  
  45.                             //alert('Record Deleted!');  
  46.                             $("#" + CustomerId).parent().parent().remove();  
  47.                             window.location.href = '/Customer';  
  48.                         }  
  49.                         else {  
  50.                             alert('Record not Affected to DataBase!!');  
  51.                         }  
  52.   
  53.                         $("#loader").hide();  
  54.                     },  
  55.                     error: function (jqXHR, textStatus, errorThrown) {  
  56.                         alert(jqXHR + "-" + textStatus + "-" + errorThrown);  
  57.                     }  
  58.                 });  
  59.             }  
  60.             return false;  
  61.         });  
  62.     });  
  63. </script>      
Create.cshtml

In this view we have sent a JSON object to MVC Action Method and have your Action Method return you a JSON result.
  1. @model CRUD_MVC5.Models.tblCustomer  
  2.   
  3. @{  
  4.     ViewBag.Title = "Create";  
  5. }  
  6.   
  7. <h2>Create</h2>  
  8.   
  9. <div id="loader" style="text-align: center; display: none; margin-bottom: 20px;">  
  10.     <img alt="Loading" src="@Url.Content("~/Content/loader.gif")" /> Loading...  
  11. </div>  
  12.   
  13. @using (Html.BeginForm(nullnull, FormMethod.Post, new { @id = "CustomerForm", @class = "form" }))  
  14. {  
  15.     @Html.AntiForgeryToken()  
  16.   
  17.     <div class="form-horizontal">  
  18.         <h4>Create Customer</h4>  
  19.         <hr />  
  20.         @Html.ValidationSummary(true""new { @class = "text-danger" })  
  21.   
  22.         <div class="form-group">  
  23.             @Html.LabelFor(model => model.CustName, htmlAttributes: new { @class = "control-label col-md-2" })  
  24.             <div class="col-md-10">  
  25.                 @Html.EditorFor(model => model.CustName, new { htmlAttributes = new { @class = "form-control" } })  
  26.                 @Html.ValidationMessageFor(model => model.CustName, ""new { @class = "text-danger" })  
  27.             </div>  
  28.         </div>  
  29.   
  30.         <div class="form-group">  
  31.             @Html.LabelFor(model => model.CustEmail, htmlAttributes: new { @class = "control-label col-md-2" })  
  32.             <div class="col-md-10">  
  33.                 @Html.EditorFor(model => model.CustEmail, new { htmlAttributes = new { @class = "form-control" } })  
  34.                 @Html.ValidationMessageFor(model => model.CustEmail, ""new { @class = "text-danger" })  
  35.             </div>  
  36.         </div>  
  37.   
  38.         <div class="form-group">  
  39.             @Html.LabelFor(model => model.CustAddress, htmlAttributes: new { @class = "control-label col-md-2" })  
  40.             <div class="col-md-10">  
  41.                 @Html.EditorFor(model => model.CustAddress, new { htmlAttributes = new { @class = "form-control" } })  
  42.                 @Html.ValidationMessageFor(model => model.CustAddress, ""new { @class = "text-danger" })  
  43.             </div>  
  44.         </div>  
  45.   
  46.         <div class="form-group">  
  47.             @Html.LabelFor(model => model.CustContact, htmlAttributes: new { @class = "control-label col-md-2" })  
  48.             <div class="col-md-10">  
  49.                 @Html.EditorFor(model => model.CustContact, new { htmlAttributes = new { @class = "form-control" } })  
  50.                 @Html.ValidationMessageFor(model => model.CustContact, ""new { @class = "text-danger" })  
  51.             </div>  
  52.         </div>  
  53.   
  54.         <div class="form-group">  
  55.             <div class="col-md-offset-2 col-md-10">  
  56.                 <input type="submit" value="Create" class="btn btn-default" id="SubmitForm" />  
  57.             </div>  
  58.         </div>  
  59.     </div>  
  60. }  
  61.   
  62. <div>  
  63.     @Html.ActionLink("Back to List""Index")  
  64. </div>  
  65.   
  66. <script src="~/Scripts/jquery-1.10.2.min.js"></script>  
  67. <script src="~/Scripts/jquery.validate.min.js"></script>  
  68.   
  69. <script type="text/javascript">  
  70.     $(document).ready(function () {  
  71.   
  72.         $("#SubmitForm").click(function (e) {  
  73.             e.preventDefault();  
  74.             $("#loader").show();  
  75.   
  76.             var model_data = {};  
  77.             model_data.CustName = $("#CustName").val();  
  78.             model_data.CustEmail = $("#CustEmail").val();  
  79.             model_data.CustAddress = $("#CustAddress").val();  
  80.             model_data.CustContact = $("#CustContact").val();  
  81.   
  82.             if (model_data.CustEmail != '') {  
  83.   
  84.                 $.ajax({  
  85.                     url: '/Customer/Create',  
  86.                     async: false,  
  87.                     type: "POST",  
  88.                     data: JSON.stringify(model_data),  
  89.                     dataType: "json",  
  90.                     contentType: "application/json; charset=utf-8",  
  91.                     success: function (data, textStatus, jqXHR) {  
  92.                         if (data.Message == 1) {  
  93.                             alert('Record Successfully Saved!');  
  94.                             window.location.href = '/Customer';  
  95.                         }  
  96.                         else {  
  97.                             alert('Record not Affected to DataBase!!');  
  98.                         }  
  99.   
  100.                         $("#loader").hide();  
  101.                     },  
  102.                     error: function (jqXHR, textStatus, errorThrown) {  
  103.                         alert(jqXHR + "-" + textStatus + "-" + errorThrown);  
  104.                     }  
  105.                 });  
  106.             }  
  107.             else {  
  108.                 return;  
  109.             }  
  110.         });  
  111.     });  
  112. </script>  
Edit.cshtml

In this view we have sent a JSON object to MVC Action Method and have your Action Method return you a JSON result.
  1. @model CRUD_MVC5.Models.tblCustomer  
  2.   
  3. @{  
  4.     ViewBag.Title = "Edit";  
  5. }  
  6.   
  7. <h2>Edit</h2>  
  8.   
  9. <div id="loader" style="text-align: center; display: none; margin-bottom: 20px;">  
  10.     <img alt="Loading" src="@Url.Content("~/Content/loader.gif")" /> Loading...  
  11. </div>  
  12.   
  13. @using (Html.BeginForm(nullnull, FormMethod.Post, new { @id = "CustomerForm", @class = "form" }))  
  14. {  
  15.     @Html.AntiForgeryToken()  
  16.       
  17.     <div class="form-horizontal">  
  18.         <h4>Edit Customer</h4>  
  19.         <hr />  
  20.         @Html.ValidationSummary(true""new { @class = "text-danger" })  
  21.         @Html.HiddenFor(model => model.CustID)  
  22.   
  23.         <div class="form-group">  
  24.             @Html.LabelFor(model => model.CustName, htmlAttributes: new { @class = "control-label col-md-2" })  
  25.             <div class="col-md-10">  
  26.                 @Html.EditorFor(model => model.CustName, new { htmlAttributes = new { @class = "form-control" } })  
  27.                 @Html.ValidationMessageFor(model => model.CustName, ""new { @class = "text-danger" })  
  28.             </div>  
  29.         </div>  
  30.   
  31.         @Html.HiddenFor(model => model.CustEmail)  
  32.   
  33.         <div class="form-group">  
  34.             @Html.LabelFor(model => model.CustAddress, htmlAttributes: new { @class = "control-label col-md-2" })  
  35.             <div class="col-md-10">  
  36.                 @Html.EditorFor(model => model.CustAddress, new { htmlAttributes = new { @class = "form-control" } })  
  37.                 @Html.ValidationMessageFor(model => model.CustAddress, ""new { @class = "text-danger" })  
  38.             </div>  
  39.         </div>  
  40.   
  41.         <div class="form-group">  
  42.             @Html.LabelFor(model => model.CustContact, htmlAttributes: new { @class = "control-label col-md-2" })  
  43.             <div class="col-md-10">  
  44.                 @Html.EditorFor(model => model.CustContact, new { htmlAttributes = new { @class = "form-control" } })  
  45.                 @Html.ValidationMessageFor(model => model.CustContact, ""new { @class = "text-danger" })  
  46.             </div>  
  47.         </div>  
  48.   
  49.         <div class="form-group">  
  50.             <div class="col-md-offset-2 col-md-10">  
  51.                 <input type="submit" value="Create" class="btn btn-default" id="SubmitForm" />  
  52.             </div>  
  53.         </div>  
  54.     </div>  
  55. }  
  56.   
  57. <div>  
  58.     @Html.ActionLink("Back to List""Index")  
  59. </div>  
  60.   
  61.   
  62. <script src="~/Scripts/jquery-1.10.2.min.js"></script>  
  63. <script src="~/Scripts/jquery.validate.min.js"></script>  
  64. <script type="text/javascript">  
  65.     $(document).ready(function () {  
  66.   
  67.         $("#SubmitForm").click(function (e) {  
  68.             e.preventDefault();  
  69.             $("#loader").show();  
  70.   
  71.             var model_data = {};  
  72.             model_data.CustID = $("#CustID").val();  
  73.             model_data.CustName = $("#CustName").val();  
  74.             model_data.CustEmail = $("#CustEmail").val();  
  75.             model_data.CustAddress = $("#CustAddress").val();  
  76.             model_data.CustContact = $("#CustContact").val();  
  77.   
  78.             if (model_data.CustEmail != '') {  
  79.   
  80.                 $.ajax({  
  81.                     url: '/Customer/Edit',  
  82.                     async: false,  
  83.                     type: "POST",  
  84.                     data: JSON.stringify(model_data),  
  85.                     dataType: "json",  
  86.                     contentType: "application/json; charset=utf-8",  
  87.                     success: function (data, textStatus, jqXHR) {  
  88.                         if (data.Message == 1) {  
  89.                             alert('Record Successfully Saved!');  
  90.                             window.location.href = '/Customer';  
  91.                         }  
  92.                         else {  
  93.                             alert('Record not Affected to DataBase!!');  
  94.                         }  
  95.   
  96.                         $("#loader").hide();  
  97.                     },  
  98.                     error: function (jqXHR, textStatus, errorThrown) {  
  99.                         alert(jqXHR + "-" + textStatus + "-" + errorThrown);  
  100.                     }  
  101.                 });  
  102.             }  
  103.             else {  
  104.                 return;  
  105.             }  
  106.         });  
  107.     });  
  108. </script>  
Let’s Start with Coding

In this app we are going to apply CRUD operation on a single table named Customer. To do first we need to create a Controller for the operations. To add a new Controller file we need to click right mouse and an option menu will appear. Click Add, then Controller.

add controller

Let’s name it CustomerController. In the controller we will create action methods to perform CRUD operations:

Below code sample is for Customer CRUD operations:
  1. // Customer Controller   
  2. public class CustomerController : Controller  
  3. {  
  4.     // GET: Customer  
  5.     public ActionResult Index()  
  6.     {  
  7.         return View();  
  8.     }  
  9.   
  10.     // GET: Customer  
  11.     [HttpGet]  
  12.     public ActionResult GetCustomer(int RowCountPerPage)  
  13.     {  
  14.         try  
  15.         {  
  16.             int PageNo = 0;  
  17.             int IsPaging = 0;  
  18.   
  19.             CrudDataService objCrd = new CrudDataService();  
  20.             List<tblCustomer> modelCust = objCrd.GetCustomerList(PageNo, RowCountPerPage, IsPaging);  
  21.             return PartialView("_ListCustomer", modelCust);  
  22.         }  
  23.         catch (Exception ex)  
  24.         {  
  25.             throw ex;  
  26.         }  
  27.     }  
  28.   
  29.     // GET: Customer/InfinitScroll  
  30.     [HttpGet]  
  31.     public ActionResult GetCustomer_Scroll(int PageNo, int RowCountPerPage)  
  32.     {  
  33.         try  
  34.         {  
  35.             Thread.Sleep(2000);  
  36.             int IsPaging = 1;  
  37.             CrudDataService objCrd = new CrudDataService();  
  38.             List<tblCustomer> modelCust = objCrd.GetCustomerList(PageNo, RowCountPerPage, IsPaging);  
  39.             return PartialView("_ListCustomer", modelCust);  
  40.         }  
  41.         catch (Exception ex)  
  42.         {  
  43.             throw ex;  
  44.         }  
  45.     }  
  46.   
  47.     // GET: Customer/Create  
  48.     public ActionResult Create()  
  49.     {  
  50.         return View();  
  51.     }  
  52.   
  53.     // GET: Customer/Create  
  54.     [HttpPost]  
  55.     public JsonResult Create(tblCustomer objCust)  
  56.     {  
  57.         try  
  58.         {  
  59.             CrudDataService objCrd = new CrudDataService();  
  60.             Int32 message = 0;  
  61.   
  62.             if ((objCust.CustName != null) && (objCust.CustEmail != null)) message = objCrd.InsertCustomer(objCust);  
  63.             else message = -1;  
  64.             return Json(new  
  65.             {  
  66.                 Success = true,  
  67.                 Message = message  
  68.             });  
  69.         }  
  70.         catch (Exception ex)  
  71.         {  
  72.             throw ex;  
  73.         }  
  74.     }  
  75.   
  76.   
  77.     // GET: Customer/Edit  
  78.     public ActionResult Edit()  
  79.     {  
  80.         return View();  
  81.     }  
  82.   
  83.     // GET: Customer/Edit  
  84.     [HttpGet]  
  85.     public ActionResult Edit(long? id)  
  86.     {  
  87.         try  
  88.         {  
  89.             CrudDataService objCrd = new CrudDataService();  
  90.             tblCustomer modelCust = objCrd.GetCustomerDetails(id);  
  91.             return View(modelCust);  
  92.         }  
  93.         catch (Exception ex)  
  94.         {  
  95.             throw ex;  
  96.         }  
  97.     }  
  98.   
  99.     // GET: Customer/Edit  
  100.     [HttpPost]  
  101.     public JsonResult Edit(tblCustomer objCust)  
  102.     {  
  103.         try  
  104.         {  
  105.             CrudDataService objCrd = new CrudDataService();  
  106.             Int32 message = 0;  
  107.             message = objCrd.UpdateCustomer(objCust);  
  108.             return Json(new  
  109.             {  
  110.                 Success = true,  
  111.                 Message = message  
  112.             });  
  113.   
  114.         }  
  115.         catch (Exception ex)  
  116.         {  
  117.             throw ex;  
  118.         }  
  119.     }  
  120.   
  121.   
  122.     // GET: Customer/Delete  
  123.     [HttpPost]  
  124.     public JsonResult Delete(long? id)  
  125.     {  
  126.         try  
  127.         {  
  128.             CrudDataService objCrd = new CrudDataService();  
  129.             Int32 message = 0;  
  130.             message = objCrd.DeleteCustomer(id);  
  131.             return Json(new  
  132.             {  
  133.                 Success = true,  
  134.                 Message = message  
  135.             });  
  136.   
  137.         }  
  138.         catch (Exception ex)  
  139.         {  
  140.             throw ex;  
  141.         }  
  142.     }  
  143. }  
As we know earlier that we will use ADO.NET, Stored Procedure, so to connect the database we need to modify our config file to add Connection String for database connection:
  1. <connectionStrings>  
  2.    <add name="dbConn" connectionString="Data source=DESKTOP-4L9DM2J; Initial Catalog=SampleDB; User Id=sa; Password=sa@123" providerName="System.Data.SqlClient"/>  
  3. </connectionStrings>  
Now we need to create another class to use connection string and open our database connection. Let’s name it dbConnector.
  1. // Database Connection  
  2. public class dbConnector  
  3. {  
  4.     private SqlConnection SqlConn = null;  
  5.   
  6.     public SqlConnection GetConnection  
  7.     {  
  8.         get { return SqlConn; }  
  9.         set { SqlConn = value; }  
  10.     }  
  11.   
  12.     public dbConnector()  
  13.     {  
  14.         string ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString;  
  15.         SqlConn = new SqlConnection(ConnectionString);  
  16.     }  
  17. }   
To perform CRUD operations we will create a separate class called CrudDataService. In this class we have five methods that will interact with the database to perform CRUD operations.
  1. // Database Service  
  2. public class CrudDataService  
  3. {  
  4.     public List<tblCustomer> GetCustomerList(int PageNo, int RowCountPerPage, int IsPaging)  
  5.     {  
  6.         dbConnector objConn = new dbConnector();  
  7.         SqlConnection Conn = objConn.GetConnection;  
  8.         Conn.Open();  
  9.   
  10.         try  
  11.         {  
  12.             List<tblCustomer> _listCustomer = new List<tblCustomer>();  
  13.             //_listCustomer = null;  
  14.   
  15.             if (Conn.State != System.Data.ConnectionState.Open)  
  16.                 Conn.Open();  
  17.   
  18.             SqlCommand objCommand = new SqlCommand("READ_CUSTOMER", Conn);  
  19.             objCommand.CommandType = CommandType.StoredProcedure;  
  20.             objCommand.Parameters.AddWithValue("@PageNo", PageNo);  
  21.             objCommand.Parameters.AddWithValue("@RowCountPerPage", RowCountPerPage);  
  22.             objCommand.Parameters.AddWithValue("@IsPaging", IsPaging);  
  23.             SqlDataReader _Reader = objCommand.ExecuteReader();  
  24.   
  25.             while (_Reader.Read())  
  26.             {  
  27.                 tblCustomer objCust = new tblCustomer();  
  28.                 objCust.CustID = Convert.ToInt32(_Reader["CustID"]);  
  29.                 objCust.CustName = _Reader["CustName"].ToString();  
  30.                 objCust.CustEmail = _Reader["CustEmail"].ToString();  
  31.                 objCust.CustAddress = _Reader["CustAddress"].ToString();  
  32.                 objCust.CustContact = _Reader["CustContact"].ToString();  
  33.                 _listCustomer.Add(objCust);  
  34.   
  35.             }  
  36.   
  37.             return _listCustomer;  
  38.         }  
  39.         catch (Exception ex)  
  40.         {  
  41.             throw ex;  
  42.         }  
  43.         finally  
  44.         {  
  45.             if (Conn != null)  
  46.             {  
  47.                 if (Conn.State == ConnectionState.Open)  
  48.                 {  
  49.                     Conn.Close();  
  50.                     Conn.Dispose();  
  51.                 }  
  52.             }  
  53.         }  
  54.     }  
  55.   
  56.     public tblCustomer GetCustomerDetails(long? id)  
  57.     {  
  58.   
  59.         dbConnector objConn = new dbConnector();  
  60.         SqlConnection Conn = objConn.GetConnection;  
  61.         Conn.Open();  
  62.   
  63.         try  
  64.         {  
  65.             tblCustomer objCust = new tblCustomer();  
  66.   
  67.             if (Conn.State != System.Data.ConnectionState.Open)  
  68.                 Conn.Open();  
  69.   
  70.             SqlCommand objCommand = new SqlCommand("VIEW_CUSTOMER", Conn);  
  71.             objCommand.CommandType = CommandType.StoredProcedure;  
  72.             objCommand.Parameters.AddWithValue("@CustID", id);  
  73.             SqlDataReader _Reader = objCommand.ExecuteReader();  
  74.   
  75.             while (_Reader.Read())  
  76.             {  
  77.                 objCust.CustID = Convert.ToInt32(_Reader["CustID"]);  
  78.                 objCust.CustName = _Reader["CustName"].ToString();  
  79.                 objCust.CustEmail = _Reader["CustEmail"].ToString();  
  80.                 objCust.CustAddress = _Reader["CustAddress"].ToString();  
  81.                 objCust.CustContact = _Reader["CustContact"].ToString();  
  82.             }  
  83.   
  84.             return objCust;  
  85.         }  
  86.         catch (Exception ex)  
  87.         {  
  88.             throw ex;  
  89.         }  
  90.         finally  
  91.         {  
  92.             if (Conn != null)  
  93.             {  
  94.                 if (Conn.State == ConnectionState.Open)  
  95.                 {  
  96.                     Conn.Close();  
  97.                     Conn.Dispose();  
  98.                 }  
  99.             }  
  100.         }  
  101.     }  
  102.   
  103.     public Int32 InsertCustomer(tblCustomer objCust)  
  104.     {  
  105.         dbConnector objConn = new dbConnector();  
  106.         SqlConnection Conn = objConn.GetConnection;  
  107.         Conn.Open();  
  108.   
  109.         int result = 0;  
  110.   
  111.         try  
  112.         {  
  113.             if (Conn.State != System.Data.ConnectionState.Open)  
  114.                 Conn.Open();  
  115.   
  116.             SqlCommand objCommand = new SqlCommand("CREATE_CUSTOMER", Conn);  
  117.             objCommand.CommandType = CommandType.StoredProcedure;  
  118.             objCommand.Parameters.AddWithValue("@CustName", objCust.CustName);  
  119.             objCommand.Parameters.AddWithValue("@CustEmail", objCust.CustEmail);  
  120.             objCommand.Parameters.AddWithValue("@CustAddress", objCust.CustAddress);  
  121.             objCommand.Parameters.AddWithValue("@CustContact", objCust.CustContact);  
  122.   
  123.             result = Convert.ToInt32(objCommand.ExecuteScalar());  
  124.   
  125.             if (result > 0)  
  126.             {  
  127.                 return result;  
  128.             }  
  129.             else  
  130.             {  
  131.                 return 0;  
  132.             }  
  133.         }  
  134.         catch (Exception ex)  
  135.         {  
  136.             throw ex;  
  137.         }  
  138.         finally  
  139.         {  
  140.             if (Conn != null)  
  141.             {  
  142.                 if (Conn.State == ConnectionState.Open)  
  143.                 {  
  144.                     Conn.Close();  
  145.                     Conn.Dispose();  
  146.                 }  
  147.             }  
  148.         }  
  149.     }  
  150.   
  151.     public Int32 UpdateCustomer(tblCustomer objCust)  
  152.     {  
  153.         dbConnector objConn = new dbConnector();  
  154.         SqlConnection Conn = objConn.GetConnection;  
  155.         Conn.Open();  
  156.   
  157.         int result = 0;  
  158.   
  159.         try  
  160.         {  
  161.             if (Conn.State != System.Data.ConnectionState.Open)  
  162.                 Conn.Open();  
  163.   
  164.             SqlCommand objCommand = new SqlCommand("UPDATE_CUSTOMER", Conn);  
  165.             objCommand.CommandType = CommandType.StoredProcedure;  
  166.             objCommand.Parameters.AddWithValue("@CustID", objCust.CustID);  
  167.             objCommand.Parameters.AddWithValue("@CustName", objCust.CustName);  
  168.             objCommand.Parameters.AddWithValue("@CustEmail", objCust.CustEmail);  
  169.             objCommand.Parameters.AddWithValue("@CustAddress", objCust.CustAddress);  
  170.             objCommand.Parameters.AddWithValue("@CustContact", objCust.CustContact);  
  171.   
  172.             result = Convert.ToInt32(objCommand.ExecuteScalar());  
  173.   
  174.             if (result > 0)  
  175.             {  
  176.                 return result;  
  177.             }  
  178.             else  
  179.             {  
  180.                 return 0;  
  181.             }  
  182.         }  
  183.         catch (Exception ex)  
  184.         {  
  185.             throw ex;  
  186.         }  
  187.         finally  
  188.         {  
  189.             if (Conn != null)  
  190.             {  
  191.                 if (Conn.State == ConnectionState.Open)  
  192.                 {  
  193.                     Conn.Close();  
  194.                     Conn.Dispose();  
  195.                 }  
  196.             }  
  197.         }  
  198.     }  
  199.   
  200.     public Int32 DeleteCustomer(long? id)  
  201.     {  
  202.         dbConnector objConn = new dbConnector();  
  203.         SqlConnection Conn = objConn.GetConnection;  
  204.         Conn.Open();  
  205.   
  206.         int result = 0;  
  207.   
  208.         try  
  209.         {  
  210.             if (Conn.State != System.Data.ConnectionState.Open)  
  211.                 Conn.Open();  
  212.   
  213.             SqlCommand objCommand = new SqlCommand("DELETE_CUSTOMER", Conn);  
  214.             objCommand.CommandType = CommandType.StoredProcedure;  
  215.             objCommand.Parameters.AddWithValue("@CustID", id);  
  216.             result = Convert.ToInt32(objCommand.ExecuteScalar());  
  217.   
  218.             if (result > 0)  
  219.             {  
  220.                 return result;  
  221.             }  
  222.             else  
  223.             {  
  224.                 return 0;  
  225.             }  
  226.         }  
  227.         catch (Exception ex)  
  228.         {  
  229.             throw ex;  
  230.         }  
  231.         finally  
  232.         {  
  233.             if (Conn != null)  
  234.             {  
  235.                 if (Conn.State == ConnectionState.Open)  
  236.                 {  
  237.                     Conn.Close();  
  238.                     Conn.Dispose();  
  239.                 }  
  240.             }  
  241.         }  
  242.     }  
  243. }  
Note: This is a very basic app to demonstrate the process who are still confused how to start, my goal was to make it simple. 


Similar Articles