Creating an ASP.NET report using Visual Studio 2010 - Part 1

Creating an ASP.NET report using Visual Studio 2010 - Part 1
This tutorial walks you through creating an report based on the Northwind sample database. You
will add a client report definition file (RDLC), create a dataset for the RDLC,
define queries using LINQ to Entities, design the report and add a ReportViewer
web control to render the report in a ASP.NET web page. The report will have a chart
control. Different results will be generated by changing filter criteria. At the
end of the walkthrough, you should have a UI like the following. From the UI below,
a user is able to view the product list and can see a chart with the sum of Unit
price for a given category. They can filter by Category and Supplier. The drop downs
will auto post back when the selection is changed. This demo uses Visual Studio
2010 RTM. This post is split into three parts. The last part has the
sample code attached. Creating an ASP.NET report using Visual Studio 2010 - Part 2 Creating an ASP.NET report using Visual Studio 2010 - Part 3



Lets start by creating a new ASP.NET empty web application called “NorthwindReports”



Creating the Data Access Layer (DAL) Add a web form called index.aspx
to the root directory. You do this by right clicking on the NorthwindReports web
project and selecting “Add item..” . Create a folder called “DAL”. We will store
all our data access methods and any data transfer objects in here.



Right click on the DAL folder and add a ADO.NET Entity data model called Northwind.



Select “Generate from database” and click Next.




Create a connection to your database containing the Northwind sample database and
click Next.



From the table list, select Categories, Products and Suppliers and click next.



Our Entity data model gets created and looks like this:



Adding data transfer objects Right click on the DAL folder and
add a ProductViewModel. Add the following code. This class contains properties we
need to render our report.

public class ProductViewModel

{

    public int? ProductID { get; set; }

    public string ProductName { get; set; }

    public System.Nullable<decimal> UnitPrice { get; set; }

    public string CategoryName { get; set; }

    public int? CategoryID { get; set; }

    public int? SupplierID { get; set; }

    public bool Discontinued { get; set; }

}

Add a SupplierViewModel class. This will be used to render the supplier DropDownlist.

public class SupplierViewModel

{

    public string CompanyName { get; set; }

    public int SupplierID { get; set; }

}

Add a CategoryViewModel class.

public class CategoryViewModel

{

    public string CategoryName { get; set; }

    public int CategoryID { get; set; }

}

Create an IProductRepository interface. This will contain the signatures of all
the methods we need when accessing the entity model. This step is not needed but
follows the repository
pattern.

interface IProductRepository

{

IQueryable<Product> GetProducts();

IQueryable<ProductViewModel> GetProductsProjected(int? supplierID, int? categoryID);

IQueryable<SupplierViewModel> GetSuppliers();

IQueryable<CategoryViewModel> GetCategories();

}

Create a ProductRepository class that implements the IProductReposity above. The
methods available in this class are as follows:

• GetProducts – returns an IQueryable of all products.
• GetProductsProjected – returns an IQueryable of ProductViewModel. The method filters
  all the products based on SupplierId and CategoryId if any. It then projects the
  result into the ProductViewModel.
• GetSuppliers() – returns an IQueryable of all suppliers projected into a SupplierViewModel
• GetCategories() – returns an IQueryable of all categories projected into a CategoryViewModel

public class ProductRepository : IProductRepository

{

/// <summary>

/// IQueryable of all Products

/// </summary>

/// <returns></returns>

public IQueryable<Product> GetProducts()

{

var dataContext = new NorthwindEntities();

var products = from p in dataContext.Products

select p;

return products;

}

/// <summary>

/// IQueryable of Projects projected 

/// into the ProductViewModel class

/// </summary>

/// <returns></returns>

public IQueryable<ProductViewModel> GetProductsProjected(int? supplierID, int? categoryID)

{

var projectedProducts = from p in GetProducts()

select new ProductViewModel

{

ProductID = p.ProductID,

ProductName = p.ProductName,

UnitPrice = p.UnitPrice,

CategoryName = p.Category.CategoryName,

CategoryID = p.CategoryID,

SupplierID = p.SupplierID,

Discontinued = p.Discontinued

};

// Filter on SupplierID 

if (supplierID.HasValue)

{

projectedProducts = projectedProducts.Where(a => a.SupplierID == supplierID);

}

// Filter on CategoryID 

if (categoryID.HasValue)

{

projectedProducts = projectedProducts.Where(a => a.CategoryID == categoryID);

}

return projectedProducts;

}

public IQueryable<SupplierViewModel> GetSuppliers()

{

var dataContext = new NorthwindEntities();

var suppliers = from s in dataContext.Suppliers

select new SupplierViewModel

{

SupplierID = s.SupplierID,

CompanyName = s.CompanyName

};

return suppliers;

}

public IQueryable<CategoryViewModel> GetCategories()

{

var dataContext = new NorthwindEntities();

var categories = from c in dataContext.Categories

select new CategoryViewModel

{

CategoryID = c.CategoryID,

CategoryName = c.CategoryName

};

return categories;

}

}

Your solution explorer should look like the following.

Build your project and make sure you don’t get any errors. In the
next part, we will see how to create the client report definition file using the
Report Wizard. Creating an ASP.NET report using Visual Studio 2010 - Part 2