# Wednesday, October 20, 2010

Reporting using Entity Framework

For many years the mantra for implementing business logic in your line of business application has been: “don’t put it in the database, don’t put it in the user interface”. In other words, apply the layers design pattern if at all possible, together with implementing the Model-View-ViewModel (MVVM) or Model-View-Controller (MVC) pattern. Technologies like Entity Framework help us convert data in the database to .NET objects and add logic. Life is good.

Then it is time to create a report. Traditionally reports are run against the database and any self respecting reporting technology will to this day still offer you the option of building a report by querying directly against the database. Out the door goes the reuse of your .NET based business logic, right? No need to fear, Visual Studio offers a solution. Starting with Visual Studio 2005 Microsoft started shipping the ReportViewerControl with Visual Studio. Where SQL Server Reporting Services is full fledged reporting solution, with it’s own server, scheduling engine, user interface, the ReportViewerControl is only a small part of the food chain. The ReportViewerControl will render a report defined by an RDLC file against the data you feed into it. The data can still come from a database, but also from a WCF Service, any .NET object or SharePoint.

Let’s look at a sample. The sample will work on the AdventureWorks2008R2 database which can be downloaded from CodePlex. I’ve then created two views: CustomerView and OrderView. These views limit the data to Massachusetts and join a couple of table to make for more demo-friendly data.

The following script will add the two views that we’ll be using:


USE [AdventureWorks2008R2]
GO
 
/****** Object:  View [dbo].[CustomerView]    Script Date: 01/21/2011 05:53:18 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[CustomerView]'))
DROP VIEW [dbo].[CustomerView]
GO
 
/****** Object:  View [dbo].[CustomerView]    Script Date: 01/21/2011 05:53:18 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[CustomerView]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[CustomerView]
AS
SELECT DISTINCT Sales.Customer.AccountNumber, Person.Person.LastName, Person.Person.FirstName, Sales.Store.Name AS StoreName, Sales.Customer.CustomerID
FROM         Sales.Customer INNER JOIN
                      Person.Person ON Sales.Customer.PersonID = Person.Person.BusinessEntityID INNER JOIN
                      Sales.Store ON Sales.Customer.StoreID = Sales.Store.BusinessEntityID INNER JOIN
                      dbo.OrderView ON Sales.Customer.CustomerID = dbo.OrderView.CustomerID
' 
GO
 
/****** Object:  View [dbo].[OrderView]    Script Date: 01/21/2011 05:54:51 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[OrderView]'))
DROP VIEW [dbo].[OrderView]
GO
 
/****** Object:  View [dbo].[OrderView]    Script Date: 01/21/2011 05:54:51 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[OrderView]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[OrderView]
AS
SELECT     Sales.SalesOrderHeader.SalesOrderID, Sales.SalesOrderHeader.CustomerID, Production.Product.Name AS ProductName, Sales.SalesOrderDetail.OrderQty, 
                      Sales.SalesOrderDetail.UnitPrice, Sales.SalesOrderDetail.UnitPriceDiscount, Sales.SalesOrderDetail.LineTotal, Person.Address.AddressLine1, 
                      Person.Address.AddressLine2, Person.Address.City, Person.Address.PostalCode, Person.StateProvince.StateProvinceCode, Person.Address.SpatialLocation
FROM         Person.StateProvince INNER JOIN
                      Person.Address ON Person.StateProvince.StateProvinceID = Person.Address.StateProvinceID AND 
                      Person.StateProvince.StateProvinceID = Person.Address.StateProvinceID AND Person.StateProvince.StateProvinceID = Person.Address.StateProvinceID INNER JOIN
                      Sales.SalesOrderDetail INNER JOIN
                      Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID INNER JOIN
                      Production.Product ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID ON 
                      Person.Address.AddressID = Sales.SalesOrderHeader.ShipToAddressID
WHERE     (Person.StateProvince.StateProvinceCode = N''MA'')
' 
GO

Next step is to create a WCF service application, add an Entity Framework model and drag the two views onto the model:
image
Next we’ll implement two methods to use the Entity Framework model to select the data and return a list of CustomerView or OrderView objects. 
Note: Normally you would not select all the contents in a view, but since we know that the number of rows in our views are already limited in numbers there is no problem here.

1: using  System;
2: using  System.Collections.Generic;
3: using  System.Linq;
4: using  System.Runtime.Serialization;
5: using  System.ServiceModel;
6: using  System.ServiceModel.Web;
7: using  System.Text;
8: 
9: namespace  AdventureServices
10: {
11:     public  class  AdventureService  : IAdventureService 
12:     {
13: 
14:         #region  IAdventureService Members
15: 
16:         public  List <CustomerView > GetReportCustomerData()
17:         {
18:             using  ( AdventureEntities  db = new  AdventureEntities () )
19:             {
20:                 var  query =  from  customer in  db.CustomerViews select  customer;
21:                 return  query.ToList();
22:             }
23:         }
24: 
25:         public  List <OrderView > GetReportOrderData()
26:         {
27:             using  ( AdventureEntities  db = new  AdventureEntities () )
28:             {
29:                 var  query = from  customer in  db.OrderViews select  customer;
30:                 return  query.ToList();
31:             }
32:         }
33: 
34:         #endregion 
35:     }
36: }
37: 
38: 

Next step is to create a report client. We can use any Windows or ASP.NET application and add start using the ReportViewerControl, but Visual Studio also offers a report application template. Very useful for quick demos:

image

Create the project (skip the wizard), then delete the Report1.rdlc. Add service reference to you AdventureServices and then add a new report using the Report Wizard (on my machine I’ve had poor luck adding the service reference as part of the wizard steps.

image

On the first screen of the wizard give the dataset a name (CustomerDataSet), select the service reference as a datasource and pick CustomerView as the available dataset.

image

On the next screen drag the fields we want to display to the ‘Value’ grid. More complex grouping per row and column is also possible.

image

Since we’re doing a very basic report the next screen offers no selectable options, although we’re starting to see part of our report.

image

Next we pick a style. There are a couple to choose from.

image

We click finish to close the wizard.  Our report looks like this:

image

Use the designer and the tool box to enhance the report just a little:

image

Now the next step is to make sure our form will display this report. Go to the Form1 designer, select the ReportViewerControl and look for the smart tag in the top right hand corner of the control.
Activate the smart tag and you’ll see that you have to option to select a report. Select the report you’ve just created:

image

Notice how at the bottom of the forms designer there now is a design time control:

image

The design time binding source allows us to feed data into the report. So far the ‘links’ that we created to the service have only been used to pull in the schema of the data to be used. The actual data needs to be fed into the report when the form is run. For this we implement a call to our AdventureService:

1: using  System;
2: using  System.Collections.Generic;
3: using  System.ComponentModel;
4: using  System.Data;
5: using  System.Drawing;
6: using  System.Text;
7: using  System.Windows.Forms;
8: 
9: namespace  AdventureReports
10: {
11:     public  partial  class  Form1  : Form 
12:     {
13:         public  Form1()
14:         {
15:             InitializeComponent();
16:         }
17: 
18:         private  void  Form1_Load(object  sender, EventArgs  e)
19:         {
20:             using  ( AdventureServiceReference.AdventureServiceClient  client = new  AdventureServiceReference.AdventureServiceClient () )
21:             {
22:                 this .CustomerViewBindingSource.DataSource = client.GetReportCustomerData();
23:             }
24:             this .reportViewer1.RefreshReport();
25:         }
26:     }
27: }
28: 

Note: Even though our service does not take any parameters to filter the data I hope you can see that it would only take a small amount of coding to add a couple of fields to the form and pass any kind of selection to the service. I leave the actual implementation of that up to you, when you’re building your ‘real’ report.

#    Comments [0] |