Use LINQ as the data source for rdlc reports

Most of the reports are processed by filling data sets with ADO. net. Today, I tried to use LINQ for processing, which is also very convenient, but it took a long time.

Download Sample Code

 

I. Preparations

1. northwind database

2. Development Environment: vs2008 + SQL Server 2008

3. This test uses Web pages. winform is similar. If you are interested, you can test it by yourself.

 

Ii. Prepare a report

This time, we first use rdlc for testing, and next time we use a crystal report. The layout of reports is still very simple. Due to the test, only a few controls are put:

The core control is a table with three columns added, and a label is placed in the header. For ease of query, a parameter p_country is added to the report. After the user selects the control, information can be queried instantly.

 

3. Prepare the data source

This step is critical. The classic dataset drag-and-drop method has been deeply rooted in the hearts of the people. You can drag several tables through Server Explorer and then drag the corresponding fields from the website data source. But what about LINQ? You will find that website data source is empty except for three nodes:

There is no field we need at all. How can we drag it? I found some information and found that it is possible to use the stored procedure. First, define the stored procedure, execute it in the database, and then update northwind. dbml file, drag the newly created stored procedure to the method bar:

Press Ctrl + Shift + S to save all the required fields. Then, when you open the report, you can find the required fields:

The following are the familiar steps: Drag and Drop, center alignment, bold...

 

Iv. BackgroundCode

After the front-end preparation is complete, the rest of the work will be much easier. A reportviewer control is placed on webform to present a report, and a dropdownlist is provided for the user to choose from:

 

Initialize the dropdownlist data source and query all country values in the MERs table ):

Code

Private   Void Fndataini ()
{
Northwinddatacontext CTX =   New Northwinddatacontext ();
VaR result = From C In CTX. MERs
Select C. Country;
Foreach ( String List In Result. Distinct < String > (). Tolist < String > ())
{
This . Ddlcountry. Items. Add (list );
}
}

 

 

Last step: Construct a data source, PASS Parameters, and refresh the report

Code

Private   Void Fnbuilddatasource ( String V_strcountry)
{
Northwinddatacontext CTX =   New Northwinddatacontext ();
VaR datasource = From C In CTX. sp_linqtest (v_strcountry)
Orderby C. customerid
Select C;

Reportparameter rpcountry =   New Reportparameter ( " P_country " , V_strcountry );
This . Rvnorthwind. localreport. setparameters ( New Reportparameter [] {rpcountry });
This . Rvnorthwind. localreport. CES. Add ( New Reportdatasource ( " Sp_linqtestresult " , Datasource. tolist ()));
This . Rvnorthwind. localreport. Refresh ();
}

 

 

The final result is as follows:

 

Summary:

Although data sources can be implemented using LINQ, compared with traditional dataset, the efficiency is worth considering. Especially when the data volume is large, the report response speed is particularly important, currently, Microsoft does not have enough support for the data source of LINQ. What new features will Microsoft expect in vs2010!

This time I also left some questions:

1. Is it feasible to not use stored procedures (or views?

2. can I obtain the rdlc control instance through background programming?

3. Can I operate the RDL language to embed the data into the LINQ language?

4. How can I drag an anonymous data type returned by LINQ?