Merge function in DataSet

[Praveen]

Hi ,
I have an application which stores data as in XML. This data would get
updated at some intervals... I would be getting only update and added data
in an XML which I wanted to merge to the existing XML. I was reading about
the merge function of the dataset class and looks like that is exactly what
I need. I did some research and developed and XSD for the xml defenition. In
this XSD I have defined ID tag as the primary key. According to the article
found at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcondiffgrams.asp
when there is a primary key in the xml schema the merge function would
update the destination dataset with the data from the source. Unfortunately
that did not happen for me. I was getting the below error.

An unhandled exception of type 'System.Data.ConstraintException' occurred in
system.data.dll
Additional information: Failed to enable constraints. One or more rows
contain values violating non-null, unique, or foreign-key constraints.

Also below is the code snippet, XML and XSD that I used. Can you advice me
what I am doing wrong.

Thanks in advance.
Praveen

DataSet DSSource = new DataSet();
DSSource.ReadXmlSchema(@"C:\custom.xsd");
DSSource.ReadXml(@"C:\Source.xml");

DataSet DSDEST = new DataSet();
DSDEST.ReadXmlSchema(@"C:\custom.xsd");
DSDEST.ReadXml(@"C:\Dest.xml");

DSDEST.Merge(DSSource,true,MissingSchemaAction.Add);

----------------------------------------------------------------------------------------------------------------------------------------------------------------
Dest.xml

<ROOT>
<I><ID>2</ID><Name>John</Name></I>
<I><ID>5</ID><Name>Job</Name></I>
<I><ID>4</ID><Name>Jacob</Name></I>
<I><ID>6</ID><Name>Jimmy</Name></I>
</ROOT>
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Source.xml

<ROOT>
<I><ID>2</ID><Name>Mohit Mathew</Name></I>
<I><ID>3</ID><Name>Mac</Name></I>
</ROOT>
----------------------------------------------------------------------------------------------------------------------------------------------------------------
custom.xsd

<?xml version="1.0" standalone="yes" ?>
<xs:schema id="ROOT" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="ROOT" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="I">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" type="xs:decimal" />
<xs:element name="Name" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:key name="ROOTKey1">
<xs:selector xpath=".//I" />
<xs:field xpath="ID" />
</xs:key>
</xs:element>
</xs:schema>
----------------------------------------------------------------------------------------------------------------------------------------------------------------

 

[Sahil Malik [MVP]]

Praveen,

ID = 2 is being attempted to be added twice - which is why you are getting
that constraint violation. You can tweak the existing schemas and rowstates
to affect the final merge result.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Kevin Yu [MSFT]]

Hi Praveen,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to merge two datasets with the
same schema. However, a contraint exception was thrown when merging. If
there is any misunderstanding, please feel free to let me know.

Based on my research, in your schema file, you have set a key for the
column ID, however, you didn't specify it as the primary key column.
Because merge method looks for the primary key column to identify a row, so
the exception was thrown. You can add msdatarimaryKey="true" to the
schema file and it works well.

<xs:key name="ROOTKey1" msdatarimaryKey="true">

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[sloan]

While it isn't explicitedly documented ... DataSet.Merge works best on a "row by row" basis.

What I mean is that.... the .Merge can take 10 rows from DataSet1, and 15 rows from DataSet2, and merge them.... row by row.

Your issue is that you have a primary key violiation.

If you change your one xml document to:

< ROOT >
< I >< ID >101< /ID >< Name >Mohit Mathew< /Name >< /I >
< I >< ID >102< /ID >< Name >Mac< /Name >< /I >
< /ROOT >

then it will work .. BECAUSE each < ID > is now unique. the issue is that your trying to have 2 rows of < ID > of "2", which is causing the primary key violation.

DataSet.Merge also works well with ... combining 2 different tables.

DataSet.Employee (table) and a DataSet.Order (table). Perhaps?? one table comes from 1 db. and the second table comes from another db... this is why .Merge is good, you can get 1 dataset, which contains different table data from multiple sources.



Here is some OLD code, probably some MS code.. that I altered. I haven't looked at it in 3 years, but I remember running into the same issue you had.

If you are looking for "Matthew" to become "John" or vice versa, this is not (as) trivial.

Here is some code.. I'm sure its MS KB code that I hacked ....




public void DemonstrateMergeCustomized()
{
// Create a DataSet with one table, two columns, and three rows.
DataSet ds = new DataSet("myDataSet");
DataTable t = new DataTable("Items");
DataColumn c1 = new DataColumn("id", Type.GetType("System.Int32"));
c1.AutoIncrement=true;
DataColumn c2 = new DataColumn("lastname", Type.GetType("System.String"));
// DataColumn array to set primary key.
DataColumn[] keyCol= new DataColumn[1];
DataRow r;
// Create variable for temporary DataSet.
// Add RowChanged event handler for the table.
//t.RowChanged+= new DataRowChangeEventHandler(Row_Changed);
ds.Tables.Add(t);
t.Columns.Add(c1);
t.Columns.Add(c2);
// Set primary key column.
keyCol[0]= c1;
t.PrimaryKey=keyCol;
// Add ten rows.
for(int i = 0; i < 10;i++)
{
r=t.NewRow();
r["lastname"]= "smith" + i.ToString();
t.Rows.Add(r);
}
// Accept changes.
ds.AcceptChanges();
PrintValues(ds, "Original values");


debugMsg = ds.GetXml();


// Change row values.
t.Rows[0]["lastname"]= "craig";
t.Rows[1]["lastname"]= "jones";
// Add one row.
r=t.NewRow();
r["lastname"]="newguy";
t.Rows.Add(r);


//saveDSToGuidFile(ds, "ds");



DataSet dsx = new DataSet("myDataSet");
DataTable tx = new DataTable("Items");
DataColumn c1x = new DataColumn("id", Type.GetType("System.Int32"));
//c1x.AutoIncrement=true;
DataColumn c2x = new DataColumn("firstname", Type.GetType("System.String"));
// DataColumn array to set primary key.
DataColumn[] keyColx= new DataColumn[1];
DataRow rx;

// Add RowChanged event handler for the table.
//tx.RowChanged+= new DataRowChangeEventHandler(Row_Changed);



dsx.Tables.Add(tx);
tx.Columns.Add(c1x);
tx.Columns.Add(c2x);
// Set primary key column.
keyColx[0]= c1x;
tx.PrimaryKey=keyColx;

// Add ten rows.
for(int i = 0; i < 10;i++)
{
rx=tx.NewRow();
rx["id"]= + i;
rx["firstname"]= "john" + i.ToString();
tx.Rows.Add(rx);
}
// Accept changes.
dsx.AcceptChanges();

//saveDSToGuidFile(dsx, "dsx");


debugMsg = dsx.GetXml();


// Merge changes back to first DataSet.
ds.Merge(dsx,false,System.Data.MissingSchemaAction.Add);


PrintValues(ds, "Merged Values");

debugMsg = ds.GetXml ();

//saveDSToGuidFile(ds, "dsx");

}



public void DemonstrateMerge()
{
// Create a DataSet with one table, two columns, and three rows.
DataSet ds = new DataSet("myDataSet");
DataTable t = new DataTable("Items");
DataColumn c1 = new DataColumn("id", Type.GetType("System.Int32"));
c1.AutoIncrement=true;
DataColumn c2 = new DataColumn("lastname", Type.GetType("System.String"));
// DataColumn array to set primary key.
DataColumn[] keyCol= new DataColumn[1];
DataRow r;
// Create variable for temporary DataSet.
DataSet xSet;
// Add RowChanged event handler for the table.
t.RowChanged+= new DataRowChangeEventHandler(Row_Changed);
ds.Tables.Add(t);
t.Columns.Add(c1);
t.Columns.Add(c2);
// Set primary key column.
keyCol[0]= c1;
t.PrimaryKey=keyCol;
// Add ten rows.
for(int i = 0; i < 10;i++)
{
r=t.NewRow();
r["lastname"]= "smith" + i.ToString();
t.Rows.Add(r);
}
// Accept changes.
ds.AcceptChanges();
PrintValues(ds, "Original values");


// Change row values.
t.Rows[0]["lastname"]= "craig";
t.Rows[1]["lastname"]= "jones";
// Add one row.
r=t.NewRow();
r["lastname"]=74;
t.Rows.Add(r);
// Insert code for error checking. Here we set one row in error.
t.Rows[1].RowError= "over 100";
PrintValues(ds, "Modified and New Values");
// If the table has changes or errors, create a subset DataSet.
if(ds.HasChanges(DataRowState.Modified | DataRowState.Added)& ds.HasErrors)
{
// Use GetChanges to extract subset.
xSet = ds.GetChanges(DataRowState.Modified|DataRowState.Added|DataRowState.Deleted |DataRowState.Detached |DataRowState.Unchanged );
PrintValues(xSet, "Subset values");
// Insert code to reconcile errors. In this case, we'll reject changes.
foreach(DataTable xTable in xSet.Tables)
{
if (xTable.HasErrors)
{
foreach(DataRow xRow in xTable.Rows)
{
//Console.WriteLine(xRow["lastname"]);
if((string)xRow["lastname",DataRowVersion.Current ]== "100")
{
xRow.RejectChanges();
xRow.ClearErrors();
}
}
}
}
// Add a column to the xSet. This changes the schema.
xSet.Tables["Items"].Columns.Add(new DataColumn("firstname"));
PrintValues(xSet, "Reconciled subset values");
// Add values to the rows for each column.
foreach(DataRow myRow in xSet.Tables["Items"].Rows)
{
myRow["firstname"] = "kenny";
}
// Merge changes back to first DataSet.
ds.Merge(xSet,false,System.Data.MissingSchemaAction.Add);
}



PrintValues(ds, "Merged Values");



}

private void Row_Changed(object sender, DataRowChangeEventArgs e)
{
Console.WriteLine("Row Changed " + e.Action.ToString() + "\t" + e.Row.ItemArray[0]);
}

private void PrintValues(DataSet ds, string label)
{
Console.WriteLine("\n" + label);

debugMsg = "columnNames\n";

foreach(DataTable t in ds.Tables)
{
foreach( DataColumn dc in t.Columns )
{
debugMsg += " : " + dc.Caption;
}
}

Console.WriteLine (debugMsg);
foreach(DataTable t in ds.Tables)
{
Console.WriteLine("TableName: " + t.TableName);
foreach(DataRow r in t.Rows)
{
foreach(DataColumn c in t.Columns)
{
Console.Write("\t " + r[c] );
}
Console.WriteLine();
}
}
}



private void PrintValues(DataSet ds, string label, int i)
{
Console.WriteLine("\n" + label);

debugMsg = "";

foreach(DataTable t in ds.Tables)
{
foreach( DataColumn dc in t.Columns )
{
debugMsg += " " + dc.Caption;
}
}

Console.WriteLine (debugMsg);


foreach(DataTable t in ds.Tables)
{
Console.WriteLine("TableName: " + t.TableName);
foreach(DataRow r in t.Rows)
{
foreach(DataColumn c in t.Columns)
{
Console.Write("\t " + r[c] );
}
Console.WriteLine();
}
}
}



From http://developmentnow.com/g/7_2005_10_0_0_610394/Merge-function-in-DataSet.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

 

[Praveen]

After adding msdatarimaryKey="true" in the schema, Merge function worked
fine...

Thanks a lot,
Praveen

 

[Kevin Yu [MSFT]]

You're welcome!

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."