Save XML data into database as columns in C#

Question

I'm writing an C# application that watch for newly created xml files, and I want to insert some of the data in these XML files to a SQL database. One file is one row in the database.

What is the best and easiest way to do this?

What I have done so far is to defined a DataTable with the columns I want from the XML. After that I use ReadXML on the DataSet. This give me a DataSet with one table and one row, and the columns I want. So far it's perfect. But I can't find a good way to ONLY insert this new DataRow into my MSSQL database. I don't have any unique ID for that row yet. Would this make it easier? I don't want to map my dataset with the database, I'm only doing an insert...

SqlConnection -> SqlCommand -> SqlParameter as a start. Then "INSERT INTO [Table] ([Field1], [Field2] ...) VALUES (@Field1Value, @Field2Value ...)". Set the parameter values and ExecuteNonQuery the SqlCommand. — Corak
– Corak, Commented Apr 25, 2013 at 15:03
if you don't have unique ID that is not problem as far as inserting data, but you might not be able to set unique column later. — Dilshod
– Dilshod, Commented Apr 25, 2013 at 15:04

user1064248 · Accepted Answer · 2013-04-25 15:03:57Z

0

You can do something like this to get the ID:

    string query = "INSERT INTO Employees (EmployeeID, Name, Phone) "+
                   "OUTPUT INSERTED.EmployeeID "+
                   "VALUES (NEWID(), 'John Kris', '99-99999')";
    Guid lastId = (Guid)command.ExecuteScalar();

The rest of the things you do sound ok for me.

answered Apr 25, 2013 at 15:03

user1064248

Sign up to request clarification or add additional context in comments.

2 Comments

smarty Over a year ago

So you're saying that I can't save my DataTable directly into the database without creating my Insert statement from scratch?

user1064248 Over a year ago

As for my knowledge you will allways have to somehow map the values from one datasource (xml) to the other (db). Be it in code or your sql statement.

Community · Accepted Answer · 2017-05-23 12:18:56Z

0

I used LINQ to SQL to solve this. I set it up somewhat like described here: LINQ to SQL. On the data context I used InsertOnSubmit.

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Apr 27, 2013 at 11:31

smarty

3883 silver badges18 bronze badges

Collectives™ on Stack Overflow

Save XML data into database as columns in C#

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related