How to Upload Xml File to Database in Entity Framework Database First Using C#

• 
• Updated date Jul 27, 2018

• 35.1k
• 12

In this article. I will demonstrate how to import XML data into SQL server using MVC 5 and entity framework. I volition create a XML file and upload information technology into FileUpload in project. I volition also employ jQuery datatable plugging for searching, shorting and paging.

Introduction

In this article, I will demonstrate how to import XML data into SQL server using MVC 5 and entity framework. I will create a XML file and upload it into FileUpload in project. I will besides utilise jQuery datatable plugging for searching, shorting and paging.

Pace 1

Open up SQL server 2014 and create a database table.

1. CREATE Table [dbo].[Product](
2.     [Id] [int ] IDENTITY(i,1) Non Zip,
3.     [Name] [nvarchar](l) NULL,
4.     [Cost] [decimal](18, 0) Null,
5.     [Quantity] [int ] NULL,
6.  CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
7. (
8.     [Id] ASC
9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Chief]
10. ) ON [Chief]
12. Become

Step 2Open Visual Studio 2015, click on New Project, and create an empty web application project.

Screenshot for creating new projection i

After clicking on New Project, i window will appear. Select Spider web from the left panel, choose ASP.Net Web Application, give a meaningful proper noun to your project, and and so click on OK as shown in below screenshot.

Screenshot for creating new projection 2

After clicking on OK ane more window volition appear; choose empty, bank check on MVC checkbox and click on OK equally shown below screenshot.

Screenshot for creating new project iii

Afterwards clicking on OK, the project will be created with the name of MvcImportXMLData_Demo.

Step 3Add Entity Framework at present. For that, right click on Models folder, select Add, then select New Particular, then click on it.

Screenshot for adding entity framework one

After clicking on new detail, you will get a window; from there, select Data from the left panel and choose ADO.Internet Entity Data Model, give information technology the proper name DBModels (this name is not mandatory you lot can give any name) and click on Add.

Screenshot for adding entity framework 2

Subsequently you click on "Add a window", the wizard will open, choose EF Designer from database and click Next.

Screenshot for adding entity framework iii

Afterward clicking on Adjacent a window will announced. Choose New Connectedness. Some other window will announced, add together your server name if it is local then enter dot (.). Choose your database and click on OK.

Screenshot for adding entity framework 4

Connection will exist added. Save connect as you want. You can change the name of your connection beneath. Information technology volition save connection in web config and so click on Side by side.

Screenshot for adding entity framework v

Afterwards clicking on Next some other window volition appear cull database table proper name as shown in the below screenshot then click on Finish.

Screenshot for adding entity framework half-dozen

Screenshot for adding entity framework-vii

Entity framework will be added and respective grade gets generated under Models folder.

Screenshot for calculation entity framework 8

Post-obit class volition be added,

1. namespace  MvcImportXMLData_Demo.Models
2. {
3. using  Organization;
4. using  System.Collections.Generic;
6. public  partial course  Product
7.     {
8. public int  Id { get; set; }
9. public  cord Proper noun { get; set up; }
10. public  Nullable<decimal> Price { go; ready; }
11. public  Nullable< int > Quantity { become; fix; }
12.     }
13. }

Step 4

Create a class in Models Folder name it ProductMetaData.cs

Screenshot-1

Screenshot-2

Write the post-obit code

1. using  Organization;
2. using  System.Collections.Generic;
3. using  System.ComponentModel.DataAnnotations;
4. using  Arrangement.Linq;
5. using  System.Spider web;
6. using  System.Xml.Serialization;
7. namespace  MvcImportXMLData_Demo.Models
8. {
9.     [Serializable]
10.     [XmlRoot("product" )]
11. public class  ProductMetaData
12.     {
13.         [XmlElement("id" )]
14. public int  Id { get; prepare; }
16.         [XmlElement("proper noun" )]
17. public  string Proper noun { become; set; }
19.         [XmlElement("cost" )]
20. public  Nullable<decimal> Toll { become; set; }
22.         [XmlElement("quantity" )]
23. public  Nullable< int > Quantity { go; set; }
24.     }
26.     [MetadataType(typeof(ProductMetaData))]
27. public  partial class  Product
28.     {
29.     }
30.     }

Step fiveRight click on Controllers folder, select Add, and so choose Controller every bit shown in the below screenshot.

After clicking on controller a window will appear choose MVC5 Controller-Empty click on Add together.

After clicking on Add another window will appear with DefaultController. Change the proper noun to HomeController so click on Add together. HomeController volition be added under Controllers folder. Remember don't change the Controller suffix for all controllers, change only highlight, and instead of Default simply change Home every bit shown in the below screenshot.

Complete code for controller

1. using  MvcImportXMLData_Demo.Models;
2. using  Arrangement;
3. using  Organization.Collections.Generic;
4. using  Arrangement.Linq;
5. using  System.Spider web;
6. using  Organization.Spider web.Mvc;
7. using  Organisation.Xml.Linq;
9. namespace  MvcImportXMLData_Demo.Controllers
10. {
11. public form  HomeController : Controller
12.     {
14. public  ActionResult Index()
15.         {
16. return  View();
17.         }
18. public  ActionResult GetData()
19.         {
20. using  (DBModel db = new  DBModel())
21.             {
22.                 List<Product> employeeList = db.Products.ToList<Production>();
23. return  Json( new  { data = employeeList }, JsonRequestBehavior.AllowGet);
24.             }
25.         }
27.         [HttpPost]
28. public  ActionResult Upload(HttpPostedFileBase xmlFile)
29.         {
30. if  (xmlFile.ContentType.Equals( "application/xml" ) || xmlFile.ContentType.Equals( "text/xml" ))
31.             {
32.                 var xmlPath = Server.MapPath("~/FileUpload"  + xmlFile.FileName);
33.                 xmlFile.SaveAs(xmlPath);
34.                 XDocument xDoc = XDocument.Load(xmlPath);
35.                 List<Product> productList = xDoc.Descendants("product" ).Select
36.                     (production =>new  Production
37.                     {
38.                         Id = Convert.ToInt32(product.Element("id" ).Value),
39.                         Proper name = product.Element("proper name" ).Value,
40.                         Cost = Convert.ToDecimal(production.Element("price" ).Value),
41.                         Quantity = Catechumen.ToInt32(production.Element("quantity" ).Value)
42.                     }).ToList();
44. using  (DBModel db = new  DBModel())
45.                 {
46.                     foreach (var i in productList)
47.                     {
48.                         var v = db.Products.Where(a => a.Id.Equals(i.Id)).FirstOrDefault();
50. if  (5 != null)
51.                         {
52.                             v.Id = i.Id;
53.                             v.Name = i.Name;
54.                             v.Cost = i.Price;
55.                             v.Quantity = i.Quantity;
56.                         }
57. else
58.                         {
59.                             db.Products.Add(i);
60.                         }
61.                         db.SaveChanges();
62.                     }
63.                 }
64.                 ViewBag.Success ="File uploaded successfully.." ;
65.             }
66. else
67.             {
68.                 ViewBag.Mistake ="Invalid file(Upload xml file only)" ;
69.             }
70. return  View( "Alphabetize" );
71.         }
72.     }
73. }

Stride vi

Create a folder to upload file with name FileUpload.

Step 7Correct click on alphabetize activity method in controller. Add view window will announced with default index name unchecked (utilise a Layout folio), and click on Add as shown in the below screenshot. View will be added in views folder nether Abode folder with name alphabetize.

Screenshot for adding view

Step 8Design view with HTML, cshtml and bootstrap four classes,

Consummate index view code

1. @{
2.     Layout = zilch;
3. }
5. <!DOCTYPE html>
7. <html>
8. <head>
9.     <meta name="viewport"  content= "width=device-width"  />
10.     <title>Alphabetize</championship>
11.     <link href="~/Content/bootstrap.min.css"  rel= "stylesheet"  />
12.     <script src="~/scripts/jquery-three.iii.1.min.js" ></script>
13.     <script src="~/scripts/bootstrap.min.js" ></script>
14.     <link href="~/Content/dataTables.bootstrap4.min.css"  rel= "stylesheet"  />
15.     <script src="~/scripts/jquery.dataTables.min.js" ></script>
16.     <script src="~/scripts/dataTables.bootstrap4.min.js" ></script>
17.     <script blazon="text/javascript"  src= "http://ajax.aspnetcdn.com/ajax/jquery.validate/1.12.0/jquery.validate.min.js" ></script>
18.     <way type="text/css" >
19.         .fault {
20.             color: cherry;
21.             display:inline -block;
22.             margin-bottom: -57px !important;
23.             width: 100%;
24.         }
25.     </style>
26.     <script blazon="text/javascript" >
27.         $(document).ready(function () {
28.             $('grade' ).validate({
29.                 rules: {
30.                     xmlFile: {
31.                         required:truthful
32.                     }
33.                 },
34.                 messages: {
35.                     xmlFile:"Choose file to upload" ,
36.                 }
37.             })
38.             $('#dataTable' ).DataTable({
39. "ajax" : {
40. "url" : "/Habitation/GetData" ,
41. "type" : "GET" ,
42. "datatype" : "json"
43.                 },
44. "columns" : [
45.                     {"data" : "Id"  },
46.                     {"data" : "Proper name"  },
47.                     {"information" : "Price"  },
48.                     {"data" : "Quantity"  }
49.                 ]
50.             });
51.         });
52.     </script>
53. </caput>
54. <body>
55.     <divgrade = "container py-5" >
56.         @using  (Html.BeginForm( "Upload" , "Home" , FormMethod.Postal service, new  { enctype = "multipart/class-data"  }))
57.         {
58.             <divclass = "row" >
59.                 <divcourse = "col-sm-1 col-md-6 col-xs-12" >
60.                     <h5class = "text-danger" >@ViewBag.Error</h5>
61.                     <h5class = "text-success" >@ViewBag.Success</h5>
62.                     <divform = "form-group" >
63.                         <label>Choose XML File:</label>
64.                         <divclass = "input-group" >
65.                             <divclass = "custom-file" >
66.                                 <input id="xmlFile"  proper name= "xmlFile"  type= "file" grade = "custom-file-input"  />
67.                                 <labelcourse = "custom-file-label" ></label>
68.                             </div>
69.                             <divclass = "input-grouping-append" >
70.                                 <input type="submit" form = "btn btn-outline-primary"  value= "Upload"  />
71.                             </div>
72.                         </div>
73.                     </div>
74.                 </div>
75.             </div>
76.         }
77.         <table id="dataTable" class = "table table-bordered table-striped" >
78.             <thead>
79.                 <tr>
80.                     <thursday>ID</th>
81.                     <th>Name</th>
82.                     <thursday>Price</th>
83.                     <th>Quantity</thursday>
84.                 </tr>
85.             </thead>
86.         </table>
87.     </div>
88. </torso>
89. </html>

Pace 9Create an xml file with the name products.xml

1. <?xml version= "1.0"  encoding= "utf-8"  ?>
2. <products>
3.   <product>
4.     <id>1</id>
5.     <proper name>Mobele Telephone</name>
6.     <cost>12000</toll>
7.     <quantity>2</quantity>
8.   </product>
9.   <product>
10.     <id>two</id>
11.     <name>Manus Sentinel</name>
12.     <cost>1200</price>
13.     <quantity>v</quantity>
14.   </product>
15.   <product>
16.     <id>3</id>
17.     <proper name>T-Shirt</name>
18.     <cost>499</price>
19.     <quantity>2</quantity>
20.   </product>
21.   <product>
22.     <id>iv</id>
23.     <proper noun>Shoe</name>
24.     <price>999</cost>
25.     <quantity>2</quantity>
26.   </production>
27.   <production>
28.     <id>5</id>
29.     <name>Jeans</proper noun>
30.     <price>999</price>
31.     <quantity>two</quantity>
32.   </product>
33. </products>

Pace tenRun Projection ctrl+F5

Screenshot-1

Screenshot-ii

Screenshot-three

Screenshot-four

Screenshot-five

Screenshot-6

Determination

In this commodity I accept explained how to import xml Meta data in SQL server using MVC 5 step by step. I hope information technology volition assistance y'all in your project.

grantthalow.blogspot.com

Source: https://www.c-sharpcorner.com/article/how-to-import-xml-data-in-sql-server-using-mvc-5/

Share this post