How to consolidate data into tables c# SQL

Question

I have XML data which I read into a dataset via xml reader.

The tables generated have several relationships but mostly the parent node to child node.

I plan to read multiple files each day and consolidate the files into a sql server. Where they can easily be utilized by other systems.

Considering the XML file the node children have sequential Id's for each node for example

<main mainGUID='BlahWeSayItIsUnique' stuff type=1>
   <children thingy Number> 
      <child other things>
      <child other things>

Children will get children_Id as the primary key and child will get children_id as a relation and its own child_Id as the primary key. These numbers will be sequential in 1...n , so each xml file will most definitely have these id values duplicated.

So what is the proper method or a good method to insert the data without duplicating id's ? Do I get the sql servers last inserted values of id , and then modify the data tables that exist in c# datatable in memory and then insert into sql ?

Or is there a better way like a versioning method using a GUID from the root node?

EDIT: I am receiving xml files from an outside source they come with the data as they are. I read those XML files into a dataset which basically gives me 8 tables , and the rows with in the tables are sequential and the tables are related one to another based on this sequential number.

The data already have sequential id's assigned , so how do I insert these records into a SQL database without duplicating the previously imported xml files ?

Answer 1

You should have identity on every id column. When you insert the record, the database would generate new unique id for you. You can read the value of the id and update all references with this new value. It can look like this

DataSet dataSet = new DataSet();

ParentDataTable parentTable = dataSet.Parent;
ParentTableAdapter parentAdapter = new ParentTableAdapter();
ParentRow parentRow = parentTable.NewParentRow();
parentRow.p_id = -1;
parentRow.p_name = "Best parent";
parentTable.AddParentRow(parentRow);
parentAdapter.Update(parentTable);            

ChildDataTable childTable = dataSet.Child;
ChildTableAdapter childAdapter = new ChildTableAdapter();
ChildRow childRow = childTable.NewChildRow();
childRow.ch_id = -1;                         
childRow.ch_name = "Best child";            

childRow.ch_parent = parentRow.p_id;
or
childRow.ParentRow = parentRow;

childTable.AddChildRow(childRow);
childAdapter.Update(childRow);

Assiging id a negative value is a way to deal with collisions. You have to update one table a time, in the right order. I would personally leave datasets and use datacontext. It would look like

var parent = new Parent()
{
    p_name = "Best parent"
};
var child = new Child()
{
    ch_name = "Best child",
    Parent = parent
};

using (var dc = new DataContext())
{
    dc.Parents.InsertOnSubmit(parent);
    dc.Childs.InsertOnSubmit(child);
    dc.SubmitChanges();
}