To create the AdventureProducts
DataSet
, we use the Visual Studio 2005 or Visual Web Developer
2005 Express wizards.
The AdventureWorks database must be in the application's App_Data folder. You can download the database from this URL:
http://msdn2.microsoft.com/en-us/library/ms124659.aspx
Start by selecting Add New Item... from the project's context menu
in Solution Explorer. In the dialog that appears, select DataSet and
name it AdventureProducts.xsd
. Visual
Studio asks you for your permission to create the
App_Code directory if it doesn't already exist and
to put the new DataSet file in it. Say yes to
that.
After a few seconds, a new wizard will appears and ask you for a connection. If you haven't already created a connection for the database, click the New Connection... button, choose Microsoft SQL Server Database File, and click Continue. In the next dialog, click the Browse... button and navigate to the location of the datafile. You can test the connection and check that it works before clicking OK.
You should now have a working connection stored in Web.config. Click Next. On the next screen, the wizard asks you to choose the access mode for the table adapter you're creating. Choose "Use SQL statements" and click Next.
Paste this SQL request in the query builder:
SELECT ProductCategoryID, Name FROM Production.ProductCategory ORDER BY Name
and click Next. On the next screen, uncheck the third checkbox, "Create methods to send updates..." (you won't be needing those).
Click Finish.
You've created the ProductCategory
table adapter you'll use to
fill the category DropDownList
.
Right-click on the design surface to bring out the context menu. Choose Add→Table Adapter.... Click Next, as the right connection should already be selected. Click Next, as "SQL statements" is the default you want to use. Copy the following query into the text area of the next screen:
SELECT Production.Product.ProductID, Production.Product.Name, Production.Product.Color, Production.ProductSubcategory.Name AS SubCategoryName, Production.ProductCategory.Name AS CategoryName, Production.ProductPhoto.ProductPhotoID FROM Production.Product INNER JOIN Production.ProductProductPhoto ON Production.Product.ProductID = Production.ProductProductPhoto.ProductID INNER JOIN Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID INNER JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID INNER JOIN Production.ProductPhoto ON Production.ProductProductPhoto.ProductPhotoID = Production.ProductPhoto.ProductPhotoID
and click Next. On the next screen, replace the method names
Fill
and GetData
with FillProducts
and GetAllProducts
and click Finish.
Rename the new table adapter as Product by slowly clicking twice on its title bar or by selecting Rename in its context menu.
Right-click on the ProductTableAdapter bar near the bottom of the adapter representation on the design surface. In the context menu, choose Add Query. Click Next twice. Paste the following query in the text area: SELECT Production.Product.ProductID, Production.Product.Name, Production.Product.Color, Production.ProductSubcategory.Name AS SubCategoryName, Production.ProductCategory.Name AS CategoryName, Production.ProductPhoto.ProductPhotoID FROM Production.Product INNER JOIN Production.ProductProductPhoto ON Production.Product.ProductID = Production.ProductProductPhoto.ProductID INNER JOIN Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID INNER JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID INNER JOIN Production.ProductPhoto ON Production.ProductProductPhoto.ProductPhotoID = Production.ProductPhoto.ProductPhotoID WHERE (Production.ProductCategory.ProductCategoryID = @ProductCategoryID) OR (@ProductCategoryID = − 1)
Click Next and replace the method names Fill
and GetData
with FillProductsByCategory
and GetProductsByCategory
. Click Finish.
Follow the same procedure to create a new table adapter. This
time, name it "ProductDetails," and provide the method names FillProductDetails
and GetProductDetails
:
SELECT Production.Product.ProductID, Production.Product.Name, Production.Product.Color, Production.Product.ListPrice, Production.Product.Size, Production.Product.SizeUnitMeasureCode, Production.Product.Weight, Production.Product.WeightUnitMeasureCode, Production.ProductSubcategory.Name AS SubCategoryName, Production.ProductCategory.Name AS CategoryName, Production.ProductProductPhoto.ProductPhotoID, Production.ProductDescription.Description FROM Production.ProductModelProductDescriptionCulture INNER JOIN Production.ProductDescription ON Production.ProductModelProductDescriptionCulture.ProductDescriptionID = Production.ProductDescription.ProductDescriptionID RIGHT OUTER JOIN Production.Product ON Production.ProductModelProductDescriptionCulture.ProductModelID = Production.Product.ProductModelID AND Production.ProductModelProductDescriptionCulture.CultureID = @CultureID LEFT OUTER JOIN Production.ProductCategory INNER JOIN Production.ProductSubcategory ON Production.ProductCategory.ProductCategoryID = Production.ProductSubcategory.ProductCategoryID ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID LEFT OUTER JOIN Production.ProductProductPhoto ON Production.Product.ProductID = Production.ProductProductPhoto.ProductID WHERE (Production.Product.ProductID = @ProductID)
This time, we'll create queries directly. From the design surface, right-click to bring the context menu and choose Add→Query.... Click "Next" (three times). Paste this code into the text area:
SELECT LargePhoto FROM Production.ProductPhoto WHERE (ProductPhotoID = @ProductPhotoID)
Click Next, name the function GetProductPhoto
, and click Finish.
Right-click on the newly created QueriesTableAdapter and choose Add Query from the context menu. Click Next (three times) and paste this query into the text area:
SELECT ThumbNailPhoto FROM Production.ProductPhoto WHERE (ProductPhotoID = @ProductPhotoID)
Click Next, name the new function GetProductThumbnail
, and click Finish.
You should now have the DataSet
necessary to run the master/details sample (it should look like Figure 9). An
additional DataAdapter
must be added
to the DataSet
to implement the last
sample, the search engine. The instructions to do that are in the sample
description itself (see the section "Creating
a Search Page with a Pop-up Details Preview").