Loosing ChildRelations when doing an update via remoting

[Joe]

I have a method in my remote object which takes a DataTable. When the
function returns my ChildRelations are gone. This means I have no way of
updating the rows in the child table.

Is there anyway around this?

public override void UpdateTable(ref DataTable dt)
{
if (dt == null)
return;

SqlDataAdapter da = new SqlDataAdapter();
SqlDataAdapter datmp = new SqlDataAdapter();

SqlCommand cmd = m_conn.CreateCommand();

SqlCommandBuilder cb = new SqlCommandBuilder();

cmd.CommandText = string.Format("select * from {0}", dt.TableName);
datmp.SelectCommand = cmd;
da.SelectCommand = cmd;
cb.DataAdapter = datmp;

DataColumn dc = null;

foreach (DataColumn c in dt.Columns)
{
if (c.AutoIncrement == true)
{
dc = c;
break;
}
}

da.InsertCommand = cb.GetInsertCommand();

if (dc != null)
da.InsertCommand.CommandText = da.InsertCommand.CommandText + " SELECT
SCOPE_IDENTITY() As " + dc.ColumnName;

da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
da.UpdateCommand = cb.GetUpdateCommand();
da.DeleteCommand = cb.GetDeleteCommand();

try
{
da.Update(dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message + "\nTable: " + dt.TableName,
ex.InnerException);
}
}

 

[Fred Hirschfeld]

Not sure if this will help but do you really want to pass in a DataTable to
remoted interface by REF? Also, what relations are you refering to? Do you
have a self referencing table?

Fred

 

[Joe]

The DataTable that is being passed in is from GetChanges() from the actual
table.

I need to pass it by ref in order to get my id's back.

Once the table returns, I can submit the child table for updates because the
DataRelation would have corrected the childs' parent id.

This all works fine locally but remotely it's not good because of the break
in the relations.

I was thinking I might be able to get around this by passing in a DataSet
but I'm not sure how I would modify my UpdateTable method to work with a
DataSet. More specifically, I would need to do something with the
InsertCommand for the DA.

 

[Fred Hirschfeld]

We used a dataset and just passed it around (remoting over a very fast
network). We had a function to loop through all tables in the dataset and do
a similar update that you are doing. I am still not clear on the relation
part of this...

Fred

 

[Joe]

Hi Fred,

I guess I'm thinking more than typing...

I have 2 tables which are part of a dataset of many more tables. These 2
tables have a DataRelation between them (Parent/Child).
When I do the updates via calling the remote object, I pass it one table at
a time. When I pass the parent table to the UpdateTable method, the update
works fine but when it returns the ChildRelation that this table had before
is now gone.

I guess I could put these tables into their own DataSet and just pass the
DataSet which could make more sense so there's only one call instead on one
for the parent changes and the other for the child changes.
This would bring up another question; If I call GetChanges() for the DataSet
and pass the DataSet to the remoting object, how do I get the changes made
back into the client side DataSet?

-Joe

 

[Joe]

After some testing it seems I have 2 choices:

1 - I can use my existing UpdateTable method and pass a copy of the table
returned from DataTable.GetChanges() to UpdateTable() and merge the results
back to the original table using the copy of the changes table for my
mapping.

2 - Pass a DataSet into another method in the remote object and loop through
the tables calling the original UpdateTable() method. Once the call returns
I can call DataSet.RejectChanges() on the original DataSet then call
DataSet.Merge(changedDataSet).

It seems to me there is no easy way of doing this without being able to pass
a DataSet or DataTable truly by reference.

I'm open to any other suggestions.

-Joe