in .NET

How to use LINQ to SQL for binding data in CrystalReports 2008

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.

Add CrystalReportViewer

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:

LINQtoSQLClass

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:

Selecting Article Class

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):

Add Title To My Report

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?

  • nice job. anyway thanks mohammad mahdi.

  • You’re welcome.

  • sadi

    I try to do the same but I got ""The report has no tables" error message …

  • Thanks for the know-how. There isn’t much info on Crystal Reports using Linq to Sql.
    It took me a while to figure out what your object, "CrystalReport1" was? Maybe I missed something but I discovered that it’s really just of type CrystalDecisions.CrystalReports.Engine.ReportDocument (for anyone who’s confused as I was.)

    You could also just say:
    CrystalReportSource1.ReportDocument.SetDataSource(data);

    given that in your web file you have something like:

    <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server"
    AutoDataBind="True" Height="1055px" ReportSourceID="CrystalReportSource1"
    Width="920px" />

    <CR:CrystalReportSource ID="CrystalReportSource1" runat="server">
    <Report FileName="CrystalReport.rpt">
    </Report>
    </CR:CrystalReportSource>

    Also, you did not mention a "rpt" file as above.

    Also, the linq query may need to include a "new" specifier after "select" to make sure the report does not come out blank.

    Thanks again,
    Would not have be able to learn a quick without your post.

  • @Frank:
    As I said in my post, This is a Windows Application not Web Application.
    When you’re working with CrystalReports in Windows Applications, .NET Framework will create a class for each of your CrystalReports files. You will use the class not file as I used in my post.

  • baixinha

    Hi,

    My english isn’t good. I used your information:

    CrystalReport1 relatorio = new CrystalReport1();
    var data = (from p in baseDados.Cliente select p).ToList();
    relatorio.SetDataSource(data);
    crystalReportViewer1.ReportSource = relatorio;
    (…)

    When I run the aplication in the row relatorio.SetDataSource(data) i have a System.NotSupportedException.

    You know what is my problem?

    Thanks,
    baixinha

  • @baixinha:
    It’s a little confusing but I think the problem is from your Crystal Report.
    Which version of Crystal Report you’re using?
    The above code sample had been tested on Crystal Reports 2008.

  • nesma

    i have web Application and i user like u say by class it get me very strang error
    this is the code

    ClientsDataContext db=new ClientsDataContext();
    var Client = from p in db.ClientMains
    select p.arName;
    CrystalReportSource1.ReportDocument.SetDataSource(Client );
    pp.SetDataSource(Client);
    and the error is
    String cannot have zero length.
    in line
    pp.SetDataSource(Client);
    can u help me:(

  • @nesma:
    As I understood your comment correctly, The problem is that you have a null value in one of your fields you’ve used in your crystal report file.

    Try to manage null values by check them with ISNULL() function in SQL Server, IIF function in VB, and ?? in C#.

    If you still have this problem, just tell me.

  • misxa

    Thanks for the tips. It works for a single table. I tried creating a report out of LinQ query data, which joins two tables. When I debug the project, I can clearly see that query returns valid data, but nevertheless, report viewer shows only column headers on the report… Why is the report empty? I tried only with a sigle text column from each table, which makes things simple, but I got no results. I followed the procedure: created database context, generated valid LinQ data, created report, linked it’s data source to the LinQ data result and finally, added that report to report viewer.

    I lost several hours in vain.
    Please, help…

  • @misxa:
    It’s better to use stored procedure. As you may know, when you’re adding a SP to your application using LINQ to SQL Classes, you can set output type (The default is [b]Auto[/b] which returns an Auto generated type).

    You can use that [b][i]Auto[/i][/b] type or you can create your own class for it.

    When working with LINQ you can create partial classes. Try to use this and contact me if you still have this problem.

  • Deepak

    I am trying to do the same , i mean Crystal Reports with LINQ. The problem i am facing is , when i add .NET Objects from Database Expert, it is not showing me all the data fields(properties) of a Table(Class) on the Database Fields section. Specially it is not showing DateTime and boolean fields. Do you have any idea about the same, what could be the reason.

    Thanks In Advance.

    Deepak

  • @Deepak:
    Thanks for the comment.
    As I know, your problem is from your dbml file not Crystal Reports or something like this; because of your sentences. If you still have this problem, reinstalling Visual Studio may solve that issue.

  • Thanks Mohammad,
    Its Very Good

  • @Iman:
    You’re welcome.

  • Peter

    Dear Sir,

    Thank you for the example. How do you access the .Net Objects if they are in a different class library and in an N-Layer solution. I have this problem with a web application where i have my WebUI and a Model class library with the LINQ classes. It only shows all the classes in the WebUI and not those in the Model class library. It seems that i can not browse to a specific assembly to find the LINQ classes. Can you help me please ?

  • @Peter:
    As I tried, you may need to do it programmatically. I really don’t have any other idea about it. πŸ™‚
    Anyone has?

  • Nick

    Do you know how to create a data source for a report that is not embedded using linq? I have created a blank report and would like to insert a data source for the report and then add fields to the report from the inserted data source. I have figured out how to add the fields to the report using the ReportClientDocument but I can’t seem to figure out how to set a linq query as the data source for the report. Any help you could offer would be greatly appreciated.

  • @Nick:
    As I can understand from your comment, you want to create such a dynamic report and you want to know how to set a LINQ data source to it.
    As I described, You can make a use of SetDataSource method in your crystal report file.
    Please describe more if I understood your question wrong.

  • Nick

    I’ve tried using that and keep receiving errors about having no tables. Courtesy of a helpful coworker I have managed to get a SQL dataset that feeds the report using the SqlDataAdapter’s TableMappings.Add(). Is there something similar to that for LINQ?

  • @Nick:
    Your words made me ask a question. What is the version of your Crystal Reports?
    I will let you know that this mechanism is only works with Crystal Reports 2008 so that you cannot do such a thing on the earlier version of Crystal Reports.

  • Nick

    CR X R2. I’ve managed to get the SQL dataset to work. And The example you laid out above works fine. I really was wanting to use LINQ to manage the dataset for this project, but I guess I’ll use LINQ to gather information to build my statements and just use SQL to build the dataset for CR.

    Thanks for the advice!

  • Nick

    Oops that’s CR XI R2 not X.

  • k

    thanks for your sample,but if i have detail table like ArticlesJoinCategory Table in your sample and want to show its details in the subreport in my crystal report how can i link these 2 tables in crystal report,i did this by dataset but now i dont know how do with datacontext

  • @K:
    If your tables have relations so you can access your child table from the parent.
    assume that you have two tables:
    1. Orders
    2. OrderDetails
    So the OrderDetails table is an object in your Orders table and you can access it.

    Tell me if I didn’t understand your comment correctly and describe a little more.

  • Tim Chimento

    Great Post! I’m having trouble using stored procedures though. If I do a straightforward SELECT in my SP the fields appear under the .NET Objects section in the Database Expert and I can drag the fields on to the report. When I do something like SELECT COUNT (tablename.fieldname) though the field does not display in the Database Explorer. Here’s an example of what I am talking about (this is from the stored procedure):

    select a.Agency,

    (Select count(t.ID) from [FlexFund_Transaction]t where a.id=t.agencyID) as Transactions,
    (Select count(ti.ID)) as Items,
    (Select sum(ti.Amount)) as ‘Amount Spent’

    In this case, the field ‘Agency’ appears in the Database Expert to drag on to my report but the fields ‘Items’ and ‘Amount Spent’ to not display. How can I get these two to display? When I execute the stored procedure in a stand alone query window I get all 3 fields to return.

  • Tim Chimento

    Sorry I mean the ‘Database Expert’ not ‘Databse Explorer’

  • Satrio

    private void crystalReportViewer2_Load(object sender, EventArgs e)
    {
    CrystalReport1 rpt = new CrystalReport1();
    cbsplusDataClassesDataContext db = new cbsplusDataClassesDataContext();
    var report = (from r in db.Student_Details
    select r).ToList();
    rpt.SetDataSource(report);
    crystalReportViewer2.ReportSource = rpt;

    }

    i got NotSupportedException was unhandled in rpt.setdatasource(report);

    can u help me? I use VS2008 pro..
    thx b4…

  • @Tim:
    When you adding a SP to your Database Model, you can set the output type.
    So you can specified an object to it and the SP returns that type.
    This is a LINQ to SQL feature.

    @Satrio:
    You need to set these items in the PreInit event.

  • Tim Chimento

    Is there an example somewhere of how to specify an object for the output type of a stored procedure? Thanks again.

  • @Tim:
    I’ll blog a new post about it for you.

  • Satrio

    "NotSupportedException was unhandled"
    DataSet does not support System.Nullable<>.

    can you help me to fix it? i’m very new to linq n crystal reports..
    thx b4

  • @Satrio:
    Everything is OK in code. Trace your code again maybe the problem is from another part of your code.

  • @Tim:
    Update:
    As I searched the internet about your question, found a lot of articles and stuff; so I decided not to blog this. You can google your question and you’ll find it immediately.

  • Ashwani

    try to do the same but I got ""The report has no tables" error message …

    var data = (from records in objdb.tblFacilities
    select records).ToList();
    orpt.SetDataSource(data);
    CrystalReportViewer1.ReportSource = orpt;

  • Manzi

    Thanks indeed! This is a great job!

  • Brij

    This is nice article.

    Question: I want to develop the report with LINQ class in N-Tier technology. I have tried to create report with .NET objects but it is displayed only from the same project class libraries. In our case, data layer is in different project so can you please explain us how to get the class libraries from other project.

    Thanks in advance.

  • rajah

    how creating dynami report???? in wpf ???and linq???[

  • Riyaz

    Hi
    The example you showed is of LinqtoSql classes (dbml) file, but in my project i have not created dbml file, instead created normal module to create linq classes, how can i select these classes for crystal reports data source?

    Please reply

  • Do you accept guest posts? I would love to write couple articles here.

  • Thanks – Just the info I was looking for.. My search ends here..

  • Please let me know if you are interested to work as article writer for me? I can offer $10/article.

  • Bill Fitzgerald

    While designing the report, after select the table object, I only see string fields and ID fields to select from the table . Dates and money fields don’t appear.
    I’m using the CR designer built into VS 2008

    Any suggestions?
    Thanks

  • what is this! are you kiddin me!

  • MinhTho

    Thanks for everybody, it’s very useful for me

  • Bilal

    I GOT THE FOLLOWING ERROR
    "DataSet does not support System.Nullable<>"
    kindly solve this and if i select any particula field of table it doesn’t show any colum in the report but show the report only.

  • Rusho

    This is VERY GOOD……..THANK YOU

  • Uriel Golab

    Images are broken.
    Cant figure out how to display all the tables and classes.
    CR is only showing up the DataContext.

    anybody can help?

  • Mehdi

    Hi dear Mohammad Mahdi πŸ™‚

    I have a Windows form that uses CrystalReports.
    I want to use LinQ as DataSource for CrystalReports.

    I have a table with 3 columns {Date, FactId, Sum} and I want Show them in my report
    Can you guide me how I can do it?

  • Creating such a report in CR is so easy in Windows applications.
    You have to follow the code I used in the post and you’re done. Do you have any other problems?

  • Many thanks for the exciting blog posting! I really enjoyed reading it, you are a brilliant writer. I actually added your blog to my favorites and will look forward for more updates. Great Job, Keep it up.

  • Adam Tentis

    Did you ever resolve this?

  • Adam

    Did you solve this? I am having the same issue.

  • Elameer

    The problem of not showing all fields in the crystal report is happen with me also and float type is not shown in the crystal report , only can see Varchar(50) in the fields of crystal report , i try to install vb.net 2008 and try to install win7 from scratch , but the same problem , could you please help me

  • Elameer

    to solve problem DataSet does not support System.Nullable<>.
    hust select fields which put in the Crystal report
    i try this and it works with me

  • Elameer

    ' you can use use a where in the code like this
    Dim Rpt As New CrystalReport1()
    Dim DB As New DataClasses1DataContext
    Dim Sql = (From TblName In DB.TblTests _
    Where TblName.Name = "ameer" _
    Select TblName.Name, TblName.Address)
    Rpt.SetDataSource(Sql)
    CrystalReportViewer1.ReportSource = Rpt

  • Elameer

    I try to Uncheck Allow Nulls in the SQL data base then you can See all fields in the .Net Objects at a Crystal report

  • klevin

    is it possible that you could make a video on the steps on doing it? cause i'm a slow learner and difficult to understand what you are doing, plus from my view all pictures cannot be displayed.

  • Have you ever considered about including a little bit more than just your articles? I mean, what you say is fundamental and everything. However just imagine if you added some great pictures or videos to give your posts more, "pop"! Your content is excellent but with images and video clips, this site could certainly be one of the best in its field. Wonderful blog!

    Feel free to visit my homepage :: free e learning [ Tony – http://meubuzz.com/index.php?do=/profile-43445/info/ ]

  • Your final keychain 25300 considered article promotion as well as friends personally.

    Have a look at my website: a [ Daniele – http://www.hram-suoyarvi.ru/content/schluesselanhaenger-info.html ]

  • I spent two days removing the water is left standing, the more problems you will find yourself with. Disinfecting your space helps to ensure that things water damage restoration minneapolis mn that are damaged inside a office building can range from certificates of schooling, colleges, or degrees in the field of loss containment.

    My web-site :: carpet cleaning machines domestic ( erinjjdy.crowdvine.com – http://erinjjdy.crowdvine.com/posts/create )

  • Many SEO tools on the market now will do this for you. With blog posting, directory submission, press release submission, social bookmarking and article submission, desired amount of online traffic can be achieved. Since search engines are now banning and blacklisting certain "Black Hat Tricks" search engine optimization techniques, web designers were looking for new techniques to replace those unscrupulous methods of SEO techniques that they would rely on to get a great ranking for their clients.

    my blog … google seo ranking tennis atp 2013 – http://wiki.musica-electronica.org/index.php?title=Proper_Search_term_Exploration_-_The_inspiration_Of_Virtually_any_Great_Yahoo_Web_optimization_Technique.

  • Roof product will at times become utilized or endured, including bending, leaking, and other damage can lead to weaknesses being found. On older roofs or roofs in very verdant areas, small plants may have roofing supply brooklyn ny to spend more money in near future. Once again roofing supply brooklyn ny the job of installing wood shakes. This can be simply understood by the customer. That is where a roof consultant can end up saving you in the right place in the roofing business may be looking to perform the best work done at a fair price.

    my webpage … carpet shampooer ( http://vanpiepenstein.weebly.comhttp://vanpiepenstein.weebly.com/ )

  • Most homeowners focus and plan well, too; however, the mobile home roof roof repair vs replacement repair is one of the reasons behind the increasing popularity of fiberglass roofing sheets. They're catering the roofing company must be able to explain the process and roof repair vs replacement its advantages.

    Feel free to surf to my site steel roofing ( http://greatbarrierreeftravelpromotions.weebly.comhttp://greatbarrierreeftravelpromotions.weebly.com/ )

  • My brother suggested I might like this web site. He was totally right. This post truly made my day. You can not imagine simply how much time I had spent for this info! Thanks!

    Feel free to surf to my weblog … http://www.energysmart.com.au – Shela – http://Wiki.iesvalledeloja.edurioja.org/index.php/Usuario:LaverneRobertso ,

  • The girls' slick lobbying efforts and palm oil zsl social media campaign persuaded Girl Scouts USA to ditch Southeast Asian palm oil from its girl scout cookie recipe.

    My page … perusahaan sawit di kalbar – http://www.investasikelapasawit.com

  • And so I think we're starting now to see the future of payments in terms of volume. Those areas tend to be on track grosiran pulsa to pass 9 million homes in 2007.

    Have a look at my web site dealer pulsa murah – http://www.youtube.com/watch?v=cP2Ir1mn8x0

  • Heat filled up within the solar power systems has rewards.

    my web site; solar panels – http://www.stariy-surgut.ru/node/74290

  • I know this if off topic but I'm looking into starting my own blog and was curious what all is needed to get set up? I'm assuming having a blog like yours would cost a pretty penny? I'm not very web savvy so I'm not 100% sure. Any suggestions or advice would be greatly appreciated. Cheers

    Feel free to visit my weblog; Arabic Blowjobs – http://www.videoishere.com/user/GennieGil

  • My spouse and I absolutely love your blog and find almost all of your post's to be what precisely I'm looking for. Do you offer guest writers to write content to suit your needs? I wouldn't mind publishing a post or elaborating on some of the subjects you write in relation to here. Again, awesome weblog!

    My weblog :: news – http://surpluspurpose.weebly.com/

  • Spot on with this write-up, I honestly think this web site needs far more attention. I'll probably be back again to read more, thanks for the info!

    Also visit my web site learning Toys – http://blog.travelmate.co.kr/tag/%EC%97%AC%ED%96%89%EA%B0%80%EB%B0%A9%EC%86%8C%EC%9E%AC

  • Hi there, everything is going fine here and ofcourse every one is sharing data, that's in fact fine, keep up writing.

    Also visit my web site :: pest control columbus – http://www.youtube.com/watch?v=BlVnO1XhHjY

  • Actually no matter if someone doesn't understand afterward its up to other viewers that they will help, so here it takes place.

    Here is my web-site :: Facial Indians – http://www.gotitontape.com/user/13506/RNKArlie

  • Very good post. I am facing many of these issues as well..

    Feel free to surf to my blog post: Milf Aunt – http://down.playwares.com/xe/index.php?mid=pws_case&document_srl=87397

  • Posting on Facebook profiles that you don't run can get your page gets attention. With Likebox visitors to your website can see how many "Likes" you've gotten, and they can tell their friends that they like your site too with just the push of a button. You however, were probably surprised by the decision to end the relationship.

    my website :: buy facebook likes – http://youtu.be/kSL-UXlW-rE

  • Hello there! This blog post couldn't be written any better! Looking through this post reminds me of my previous roommate! He constantly kept talking about this. I am going to send this post to him. Pretty sure he'll have a great read. Many thanks for sharing!

    Here is my blog post Cumshots Interracial – http://wiki.sejapan.net/%E5%88%A9%E7%94%A8%E8%80%85:EnriquetaJeanne

  • I needed to thank you for this fantastic read!! I definitely enjoyed every little bit of it. I've got you saved as a favorite to look at new stuff you post…

    Feel free to visit my page BBW Granny Porn Video – http://www.spirit-of-rock.com/wiki/index.php?title=Dating_with_Fat_singles_can_be_a_dream_comes_true_for_males

  • It was too good to be true; they have been being scammed. The particular person they have been coping with wasn't even the proprietor of the home, but they would not come to that realization for one more few days.

    my weblog … ross hamilton connected investors – https://get.connectedinvestors.com/signup