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.
- "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 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:
- Right-click on Solution, find Manage NuGet Package Manager, and click on it.
- After as shown in the image type in the search box "dapper".
- Select Dapper as shown in the image.
- Choose the version of a dapper library and click on the install button.
- After installing the Dapper library, it will be added to the References of our solution explorer of MVC applications such as.
- If you want to learn how to install the correct Dapper library, watch my video tutorial using the following link,
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();
}
}
Vithal WadjePosted Apr 25, 2016, 5:17 AM
Welcome...
Rupali ShindePosted Apr 25, 2016, 12:33 AM
new concept to learn. Thanks for sharing