Development strategy with VS Designer and Datasets

[Robert_SF]

I've programmed in various languages but am just getting started with VB Net under VS2010 Express and SQL CE 3.5. It's pretty cool how you can drag-and-drop from a Dataset and create forms that display parent records and their synchronized child records with no programming at all. But I've run into this limitation.

If you just drag and drop, each table adapter in a dataset will correspond to a table (one) in the database, but a user will frequently want to see a "list of records" that is really a composite of several tables or calculated values. For example, a user might want to see a list of orders including the order balance for each. For that, you're going to have to sum the totals of the order details, which exist in a different table. I guess you would call this a "view," except SQL CE 3.5 doesn't support them.

I've discovered that you can create table adapters from various real tables through the Query Builder, but then of course that table adapter can't update, so how do you update? I've tried keeping the actual data tables in the data set along with the "view" tables, but keeping the synchronized is turning into such a Rube Goldberg device that I'm thinking there's a different way. How do you do it?

 

[jmcilhinney]

I've discovered that you can create table adapters from various real tables through the Query Builder, but then of course that table adapter can't update

Sure it can. You just have to write the UPDATE statement yourself.

 

[Robert_SF]

Thanks, but can you elaborate a bit? What is your overall strategy? How do you keep your various table adapters synchronized, especially between tables that are temporary and not "real?"

Here's what I've done. In the dataset, customers, orders, details, and products are the actual tables. To display a list of customers with the date of their first order, I created CustList and the fill works fine.

I try to relate it to table orders so that I can show the orders in a separate datagridview. That gives me a constraint error, of course, because the original customers table isn't include. So I drop a customers table adapter onto the form and arrange the fill commands in the load event correctly, and then it works. That is, I can show a customized list of customers instead of just the table columns, and I can synchronize the navigation of a second related table to it.



For some reason, I figured the current row in the original customers table would change as the current row in CustList changed, but it doesn't. But I think I can use the bindingsource.find method to get the corresponding row.

It seems an awful kludge, but I'm pretty clueless here. I'm thinking I need to do more manual code and less designer, but I really don't know.

 

[jswota]

Can you just add the "FirstOrder" directly to the "customers" tableadapter and get rid of the "CustList" tableadapter? Are you just doing a MIN(OrderDate) AS FirstOrder? If so, i think you could just add that to your "real" customers table.

 

[Robert_SF]

Hi jswota: I could do that, but that would solve only this one case. I've been looking around, and I've found more information. Other people have noticed that it's a challenge in VB Net to work with joined tables. Here's one good article I found. I'm still reading. I know ASP isn't exactly the same thing, but for this matter it is close enough.
Updating the TableAdapter to Use JOINs: The Official Microsoft ASP.NET Site

 

[jswota]

Hi Robert,

What i used to do if i needed to JOIN tables in a table adapter was to create the table adapter on the primary table first. This would cause the wizard to create the additional update and delete commands. After that i would modify the tableadapter's SELECT statement to handle the JOIN. This would allow you to use the tableadapter to get the joined data and still be able to update and delete. Keep in mind that you can only update and delete from one table using this method. Also, if you forget that you overrode this tableadapters select statement and use the wizard to modify it later, you will lose the wizard created update and delete commands. Because of this i have adopted the use of stored procedures for tableadapters that require more complex queries. Sproc's also allow you to know exactly what is going on when you are updating and deleting data.

Hope this helps.

 

[Robert_SF]

Yes, that sounds like a good method. I wish the designer wasn't one-way only, but I guess the changes to the SELECT statement can be coded in, and that way it won't matter if you regenerate the dataset and its table adapters. Thanks for reminding me of stored procedures. I'm using the CE version of the database, though, which doesn't support them.

 

[jswota]

...and that way it won't matter if you regenerate the dataset and its table adapters.

Careful! First you create the table adapter on a single table so the wizard generates the corresponding UPDATE and DELETE statement. Next you override the original SELECT statement with the new JOIN. I'm pretty sure that from now on, if you ever click finish on the wizard you will lose your UPDATE and DELETE commands. That is why i bind my tableadapters to sproc's. It was kind of a pain remembering which tableadapter were overwritten.