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 ?

Have you considered using DB normalisation? That will address your concern. — dj079, Sep 19 '18 at 02:42
@dj79 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 - which seem to be normalized properly with the exception of one table (which I am handling in the xml import). Can you explain to me on how normalization of the data base will allow me to insert the records I am receiving which already have sequential id's assigned into the database without duplicating the previously imported xml files ? — Ken, Sep 19 '18 at 16:12

score 0 · Accepted Answer · answered Sep 20 '18 at 01:44

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();
}

Yes Insert Parent - get ID , then update children with that Id, that was what I was thinking I would need to do but it seems so 'drawn out' - like driving the beetle when you have a brand new Porsche. The xmlreader on the dataset makes the xml available for processing and insertion - it is why I use the dataset - I don't know if datacontext has such a quick cool feature (but now you have me ducking for it (DuckDuckGo).) — Ken, Sep 20 '18 at 03:18

How to consolidate data into tables c# SQL

1 Answers1