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.
