Varshini S
asked on
C# SQL BULK INSERT CLASS
i have the following XML and it is saved in employee.xml.
<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Employee>
<EmployeeID>4</EmployeeID>
<FirstName>Rob</FirstName>
<MiddleName xsi:nil="true" />
<LastName>Walters</LastNam e>
</Employee>
<EmployeeDetail>
<EmployeeID>4</EmployeeID>
<Salary>45000</Salary>
<DOJ>2014-02-19</DOJ>
<LT>12</LT>
</EmployeeDetail>
</Employees>
And i receive this file as a file stream in a method. I have the following program that will loop through all the child tables and build the SQL insert script. There is no harcoded values in the following program.
Same program i would like to use SQL BULK Insert class using iDataReader.
Note: In the real scenario i do have more than 8 child tables.
For determine the column data type, need to check the SQL staging table.
How do i achieve this ?
<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Employee>
<EmployeeID>4</EmployeeID>
<FirstName>Rob</FirstName>
<MiddleName xsi:nil="true" />
<LastName>Walters</LastNam
</Employee>
<EmployeeDetail>
<EmployeeID>4</EmployeeID>
<Salary>45000</Salary>
<DOJ>2014-02-19</DOJ>
<LT>12</LT>
</EmployeeDetail>
</Employees>
And i receive this file as a file stream in a method. I have the following program that will loop through all the child tables and build the SQL insert script. There is no harcoded values in the following program.
var xdoc = XDocument.Load(@"C:\Working Directory\Employee.xml");
var results = (from emp in xdoc.Root.Elements()
let empID = emp.Element("EmployeeID").Value
group emp by new { emp.Name.LocalName, empID } into empGroup
select new
{
columnName = empGroup.Select(n => new
{
TableName = empGroup.Key.LocalName,
FieldName = n.Elements().Select(ln => ln.Name.LocalName).Aggregate((next, accum ) => next + ", " + accum ),
Values = n.Elements().Select(v => v.Value).Aggregate((next, accum ) => next + ", " + accum )
}).ToList()
}).ToList();
foreach (var item in results)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO " + item.columnName[0].TableName);
sb.Append(" (" + item.columnName[0].FieldName + ") ");
sb.Append("VALUES " + item.columnName[0].Values + ") ");
//Place the value of the variable sb.ToString() where you want to place the statement in your SQL Bulk insert object
Console.WriteLine(sb.ToString());
}
Same program i would like to use SQL BULK Insert class using iDataReader.
Note: In the real scenario i do have more than 8 child tables.
For determine the column data type, need to check the SQL staging table.
How do i achieve this ?
ASKER
Kumar: Can you help me to convert my code in to example you provided ?
ASKER
Can someone help me to create a c# method using mentioned above example?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Is it possible to use iDataReader in the method. I would like validate the data value.
Follow below urls for SQLBulk copy and IDataReader.
http://www.codeproject.com/Articles/228332/IDataReader-implementation-plus-SqlBulkCopy
https://blogs.msdn.microsoft.com/anthonybloesch/2013/01/23/bulk-loading-data-with-idatareader-and-sqlbulkcopy/ <<Here you can download the entire code from the bottom of the page>>
Hope it helps
Good luck