Strongly Typed Table Adapters: autogenerating CRUD statements

Visual Studio 2005 features a tool called DataSet designer which lets creating DALs for ASP.NET applications fastly and using strongly typed objects. If you're new to this topic my advice is to check out the nice tutorials written by Scott Guthrie and Scott Mitchell.

When configuring the default query for a TableAdapter, at some time during the wizard you can choose to configure some advanced settings, as shown in the figure below:

Checking "Generate Insert, Update and Delete statements" instructs the wizard to infer the queries for manipulating the table data from the SELECT statement written in the previous wizard step. Visual Studio, in fact, can derive these statements from the SELECT clause in most cases, except when the SELECT clause contains JOINS between tables.

To make an example let me quote the tutorial written by Scott Mitchell which involves the Northwind database, which in turn contains a Products table:

"Note that the ProductsTableAdapters class returns the CategoryID and SupplierID values from the Products table, but doesn't include the CategoryName column from the Categories table or the CompanyName column from the Suppliers table, although these are likely the columns we want to display when showing product information. We can augment the TableAdapter's initial method, GetProducts(), to include both the CategoryName and CompanyName column values, which will update the strongly-typed DataTable to include these new columns as well.
This can present a problem, however, as the TableAdapter's methods for inserting, updating, and deleting data are based off of this initial method."

That is, if you write standard JOINS in the select query then Visual Studio can't create the other statements automatically - like shown below.
SELECT ProductName, CategoryName, SupplierName

FROM Products JOIN Categories ON Categories.CategoryID = Products.CategoryID
JOIN Suppliers ON Suppliers.SupplierID = Products.SupplierID
Luckily a workaround for this issue exists, and consists in specifying the JOIN clauses inside the SELECT list. Back to Scott's words:

"Fortunately, the auto-generated methods for inserting, updating, and deleting are not affected by subqueries in the SELECT clause. By taking care to add our queries to Categories and Suppliers as subqueries, rather than JOINs, we'll avoid having to rework those methods for modifying data. Right-click on the GetProducts() method in the ProductsTableAdapter and choose Configure. Then, adjust the SELECT clause so that it looks like:"
SELECT ProductName,
(SELECT CategoryName FROM Categories
WHERE Categories.CategoryID = Products.CategoryID) as CategoryName,
(SELECT CompanyName FROM Suppliers
WHERE Suppliers.SupplierID = Products.SupplierID) as SupplierName
FROM Products
Using this trick you'll end up having Visual Studio generate those statements for you even if you are joining tables.

kick it on

Published 13 July 2006 11:52 PM by simoneb


# said on 24 July, 2006 06:40 AM
You've been kicked (a good thing) - Trackback from
# Abdu said on 26 July, 2006 07:25 PM
That SQL isn't very efficient. The two selects get executed for every record in the Products table.
# simoneb said on 26 July, 2006 07:40 PM
Adbu I guess you're right, but that's the way Scott Guthrie and Scott Mitchell did it, and well, if you're working with DataSets you don't care much about performance, do you?
# Matthew A. Schneider said on 09 August, 2006 11:50 AM
Simone, (if you would indulge my ignorance) when might I care about the efficiency of this particular SQL statement? Why shouldn't I be concerned when working with DataSets?
# simoneb said on 09 August, 2006 01:16 PM
Matthew, it's on record that DataSets are not as efficient as other ways of working with data, but that's not what I'm talking about here. You can google for it and you'll sure find what you're looking for.
# Mike said on 09 October, 2006 04:23 PM
simoneb, but how do you get the second statement into the VS 2005 gui. I'm using the datasource control wizard and it won't allow you to paste or build that kind of statment when selecting "specify columns from a table or view". If you choose specify custom SQL statement then you have to build the update and delete statments yourself? Am I missing something? thx for your help
# Mike said on 09 October, 2006 04:46 PM
How can you get the configure data source wizard to let you input this kind of SQL statement (embedded selects) in your query. I can't seem to figure this out. thx Mike
# simoneb said on 14 October, 2006 02:39 AM

You'll have to wite your own sql query for the select operation, the others will be autogenerated.

# Artur said on 28 March, 2007 05:27 AM
Sub query dont work with oracle database?
# simoneb said on 28 March, 2007 05:22 PM

I can't help you since I never worked with Oracle, you should ask on a Oracle forum.

This site



This Blog




  • MaximumASP