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

Background

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, 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 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 XML files in ASP.NET MVC.

Step 1. Create an MVC Application.

Now let us start with a step-by-step approach to the creation of a simple MVC application as in the following.

1. "Start", then "All Programs" and select "Microsoft Visual Studio 2015".
2. "File", then "New" and click "Project", then select "ASP.NET Web Application Template", then provide the Project a name as you wish and click OK. After clicking, the following window will appear.
   
3. As shown in the preceding screenshot, click on Empty template and check the MVC option, then click OK. This will create an empty MVC web application.

Step 2. Add The Reference of Dapper ORM into the 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 type in the search box "dapper".
3. Select Dapper as shown in the image.
4. Choose the version of a dapper library and click on the install button.
   
5. After installing the Dapper library, it will be added to the References of our solution explorer of MVC applications such as.
   
6. If you want to learn how to install the correct Dapper library, watch my video tutorial using the following link,

• Installing Dapper ORM Into MVC 5 Project

I hope you have followed the same steps and installed Dapper Library.

Step 3. Create Model Class.

Now let's create the model class named CustomerOrderModel.cs by right-clicking on the model folder as in the following screenshot.

Note. It is not mandatory that the Model class should be in the 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 without the folder name or in a separate class library.

CustomerOrderModel.cs class code snippet

using System;  
using System.Collections.Generic;  
using System.ComponentModel.DataAnnotations;  

namespace ConvertGenericListIntoXMLInMVC.Models  
{  
    public class CustomerOrderModel  
    {  
        [Display(Name="Item Code")]  
        public string ItemCode { get; set; }  
        [Display(Name = "Product Name")]  
        public string ProductName { get; set; }  
        [Display(Name = "Quantity")]  
        public Int16 Qty { get; set; }  
        public double Price { get; set; }  
        [Display(Name = "Total Amount")]  
        public double TotalAmount { get; set; }  
    }  
    public class CustomerOrder  
    {  
        //using CustomerorderModel class as generic list to add multiple orders in list   
  
        public List<CustomerOrderModel> OrderDetails { get; set; }  
  
    }  
} 

Step 4. Create Controller.

Now let us add the MVC 5 controller as in the following screenshot.

After clicking on the Add button it will show the window. Specify the Controller name as Customer with the suffix Controller.

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

Step 5. Create a Table and Store the procedure.

Now before creating the views let us create the table name CustomerOrderDetails in the 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.

Create procedure PlaceOrderDetails   
(   
@Customer_Name varchar(50),   
@OrderDetails varchar(max)   
)   
as   
Begin   

INSERT INTO [dbo].[CustomerOrderDetails]   
           (   
           Customer_Name   
           ,OrderDetails   
           )   
     VALUES   
           (   
          @Customer_Name,   
          @OrderDetails   
             
           )   

End 

Run the above script in SQL it will generate the stored procedure to insert the order details into the database.

Step 6. Create a Repository class.

Now create a Repository folder and Add CustomerOrderRepo.cs class for database-related operations. Now create a method in CustomerOrderRepo.cs to get the output parameter value from the stored procedure as in the following code snippet

public class CustomerOrderRepo   
{   
    SqlConnection con;   
    //To Handle connection related activities   
    private void connection()   
    {   
        string constr = ConfigurationManager.ConnectionStrings["SqlConn"].ToString();   
        con = new SqlConnection(constr);   
    }   
    //Place order details   
    public void PlaceOrderDetails(CustomerOrder Order)   
    {   
        //Converting List to XML using LINQ to XML   
        XDocument OrderDetails = new XDocument(new XDeclaration("1.0", "UTF - 8", "yes"),   
        new XElement("CustomerOrder",   
        from OrderDet in Order.OrderDetails   
        select new XElement("OrderDetails",   
        new XElement("ItemCode", OrderDet.ItemCode),   
        new XElement("ProductName", OrderDet.ProductName),   
        new XElement("Qty", OrderDet.Qty),   
        new XElement("Price", OrderDet.Price),   
        new XElement("TotalAmount", OrderDet.TotalAmount))));   

        //Consider customer name we are getting from current login   
        string CustomerName = "Vithal Wadje";   

        DynamicParameters ObjParm = new DynamicParameters();   
        ObjParm.Add("@Customer_Name", CustomerName);   
        ObjParm.Add("@OrderDetails", OrderDetails.ToString());   
        connection();   
        con.Open();   
        con.Execute("PlaceOrderDetails", ObjParm, commandType: CommandType.StoredProcedure);   
        con.Close();   
    }   
} 

Note. In the above code, we are manually opening and closing the 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.

using System.Collections.Generic;  
using System.Web.Mvc;  
using InsertingListAsXML.Repository;  
using System;  
using InsertingListAsXML.Models;  

namespace InsertingListAsXML.Controllers  
{  
    public class CustomerController : Controller  
    {  

        //Adding records into list ,you can populate this list from database.  
        List<CustomerOrderModel> objOrder = new List<CustomerOrderModel>()  
        {  
            new CustomerOrderModel {ItemCode="MO12",ProductName="Mouse",Qty=1,Price=150.00,TotalAmount=150.00 },  
            new CustomerOrderModel {ItemCode="CO11",ProductName="Cable",Qty=2,Price=250.00,TotalAmount=500.00 },  
            new CustomerOrderModel {ItemCode="KO67",ProductName="KeyBoard",Qty=3,Price=500.00,TotalAmount=1500.00 },  
            new CustomerOrderModel {ItemCode="PO55",ProductName="PenDrive",Qty=1,Price=200.00,TotalAmount=200.00 }  
        };  

        // GET: Customer  
        public ActionResult PlaceOrderDetails()  
        {  
           
            CustomerOrder ObjOrderDetails = new CustomerOrder();  
            //Assigning list of records to CustomerOrder generic list   
            ObjOrderDetails.OrderDetails = objOrder;  
            return View(ObjOrderDetails);  
        }  
        [HttpPost]  
        public ActionResult PlaceOrderDetails(CustomerOrder Order)  
        {  
            try  
            {  
                CustomerOrderRepo objOrder = new CustomerOrderRepo();  

                objOrder.PlaceOrderDetails(Order);  

                ViewBag.OrderStatus = "Your order placed successfully.";  
            }  
            catch (Exception)  
            {  

                ViewBag.OrderStatus= "Problem while processing order.";  
            }  
            CustomerOrder ObjOrderDetails = new CustomerOrder();  
            //Assigning list of records to CustomerOrder generic list   
            ObjOrderDetails.OrderDetails = objOrder;  
            return View(ObjOrderDetails);  

        }  

    }  
}  

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

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

Click on the 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 the MVC scaffolding template as,

PlaceOrderdetails.cshtml

@model ConvertGenericListIntoXMLInMVC.Models.CustomerOrder   

@{   
    ViewBag.Title = "www.compilemode.com";   
}   

<h3>Place Order Details</h3>   
<hr />   
@using (Html.BeginForm("PlaceOrderDetails", "Customer"))   
{   
    <div class="form-horizontal">   
        <div class="form-group">   
            <div class="col-md-12">   
                <table class="table table-condensed table-hover">   
                    <tr>   
                        <th>   
                            Item Code   
                        </th>   
                        <th>   
                            Product Name   
                        </th>   
                        <th>   
                            Quantity   
                        </th>   
                        <th>   
                            Price   
                        </th>   
                        <th>   
                            Total Amount   
                        </th>   
                    </tr>   
                    @{   
                        if (Model.OrderDetails.ToList() != null)   
                        {   
                            int i = 0;   
                            foreach (var item in Model.OrderDetails.ToList())   
                            {   
                    <tr>   
                        <td>   
                            @Html.EditorFor(o => o.OrderDetails[i].ItemCode, new { @id = "ItemCode_" + i })   
                        </td>   
                        <td>   
                            @Html.EditorFor(o => o.OrderDetails[i].ProductName, new { @id = "ProductName_" + i })   
                        </td>   
                        <td>   
                            @Html.EditorFor(o => o.OrderDetails[i].Qty, new { @id = "Qty_" + i })   
                        </td>   
                        <td>   
                            @Html.EditorFor(o => o.OrderDetails[i].Price, new { @id = "Price_" + i })   
                        </td>   
                        <td>   
                            @Html.EditorFor(o => o.OrderDetails[i].TotalAmount, new { @id = "Price_" + i })   
                        </td>   
                    </tr>   
                            i++;   
                            }   
                        }   
                    }   
                </table>   
            </div>   
        </div>   
        <hr />   
        <div class="form-group">   
            <div class="col-md-12 text-center">   
                <input type="submit" value="Order Now" class="btn btn-primary" />   
            </div>   
        </div>   
        <div class="form-group">   
            <div class="col-md-12 text-center text-success" >   
               @ViewBag.OrderStatus   
            </div>   
        </div>   
    </div>   
}

After adding the model, view, controller, and Repository folder our final solution explorer will look as follows.

Now we have done all coding to upload files.

Step 9. Now run the application.

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

In the preceding table consider we are adding orders (records) dynamically. Now click on the Order Now button and after successfully inserting the order into a database the following message will appear.

Now after clicking on the Order Now button the records get stored in the SQL table as XML file which we have created to store the order details as XML file.

In the preceding table, you have seen that the OrderDetail XML file is stored in the SQL column named OrderDetails. Now Double-click on the XML file which is highlighted as a hyperlink into the SQL table column then XML file records will be shown as follows

I hope from all the preceding examples we have learned how to Insert a List of Records into a single SQL column as an XML file in ASP.NET MVC

Note

• Download the Zip file of the sample application for a better understanding.
• Since this is a demo, it might not be using proper standards, so improve it depending on your skills.
• Configure the database connection on the web. config file depending on your database server location.

Summary

I hope this article is useful for all readers. If you have any suggestions please contact me.

Read more articles on ASP.NET

• Pass Dynamically Added HTML Table Records List To Controller In ASP.NET MVC
• Get Return Value From Stored Procedure Using Dapper ORM In ASP.NET MVC