Clearing dataset tables where they have relations

[Earl]

I'm using a Public dataset in the module for all data throughout the
project. For most forms, this is great, no need to instantiate a new object,
just clear the datatables and reload as needed.

However, an issue arises where I have data relations in a form. Reloading
the data on those forms requires clearing the relation and the datatables.
What I've done in the past is reset the dataset and clear the relations.
This also works fine -- except for the obvious, that being that it kills off
any data in the dataset.

So, I've tried to just clear the tables and then clear the dataset
relations. This causes an exception on the 2nd load of data ("The row
doesn't belong to the same DataSet as this relation.").

How do I resolve this -- without creating a new dataset for each form where
I have a data relation?

*********************************************************
TECHNIQUE A (this works well, except clears the dataset entirely)

ds.Reset()
ds.Relations.Clear()

TECHNIQUE B (this causes an exception on 2nd load of employee data)

If ds.Tables.Contains("dtEmployees") Then
ds.Tables("dtEmployees").Clear()
End If

If ds.Tables.Contains("dtEmpCategoryMatches") Then
ds.Tables("dtEmpCategoryMatches").Clear()
End If

ds.Relations.Clear()

 

[Cor Ligthert [MVP]]

Earl,

I'm using a Public dataset in the module for all data throughout the
project. For most forms, this is great, no need to instantiate a new
object, just clear the datatables and reload as needed.

Before we start discussing how to resolve your problem. Why are you doing
this. what is the advantage you want to win?

Cor

 

[Earl]

I don't want to create a new dataset every time I load a different form.

 

[Cor Ligthert [MVP]]

Earl,

In my opinion does a datarelation never belong to a form however belongs to
a dataset. That confuses me, I don't understand what you mean with a
datarelatation in a form.

Cor

 

[Miha Markic [MVP C#]]

Hi Earl,

IMO you should use a DataSet per operation (normally this is a form). For
example: editings/adding new order.
As per your problem, you have two choices:
Either not use DataRelations or watch out the data you wipe out (don't clear
related data - go row by row and use DataRow.Remove).
Again, you should really opt for a dataset per operation rather than one big
global.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

 

[W.G. Ryan MVP]

Earl:

When you say you're using a Public dataset - are you using this as an
untyped dataset and whenever you open a given form, setting the specifc
forms data to it? So that that one dataset can hold the results of say x
number of queries? If so, I'd highly recommend against going that. If you
need the public access, then create one property for each type of datasetyou
need (ideally use typed datasets).

Normally, if I have DataSet x with Tables A and B and a relation on ColumnX,
then I can reload that dataset over and over without clearing the relations.
But it sounds like you're using this as a general purpose dataset so some
forms might use the dataset and it has only one table, othes meanwhile have
x number of tables which are different from the original table. Creating
multiple properties here would make a lot more sense.

Also, if you find yourself calling Clear on the dataset very often, you may
want to rethink the strategy. Sure, there are some cases for this, but
typically you don't need to do this. SInce a dataset models a database, you
don't need to wipe your db clean over and over so the same is typically the
case for a DataSEt. Remember that a DataSet can hold any number of tables,
so you can model the whole schema of your db in ONE dataset. Mapping the
tables is the only thing to make sure you handle carefully. If I understand
you correctly (and I very well may not) , the problem sounds like it's
resulting from using datasets against their grain so to speak. If I do
misunderstand you, please let me know a little more about the
objective/benefit of this approach and specifically what you're doing and I
can probably be of more help.

Cheers,

Bill

 

[Earl]

After balancing my needs and the responses I've got here (quite a good
group, I might add), I'm going with your approach, that is, create a private
dataset per operation. Thanks also to Cor and W.G. Ryan for some good
thoughts on the issue.

Hi Earl,

IMO you should use a DataSet per operation (normally this is a form). For
example: editings/adding new order.
As per your problem, you have two choices:
Either not use DataRelations or watch out the data you wipe out (don't
clear related data - go row by row and use DataRow.Remove).
Again, you should really opt for a dataset per operation rather than one
big global.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

I don't want to create a new dataset every time I load a different form.

 

[Earl]

Fundamentally, this is what I've tried to do -- that is, model the ONE
dataset after the entire database. What I"m trying to avoid is EVER having
to call Clear on the entire dataset. I've accomplished this in all
situations EXCEPT where I've got the data relations.

And yes, the dataset is untyped (typed datasets seem to cause even more work
and less flexibility, at least for me).

I had not considered creating a property for each type, but except for the
tables where I had to build relations for grid display, I was able to use
one dataset + many datatables within.

All the tables are "original", that is to say, each of the datatables within
the single dataset have their original design. For example, my dataset has
datatables for:

Customers
Employees
Users
Orders
OrderDetails
......

All told, about 150 datatables. When I have to reload the tables, I would
like to clear JUST the datatables. In all but about 6 situations, this works
out fine. Again, just the cases where I need relations are the issue.

As I noted in Miha's response, the easiest way to handle this is to create
multiple datasets -- one per "operation". This is not the overhead I would
like to incur (I've noticed a bit more sluggish performance when loading a
form), but it is certainly a viable and easily implemented solution.

If you can comment on what I've written above, I'd love to hear your
response.

Thanks