Inserting List of Records into Single SQL Column As XML File In ASP.NET MVC

Inserting List of Records into Single SQL Column As XML File In ASP.NET MVC

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 we can solve this problem by converting these types of lists into the XML files and later on we can store created XML files into a SQL table single column by defining column data types as XML.

So, let's learn step by step so beginners can also learn how to convert generic lists into the XML file in ASP.NET MVC.

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 "ConvertGenericListIntoXMLInMVC"
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:

1. Right click on Solution, find Manage NuGet Package manager and click on it.
2. After as shown in the image and type in search box "dapper".
3. Select Dapper as shown in the image.
4. Choose the version of the dapper library and click on install button.
5. 
   

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 CustomerOrderModel.cs by right-clicking on model folder

Go to Solution Explorer -> Right Click on Models Folder ->Add -> Class ->Write the Name of class “CustomerOrderModel.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.

CustomerOrderModel.cs class code snippet:

1. using System;  
2. using System.Collections.Generic;  
3. using System.ComponentModel.DataAnnotations;  
4.   
5. namespace ConvertGenericListIntoXMLInMVC.Models  
6. {  
7.     public class CustomerOrderModel  
8.     {  
9.         [Display(Name="Item Code")]  
10.         public string ItemCode { get; set; }  
11.         [Display(Name = "Product Name")]  
12.         public string ProductName { get; set; }  
13.         [Display(Name = "Quantity")]  
14.         public Int16 Qty { get; set; }  
15.         public double Price { get; set; }  
16.         [Display(Name = "Total Amount")]  
17.         public double TotalAmount { get; set; }  
18.     }  
19.     public class CustomerOrder  
20.     {  
21.         //using CustomerorderModel class as generic list to add multiple orders in list  
22.         public List<CustomerOrderModel> OrderDetails { get; set; }  
23. 
    
24.     }  
25. }  

Step 4 : Create Controller.

Now let us add the MVC 5 controller "CustomerController"

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

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 CustomerOrderDetails 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. Create procedure PlaceOrderDetails  
2. (  
3. @Customer_Name varchar(50),  
4. @OrderDetails varchar(max)  
5. )  
6. as  
7. Begin  
8.   
9. INSERT INTO [dbo].[CustomerOrderDetails]  
10.            (  
11.            Customer_Name  
12.            ,OrderDetails  
13.            )  
14.      VALUES  
15.            (  
16.           @Customer_Name,  
17.           @OrderDetails  
18.              
19.            )  
20.   
21. End  
22. 
    

Step 6: Create Repository class.

Now Add CustomerOrderRepo.cs class for database related operations in Models Folder . 

1. public class CustomerOrderRepo  
2.    {  
3.        SqlConnection con;  
4.        //To Handle connection related activities  
5.        private void connection()  
6.        {  
7.            string constr = ConfigurationManager.ConnectionStrings["SqlConn"].ToString();  
8.            con = new SqlConnection(constr);  
9.        }  
10.        //Place order details  
11.        public void PlaceOrderDetails(CustomerOrder Order)  
12.        {  
13.   
14.            //Converting List to XML using LINQ to XML  
15.            XDocument OrderDetails = new XDocument(new XDeclaration("1.0", "UTF - 8", "yes"),  
16.            new XElement("CustomerOrder",  
17.            from OrderDet in Order.OrderDetails  
18.            select new XElement("OrderDetails",  
19.            new XElement("ItemCode", OrderDet.ItemCode),  
20.            new XElement("ProductName", OrderDet.ProductName),  
21.            new XElement("Qty", OrderDet.Qty),  
22.            new XElement("Price", OrderDet.Price),  
23.            new XElement("TotalAmount", OrderDet.TotalAmount))));  
24. 
    
25.            //Consider customer name we are getting from current login  
26.            string CustomerName = "Dhananjay Shukla";  
27.   
28.            DynamicParameters ObjParm = new DynamicParameters();  
29.            ObjParm.Add("@Customer_Name", CustomerName);  
30.            ObjParm.Add("@OrderDetails", OrderDetails.ToString());  
31.            connection();  
32.            con.Open();  
33.            con.Execute("PlaceOrderDetails", ObjParm,commandType:CommandType.StoredProcedure);  
34.            con.Close();  
35.        }  
36.    }  

Note

1. In the above code we are manually opening and closing connection, however you can directly pass the connection string to the dapper without opening it. Dapper will automatically handle it.

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

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

1. using System.Collections.Generic;  
2. using System.Web.Mvc;  
3. using InsertingListAsXML.Repository;  
4. using System;  
5. using InsertingListAsXML.Models;  
6.   
7. namespace InsertingListAsXML.Controllers  
8. {  
9.     public class CustomerController : Controller  
10.     {  
11.   
12.         //Adding records into list ,you can populate this list from database.  
13.         List<CustomerOrderModel> objOrder = new List<CustomerOrderModel>()  
14.             {  
15.  new CustomerOrderModel {ItemCode="MO12",ProductName="Mouse",Qty=1,Price=150.00,TotalAmount=150.00 },  
16. new CustomerOrderModel {ItemCode="CO11",ProductName="Cable",Qty=2,Price=250.00,TotalAmount=500.00 },  
17. new CustomerOrderModel {ItemCode="KO67",ProductName="KeyBoard",Qty=3,Price=500.00,TotalAmount=1500.00 },  
18. new CustomerOrderModel {ItemCode="PO55",ProductName="PenDrive",Qty=1,Price=200.00,TotalAmount=200.00 }  
19.             };  
20.   
21.         // GET: Customer  
22.         public ActionResult PlaceOrderDetails()  
23.         {  
24.            
25.             CustomerOrder ObjOrderDetails = new CustomerOrder();  
26.             //Assigning list of records to CustomerOrder generic list   
27.             ObjOrderDetails.OrderDetails = objOrder;  
28.             return View(ObjOrderDetails);  
29.         }  
30.         [HttpPost]  
31.         public ActionResult PlaceOrderDetails(CustomerOrder Order)  
32.         {  
33.             try  
34.             {  
35.                 CustomerOrderRepo objOrder = new CustomerOrderRepo();  
36.   
37.                 objOrder.PlaceOrderDetails(Order);  
38.   
39.                 ViewBag.OrderStatus = "Your order placed successfully.";  
40.             }  
41.             catch (Exception)  
42.             {  
43.   
44.                 ViewBag.OrderStatus= "Problem while processing order.";  
45.             }  
46.             CustomerOrder ObjOrderDetails = new CustomerOrder();  
47.             //Assigning list of records to CustomerOrder generic list   
48.             ObjOrderDetails.OrderDetails = objOrder;  
49.             return View(ObjOrderDetails);  
50.   
51.         }  
52.   
53.     }  
54. }  

 Step 8 : Create strongly typed view named PlaceOrderDetails using CustomerOrder.cs class .

Right click on View folder of created application and choose add view , select CustomerOrder class and scaffolding List template to create view as

Click on Add button then it will create the view named PlaceOrderdetails. Now open the PlaceOrderdetails.cshtml view, then the following default code you will see which is generated by MVC scaffolding template as,

PlaceOrderdetails.cshtml 

1. @model ConvertGenericListIntoXMLInMVC.Models.CustomerOrder  
2.   
3. @{  
4.     ViewBag.Title = "www.compilemode.com";  
5. }  
6.   
7. <h3>Place Order Details</h3>  
8. <hr />  
9. @using (Html.BeginForm("PlaceOrderDetails", "Customer"))  
10. {  
11.     <div class="form-horizontal">  
12.         <div class="form-group">  
13.             <div class="col-md-12">  
14.                 <table class="table table-condensed table-hover">  
15.                     <tr>  
16.                         <th>  
17.                             Item Code  
18.                         </th>  
19.                         <th>  
20.                             Product Name  
21.                         </th>  
22.                         <th>  
23.                             Quantity  
24.                         </th>  
25.                         <th>  
26.                             Price  
27.                         </th>  
28.                         <th>  
29.                             Total Amount  
30.                         </th>  
31.                     </tr>  
32.                     @{  
33.                         if (Model.OrderDetails.ToList()!=null)  
34.                         {  
35.                         int i = 0;  
36.                         foreach (var item in Model.OrderDetails.ToList())  
37.                         {  
38.                             <tr>  
39.                                 <td>  
40.   @Html.EditorFor(o => o.OrderDetails[i].ItemCode, new { @id = "ItemCode_" + i })  
41.                                 </td>  
42.                                 <td>  
43.   @Html.EditorFor(o => o.OrderDetails[i].ProductName, new { @id = "ProductName_" + i })  
44.                                 </td>  
45.                                 <td>  
46.   @Html.EditorFor(o => o.OrderDetails[i].Qty, new { @id = "Qty_" + i })  
47.                                 </td>  
48.                                 <td>  
49.   @Html.EditorFor(o => o.OrderDetails[i].Price, new { @id = "Price_" + i })  
50.                                 </td>  
51.                                 <td>  
52.   @Html.EditorFor(o => o.OrderDetails[i].TotalAmount, new { @id = "Price_" + i })  
53.                                 </td>  
54.                             </tr>  
55.                                 i++;  
56.                             }  
57.                         }  
58.                     }  
59.                 </table>  
60.             </div>  
61.         </div>  
62.         <hr />  
63.         <div class="form-group">  
64.             <div class="col-md-12 text-center">  
65.                 <input type="submit" value="Order Now" class="btn btn-primary" />  
66.             </div>  
67.         </div>  
68.   
69.         <div class="form-group">  
70.             <div class="col-md-12 text-center text-success" >  
71.                @ViewBag.OrderStatus  
72.             </div>  
73.         </div>  
74.   
75.   
76.     </div>  
77.  }  

Step 9 : Add Connection String in Web.config File:

 <add name="SqlConn" connectionString="Data Source=DHANANJAY/SQLExpress;User Id=sa;Password=1;Initial Catalog=Demo" providerName="System.Data.SqlClient" />

 Now we have done all coding to upload files .

Step 10 : Now run the application.

After running the application the initial screen will look like as follows,