Import XML File to SQL Server using ASP.NET MVC Entity Framework
Create simple SQL table :
CREATE
TABLE
[dbo].[Student](
[Id]
[int] NOT
NULL,
[Name]
[varchar](50)
NULL,
[Class]
[int] NULL,
[Fees]
[int] NULL,
CONSTRAINT
[PK_Student] PRIMARY
KEY
CLUSTERED
(
[Id]
ASC
)WITH
(PAD_INDEX
=
OFF,
STATISTICS_NORECOMPUTE
=
OFF,
IGNORE_DUP_KEY
=
OFF,
ALLOW_ROW_LOCKS
=
ON,
ALLOW_PAGE_LOCKS
=
ON)
ON
[PRIMARY]
)
ON
[PRIMARY]
GO
======================================================================
File => New Project
Templates => Visual C# => ASP.NET MVC Web Application
Right click on Controllers => Add Controller => Add => StudentController
public
class
StudentController
: Controller
{
//
//
GET: /Student/
[HttpGet]
public
ActionResult
Index()
{
return
View();
}
[HttpPost]
public
ActionResult
Index(HttpPostedFileBase
xmlFile)
{
if
(xmlFile.ContentType.Equals("application/xml")
|| xmlFile.ContentType.Equals("text/xml"))
{
try
{
var
xmlPath = Server.MapPath("~/Content/"
+ xmlFile.FileName);
xmlFile.SaveAs(xmlPath);
XDocument
xDoc = XDocument.Load(xmlPath);
List<Student>
studentList = xDoc.Descendants("student").Select
(Student
=> new
Student
{
Id
= Convert.ToInt32(Student.Element("id").Value),
Name
= Student.Element("name").Value,
Class
= Convert.ToInt32(Student.Element("class").Value),
Fees
= Convert.ToInt32(Student.Element("fees").Value)
}
).ToList();
using
(DemoEntities
de = new
DemoEntities())
{
foreach
(var
i in
studentList)
{
var
v = de.Students.Where(a => a.Id.Equals(i.Id)).FirstOrDefault();
if
(v != null)
{
v.Id
= i.Id;
v.Name
= i.Name;
v.Class
= i.Class;
v.Fees
= i.Fees;
}
else
{
de.Students.Add(i);
}
}
de.SaveChanges();
ViewBag.Result
= de.Students.ToList();
}
return
View("Success");
}
catch
{
ViewBag.Error
= "XML
Import failed !";
return
View("Index");
}
}
else
{
ViewBag.Error
= "XML
Import failed !";
return
View("Index");
}
}
}
==================================================================================================
Right click on Models => Add => ADO.NET Entity Data Model
=> ModelDemo => OK => Generate from Database =>
next => New Connection => Provide Server details and select Database
=> Test Connection => Rename Entity Connection => DemoEntities => next => Select your table
=> Finish.
==================================================================================================
Right click on Student view folder => Add view => index =>Add
@{
Layout
= null;
}
<!DOCTYPE
html>
<html>
<head>
<meta
name="viewport"
content="width=device-width"
/>
<title>Index</title>
</head>
<body>
@using
(Html.BeginForm("Index",
"Student",
FormMethod.Post,
new
{ enctype = "multipart/form-data"
}))
{
@ViewBag.Error
<table
cellpadding="2"
cellspacing="2">
<tr>
<td>XML
File</td>
<td>
<input
type="file"
name="xmlFile"
/></td>
</tr>
<tr>
<td> </td>
<td>
<input
type="submit"
name="Import"></td>
</tr>
</table>
}
</body>
</html>
========================================================================
Right click on Models => Add => Class =>StudentImportMetaData.cs
using
System;
using
System.Collections.Generic;
using
System.ComponentModel.DataAnnotations;
using
System.Linq;
using
System.Web;
using
System.Xml.Serialization;
namespace
Import_XML_File_to_SQL_Server_in_ASP.NET_MVC_5.Models
{
[Serializable]
[XmlRoot("student")]
public
class
StudentImportMetaData
{[XmlElement("Id")]
public
int
Id { get;
set;
}
[XmlElement("Name")]
public
string
Name { get;
set;
}
[XmlElement("Class")]
public
Nullable<int>
Class { get;
set;
}
[XmlElement("Fees")]
public
Nullable<int>
Fees { get;
set;
}
}
[MetadataType(typeof(StudentImportMetaData))]
public
partial
class
Student
{
}
}
==================================================================================================
Set your default route in config file
routes.MapRoute(
name:
"Default",
url:
"{controller}/{action}/{id}",
defaults:
new
{ controller = "Student",
action = "Index",
id = UrlParameter.Optional
}
);
===============================================================================================
Right click on Student view folder => Add view => Success=>Add
@{
Layout
= null;
}
<!DOCTYPE
html>
<html>
<head>
<meta
name="viewport"
content="width=device-width"
/>
<title>Success</title>
</head>
<body>
<div>
<table
cellpadding="2"
cellspacing="2"
border="1">
<tr>
<th>Id</th>
<th>Name</th>
<th>Class</th>
<th>Fees</th>
</tr>
@foreach
(var
student in
ViewBag.Result)
{
<tr>
<td>@student.Id</td>
<td>@student.Name</td>
<td>@student.Class</td>
<td>@student.Fees</td>
</tr>
}
</table>
</div>
</body>
</html>
====================================================================
Sample XML file to Upload :
<?xml
version="1.0"
encoding="utf-8"
?>
<students>
<student>
<id>1</id>
<name>John</name>
<class>6</class>
<fees>12000</fees>
</student>
<student>
<id>2</id>
<name>David</name>
<class>6</class>
<fees>12000</fees>
</student>
<student>
<id>3</id>
<name>Mark</name>
<class>6</class>
<fees>12000</fees>
</student>
</students>
======================================================================
No comments:
Post a Comment