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();

   5:  

   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,

  14:  

  15:                                          };

  16:  

  17:        var stringBuiler = new StringBuilder();

  18:  

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

  23:  

  24:            if ((i + 1) % 2 == 0) stringBuiler.Append("\r\n");

  25:        }

  26:  

  27:        

  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.

Bryan

I am a big fan of LLBLGen Pro, an n-tier generator and O/R mapper, written mostly by Frans Bouma.  For those of you who are not familiar with this product the true value is its creator and evangelist Frans Bouma.  The quality product, support and enthusiasm brought by him and his organization is refreshing.

Also recently Microsoft has been working on a ASP.NET extension called Dynamic Data.  Dynamic Data currently was scheduled to work with Microsoft O/R mapping initiatives LINQ to SQL and the Entity Framework.  Dynamic Data is designed to allow developers to quickly create CRUD pages surrounded by a base scaffolding with a central place to update UI and Validation logic.  I know that was a mouthful, and if you are looking for more information there are numerous blogs and sites that you can look at to get familiar with this technology. Some of the more prominent links are below.

• MSDN code gallery
• Microsoft Evangelists
  • David Ebbo's Blog
  • Scott Hunter's Blog
  • Marcin Dobosz's blog

Good news!

Both Dynamic Data and LLBLgen will be playing nice.  It started about a month ago when I was talking David Ebbo whether or not the extension was going to work with other O/R tools and with Frans Bouma about Dynamic Data.  After I took Frans through a demo he immediately contacted Microsoft to make it happen.  Today I got the email from him telling me they were going to release the product with the appropriate API to allow his product to integrate with Dynamic Data.

I will hopefully have time to go through the bits Frans sent me today.  I am excited this produced a situation where I get what I believe to be the best of both worlds, for small projects.

Microsoft was not planning on releasing this API until this happened and I applaud both Microsoft and Frans for bringing the tools together for the community to enjoy.

Thanks!

Bryan Reynolds

C# Developer

Ilya Ryzhenkov, a .NET Tools Product Manager at JetBrains, relays some of his thoughts on the var keyword here.

If you had a chance to use some of the latest c# language features you have no doubt heard of var.  Coming from the idea of KISS, keep it simple stupid, I not only agree with Ilya, but would say that this is the way it should have always been.  Especially now that we are in the day of IDE's and IDE addon's like JetBrain's resharper we should be reducing the amount of code we need to maintain and making the syntax easier to read.

If you have not had a chance to check out Microsoft's DynamicData you should.  I have been working on the latest bits via Microsoft Connect and I have been pleased.  It does not fit every need of course.  Its not "Enterprise Level" and we can debate what that means for ever.  Suffice it to say,  if you have a large complex architecture with many developers,  this is not the tool.  Good news is not all projects are like that.  For the many small applications that offices and companies of all size need on a regular basis.  This will fill the bill. 

Below with virtually no code I was able to modify the default templates to make a customer detail screen that shows the customers jobs.  The field captions all the way to the validation are described in a single location to reduce the amount of work you have to do.  Its all about being lazy!  Or better yet code reduction without functionality reduction.

You can see links on the left below that shows a list of links to function of this tiny program.  After talking with the client for about 3 hours.  I generated in 30 minutes a complete site for data entry.  As the customer requested changes I was able to quickly respond to there requests. 

If a field is required via a not null property on your data field via SQL Server.  Then the data validation on the screen is linked as well.

Conclusion

Looks like Microsoft is listening to there customers.  As a software developer you must take a look at this tool.

Bryan MCPD

C# Developer