"ToHellWithUGA" wrote:
> 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.
|
Similar Threads
