Let's see with an example:
Use above command to set the identity start value to 1
In the aspx file put the following code
In code behind add the following code
We are creating a table
select * from employeeMaster
select * from employeeLocationDetails
select * from employeeDesignationDetails
DBCC CHECKIDENT ('[employeeDesignationDetails]',
RESEED, 0);
Create an XML File
Employee.xml
<?xml version="1.0" encoding="utf-8" ?>
<EmployeeData>
<Employee EmpID="1">
<EmpName>Akhshay</EmpName>
<DesignationId>2</DesignationId>
<LocationId>1</LocationId>
<Salary>32000</Salary>
</Employee>
<Employee EmpID="2">
<EmpName>Sanjeet</EmpName>
<DesignationId>1</DesignationId>
<LocationId>2</LocationId>
<Salary>28000</Salary>
</Employee>
<Employee EmpID="3">
<EmpName>Kajal</EmpName>
<DesignationId>3</DesignationId>
<LocationId>2</LocationId>
<Salary>40000</Salary>
</Employee>
<Location LocationId="1">
<LocationName>Delhi</LocationName>
</Location>
<Location LocationId="2">
<LocationName>Banglore</LocationName>
</Location>
<Designation DesignationId="1">
<DesignationName>Developer</DesignationName>
</Designation>
<Designation DesignationId="2">
<DesignationName>Designer</DesignationName>
</Designation>
<Designation DesignationId="3">
<DesignationName>Manager</DesignationName>
</Designation>
</EmployeeData>
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="XMLtoDatabase.aspx.cs" Inherits="ADONET.XMLtoDatabase" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnLoad" runat="server" OnClick="SaveXMLData_OnClick" Text="Save XML Data" />
</div>
</form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace ADONET
{
public partial class XMLtoDatabase : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void SaveXMLData_OnClick(object sender, EventArgs e)
{
String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
using (SqlConnection sqlcon = new SqlConnection(CS))
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("~/EmployeeData.xml"));
DataTable dtEmployee = ds.Tables["Employee"];
DataTable dtLocation = ds.Tables["Location"];
DataTable dtDesignation = ds.Tables["Designation"];
sqlcon.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
{
bulkCopy.DestinationTableName = "employeeMaster";
bulkCopy.ColumnMappings.Add("EmpName", "EmpName");
bulkCopy.ColumnMappings.Add("DesignationId", "DesignationId");
bulkCopy.ColumnMappings.Add("LocationId", "LocationId");
bulkCopy.ColumnMappings.Add("Salary", "Salary");
bulkCopy.WriteToServer(dtEmployee);
}
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
{
bulkCopy.DestinationTableName
= "employeeLocationDetails";
bulkCopy.ColumnMappings.Add("LocationId", "LocationId");
bulkCopy.ColumnMappings.Add("LocationName", "LocationName");
bulkCopy.WriteToServer(dtLocation);
}
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
{
bulkCopy.DestinationTableName = "employeeDesignationDetails";
bulkCopy.ColumnMappings.Add("DesignationId", "DesignationId");
bulkCopy.ColumnMappings.Add("DesignationName", "DesignationName");
bulkCopy.WriteToServer(dtDesignation);
}
}
}
}
}
Lets Execute our application we will get the following output
Click on Save XML Data, data will be saved in database
select * from employeeMaster
select * from employeeLocationDetails
select * from employeeDesignationDetails
Posts
0 comments:
Post a Comment