HowTo: Updating Data in tables with an 1:n Relation

[Guest]

Hi,
I have a problem updating data in two tables which are related to each other.
The parent table is called "Owners" and each owner can have multiple
animals, standing in a table "Animals". "Owners" has a column "ID", which is
it's primary key. In "Animals" there is a column "owner" wich is the foreign
key.

Now I want to add some owners and animals to the database and update the
tables with those new values, but this doesn't seem to work, because I am
doing something wrong, but I cannot figure out my mistake. Here's a bit of
code:

MyDataSet.OwnersRow newOwner = MyDataSet.Owners.NewOwnersRow();
MyDataSet.AnimalsRow newAnimal = MyDataSet.Animals.NewAnimalsRow();

// filling the columns with data and add a new Row- works fine
newOwner.fname = customer.firstname;
newOwner.lname = customer.name;
newOwner.phone_home = customer.phone;
MyDataSet.Owners.Rows.Add(newOwner);

// now I want to add an animal to the customer

newAnimal.name = animal.name;
newAnimal.age = animal.age;

MyDataSetAnimals.Rows.Add(newAnimal);

// Getting the updated values and write them to DB
MyDataSet.OwnersDataTable rowToadd =
(MyDataSet.OwnersDataTable)MyDataSet.Owners.GetChanges(DataRowState.Added);
MyDataSet.AnimalsDataTable rowToadd1 =
(MyDataSet.AnimalsDataTable)MyDataSet.Animals.GetChanges(DataRowState.Added);

OwnersTableAdapter.Update(rowToadd);
AnimalsTableAdapter.Update(rowToadd1);

This doesn't work. I always get an exception, where it says the "owner"
column in the animals data table must not be DBNull.

If I include a DataRelation object in my code, connecting the columns
explicitly in the manner as described above, a different exception is thrown,
where it says a DataRelation like that was already there. That is true,
because the relation was defined in the SQL Server istself and is known to my
DataSet.

The next thing I tried was setting the value for the owner column in
"Animals" myself, like this:

// filling the columns with data and add a new Row- works fine
newOwner.fname = customer.firstname;
newOwner.lname = customer.name;
newOwner.phone_home = customer.phone;
MyDataSet.Owners.Rows.Add(newOwner);

// now I want to add an animal to the customer

newAnimal.name = animal.name;
newAnimal.age = animal.age;
newAnimal.owner = newOwner.ID;

MyDataSetAnimals.Rows.Add(newAnimal);

Now an exception was thrown when trying to write the data back into the SQL
Server, stating there was something wrong with the foreign key.

So where's my fault? How can I update my database with two related tables,
using the correct foreign keys etc.?

 

[Earl]

The reason you are getting the error from SQL Server is because you cannot
insert a null into the FK when you have the constraint relation set (for
example, you cannot have a PK in the Owner's table of 48 pointing to an FK
of null). You did not say whether you were using a stored procedure, but in
any event, the easiest method is to use an output parameter for the Owner's
table insert in order to retrieve the identity value to give you the new FK
for the Animals table.

Thus,

Insert into Owners
Retrieve newly created identity PK from Owners
Insert FK along with rest of datatable into Animals

 

[Cor Ligthert [MVP]]

Caroline,

The answer for this is simple in AdoNet, don't use an autoidentifier,
because than you have to update everytime the database to get the real
created autoidentifier by the database. (In the dataset is set an temporary
identifier which works (I will not say well) with a single table but not
with a relation).

The best uniqueidentifier there is for AdoNet is the GUID (to create as new
GUID()

I hope this helps,

Cor

 

[Miha Markic [MVP C#]]

Hi Caroline,

The process is usuall the following: master table has an autoinc key with
negative step and seed.
You create master row and a negative value (temporary value) is assigned as
Id (to avoid interference with real ids)
You create a detail row and set its OwnerId = Owner.Id (you latest attempt
seems fine).

Then you save added master rows. Usually, the adapter retrieves the server
assigned id on the fly (in the same pass) thus the master row gets proper
(positive) Id (you might inspect its InsertCommand.CommandText). If
DataRelation is in place, the new id is propagated to detail row(s) Owner.Id
field. Otherwise you have to do it manually.
You can save detail row(s) now.

HTH

 

[Guest]

Hi Cor,

First, thank you for the answer. Please correct me, if I have understood
something wrong: As I am using a number (INT) as a primary key for my Owners
table, a temporary identifier is created in my primary key column when adding
a row to the table in the dataset. Now, if I want to add a new row in my
"Animals" table I need to update the SQL DB with the newly created entries in
"Owners" first, retrieve the "real" Keys as created by SQL server, add those
in my "owner" row in Animals and then I can update "Animals". That meant to
connect to the SQL Server for every new entry, wich is against the principles
behind ADO.NET.

So if I used a GUID instead of an INT as the primary key for my master table
(Owners) my code should work then because it was the same in SQL Server as in
my (offline) DataSet?

Thank you,

Caroline

 

[Guest]

Hi Miha,

this also sounds very logical to me. I'll try ist out and hope it will work.
Due to your answers (all of you, guys) I now at least that my code and ideas
weren't absolutely wrong.

Ciao,
Caro

 

[Cor Ligthert [MVP]]

Caroline,

Reading your text does let me assume that you have understood what I wanted
to say.
(Although for int I read than an autoidentifier integer, while a self
populated int is almost impossible to do because of multiuser use).



Cor

 

[Jim Rand]

Sorry for breaking into the conversation, but I'm still celebrating for
finally understanding the whole back to front end data marshalling process
with ADO.Net for Windows forms development.

If your interested, read on.

You've got 4 layers: the user interface, the dataset, data marshalling, and
the database.

Starting with database (assuming SQL Server)
-----------------------------------------------

1) Create two tables, Owner and Animal. In each table add a primary
autoincrement key and name it <tablename>ID. For example OwnerID and
AnimalID. Also add the field TS to each table (timestamp not null).

2) Assuming you're using VS 2005, create the dataset layer by dragging both
Owner and Animal tables onto the designer surface. Create the relationship
between the Owner and Animal tables. Set the relation to "Both Relation and
Foreign Key Constraint" with the update and delete rules set to "cascade".
For the primary key in each table, make sure AutoIncrement is true, and the
seed and step values are -1.

Examine the table adapters for the SELECT, INSERT, UPDATE and DELETE
statements. Look how both concurrency and getting back the primary keys are
automatically handled. You can clean these up a little to only get back the
ID and TS fields.

3) Customize the dataset code by creating partial classes (double click the
designer surface).

a) For each table adapter, add the following code (example for table
NetRole). You need this so that you can get to the underlying data
adapters.
public partial class NetRoleTableAdapter
{
public System.Data.SqlClient.SqlDataAdapter daNetRole
{
get
{
this.Adapter.SelectCommand = this.CommandCollection[0];
return this.Adapter;
}
}
} /* public partial class NetRoleTableAdapter */

b) For the dataset itself, create a generic list that contains a list of the
the adapters ordered parent to child. The order is very important. Here is
some sample code:

partial class DSUserAccess
{

private static readonly log4net.ILog _log =
log4net.LogManager.GetLogger(typeof(DSUserAccess));
private
System.Collections.Generic.List<System.Data.SqlClient.SqlDataAdapter>
_adapterList = new
System.Collections.Generic.List<System.Data.SqlClient.SqlDataAdapter>();

internal void Fill()
{

Helpers.DataAccessLayer.Fill((System.Data.DataSet)this, _adapterList);

} /* internal void Fill */

internal void PrepareDatasetForUse()
{
_adapterList.Add((new
DSUserAccessTableAdapters.NetApplicationTableAdapter()).daNetApplication);
_adapterList.Add((new
DSUserAccessTableAdapters.NetRoleTableAdapter()).daNetRole);
_adapterList.Add((new
DSUserAccessTableAdapters.NetFunctionTableAdapter()).daNetFunction);
_adapterList.Add((new
DSUserAccessTableAdapters.NetRoleCanDoTableAdapter()).daNetRoleCanDo);
_adapterList.Add((new
DSUserAccessTableAdapters.NetMemberTableAdapter()).daNetMember);
_adapterList.Add((new
DSUserAccessTableAdapters.NetRoleMemberTableAdapter()).daNetRoleMember);

Helpers.DataAccessLayer.FlipToProduction(_adapterList);

} /* internal void PrepareDatasetForUse */

internal void TearDown()
{
_adapterList.Clear();
_adapterList = null;
} /* internal void TearDown */

/* Update all tables in the backend */
internal void Update()
{
Helpers.DataAccessLayer.Update((System.Data.DataSet)this, _adapterList);
} /* internal void Update */

} /* partial class DSUserAccess */



c) Create a static helper class (this littly puppy is going to get much
smarter over time!)

using System;
using System.Collections.Generic;
using System.Text;

namespace MyApp.Helpers
{
static class DataAccessLayer
{

private static readonly log4net.ILog _log =
log4net.LogManager.GetLogger(typeof(DataAccessLayer));

/* Fill all tables in the dataset */
internal static void Fill(System.Data.DataSet ds,
System.Collections.Generic.List<System.Data.SqlClient.SqlDataAdapter>
adapterList)
{
ds.Clear();
foreach (System.Data.SqlClient.SqlDataAdapter da in adapterList)
{
string tableName = da.TableMappings[0].DataSetTable;
da.Fill(ds, tableName);
}
} /* internal static void Fill */

/* */
internal static void
FlipToProduction(System.Collections.Generic.List<System.Data.SqlClient.SqlDataAdapter>
adapterList)
{

/* Skip current row for statement type insert. Necessary to prevent
adding a new row for
* each insert instead of changing the autoincrement primary key */
foreach (System.Data.SqlClient.SqlDataAdapter da in adapterList)
{
da.RowUpdated += new
System.Data.SqlClient.SqlRowUpdatedEventHandler(da_RowUpdated);
}

} /* internal static void FlipToProduction() */

/* Update all tables in the dataset */
internal static void Update(System.Data.DataSet ds,
System.Collections.Generic.List<System.Data.SqlClient.SqlDataAdapter>
adapterList)
{
System.Data.DataSet dsDeleted =
ds.GetChanges(System.Data.DataRowState.Deleted);
System.Data.DataSet dsAdded =
ds.GetChanges(System.Data.DataRowState.Added);
System.Data.DataSet dsModified =
ds.GetChanges(System.Data.DataRowState.Modified);

UpdateOperation(dsDeleted, adapterList,
System.Data.DataRowState.Deleted);
UpdateOperation(dsAdded, adapterList, System.Data.DataRowState.Added);
UpdateOperation(dsModified, adapterList,
System.Data.DataRowState.Modified);

if ( dsDeleted != null ) ds.Merge(dsDeleted, false);
if ( dsAdded != null ) ds.Merge(dsAdded, false);
if ( dsModified != null ) ds.Merge(dsModified, false);

ds.AcceptChanges();

} /* internal static void Update */

private static void UpdateOperation(System.Data.DataSet ds,
System.Collections.Generic.List<System.Data.SqlClient.SqlDataAdapter>
adapterList,
System.Data.DataRowState state)
{
if (ds != null)
{
if (state == System.Data.DataRowState.Deleted)
{
for (int i = adapterList.Count - 1; i >= 0; i--)
{
System.Data.SqlClient.SqlDataAdapter da = adapterList;
string tableName = da.TableMappings[0].DataSetTable;
if ((ds.Tables.Contains(tableName)) &&
(ds.Tables[tableName].Rows.Count > 0))
{
da.Update(ds, tableName);
}
}
}
else
{
foreach (System.Data.SqlClient.SqlDataAdapter da in adapterList)
{
string tableName = da.TableMappings[0].DataSetTable;
if ((ds.Tables.Contains(tableName)) &&
(ds.Tables[tableName].Rows.Count > 0))
{
da.Update(ds, tableName);
}
}
}

}
} /* private void UpdateOperation */

static void da_RowUpdated(object sender,
System.Data.SqlClient.SqlRowUpdatedEventArgs e)
{
if (e.StatementType == System.Data.StatementType.Insert) e.Status =
System.Data.UpdateStatus.SkipCurrentRow;
}

} /* static class DataAccessLayer */
}




4) In the user interface layer



a) Prepare the dataset for use in the UI class constructor:
dsUserAccess.PrepareDataSetForUse();

b) To fill the dataset: dsUserAccess.Fill(); /* data is filled in order of
the adapterList */

c) With the UI, update the data - add owners, animals for the owners, modify
some, delete some - whatever.

d) Update the dataset: dsUserAccess.Update(); /* put in try / catch block
*/

e) When you're done, tear it down: dsUserAccess.TearDown();

=================================================================

That's it. This is my way of thanking this newsgroup for answering a bunch
of little questions that ultimately arrived at the solution presented.

Jim