DataTable made from JOINed tables and the DataAdapter wizard

  • Thread starter Thread starter elixxir
  • Start date Start date
E

elixxir

Hi,

I'm relatively new to ADO.NET and the issue that I'm facing at the
moment is that I have an OleDbDataAdapter that JOINs two tables via the
Query Builder.

For example, I have two Customers and Bookings tables and I'm creating
a CustomerBookings DataTable that contains fields from both Customers
(such as name etc) and fields from Bookings (date, time, booking type,
etc).

Now, this all gets displayed in a DataGrid and I need to be able to
allow the user to Insert/Update this data back up to the DB. Only the
Bookings table in the DB will need to be changed by this.

Obviously, the Query Builder refuses to generate Insert/Update code
because it complains about multiple tables.

I've seen this article
http://www.knowdotnet.com/articles/datarelation.html which suggests to
use DataRelations, but I don't understand how this can be done. I've
used DataRelations before but only to create Master/Detail type
relations.

I've also been able to manually code updates/inserts of the relevant
columns but I'm sure there must be a more elegant way.

I'd really appreciate if someone can let me know what's the best way to
do this type of thing. Links to articles/books appreciated ;)

Paul
 
If you're using a Join on the SQL Side, you are not going to be able to use
any of the tools like the Configuration wizard or the command builder. Set
up a DataRelation between the two tables and call update on each of the
tables respectively. They're going to need to be treated as two separate
entities. The problem is that the "joined' datatable has redundant data -
so you could change it in the datatable in a manner that has multiple values
pointing to the same record in the DB due to the join - remember that the
join will cause the fields to appear as though they were part of the first
table, even though they are pointing to only one record on the other side -
so it will look like there are ten records on a join that returns 10 rows
even though it may only be referencing one record on the joined side.

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups
 
I have found a way to do what you want but it's a bit convoluted.

I initially create the data adapter and dataset without the join and get
everything to work adds, updates, deletes etc. Once I am satisfied that
everything functions with my main table. I go into the code that was
generated for the data adapter and change the select command to include my
join. Notice I do NOT use the query builder for this. It's strictly a
manual process. I then right click on the data adapter in form view and
have it recreate the dataset. This step appears to use the select statement
as it creates a valid dataset. Because I didn't touch the add, delete or
update sql statements created initially by the data adapter they still
reference ONLY the main table.

To make "Adds" work, there is one thing I have to tweak. For each field in
the joined table I go into the vb code for the dataset and find the line
that looks like this:

Me.columnUnitOfMeasure.AllowDBNull = FALSE

I change the false to true for each of the joined fields. This is needed
because even though the ADD sql command is not referencing the joined fields
when it writes back to the database, the code in the dataset.vb module is
used for validation prior to writing to the database. So it needs to know
that the joined fields may be blank on a "add new" record.

This process works GREAT and it allows me to use the tools to create all my
data adapters and datasets. And it allows me to DISPLAY joined data on my
forms and only update my main table.

Gary
 
Obviously my solution is only applicable where you want to use a join to
DISPLAY additional info and have no need to update it directly.

Gary
 
I have a class that uses a data adapter, and handles OnRowUpdating and
OnRowUpdated. When OnRowUpdating fires, I check the row, decide which
stored procedure to call, and set that as the <whatever is happening to
the row>Command of the data adapter. OnRowUpdated helps me notice errors
early and get more contextual information about the errors as they occur.

You can check DataRow state, perhaps table name and dataset name (that's
what I do) to help you decide which stored procedure you want to call.

If it's not obvious by now, I have stored procedures do the hard work of
figuring out how to update stuff in different tables that where JOIN'd
when the data was selected out of the database.
 
Back
Top