Saturday 3 August 2019

Populate (Bind) Crystal Report using Entity Framework DataSource in ASP.Net MVC

In this article I will explain with an example, how to populate (bind) Crystal Report using Entity Frameworkas Data Source in ASP.Net MVC Razor.
This article will illustrate how to configure Entity Framework and connect to SQL Server database and then finally use the Entity Framework data to populate Crystal Reports in ASP.Net MVC Razor.
 
 
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
 
 
Creating an Entity Data Model
The very first step is to create an ASP.Net MVC Application and connect it to the Northwind Database using Entity Framework. For more details please refer my article ASP.Net MVC: Simple Entity Framework Tutorial with example.
Following is the Entity Data Model of the Customers Table of the Northwind Database which will be used later in this project.
Populate (Bind) Crystal Report using Entity Framework DataSource in ASP.Net MVC
 
 
Controller
Entity Framework is now configured and hence now we can create a Controller and write code to fetch the records from the Customers Table of the Northwind Database.
Inside the Index Action method, the Top 10 Customer records are fetched and returned to the View.
public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        NorthwindEntities entities = new NorthwindEntities();
        return View(from customer in entities.Customers.Take(10)
                    select customer);
    }
}
 
 
View
Now you will need to Right Click inside the Controller class and click on the Add View option in order to create a View for the Controller.
The Name of the View is set to Index, the Template option is set to Empty, the Model class is set to Customer Entity (the one we have generated using Entity Framework) and finally the Data context class is set to NorthwindEntities.
Inside the View, in the very first line the Customer Entity is declared as IEnumerable which specifies that it will be available as a Collection.
For displaying the records, an HTML Table is used. A loop will be executed over the Model which will generate the HTML Table rows with the Customer records.
There is an HTML Anchor Link which redirects the User to the Reports.aspx (Web Form page discussed later) used for displaying the Crystal Report.
@model IEnumerable<Crystal_Reports_MVC.Customer>
 
@{
    Layout = null;
}
 
<!DOCTYPE html>
 
<html>
<head>
    <meta name="viewport" content="width=device-width"/>
    <title>Index</title>
</head>
<body>
    <h4>Customers</h4>
    <hr/>
    <table cellpadding="0" cellspacing="0">
        <tr>
            <th>CustomerID</th>
            <th>ContactName</th>
            <th>City</th>
            <th>Country</th>
        </tr>
        @foreach (Customer customer in Model)
        {
            <tr>
                <td>@customer.CustomerID</td>
                <td>@customer.ContactName</td>
                <td>@customer.City</td>
                <td>@customer.Country</td>
            </tr>
        }
    </table>
    <br/>
    <a href="~/Reports/Report.aspx">View Report</a>
</body>
</html>
 
 
Configuring Crystal Report in ASP.Net MVC
Note: By default Visual Studio 2010, 2012, 2013 and 2015 does not include Crystal Reports hence you need to download the Crystal Reports 13. Refer my article for details Download Crystal Reports for Visual Studio 2015.
 
1. The very first step is to right click the Project in the Solution Explorer and click Add and then Crystal Reports.
Populate (Bind) Crystal Report using Entity Framework DataSource in ASP.Net MVC
 
2. The above action will open the following dialog where you will need to set a name for the Crystal Report.
Populate (Bind) Crystal Report using Entity Framework DataSource in ASP.Net MVC
 
3. In this dialog, you need to select the Using the Report Wizard option.
Populate (Bind) Crystal Report using Entity Framework DataSource in ASP.Net MVC
 
4. Once you press OK in the above dialog, the Report Wizard starts and you get the following dialog where you need to choose the type of Database connection for your Crystal Report. Since here we are using Entity Framework, you will need to choose the Customers Entity Framework Data Model.
Populate (Bind) Crystal Report using Entity Framework DataSource in ASP.Net MVC
 
5. Next the dialog will ask for the Columns or Fields from the Customer DataSet you need to display on the Crystal Reports. You can choose either all or specific fields as per you choice.
Populate (Bind) Crystal Report using Entity Framework DataSource in ASP.Net MVC
 
NoteThere are more steps in the Wizards but those are Optional hence are not included in this article.
 
Once you click Finish button, your Crystal Report should look as below.
Populate (Bind) Crystal Report using Entity Framework DataSource in ASP.Net MVC
 
6. Crystal Report works only with a Crystal Report Viewer control which is available only in ASP.Net Web Forms and hence for displaying a Crystal Report, you will need to add a Web Forms page.
Populate (Bind) Crystal Report using Entity Framework DataSource in ASP.Net MVC
 
7. Now in the ASP.Net Web Forms page, you will need to add a Crystal Report Viewer control from the ToolBox.
Populate (Bind) Crystal Report using Entity Framework DataSource in ASP.Net MVC
 
 
HTML Markup
<%@Page Language="C#" AutoEventWireup="true" CodeBehind="Report.aspx.cs" Inherits="Entity_Framework_MVC.Reports.Report" %>
 
<%@Register Assembly="CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" Namespace="CrystalDecisions.Web" TagPrefix="CR" %>
 
<!DOCTYPE html>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" Height="400" Width="600"/>
    </form>
</body>
</html>
 
 
Populating the Crystal Report using Entity Framework DataSource
Inside the Page Load event, the Crystal Report is populated with the records of the database using Entity Framework.
protected void Page_Load(object sender, EventArgs e)
{
    CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.None;
    CustomerReport crystalReport = new CustomerReport();
    NorthwindEntities entities = new NorthwindEntities();
    crystalReport.SetDataSource(from customer in entities.Customers.Take(10)
                                select customer);
    CrystalReportViewer1.ReportSource = crystalReport;
    CrystalReportViewer1.RefreshReport();
}
 
 
Screenshots
HTML Grid displaying Entity Framework records
Populate (Bind) Crystal Report using Entity Framework DataSource in ASP.Net MVC
 
Crystal Report displaying Entity Framework records
Populate (Bind) Crystal Report using Entity Framework DataSource in ASP.Net MVC
 
 
Error
In case the Crystal Report is not displaying and also you could get a JavaScript error.
Populate (Bind) Crystal Report using Entity Framework DataSource in ASP.Net MVC
 

No comments:

Post a Comment