Problems with DataSet Update w/ Multiple DataRelations

[David Adams]

Hi,

I am having a big problem with trying to update a strongly typed dataset
with multiple tables and datarelations. Here is the basic schema:

Table: Application
APP_ID (PK)
ADDRESS
CITY

Table: FamilyMembers
FM_ID (PK)
APP_ID (FK)
NAME

Table: Education
ED_ID (PK)
APP_ID (FK)
FM_ID (FK)
SCHOOL

Table: Jobs
JB_ID (PK)
APP_ID (FK)
FM_ID (FK)
JOB

The Application table is the parent with three child tables, FamilyMembers,
Education, and Job. The FamilyMembers table is the parent of the Education
and Job tables as well. I have 3 datarelations set on App_ID with the
parent Application, and 2 datarelations set on FM_ID with the parent
FamilyMembers. Each datarelation has an Update/Delete rule set to "Cascade"
and a Accept/Reject rule set to "None". Each primary key value has an
autoincrement of -1 with a start of -1. The identity values from the
database update the temporary primary key values on an update.

I am peroforming the following dataadapter update actions:

adp1.Update(ds.Application)
adp2.Update(ds.FamilyMembers)
adp3.Update(ds.Application)
adp4.Update(ds.Jobs)

The order is important so that the newly updated primary key values cascade
to the children. The first update cascades the APP_ID perfectly. Each of
the 4 tables will show the new APP_ID returned after the
adp1.Update(ds.Application) line. However, when I step through the code
after the adp2.Update(ds.FamilyMembers) line, the FM_ID cascades to the
Education table, but the Jobs table does not cascade: the temporary value
from the autoincrement is still there.

A few interesting notes:

When I step through the code, immediately prior to the
adp2.Update(ds.FamilyMembers) line, I can see the related FamilyMembers row
(ds.Jobs[0].FamilyMembersRow). But when execute the adp2.Update line, the
related FamilyMembers row is set to "undefined value", as if the relation
has been removed temporarily. The datarelation still exists. However, the
related parent row is null.

When I remove the datarelation between FamilyMembers and Education, the
FM_ID cascades to the Jobs table just fine.

------------

If anyone could provide some insight as to why the Jobs table does not
receive the cascading update to the FM_ID, I would really appreciate it.

Thanks!

Dave

 

[David Sceppa]

David,

Any chance you could provide some isolated code that
reproduces the behavior you're seeing?

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

 

[David Adams]

Thanks David for your interest, I am providing an abbreviated form of the
code.

I am using a strongly typed dataset (dsApplication) referenced from form A
which contains datagrids for Family Members, Education, and Jobs. The user
clicks on a datagrid row to get the detail for that particular record on
form B (textboxes, combos, etc) and contains a reference to the original
dataset (dsApplication). A user control that manages adds, updates, deletes
and record navigation is on form B. The user control references a
currencymanager object from form B.

Form B (Family Member Detail)

public void MyInitializeComponent(dsApplication ds, int pos)
{
_dsApplication = ds;

this.txFirst.DataBindings.Add("Text", this._dsApplication.FamilyMembers,
"FirstName");
this.txMI.DataBindings.Add("Text", this._dsApplication.FamilyMembers,
"MiddleInit");
this.txLast.DataBindings.Add("Text", this._dsApplication.FamilyMembers,
"LastName");

_CM = (CurrencyManager)this.BindingContext[_dsApplication.FamilyMembers];

the variable "pos" is passed from the datagrid on form A and represents the
row to be edited.

if (pos==-1)
ucfrmEdit.btnAdd_Click(null,null);
else
_CM.Position = pos;
}

The currencyManager is passed to the user control and used as follows:

Add a Record: _CM.AddNew();
Save a Record: _CM.EndCurrentEdit();
Next/Prev: _CM.Position++/_CM.Position--

There is a separate data component that is passed the dsApplication object:

public void UpdateApplication(dsApplication ds)
{
if (!ds.HasChanges()) return;

SqlCommand cmdU = new SqlCommand();
cmdU.Connection = GAConn;
cmdU.CommandType = CommandType.StoredProcedure ;
SqlCommand cmdD = new SqlCommand();
cmdD.CommandType = CommandType.StoredProcedure ;
SqlDataAdapter adp = new SqlDataAdapter();

adp.InsertCommand = cmdU;
adp.UpdateCommand = cmdU;
adp.DeleteCommand = cmdD;
SqlTransaction sqlT=null;
try
{
GAConn.Open();
sqlT = GAConn.BeginTransaction();
cmdU.Transaction = sqlT;
cmdD.Transaction = sqlT;
GetApplicationParams(cmdU,cmdD);
adp.Update(ds.Application);

GetFamilyMembersParams(cmdU,cmdD);
adp.Update(ds.FamilyMembers);

GetEducationParams(cmdU,cmdD);
adp.Update(ds.Education);

GetJobsParams(cmdU,cmdD);
adp.Update(ds.Jobs);

sqlT.Commit();
}

The GetxxxParams Method clears the current command parameters and refils
them, as shown here:

private void GetFamilyMembersParams(SqlCommand cmdU, SqlCommand cmdD)
{
cmdU.CommandText = "FamilyMembers_Update";
cmdD.CommandText = "FamilyMembers_Delete";
cmdU.Parameters.Clear();
cmdD.Parameters.Clear();

cmdU.Parameters.Add(new SqlParameter ("@FM_ID",SqlDbType.Int
,4,"fm_id")).Direction=ParameterDirection.InputOutput;
cmdU.Parameters.Add(new SqlParameter("@CaseID",SqlDbType.Char
,10,"caseid"));
cmdU.Parameters.Add(new SqlParameter("@Firstname",SqlDbType.VarChar
,20,"firstname"));
cmdU.Parameters.Add(new SqlParameter("@Middleinit",SqlDbType.Char
,1,"middleinit"));
cmdU.Parameters.Add(new SqlParameter("@Lastname",SqlDbType.VarChar
,30,"lastname"));
cmdU.Parameters.Add(new SqlParameter("@Relationship",SqlDbType.VarChar
,30,"relationship"));
cmdU.Parameters.Add(new SqlParameter("@SSN",SqlDbType.Char ,11,"ssn"));
cmdU.Parameters.Add(new SqlParameter("@IsPrimary",SqlDbType.Bit
,1,"isprimary"));
cmdD.Parameters.Add(new SqlParameter("@FM_ID",SqlDbType.Int
,4,"fm_id"));
}

The XSD is abbreviated as follows:

<xs:element name="Application">
<xs:complexType>
<xs:sequence>
<xs:element name="App_ID" msdata:AutoIncrement="true" type="xs:int"
nillable="false" msdata:AutoIncrementSeed="-1"
msdata:AutoIncrementStep="-1" msdata:ReadOnly="false" />
<xs:element name="Address" type="xs:string" />
<xs:element name="City" type="xs:string" default="" />
</xs:sequence>
</xs:complexType>
</xs:element>

<xs:element name="FamilyMembers">
<xs:complexType>
<xs:sequence>
<xs:element name="FM_ID" msdata:AutoIncrement="true" type="xs:int"
nillable="false" msdata:AutoIncrementSeed="-1"
msdata:AutoIncrementStep="-1" msdata:ReadOnly="false" />
<xs:element name="App_ID" type="xs:string" />
<xs:element name="FirstName" type="xs:string" />
<xs:element name="MiddleInit" type="xs:string" default="" />
<xs:element name="LastName" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>

<xs:element name="Education">
<xs:complexType>
<xs:sequence>
<xs:element name="ED_ID" msdata:AutoIncrement="true" type="xs:int"
nillable="false" msdata:AutoIncrementSeed="-1"
msdata:AutoIncrementStep="-1" msdata:ReadOnly="false" />
<xs:element name="App_ID" type="xs:string" />
<xs:element name="FM_ID" type="xs:string" />
<xs:element name="School" type="xs:string" default="" />
</xs:sequence>
</xs:complexType>
</xs:element>

<xs:element name="Jobs">
<xs:complexType>
<xs:sequence>
<xs:element name="JB_ID" msdata:AutoIncrement="true" type="xs:int"
nillable="false" msdata:AutoIncrementSeed="-1"
msdata:AutoIncrementStep="-1" msdata:ReadOnly="false" />
<xs:element name="App_ID" type="xs:string" />
<xs:element name="FM_ID" type="xs:string" />
<xs:element name="Job" type="xs:string" default="" />
</xs:sequence>
</xs:complexType>
</xs:element>

The keys and datarelations:

<xs:key name="App_ID" msdatarimaryKey="true">
<xs:selector xpath=".//mstns:Application" />
<xs:field xpath="mstns:App_ID" />
</xs:key>
<xs:key name="FamilyMembers_FM_ID" msdatarimaryKey="true">
<xs:selector xpath=".//mstns:FamilyMembers" />
<xs:field xpath="mstns:FM_ID" />
</xs:key>
<xs:key name="Education_ED_ID" msdatarimaryKey="true">
<xs:selector xpath=".//mstns:Education" />
<xs:field xpath="mstns:ED_ID" />
</xs:key>
<xs:key name="Jobs_JB_ID" msdatarimaryKey="true">
<xs:selector xpath=".//mstns:Jobs" />
<xs:field xpath="mstns:JB_ID" />
</xs:key>

Application to Family Members relations:

<xs:keyref name="ApplicationFamilyMembers" refer="App_ID"
msdata:AcceptRejectRule="None"
msdataeleteRule="Cascade" msdata:UpdateRule="Cascade">
<xs:selector xpath=".//mstns:FamilyMembers" />
<xs:field xpath="mstns:App_ID" />
</xs:keyref>

<xs:keyref name="ApplicationEducation" refer="App_ID"
msdata:AcceptRejectRule="None"
msdataeleteRule="Cascade" msdata:UpdateRule="Cascade">
<xs:selector xpath=".//mstns:Education" />
<xs:field xpath="mstns:App_ID" />
</xs:keyref>

<xs:keyref name="ApplicationJobs" refer="App_ID"
msdata:AcceptRejectRule="None"
msdataeleteRule="Cascade" msdata:UpdateRule="Cascade">
<xs:selector xpath=".//mstns:Jobs" />
<xs:field xpath="mstns:App_ID" />
</xs:keyref>

FamilyMembers to Education, Jobs relations:

<xs:keyref name="FamilyMembersEducation" refer="FamilyMembers_FM_ID"
msdataeleteRule="Cascade"
msdata:UpdateRule="Cascade" msdata:AcceptRejectRule="None">
<xs:selector xpath=".//mstns:Education" />
<xs:field xpath="mstns:FM_ID" />
</xs:keyref>

<xs:keyref name="FamilyMembersJobs" refer="FamilyMembers_FM_ID"
msdataeleteRule="Cascade"
msdata:UpdateRule="Cascade" msdata:AcceptRejectRule="None">
<xs:selector xpath=".//mstns:Jobs" />
<xs:field xpath="mstns:FM_ID" />
</xs:keyref>

I tried to summarize as best as possible. There is obviously more code (and
tables) but I hope something in this will stand out. Thanks again David,
I appreciate your help!

- David Adams

 

[David Adams]

I think this may have something to do with the CurrencyManager object. I am
using the CM.AddNew for new records, which may affect strongly typed
datasets in a negative way. When I bypass the add/edit forms and add the
rows manually, everything works fine:

Data.dsApplication.FamilyMembersRow r =
_dsApplication.FamilyMembers.NewFamilyMembersRow();
r.FirstName = "Dave";
r.LastName = "Adams";
r.SSN="555-41-8888";
r.Relationship = "Parent";
r.IsPrimary=true;

_dsApplication.FamilyMembers.AddFamilyMembersRow(r);

(Do the same for the Education and Jobs tables)
Are there issues with the CurrencyManager.AddNew() and strongly typed
datasets with datarelations?

Thanks!

David

 

[David Sceppa]

I'm not aware of any such problem, but if you could provide
steps to reproduce the behavior then you stand a better chance of
getting to the root of the problem.

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

 

[David Adams]

Hi David,

All it takes is for me to duplicate this is to add an Application row,
FamilyMembers row, Education row, and Jobs row.

When I call dataAdapter.Update on the Appliation row, the APP_ID (identity
value) cascades perfectly to the 3 child tables. When I call
dataAdapter.Update on the FamliyMembers row, the FM_ID (identity value)
cascades only to the Education row. The Jobs row retains the dummy FM_ID
of -1. When I remove the datarelation between FamilyMembers and Education,
the FM_ID cascades to the Jobs row just fine. Obviously though, the FM_ID
does not cascade to the Education table.

Thanks,

David Adams

 

[David Sceppa]

David,

In the schema you described, Table4 contains its own
auto-increment primary key column, as well as the foreign key
columns from each of the tables up the chain, but those foreign
keys are all separate. In other words, you've defined
DataRelations between Table1-Table2, Table1-Table3,
Table1-Table4, Table2-Table3, Table2-Table4, and Table3-Table4.
Is that accurate?

If you bind controls to a DataRelation and add child rows
via CurrencyManager.AddNew, the new row will auto-magically
receive foreign key value(s) from the parent based on that
DataRelation. Say you add a row to Table4 via bound controls
using the schema you described. This will return a row with a
placeholder value for its primary key. The new row will also
have the appropriate value(s) from its foreign key w/ Table3.

If I understand your schema correctly, this means that this
new row for Table4 will have null values for Table1_ID and
Table2_ID. Are you assigning value(s) to these columns prior to
submitting changes?

Prior to submitting the pending changes, are the rows
associated with the proper parents at every level? If for some
reason a row in Table4 does not include a value for its Table2_ID
column, when you fetch the new ID values for Table2 that value
will not trickle down to that row in Table4.

Inputting data through bound controls may affect how you
push data into the DataSet, but whether or not you're using bound
controls should not affect cascading the newly retrieved
auto-increment values throughout the DataSet. I managed to make
this multi-table cascading auto-increment approach work with a
couple different schema (including the one you described),
strongly-typed DataSets, and bound controls. Perhaps there's a
step that's critical to seeing this behavior that's missing from
the description.

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

 

[David Adams]

Well David, I went out and bought your book. Your example in Ch 13 helped
me figure out what was wrong (binding the currencymanager to datarelations).
I was setting the currencyManager in the user control to the table in the
dataset and not the datarelation in all cases. I also setting the bound
text fields to the table and not the mastertable.datarelation.field. Once I
changed this, everything worked fine.

BTW, the "old" way I was doing this would work when I did a "GetChanges" for
some reason. That still doesn't make sense.

Also, Great book! I'm going to get a lot out of it.

Thanks for your assistance,

David Adams