4

I have a DataSet and read data from two sources in it. One table from a XML file and another table from a Firebird SQL database. What I try to get is only one table that has all columns from the XML file AND a few fields from the SQL data in a single table. Both tables have the same unique key field in it so it could be merged easily. I would like to bind all fields of this table to fields on a form.

Is it possible like described or do I not see that there is a simpler solution to my problem?

Edit: To show what I try to do a bit of extra code.

DataSet dataSet = new DataSet();

DataTable table1 = new DataTable("test1", "test1");
table1.Columns.Add("id");
table1.Columns.Add("name");
table1.Columns[0].Unique = true;

table1.Rows.Add(new object[2] { 1, "name1" });
table1.Rows.Add(new object[2] { 2, "name2" });

DataTable table2 = new DataTable("test2", "test2");
table2.Columns.Add("id");
table2.Columns.Add("thing");
table2.Columns[0].Unique = true;

table2.Rows.Add(new object[2] { 1, "thing1" });
table2.Rows.Add(new object[2] { 2, "thing2" });

dataSet.Tables.Add(table1);
dataSet.Tables[0].Merge(table2, false);

When I run this code I get a ConstraintException. When I remove the unique on the id fields it fills the list with all the needed columns but one row with data from table1 and another one with table2 data. How can I merge them?

Edit 2: I tried to use the PrimaryKey solution as follows in live data.

xmlData.Tables[0].PrimaryKey = new[] { xmlData.Tables[0].Columns["usnr"] };
dbData.PrimaryKey = new[] { dbData.Columns["usid"] };
xmlData.Tables[0].Merge(dbData, true, MissingSchemaAction.Add);

xmlData is a DataSet which comes from a XML file. It has id, usnr and a few other fields in it. dbData is a DataTable with data from db it has id, usid, name and a few other fields. The id fields are not relevant to my data. Both fields usnr and usid are strings in the table as I tested with GetType().

When I now add xmlData.Tables[0].Merge(dbData, true, MissingSchemaAction.Add); it throws a DataException

<target>.ID and <source>.ID have conflicting properties: DataType property mismatch.

While writing this I realized that the id fields where different in both tables but I dont need them anyways so did remove the column before changing the primaryKey entries and merging. Now I get a NullReferenceException with no further information in it. The tables are all fine and have data in them, where could the Exception come frome now?

Booser
  • 576
  • 2
  • 9
  • 25

3 Answers3

2

Instead of ...

table1.Columns[0].Unique = true;
table2.Columns[0].Unique = true;

... add these lines:

table1.PrimaryKey = new[] { table1.Columns[0] };
table2.PrimaryKey = new[] { table2.Columns[0] };

Because the merge command needs tho know the primary keys of the data tables. Just indicating which columns are unique is not enough. With the primary keys the output will be:

id name  thing
== ===== ======
1  name1 thing1 
2  name2 thing2

Note that for this to work properly, the primary key fields must have matching data types and names. If the data types don't match, you get a decent error message. However, if the names don't match, a nondescript null reference exception is thrown. Microsoft could have done a better job there.

That means that in your case, I'd recommend to rename either usnr or usid before merging the data tables.

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
  • The answer is nicer than the solution s.akbari but when i try to apply it to my live data it throws some errors at me. It means my string PrimaryKey Fields i am using where not compatible. – Booser May 06 '16 at 11:57
  • Can you be more specific than *some errors*? Can you make the keys match by converting data types/column names when you create one of the data tables? Using matching primary keys would make life much easier. – Gert Arnold May 06 '16 at 12:03
  • I am a bit under time pressure but i try to explain it later in detail. So i evetually can refactor it then. – Booser May 06 '16 at 12:22
  • Oh, i forgot to anounce my edit in the question. How can i control which fields get merged, changed in datatype or how they get used? – Booser May 07 '16 at 13:00
  • Thanks, that hit the point. I changed the db query to return the value as usnr and now it works like expected. Thanks for your nice help. – Booser May 07 '16 at 17:16
1

You can use Linq for this purpose and join your two DataTables like this:

.........
.........
dataSet.Tables.Add(table1);
//dataSet.Tables[0].Merge(table2, false);

var collection = from t1 in dataSet.Tables[0].AsEnumerable()
                 join t2 in table2.AsEnumerable()
                 on t1["id"] equals t2["id"]
                 select new
                 {
                     ID = t1["id"],
                     Name = t1["name"],
                     Thing = t2["thing"]
                 };

DataTable result = new DataTable("Result");
result.Columns.Add("ID", typeof(string));
result.Columns.Add("Name", typeof(string));
result.Columns.Add("Thing", typeof(string));

foreach (var item in collection)
{
    result.Rows.Add(item.ID, item.Name, item.Thing);
}

The result in a DataGridView will be what you want as shown below:

dataGridView1.DataSource = result;

enter image description here

Salah Akbari
  • 39,330
  • 10
  • 79
  • 109
  • I am using this solution at the moment because the Answer from Gert Arnold did throw a few errors on me. But for now yours is the best what i can use for my problem. I changed it to fill in a concrete class instead of an anonymous one. – Booser May 06 '16 at 12:06
0

Here you cannot merge those two data tables together. you need to merge data in those two tables iterating each.

Create new data table with containing all the columns(Id,Name,Thing). Then populate that table reading other two.

Telan Niranga
  • 437
  • 3
  • 10