Today I just decided to create some reports for one of our customers in Visual Studio 2008 and .NET Framework 3.5. Because I'm developing this application using LINQ, I wanted to use LINQ to SQL in CrystalReports. In this post I want to show you how you can use LINQ in order to bind data in CrystalReports.
At first, all sections are regular. You need to create a new application (I used Windows Application) and then add a Crystal Report Viewer to you specified form. When you add this control, you will see that Visual Studio added five new references to your application. These references are:
- CrystalDecisions.CrystalReports.Engine
- CrystalDecisions.Enterprise.Framework
- CrystalDecisions.Enterprise.InfoStore
- CrystalDecisions.ReportSource
You will need these libraries in order to let your application work with CrystalReports.
You made a place to show your reports in. Now you have to get started and create some reports. I want to add a new report by right clicking on my project, point to Add and then New Item. In the list of new items you need to point to Reporting section on the left side of "Add New Item" form and select Reporting. You items on templates side will be filtering to just 3 items:
- Crystal Report
- Report
- Report Wizard
We want to work with CrystalReport here but for now I want to tell you other 2 items will work with Microsoft SQL Server Reporting Service that enables you to create flexible reports like CrystalReports but because CrystalReports is more common, most of developers prefer to use CrystalReports.
We just select Crystal Report and then select Add.
Because we want to work with LINQ classes, I need to create a LINQ to SQL class in order to add my tables to it and then use it in my CrystalReport file (For information about how you can create a LINQ to SQL class, Just check out my previous post: How to use SQL "IN" keyword in LINQ). Here is my tables structure:
All I want is to show records from my Articles table. So I should implement this in my report. As you know when you're working with LINQ to SQL, tables become classes and fields become properties of those classes. When you want to add a new data source to your CrystalReport file (Data Fields section), there are some possibilities. You can use ADO.NET Datasets that enables you to use datasets you implemented in your application; and also you can use .NET Objects. It means that you can use classes in your application to be a data source for your report file. Because LINQ is based on classes, You must use .NET Objects in order to use LINQ to SQL as your Report's data source.
When you expand .NET Objects, you can see all classes in your application and also you can see the classes that made by LINQ class designer. Because I want to use my articles table I have to select Article class in the list as shown below:
If you select this class as your data source you can see all fields available in the Articles table in database field section of our crystal report file. It means you can add each field you want to your report. So I just want to add Title field to my report (Details section):
OK. That's all we need in our report file. Just one section left and that's let your report file know what data to show. You need to assign a database query to your file.
Let's go back to our report viewer file and go to code-behind section. In order to set your report file's properties, you have to create a new instance of your report file as following:
CrystalReport1 rpt = new CrystalReport1();
Now it's time to specify a query to your report file. A CrystalReport report file has a method named SetDataSource() that takes a datasource as the following types:
- DataSet
- DataTable
- IDataReader
- IEnumerable
LINQ to SQL, enables you to convert your data to a List<entityType>, Array and more. As you know List inherits IEnumerable, so All you need is to create a LINQ object and add a ToList() method to it and finally set the "SetDataSource()" method to our LINQ object as you can see in the code below:
ReportSampleDBDataContext db =
new ReportSampleDBDataContext();
var data = (from records in db.Articles
select records).ToList();
rpt.SetDataSource(data);
There is a property in our CrystalReportViewer control name ReportSource that enable you to specify which report file you want show in your CrystalReportViewer control. So you must set it up:
crystalReportViewer1.ReportSource = rpt;
It's working fine, isn't?