Data Access Layer Last Look at Basics

Now a brief look at how to write SQL Queries for TableAdapters. Consider that we want the table Adapter to:

• Hold a custom set of table values, often a collection of values from several tables
• Handle INSERT, UPDATE and DELETE commands on the TableAdapters

If we make a table with SQL Queries that assemble data from different tables, having this functionality is simple as long as the Queries are structured with subqueries for multiple tables. Stated another way, don't use Joins, use Select statements.  Here is an example of this:

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, 

(SELECT CategoryName from Categories WHERE Categories.CategoryID = Products.CategoryID) as CategoryName, (SELECT CompanyName FROM Suppliers WHERE Suppliers.SupplierID = Products.SupplierID) as SupplierName

FROM Products

Following this, you can perform SQL queries with IntelliSense evaluation of changed data.  Next an examination of Business Layer Logic.