اضافه کردن جدولهای باقی مانده
این بخش را انجام دهید چون در بخش بعدی باهاش کار داریم
با توجه به روشهای قبلی و کدهای زیر DAL خود را کامل کنید :
• ProductsTableAdapter
o GetProducts:
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued , (SELECT CategoryName FROM
Categories WHERE Categories.CategoryID =
Products.ProductID) 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.ProductID) 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.ProductID)
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.ProductID) 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