The above tutorials hopefully provide the basic background
needed to create DAL’s using the DataSet designer and then use them from code.
Using the above knowledge, you should be able to go off and
quickly create the following strongly-typed TableAdapters with associated data
methods pretty easily (note that the designer and query builder can be used to
avoid having to manually write any of the SQL queries below – and it will
auto-suggest subsequent queries for each adapter after you add the first one).
SuppliersTableAdapter:
GetAllSuppliers:
SELECT SupplierID, CompanyName, ContactName,
ContactTitle, Address, City, Country, PostalCode, Phone
FROM Suppliers
GetSuppliersByCountry:
SELECT SupplierID, CompanyName, ContactName,
ContactTitle, Address, City, Country, PostalCode, Phone
FROM Suppliers
WHERE Country=@Country
GetSupplierBySupplierId
SELECT SupplierID, CompanyName, ContactName,
ContactTitle, Address, City, Country, PostalCode, Phone
FROM Suppliers
WHERE SupplierId=@SupplierID
GetUniqueSupplierCountries
SELECT DISTINCT Country
FROM Suppliers
CategoriesTableAdapter:
GetAllCategories:
SELECT CategoryID, CategoryName, Description
FROM Categories
GetCategoryByCategoryId
SELECT CategoryID, CategoryName, Description
FROM Categories
WHERE CategoryId=@CategoryId
EmployeesTableAdapter:
GetAllEmployees:
SELECT EmployeeID, LastName, FirstName, Title,
HireDate, Country, ReportsTo
FROM Employees
GetEmployeeByEmployeeID
SELECT EmployeeID, LastName, FirstName, Title,
HireDate, Country, ReportsTo
FROM Employees
WHERE EmployeeID=@EmployeeID
GetEmployeesByManager:
SELECT EmployeeID, LastName, FirstName, Title,
HireDate, Country, ReportsTo
FROM Employees
WHERE ReportsTo = @ReportsTo
ProductsTableAdapter:
GetAllProducts:
SELECT ProductID, ProductName, SupplierID,
CategoryID, UnitPrice, UnitsInStock, Discontinued,
(SELECT Suppliers.CompanyName from Suppliers where Suppliers.SupplierId=Products.ProductId) as SupplierName
FROM Products
GetProductsBySupplierId:
SELECT ProductID, ProductName, SupplierID,
CategoryID, UnitPrice, UnitsInStock, Discontinued,
(SELECT Suppliers.CompanyName from Suppliers where
Suppliers.SupplierId=Products.ProductId) as SupplierName
FROM Products
WHERE SupplierID=@SupplierID
GetProductsByCategoryId:
SELECT ProductID, ProductName, SupplierID,
CategoryID, UnitPrice, UnitsInStock, Discontinued,
(SELECT Suppliers.CompanyName from Suppliers where Suppliers.SupplierId=Products.ProductId)
as SupplierName
FROM Products
WHERE CategoryID=@ CategoryID
Most of the above TableAdapters are pretty straight-forward
and simple. The one that is a little more advanced is the
ProductsTableAdapter. Specifically, there in addition to retrieving the
Products table columns I’m also retrieving the CompanyName from the Suppliers
table that maps to the Product’s SupplierId column (it has a foreign key
relationship to the Suppliers table). The above SQL statement will add this as
a read-only “SupplierName” column on our ProductsDataTable.
This will allow me to be more efficient from a database
perspective when I want to build a list UI on top of my Products table, and
save me from having to hit the database an extra time per-row to retrieve this
value when displaying a product list on the site.
The good news is that the data designer can still infer
enough from the product adapter’s SQL statements to still automatically
generate the correct INSERT, UPDATE and DELETE commands for the
ProductsTableAdapter. In cases where you are doing more advanced JOINS across
multiple tables and merging results from multiple places, the data designer
might not be able to generate these automatically. The good news is that you
can still define these manually within the designer (just click on the
table-adapter, and then within its property grid choose to create a new
“Insert” (or update or delete) command and define your own logic).
When finished, my Northwinds data design-surface looks like
this (note how the designer will automatically map and detail foreign-key
relationships):
Figure 27
When I hit save on the Northwinds.xsd file (where all of the
adapters, definitions and relations are declaratively stored in an XML file),
I’ll be able to program and data-bind against any of the objects defined within
it.