Saturday, December 15, 2012
Order Form Sample–Part 31

An alternative method for creating the denormalized data set is to create a database view.

Start SQL Server Management Studio. Connect to the database server. Right-click on Databases / Northwind node, and press New Query.

Creating a new query for the 'Northwind' database.

Paste in the following script:

CREATE VIEW [dbo].[OrderFormReport]
AS
SELECT dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity, 
        dbo.[Order Details].Discount, dbo.Orders.OrderDate, dbo.Orders.RequiredDate, 
        dbo.Orders.ShippedDate, dbo.Orders.Freight, dbo.Orders.ShipName, 
        dbo.Orders.ShipAddress, dbo.Orders.ShipCity, dbo.Orders.ShipRegion, 
        dbo.Orders.ShipPostalCode, dbo.Orders.ShipCountry, 
        dbo.Employees.LastName AS EmployeeLastName, 
        dbo.Employees.FirstName AS EmployeeFirstName, 
        dbo.Shippers.CompanyName AS ShipperCompanyName, 
        dbo.Customers.CompanyName AS CustomerCompanyName
FROM   dbo.[Order Details] INNER JOIN
        dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID INNER JOIN
        dbo.Employees ON dbo.Orders.EmployeeID = dbo.Employees.EmployeeID INNER JOIN
        dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID INNER JOIN
        dbo.Shippers ON dbo.Orders.ShipVia = dbo.Shippers.ShipperID

GO

Execute the script to create the database view.

Switch back to the web application generator. Refresh the project, and include the new view in the configuration.

Refreshing the project and including the new view.

Click Design and explore the new data controller.

Newly added 'OrderReport' data controller in the Project Explorer.