تا اینجا، فقط با یک TableAdapter برای یک جدول از پایگاه داده کار کردهایم. در حالی که پایگاه داده Northwind دارای جدوال و ارتباط های بیشتری میباشد که در این برنامه تحت وب از آنها استفاده خواهیم کرد. یک TypedDataSet میتواند شامل چندین DataTable باشد. بنابراین برای تکمیل DAL نیاز است که برای هر جدول یک DataTable اضافه کنیم. برای اضافه کردن یک TableAdapter جدید به DataSet، در محیط طراحی DataSet کلیک راست کرده و Add/TableAdapter را انتخاب کنید. TableAdaperهای زیر را به DataSet اضافه کنید:
· ProductsTableAdapter
o GetProducts:
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
o GetProductsByCategoryID:
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
WHERE CategoryID = @CategoryID
o GetProductsBySupplierID
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
WHERE SupplierID = @SupplierID
o GetProductByProductID
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
WHERE ProductID = @ProductID
· CategoriesTableAdapter
o GetCategories
SELECT CategoryID, CategoryName, Description
FROM Categories
o GetCategoryByCategoryID
SELECT CategoryID, CategoryName, Description
FROM Categories
WHERE CategoryID = @CategoryID
· SuppliersTableAdapter
o GetSuppliers
SELECT SupplierID, CompanyName, Address, City,
Country, Phone
FROM Suppliers
o GetSuppliersByCountry
SELECT SupplierID, CompanyName, Address,
City, Country, Phone
FROM Suppliers
WHERE Country = @Country
o GetSupplierBySupplierID
SELECT SupplierID, CompanyName, Address,
City, Country, Phone
FROM Suppliers
WHERE SupplierID = @SupplierID
· EmployeesTableAdapter
o GetEmployees
SELECT EmployeeID, LastName, FirstName,
Title, HireDate, ReportsTo, Country
FROM Employees
o GetEmployeesByManager
SELECT EmployeeID, LastName, FirstName,
Title, HireDate, ReportsTo, Country
FROM Employees
WHERE ReportsTo = @ManagerID
o GetEmployeeByEmployeeID
SELECT EmployeeID, LastName, FirstName,
Title, HireDate, ReportsTo, Country
FROM Employees
WHERE EmployeeID = @EmployeeID