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

Vithal Wadje
8y
14k
0
2

Article

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 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, lets 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:

"Start", then "All Programs" and select "Microsoft Visual Studio 2015".
"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:

As shown in the preceding screenshot, click on Empty template and check MVC option, 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.
After as shown into the image and type in search box "dapper".
Select Dapper as shown into the image .
Choose version of dapper library and click on install button.

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

If you want to learn how to install correct Dapper library , watch my video tutorial usin 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 model folder as in the following screenshot:

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:

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 Add button it will show the window. Specify the Controller name as Customer with suffix Controller:

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

Step 5 : Create Table and Stored procedure.

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:

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 generates the stored procedure to insert the order details into database .

Step 6: Create Repository class.

Now create Repository folder and Add CustomerOrderRepo.cs class for database related operations. Now create method in CustomerOrderRepo.cs to get the output parameter value from 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 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 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

@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 model, view , controller and Repository folder our final solution explorer will look like 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 Order Now button and after successfully inserting order into database the following message will appears as

Now after clicking on Order Now button the records get stored into 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 OrderDetail XML file is stored into the SQL column named OrderDetails . Now Double click on XML file which is highlighted like 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 List of Records into single SQL column as 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 in 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: