Use LINQ to Retrieve Data for Your Crystal Reports
page 2 of 3
by Vince Varallo
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 45429/ 99

Connecting an Entity Framework Query to a Crystal Report

Step 1: Create New Solution

1.    Launch Visual Studio and select New Project… from the Start Page.

2.    Expand the Visual C# templates and click on the Web templates.  Select "ASP.NET Empty Web Application" and change the name of the project to "CRForEntityFramework".

3.    Click the OK button.

Step 2: Create the Database

1.    Right click on the project file in the Solution Explorer and select "Add New Item…" from the pop-up menu.

2.    Click the "Data" node under Visual C# Items.  Click on the "SQL Server Database" template and name the file "CRTest.mdf".  Click the Add button. You will get a message asking if you want to add the data file to the App_Data folder.  Click the Yes button.

If you don't have SQL Server Express installed you will be prompted to download the install.  If you do have SQL Server Express installed then the database file will be created and you can now add a table.

3.    Right click on the CRTest.mdf file in the Solution Explorer and select "Open" from the pop-up menu.

4.    The database should be displayed in the Server Explorer window.  Right click on the Tables node and select "Add New Table".

5.    A new table will appear in design mode.  Enter "UserAccountId" for the first field and set its DataType to "int" and uncheck the "Allow Nulls" box.  Also change this field to an Identity field by setting the IsIdentity property to "Yes".

6.    Add fields for FirstName, LastName, and EmailAddress.  Make all these fields varchar(50) and do not allow nulls.

7.    Click the Save button on the toolbar and change the name of the table to UserAccount.

8.    Add a few records to this table by right clicking on the table in the Server Explorer and selecting "Show Table Data" from the pop-up menu.  You can then just enter a few records by typing in the FirstName, LastName, and EmailAddress fields.

Step 3: Create the Entity Data Model

Now that you have the database you can create the Entity Data Model using the Entity Framework.

1.    Right click on the project file in the Solution Explorer and select AddàNew Item… from the pop-up menu.

2.    Click on the Data node under the Visual C# templates.  Click on the ADO.NET Entity Data Model template and change the name to CRTest.edmx.  Now click the Add button.

3.    The Entity Data Model Wizard should appear.  Choose "Generate From Database" and click the Next button.

4.    The "Choose Your Connection" screen should appear.  Select the CRTest.mdf connection and click the Next button.

5.    The next screen allows you to select the database objects you want to include in the Entity Data Model.  Expand the Tables node and check the box next to the UserAccount table.

6.    Click the "Finish" button.  The UserAccount entity should appear in the CRTest.edmx file.

Step 4: Create the XML Schema File

There is no direct way to connect a Crystal Report file to an Entity Framework entity but you can create an XML schema file that contains the structure of the Entity and then connect that to the Crystal Report.

1.    Right click on the Project in the Solution Explorer and select Add New Item…

2.    Select the Data node under the Visual C# Templates.

3.    Select the DataSet template, name the file UserAccountSchema.xsd, and click the Add button.

4.    Drag a DataTable object from the toolbox to the UserAccountSchema.xsd file. 

5.    Change the name of the DataTable object to UserAccountSchema.

6.    Right click on the UserAccountSchema object and select AddàColumn from the pop-up menu.

7.    Change the name of the column to UserAccountId and change the DataType to System.Int32.  To change the DataType you can go to the property window and set it there.

8.    Add the FirstName, LastName, and EmailAddress columns the same way but leave the DataType as System.String.

Step 5: Create the Crystal Report

You can now create the report using the XSD file.

1.    Right click on the project and select AddàNew Item…

2.    Select "Reporting" from the list of Visual C# templates and select the Crystal Reports template.

3.    Change the name of the file to UserAccountReport.rpt and click the Add button.

4.    The Crystal Reports Gallery dialog will appear.  Select "As a Blank Report" and click the OK button.

5.    In the Field Explorer window, right click on the Database Fields node and select "Database Expert…" from the pop-up menu.

6.    The Database Expert dialog should appear.

7.    Click the plus sign next to "Create New Connection"

8.    Click the plus sign next to "ADO.NET (XML)"

9.    The ADO.NET (XML) dialog should appear.  Click on the button with three dots to browse to the UserAccountSchema.xsd file you created in the step above.

10. Click the Finish button.

11. You should see the UserAccountSchema under the ADO.NET (XML) node.  Click on this node and then click the ">" button to move this to the Selected Tables list.

12. Click the OK button.

13. In the Field Explorer window you should see the UserAccountSchema under the Databases Fields node.  Click the plus sign next to the table name and the fields should appear.

14. Drag each field to the Details section of the report.

15. Click the save button.

Step 6: Create the ASP.NET Web Page to Display the Report

The last step is to create the ASP.NET Web Page that will display the report in the Crystal Reports Viewer control and bind a query using the Entity Framework to the report. 

1.    Right click on your project file in the Solution Explorer and select AddàNew Item from the pop-up menu.

2.    Click on the "Web" node under Visual C# templates and then select the "Web Form" template.

3.    Change the name of the form to Default.aspx and click the Add button.

4.    Drag a Crystal Report Viewer control from the toolbox to the form.

5.    Double click on the form in design mode to create the Form_Load event handler.

6.    Add the following lines to the using statements.

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

7.    Add the following lines to the Form_Load event handler.

ReportDocument report = new ReportDocument();
report.Load(Server.MapPath("UserAccountReport.rpt"));
 
using (CRTestEntities db = new CRTestEntities())
{
    report.SetDataSource(from u in db.UserAccounts
                       select new { u.UserAccountId, u.FirstName, u.LastName,
                                    u.EmailAddress });
}
 
CrystalReportViewer1.ReportSource = report;

8.    Right click on the Default.aspx file and select "Set As Start Page" from the pop-up menu.

9.    Run the project and you should see your report.


View Entire Article

User Comments

Title: The image links not work   
Name: Gary
Date: 2012-03-05 8:56:05 PM
Comment:
Hi, I found it may very useful but the image link is not work
Title: THATZ ALL I NEED   
Name: LONGNT00455
Date: 2011-04-06 2:35:00 AM
Comment:
SO USEFUL! THANKS VERY MUCH
Title: Great!   
Name: Brian Noronha
Date: 2011-04-05 12:04:17 AM
Comment:
A very insightful way of linking an entity object with an crystal report




Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-06-25 8:28:39 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search