How to insert IEnumerable object into SQL Server table?

Question

I have created below code to get Users from XML:

        string userName = "user";
        string password = "password";
        string uri = @"uri";
        WebClient webClient = new WebClient();
        webClient.Credentials = new NetworkCredential(userName, password);  
        Stream stream = webClient.OpenRead(uri);
        XElement output = XElement.Load(stream);
        IEnumerable<XElement> users = from el in output.Elements() select el;

        foreach (XElement str in users)
        {
            Console.WriteLine(str);
        }

Result:

        <User>
          <ObjectId>3cbde</ObjectId> 
          <Alias>alias</Alias> 
          <DisplayName>Display Name</DisplayName> 
          <TimeZone>0</TimeZone> 
          <Language>0</Language> 
          <ListInDirectory>false</ListInDirectory> 
          <IsVmEnrolled>true</IsVmEnrolled> 
        </User>

Now I would like to insert the data into database, but I would like to put each field as separate column - table in database has columns: ObjectId, DisplayName, TimeZone and etc. Could you please give me some advices how it can be easily done? Do I need to convert it into DataTable or else?

@SLaks I am new in Linq:) Could you please direct me into right page? — ironcurtain, Dec 09 '13 at 15:49
http://msdn.microsoft.com/pl-pl/library/system.xml.linq.xelement(v=vs.110).aspx - Here what I found and used it. — ironcurtain, Dec 09 '13 at 15:51
The point of LINQ queries is if you need to add things like `where` clauses. If you don't, they're useless. — SLaks, Dec 09 '13 at 15:53
Do you use any ORM such as Entity Framework to do your database transactions or just straight SqlClient/SqlCommand ? — Francis Ducharme, Dec 09 '13 at 15:54
@Francis I just want to straight insert values into database. — ironcurtain, Dec 09 '13 at 15:55
@SLaks So how can I do it without Linq? Because as You said it is pointless. — ironcurtain, Dec 09 '13 at 15:57
Is the content in the "Result" block a sample of the input XML you wish to use ? — Francis Ducharme, Dec 09 '13 at 15:57
@Francis Result blocs is result of the execution of c# code. I just wanted to show what is my result. — ironcurtain, Dec 09 '13 at 16:03
I would indeed use a DataTable. If there is nothing preventing you from naming the table fields the same as the XML node names, that could simplify it. If you need an example, I can post something to give you an idea. — Francis Ducharme, Dec 09 '13 at 16:03
@Francis I was thinking about DataTable. Could you please share some code? — ironcurtain, Dec 09 '13 at 16:08

Francis Ducharme · Accepted Answer · 2013-12-09T16:36:02.740

In this example, I'm taking the XML stream locally, for testing purpose. Just change it to your method of getting the stream (WebClient). Also used SqlBulkCopy, I don't see any downsides of doing so plus, if you end up having a lot of rows to insert, that'll be faster. There are probably better ways to refactor this, but roughly:

        SqlBulkCopy bulkCopy = new SqlBulkCopy("ConnectionString...");
        bulkCopy.DestinationTableName = "PhysicalTableName";
        DataTable dt = new DataTable("PhysicalTableName");


        dt.Columns.Add("ObjectId");
        dt.Columns.Add("Alias");
        dt.Columns.Add("DisplayName");
        dt.Columns.Add("TimeZone");
        dt.Columns.Add("Language");
        dt.Columns.Add("ListInDirectory");
        dt.Columns.Add("IsVmEnrolled");


        XElement output = XElement.Load("c:\\temp\\input.xml");
        IEnumerable<XElement> users = output.Elements();

        foreach (XElement str in users)
        {
            DataRow dr = dt.NewRow();
            foreach (XElement node in str.Elements())
            {
                dr[node.Name.LocalName] = node.Value;
            }

            dt.Rows.Add(dr);
        }

        bulkCopy.WriteToServer(dt);

I did not have any information on the schema of your tables so by default in this example, all the columns are of type String.

Do not forget about exception handling as well.

thank you for your reply. I think that faster will be using build in MSSQL stored procedure sp_xml_preparedocument and then use Select * from OpenXML. — ironcurtain, Dec 09 '13 at 17:38

How to insert IEnumerable object into SQL Server table?

1 Answers1

Linked