Cascading issues while updating multiple tables with SqlDataAdapter.Update method

[geeksgk]

All,

I broke my head over this for 18 hours now and tried out all the
suggestions posted here but of no use.

Here is what I'm trying to accomplish

I have two tables, 1) Criteria 2) Regulator

Criteria is a master table and Regulator is a child table. ie, the
primary key of Criteria table (CriteriaID, identity column with
autoincrement set to 1) is a foreign key in the Regulator table. pretty
classic stuff.

I wrote a console app in C# to update these two tables using
SqlDataAdapter.Update method. The only slight difference from other
postings is that I get the XML data file from an external application.
I have to import the data from that XML file into these two tables.

I tried to follow David Sceppa's suggestions but No matter what I do,
the identity value I receive from the Criteria table DOES NOT GET
CASCADED to the Regulator table. As a result records are not inserted
into the child (Regulator) table.

Here is my code

Any thoughts would be much appreciated

{
.......

SelectQuery = "select * from Criteria";
SqlDataAdapter CriteriaDataAdapter = new SqlDataAdapter();
SqlConnection CriteriaSQLConnection = new
SqlConnection(ConnectionString);
SqlCommand CriteriaSQLSelectCommand = new SqlCommand(SelectQuery,
CriteriaSQLConnection);
CriteriaDataAdapter.SelectCommand = CriteriaSQLSelectCommand;

cmdGetIdentity = new SqlCommand("SELECT @@IDENTITY as CriteriaID",
CriteriaDataAdapter.SelectCommand.Connection);

CriteriaDataAdapter.RowUpdated +=new
SqlRowUpdatedEventHandler(CriteriaDataAdapter_RowUpdated);

SqlCommandBuilder Criteriacmdb = new
SqlCommandBuilder(CriteriaDataAdapter);



SelectQuery = "select * from Regulator";
SqlDataAdapter RegulatorDataAdapter = new SqlDataAdapter();

//attempted to use the same connection object..didn't help
//SqlConnection RegulatorSQLConnection = new
SqlConnection(ConnectionString);
//SqlCommand RegulatorSQLSelectCommand = new SqlCommand(SelectQuery,
RegulatorSQLConnection);
SqlCommand RegulatorSQLSelectCommand = new SqlCommand(SelectQuery,
CriteriaSQLConnection);
RegulatorDataAdapter.SelectCommand = RegulatorSQLSelectCommand;
SqlCommandBuilder Regulatorcmdb = new
SqlCommandBuilder(RegulatorDataAdapter);


DataSet NewDataSet = new DataSet();
NewDataSet.ReadXmlSchema("PostApprovalsReport.xsd");

DataColumn NewColumn =
NewDataSet.Tables["Criteria"].Columns.Add("CriteriaID", typeof(Int32));
NewColumn.Unique = true;
NewColumn.AutoIncrement = true;
NewColumn.AutoIncrementSeed = -1;
NewColumn.AutoIncrementStep = -1;

DataColumn NewFColumn =
NewDataSet.Tables["Regulator"].Columns.Add("CriteriaID",
typeof(Int32));

NewDataSet.Relations.Add("CriReg",
NewDataSet.Tables["Criteria"].Columns["CriteriaID"],
NewDataSet.Tables["Regulator"].Columns["CriteriaID"]);

Reading the data from a xml file that came from another application
NewDataSet.ReadXml("5393_PostApprovalsReport_2004-12-01.xml");

CriteriaDataAdapter.Update(NewDataSet, "Criteria");

RegulatorDataAdapter.Update(NewDataSet, "Regulator");

}

private void CriteriaDataAdapter_RowUpdated(object sender,
SqlRowUpdatedEventArgs e)
{
if ((e.Status == UpdateStatus.Continue) && ((e.StatementType ==
StatementType.Insert)))
{
Console.WriteLine (cmdGetIdentity.ExecuteScalar().ToString());
e.Row["CriteriaID"] =
Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString());
e.Row.AcceptChanges();
Console.WriteLine (e.Row["CriteriaID"]);
}
}

 

[David Sceppa]

The ADO.NET code looks fine, but the XML schema and data aren't
included, and I believe that's a big part of the issue. Without clearer
repro steps, it's tough to understand the cause of the problem. But I'll
give it a shot...

After you've loaded the data, walk through the DataSet contents and
see if the desired rows are related. I'm going to guess that the rows are
not considered related in the DataSet via your new DataRelation, which
would explain why the changes aren't cascading through the DataRelation.

I created a really scaled down repro and managed to make the changes
cascade as a possible starting point and have included my code, schema, and
data. After changing the pending auto-increment column values for the
parent table through brute force (foregoing DataAdapters and submitting
changes to focus on the problem at hand), the code ran successfully. I
believe that once you can get your scenario to behave the same way,
submitting the changes via DataAdapters will work.

Assuming the results from the sample code differ from the scenario
you've created, I'm guessing the difference will be due to the XML schemas
we're initially loading. Give the code a shot and let me know what happens.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.


C# code:
-----------
DataSet ds = new DataSet();
ds.ReadXmlSchema(@"..\..\XmlSchema.xsd");
DataTable tblOrders = ds.Tables["Orders"];
DataColumn colOrderID = tblOrders.Columns.Add("OrderID", typeof(int));
colOrderID.AutoIncrement = true;
colOrderID.AutoIncrementSeed = -1;
colOrderID.AutoIncrementStep = -1;

DataTable tblDetails = ds.Tables["Details"];
tblDetails.Columns.Add("OrderID", typeof(int));

ds.Relations.Add("DataSet_Orders_Details",
tblOrders.Columns["OrderID"],
tblDetails.Columns["OrderID"]);

ds.ReadXml(@"..\..\XmlData.xml");

//Data after load
ds.WriteXml(Console.Out);

tblOrders.Rows[0]["OrderID"] = 101;
tblOrders.Rows[1]["OrderID"] = 102;

//Data after cascading should have occurred
ds.WriteXml(Console.Out);



XML Schema:
-------------
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="NewDataSet" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Orders">
<xs:complexType>
<xs:sequence>
<xs:element name="OrderDate" type="xs:string" minOccurs="0" />
<xs:element name="Details" minOccurs="0"
maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="ProductID" type="xs:string"
minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>


XML Data:
------------
<NewDataSet>
<Orders>
<OrderDate>2005-01-07T00:00:00.0000000-08:00</OrderDate>
<Details>
<ProductID>11</ProductID>
</Details>
<Details>
<ProductID>12</ProductID>
</Details>
</Orders>
<Orders>
<OrderDate>2005-01-08T00:00:00.0000000-08:00</OrderDate>
<Details>
<ProductID>21</ProductID>
</Details>
<Details>
<ProductID>22</ProductID>
</Details>
</Orders>
</NewDataSet>