Bulk Data Insertion in a Single Click in ASPMVC

INSERTING LIST OF RECORDS IN A SiNGLE CLICK

Managing a huge number of database records related to a particular entity is very difficult in today's modern applications. For example,one customer can purchase multiple types of items, so in this case case the customer is a single entity and the items he is purchasing are different entities having multiple records with dynamic entries where we don't know the count. So in this scenario it's very difficult to manage the database table structure, so this  article is related to Solve this issue -

So, let's learn step by step so beginners can also learn how to Insert Bulk Record in A Single Click

Step 1: Create an MVC Application.

Now let us start with a step by step approach from the creation of a simple MVC application as in the following:

1. create a New ASPMVC4 Application Named "MVCApplication7"
2. Go t File->New->Project->ASP.NET MVC4 Web Application .Then Provide the Project Name as you wish.After clicking, the following window will appear :

3. As shown in the preceding screenshot, click on the Basic template, then click OK. This will create an empty MVC web application.

Step 2: Add The Reference of Dapper ORM into Project.

Now the next step is to add the reference of Dapper ORM into our created MVC Project. Here are the steps:

        Right click on Solution, find Manage NuGet Package manager and click on it.

1. After as shown in the image and type in search box "dapper".
2. Select Dapper as shown in the image.
3. Choose the version of the dapper library and click on install button.
4. 
   

After installing the Dapper library, it will be added into the References of our solution explorer of MVC application such as:

Step 3: Create Model Class.
Now let's create the model class named ProductModel.cs by right-clicking on model folder  and 

also Create one More Class To Map Customers With  Ordered Product - EmployeeModel.cs

Go to Solution Explorer -> Right Click on Models Folder ->Add -> Class ->Write the Name of class “ProductModel.cs”.

 Note: 
It is not mandatory that Model class should be in Model folder, it is just for better readability you can create this class anywhere in the solution explorer. This can be done by creating different folder names or without the folder name or in a separate class library.

1. using System;  
2. using System.Collections.Generic;  
3. using System.ComponentModel.DataAnnotations;  
4.   
5. namespace MVCApplication7.Models  
6. {  
7.     public class EmployeeModel  
8.       { 
9.         public int ID { get; set; }  
10.         public string Name { get; set; }  
11.         public int  Age { get; set; }  
12.         public List<ProductModel>Products { get; set; }    
13.     }  
14.     public class ProductModel  
15.     {  
16.          public int ID{get;set;}
17.          public string ProductName{get;set;}
18.          public int ProductCost{ get; set; }  
19. 
    
20.     }  
21. } 

Step 4 : Create Controller.

Now let us add the MVC 5 controller "HomeController"

Go to Solution Explorer -> Right Click on Controllers Folder ->Add -> Controller  ->Write the Name of Controller “HomeController”.

Note: The controller name must have the suffix as 'Controller' after specifying the name of controller. 

Create Table and Stored procedure.

Step 5 : Now before creating the views let us create the table name customers , CustomerProduct in database to store the order details:

I hope you have created the same table structure as shown above. Now create the stored procedures to insert the order details as in the following code snippet:

1-Here I am Creating AddCustomer Procedure to Add Customer Record in Customers Table

1. Create procedure AddCustmer  
2. (  
3. @Name varchar(50),  
4. @Age  int,
5. @ID int =null output  
6. as  
7. Begin  
8.   
9. INSERT INTO [dbo].[Customers]  
10.            (  
11.              Name,Age  
12.            )  
13.      VALUES  
14.            (  
15.           @Name,  
16.           @Age  
17.            )
18. set @ID=SCOPE_IDENTITY()  
19. return @id
20. End  

2-Here I am Creating AddCustomer Procedure to Add Products  Record in  Customers Table

1. Create procedure AddCustomerProduct  
2. (  
3. @Name varchar(50),  
4. @Cost  int,
5. @ID int =null  
6. as  
7. Begin  
8.   
9. INSERT INTO [dbo].[CustomerProduct]  
10.            (  
11.              ID,ProductName,ProductCost
12.            )  
13.      VALUES  
14.            (  
15.           @ID,
16.           @Name,  
17.           @Cost  
18.            )
19. End  

Step 6: Create Repository class.

Go to Solution Explorer -> Right Click on Models Folder ->Add -> Class ->Write the Name of Class “DataLayer”.

1. using System;
2. using System.Collections.Generic;
3. using System.Linq;
4. using System.Web;
5. using System.Data.SqlClient;
6. using System.Configuration;
7. using Dapper;
8. using System.Data;
9. using System.Web.Mvc;
10. 
    
11. namespace MvcApplication7.Models
12. {
13.     public class DataLayer
14.     {
15.         SqlConnection Con;  
16. 
    
17.         public DataLayer()
18.         {
19.             Connectin();
20.         }
21. 
    
22.         private void Connectin()
23.         {
24. Con = new SqlConnection("Data Source=Dhananjay\SqlExpress;User Id=sa;Password=1;Initial Catalog=Dhananjay");
25.             if (Con.State == System.Data.ConnectionState.Open)
26.             {
27.                 Con.Close();
28.             }
29.             Con.Open();
30.         }
31. 
    
32.         
33. public bool AddCustomer(EmployeeModel E)
34.         {
35.             try
36.             {
37.                 DynamicParameters Parm = new DynamicParameters();
38.                 Parm.Add("@Name", E.Name);
39.                 Parm.Add("@Age", E.Age);
40.                 Parm.Add("@id", dbType: DbType.Int32, direction: ParameterDirection.Output, size: 5215585);
41.                 Con.Execute("AddCustmer", Parm, commandType: System.Data.CommandType.StoredProcedure);
42.                 var ProductID = Parm.Get<Int32>("@id"); 
43.                 foreach(var p in E.Products)
44.                 {
45.                     DynamicParameters Parm2 = new DynamicParameters();
46.                     Parm2.Add("@ID", ProductID);
47.                     Parm2.Add("@Name", p.ProductName);
48.                     Parm2.Add("@Cost", p.ProductCost);
49.                     Con.Execute("ADdCustomerProduct", Parm2, commandType: System.Data.CommandType.StoredProcedure);
50.                 }
51.                 return true;
52. 
    
53.             }
54.             catch (Exception c) { return false; }
55.         }
56. }
57. }

Step 7: Create Method into the HomeController.cs file.

Now open the HomeController.cs and create the following action methods:

1. using System.Collections.Generic;  
2. using System.Web.Mvc;   
3. using System;  
4. using System.Linq;
5. using System.Web;
6. using MvcApplicatin7.Models;  
7.   
8. namespace MvcApplication7.Controllers  
9. {  
10.     public class CustomerController : Controller  
11.     {  
12.   
13.         // GET: Customer  
14.         public ActionResult AddProduct()
15.         {  
16.             return View();  
17.         }  
18.         [HttpPost]  
19.         public ActionResult AddProduct(EmployeeModel E)  
20.         {  
21.            return View(new DataLayer().AddCustomer(E));
22.         } 
23.     }  
24. }  

Step 8 : Create view named AddProduct 

2. @{
3.     ViewBag.Title = "AddProduct";
4. }
5. 
   
6. <h2>AddProduct</h2>
7. 
   
8. <script src="~/Scripts/jquery-1.7.1.min.js"></script>
9. <script>
10.     $(document).ready(function () {
11.         $('input[type="text"].num').bind('keydown', function (e) {
12.             // Allow: backspace, delete, tab, escape, enter and .
13.             if ($.inArray(e.keyCode, [46, 8, 9, 27, 13, 110, 190]) !== -1 ||
14.                 // Allow: Ctrl+A
15.                 (e.keyCode == 65 && e.ctrlKey === true) ||
16.                 // Allow: Ctrl+C
17.                 (e.keyCode == 67 && e.ctrlKey === true) ||
18.                 // Allow: Ctrl+X
19.                 (e.keyCode == 88 && e.ctrlKey === true) ||
20.                 // Allow: home, end, left, right
21.                 (e.keyCode >= 35 && e.keyCode <= 39)) {
22.                 // let it happen, don't do anything
23.                 return;
24.             }
25.             if ((e.shiftKey || (e.keyCode < 48 || e.keyCode > 57)) && (e.keyCode < 96 || e.keyCode > 105)) {
26.                 e.preventDefault();
27.             }
28. 
    
29.         });
30.     });
31.         function AddMore() {
32.             $("#Products tr:last").after('<tr det="Product"><td><input type="text"></td><td><input type="text"></td></tr>');
33.         }
34.         function Add() {
35.             debugger;
36.             var OrderedProducts = [];
37.             var isOk = true;
38.             $('#Products tr[det="Product"]').each(function () {
39.                 var Product = $($(this).find('input[type="text"]')[0]).val();
40.                 var Cost = parseFloat($($(this).find('input[type="text"]')[1]).val());
41.                 OrderedProducts.push({
42.                     ProductName: Product,
43.                     ProductCost: parseInt(Cost),
44.                 });
45.             })
46.             if (!isOk) {
47.                 return;
48.             }
49. $('input[type^="text"]').each(function () {
50.                 if ($(this).val() == '') {
51.                     alert('Please Enter ' + $(this).parent().prev().text());
52.                     $(this).focus();
53.                     isOk = false;
54.                     return false;
55.                 }
56.             });
57.             if (!isOk) {
58.                 return;
59.             }
60.             var Order =
61.                     {
62.                         PurchaseOrderNo: $('#txtPONo').val(),
63.                         Name: $('#UName').val(),
64.                         Age: $('#UAge').val(),
65.                         Products: OrderedProducts
66.                     };
67.             debugger;
68.             $.ajax({
69.                 type: "POST",
70.                 dataType: 'text',
71.                 contentType: 'application/json; charset=utf-8',
72.                 url: "/Home/AddProduct",
73.                 data: JSON.stringify(Order),
74.                 success: function (res) {
75.                     if (res == "True") {
76.                         alert('Information Saved Successfully!');
77.                         window.location.reload();
78.                     }
79.                     else {
80.                         alert('Some Error occurred!');
81.                     }
82.                 },
83.                 error: function (xhr, textStatus, errorThrown) {
84.                     alert('Some Error occurred!');
85.                 }
86.             });
87.         }
88. </script>
89. <table>
90.     <tr>
91.         <td>Name</td>
92.         <td><input type="text" id="UName" /></td>
93.     </tr>
94.     <tr>
95.         <td>Age</td>
96.         <td><input type="text" id="UAge" class="num" /></td>
97.     </tr>
98. </table>
99. <table id="Products">
100.     <tr>
101.     <th>Product Name</th>
102.     <th>Product Cost</th>
103.         <th></th>
104.     </tr>
105.     <tr det="Product">
106.         <td><input type="text" /></td>
107.         <td><input type="text" class="num" /></td>
108.     </tr>
109. </table>
110. <input type="button" onclick="AddMore()" value="Add" />
111. <input type="button" value="Click" onclick="Add()" />

Step 09: Now run the application