0

Well, lets say I've got 2 Datatables from the beginning.

The first one (source) contains the data from da database. The second one also contains data from a database, nut these values have to be updated into the first database.

Unfortunately they don't have the same structure. The sourcedatatable has some additional columns which the second has not.

For example:

First DT: ID | Name | Company | Age
Second DT: Name | Company | Age

I want the FIRST DataTable to be updated with the values from the second DataTable IF THERE ARE SOME DIFFERENCES (and only the differences).

Any ideas on how to work that out? Any suggestions about performance, even if using very big databases?

Thyrador
  • 121
  • 1
  • 11
  • 1
    How can you match a record between tables, or what's table keys? – Rubens Farias Sep 20 '13 at 10:32
  • 1
    How to you want to identify the correct row in the second table because there is no `ID`. Does the `Name` identify it? Also your title suggests that you want to update/insert a third table but in the question you mention that you "want the FIRST DataTable to be updated with the values from the second DataTable". What is correct? – Tim Schmelter Sep 20 '13 at 10:33
  • jepp, i want to identify it via the column name an the value. if it's the same, as in the source, than it should just update the rest (like an autoindex). @Tim I want the updated values from DT 1 (from the values of DT 2) into DT 3 – Thyrador Sep 20 '13 at 10:43

2 Answers2

0

If you are working with a big amount of data, I would suggest doing things as close to the DB as possible(if possible within a stored procedure).

If sticking to .NET is mandatory, these are the options I would consider given the description of your scenario you provided.

First I would choose how to load the data (the order in which I would consider them):

  1. Generate Entities (LINQ to SQL).
  2. Use F# Type providers
  3. Use ADO directly

After this, I would either:

  1. use .Select and .Except on the IQueryable sources, or
  2. do something similar to http://canlu.blogspot.ro/2009/05/how-to-compare-two-datatables-in-adonet.html, if by some chance I was using ADO.NET

It is rather hard to give a specific and exact answer if you do not provide more information on the type of data, amount, hardware, database type.

Note: whichever solution you choose, you should keep in mind that it is hard to compare things of different structure, so an extra step to add empty columns to the one that is missing columns is required.

asd007
  • 1
  • 1
  • the db type is a completely own thing. it's an external database, but completely different than SQL, etc – Thyrador Sep 20 '13 at 10:47
  • Ok, my answer is irrelevant then. just a sec. :) – asd007 Sep 20 '13 at 10:58
  • that's why I need to operate via datatables ;) – Thyrador Sep 20 '13 at 11:00
  • So the only thing that can change is Company? I misread, also the age. Why is Age stored instead of birth date? Very risky data design:) – asd007 Sep 20 '13 at 11:07
  • That was just an example. The columns can be whatever you want, except of the first (sometimes 3) columns which contains additional data for the database itself. – Thyrador Sep 20 '13 at 11:16
0

This code is just for reference, I did not have time to test it. It might reuire a bit of tweaking. Try something like:

var a = new DataTable();
a.Columns.Add("ID");
a.Columns.Add("Name");
a.Columns.Add("Company");
a.Columns.Add("Age");

var b = new DataTable();
b.Columns.Add("Name");
b.Columns.Add("Company");
b.Columns.Add("Age");

var destination = a.AsEnumerable();
var localValues = b.AsEnumerable();


var diff = destination.Join(localValues, dstRow => dstRow["Name"], srcRow => srcRow["Name"],
    (dstRow, srcRow) => 
new {Destination = dstRow, Source = srcRow})
.Where(combinedView =>
    combinedView.Destination["Age"] != combinedView.Source["Age"] ||
    combinedView.Destination["Company"] != combinedView.Source["Company"]);

Also, I would really move to a proper DB, and maybe improve the data model.

asd007
  • 1
  • 1
  • I just realized somthing. This would not solve your problem with adding missing rows, it would work for existing rows only :). You can perhaps modify the join condition to behave like a SQL right join. – asd007 Sep 20 '13 at 11:26
  • I'll give it a try later. Unfortunately I can't change the database type. A whole framework is based on it. – Thyrador Sep 20 '13 at 11:32
  • You can check http://stackoverflow.com/questions/4497086/linq-left-join-and-right-join – asd007 Sep 20 '13 at 11:40