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]
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


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.

No comments:

You can find more about Code OnTime Generator, Data Aquarium Framework, and other great products here.

© 2010 Code OnTime LLC. Intelligent code generation software for ASP.NET. Visit us at