Cascade fill for tables with FK relationships

[ToHellWithUGA]

This has probably been asked and answered, so, I apologize if so. Please
point me to any links that might already answer this, if so...

We have a desktop C# .net 2.0 application which connects to a shared
MSAccess database. We have a parent table and 12 (or so) child tables that
have FK relationships through an ID field. Our database will have hundreds of
thousands (or more) of rows in each table. Here is our question:

We populate the parent table into a Dataset, limiting it to a set of search
criteria (with a max of 100k records). We are trying to figure out the best
way to populate the child Datasets once we have the parent Dataset.

Is it possible to do a join between the parent Dataset and the child tables,
without first pulling the entire child tables into Datasets themselves? The
options we have tried so far are:

1) Use an "IN" clause that does the parent table query with all the
params/limits in each of the queries that fill the child Datasets (i.e., this
means doing the parent query 12+ times). However, this seems to be
inefficient, since we really already have all of the IDs that we want to
select from the parent Dataset. Also, there is one big drawback with this:
data could be inserted while the different child tables are being filled, so
that our child table results are not in sync with the original Dataset from
the parent table.

2) So, along those lines, we tried to populate a list of IDs based on our
existing parent Dataset, and use that list for the "IN" clause. However,
since we can have up to 100k records at a time, this proved to be a very slow
and inefficient method.

3) We tried to just do an INNER JOIN on the parent/child tables, using the
search params to limit the query for the parent table. This has proved to be
the fastest response so far. This method has the same problem that #1 has, in
that records could be changed/added/etc. during the selection process.

4) We thought about possibly creating a temporary table to hold the results
of our limited query on the parent table, so that we can do a join on this
table and ensure that our child results correspond to the exact IDs from the
parent table. It seems that this might also speed up the query time, but
we're not sure about that (and, if we can even do it in MSAccess).

We tried to implement the queries in a transaction, but we were still able
to insert data during the transaction, which broke our query (maybe this is a
limit of MSAccess?).

Anyway, we really just want to know the best/most efficient way to do this.
Basically, get the parent Dataset based on a set of params, and then fill all
of the child tables with the correct rows that match the IDs of the parent
table.

Thanks in advance.

 

[John Brown]

This has probably been asked and answered, so, I apologize if so. Please
point me to any links that might already answer this, if so...

We have a desktop C# .net 2.0 application which connects to a shared
MSAccess database. We have a parent table and 12 (or so) child tables that
have FK relationships through an ID field. Our database will have hundreds of
thousands (or more) of rows in each table. Here is our question:

We populate the parent table into a Dataset, limiting it to a set of search
criteria (with a max of 100k records). We are trying to figure out the best
way to populate the child Datasets once we have the parent Dataset.

Is it possible to do a join between the parent Dataset and the child tables,
without first pulling the entire child tables into Datasets themselves?

DISCLAIMER: I am very new to ADO.NET.

Have you tried defining a DataRelation between the Master and Detail table?

Dim ds as DataSet
Dim dtMaster as DataTable
Dim dtDetail as DataTable
Dim dr as DataRelation
Dim daMaster as DataAdapter
Dim daDetail as DatAdapter

' initialise dataset, etc., then

daMaster.Fillschema(dtMaster, SchemaType.Mapped)
daDetail.Fillschema(dtDetail, SchemaType.Mapped)
Dim dr as New DataRelation("FK", dtMaster.Columns("PrimaryKey"),
dtDetail.Columns("ForeignKey"))
ds.Relations.Add(dr)
daMaster.Fill(dtMaster)
daDetail.Fill(dtDetail)

The

options we have tried so far are:

1) Use an "IN" clause that does the parent table query with all the
params/limits in each of the queries that fill the child Datasets (i.e., this
means doing the parent query 12+ times). However, this seems to be
inefficient, since we really already have all of the IDs that we want to
select from the parent Dataset. Also, there is one big drawback with this:
data could be inserted while the different child tables are being filled, so
that our child table results are not in sync with the original Dataset from
the parent table.

2) So, along those lines, we tried to populate a list of IDs based on our
existing parent Dataset, and use that list for the "IN" clause. However,
since we can have up to 100k records at a time, this proved to be a very slow
and inefficient method.

3) We tried to just do an INNER JOIN on the parent/child tables, using the
search params to limit the query for the parent table. This has proved to be
the fastest response so far. This method has the same problem that #1 has, in
that records could be changed/added/etc. during the selection process.

So you want to lock the tables while you are filling your DataSet. Even if
you could do that, what about after you have filled it? Is it OK if somebody
else updates the database after you have filled the DataSet?

4) We thought about possibly creating a temporary table to hold the results
of our limited query on the parent table, so that we can do a join on this
table and ensure that our child results correspond to the exact IDs from the
parent table. It seems that this might also speed up the query time, but
we're not sure about that (and, if we can even do it in MSAccess).

Maybe not as part of the DataSet mechanism, but you could just execute the
necessary queries to populate the temporary tables, which don't have to be
temporary, and then fill your DataTables from DataAdapters that are bound to
the temporary tables. Make sure that your DataAdapter has the right UPDATE
command before you update the database.

We tried to implement the queries in a transaction, but we were still able
to insert data during the transaction, which broke our query (maybe this is a
limit of MSAccess?).

I vaguely remember reading somewhere that a transaction odes not start until
you update a table. If this is true, you would have to perform a do-nothing
update on your table(s). Otherwise, I would look for a record locking
property in the DataAdapter.

Anyway, we really just want to know the best/most efficient way to do this.
Basically, get the parent Dataset based on a set of params, and then fill all
of the child tables with the correct rows that match the IDs of the parent
table.

I think that the DataRelation will fill your DataSet efficiently, but . I
don't know if it will solve your concurrency issue.

Regards,
Alias John Brown.