Bryan Reynolds

Software, Business, Life . . .


Bryan Reynolds
  Bryan Reynolds Linked In
E-mail me Send mail

Recent comments



The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2016

Using LINQ to SQL instead of a sub report.

The post described how to use "Linq to SQL" with XtraReports and DevExpress for displaying relational data within a column of a report.


As a developer's we have all made many reports and used many tools to generate reports. Currently I am working with DevExpress's XtraReports Suite and "LINQ to SQL".  For web based relational database system reporting there tools are pretty easy to work with. 


Below is a sample output from a report that I am working on.  The interesting challenge was the 1 to many relationship within the Competitor column.




The competitor column needs to report all competitors from a 1 to many relationship table. There are many possible solutions to this common problem.  Using XtraReports you can make a subreport, you could set the text of the control by pulling the data from the database, or you could have a stored procedure format that data in the SQL engine you use prior to printing the report.


I chose to use "LINQ to SQL" for its quick implementation and ease of use.  If you have not had a chance to look at this technology take a look at Scott Gu's post.


In shorts order I was able to have a strongly type set of code that loaded a string with the exact format I needed to solve the problem.

   1: private void Detail_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
   2:    {
   3:        var projectID = Convert.ToInt32(GetCurrentColumnValue("ProjectID"));
   4:        var db = new dbDataContext();
   6:        var queryCompitetion = from recs in db.ProjectCompetitions
   7:                               join comprecs in db.Competitions on recs.CompetitionID equals comprecs.CompetitionID
   8:                               where recs.ProjectID == projectID
   9:                               select new
  10:                                          {
  11:                                              recs.Distance,
  12:                                              recs.Direction,
  13:                                              comprecs.Name,
  15:                                          };
  17:        var stringBuiler = new StringBuilder();
  19:        for (int i = 0; i < queryCompitetion.ToList().Count-1; i++)
  20:        {
  21:            var recs = queryCompitetion.ToList()[i];
  22:            stringBuiler.AppendFormat("{0}/ {1}{2}    ", recs.Name, recs.Distance, recs.Direction);
  24:            if ((i + 1) % 2 == 0) stringBuiler.Append("\r\n");
  25:        }
  28:        xrCompetitorCell.Text = stringBuiler.ToString();
  29:    }


The "Detail_BeforePrint" function fires off before each report detail line is displayed.   Using the GetCurrentColumnValue function I retrieved the ProjectID for the current line.  Used "Linq to SQL" to grab the appropriate data using strong typing.  Iterated through the result and add the formatted string to the "xrCompetitorCell".  The "xrCompetitorCell" is a property of the report and generated via DevExpress's designer.


Hope this was useful from you.



Digg It!DZone It!StumbleUponTechnoratiRedditDel.icio.usNewsVineFurlBlinkList

Currently rated 3.0 by 5 people

  • Currently 3/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Posted by Bryan on Wednesday, April 30, 2008 7:21 AM
Permalink | Comments (1) | Post RSSRSS comment feed

Related posts


Melayu Boleh us

Wednesday, May 13, 2009 7:30 AM

Melayu Boleh

yes that right ..this is what i want . tHANK yOOU