Crystal Reports and SQL Server CE

While searching the internet for some inspiration regarding Crystal Reports design layouts, I happened to come across a large number of people who were all asking a similar question: How can I make a Crystal Report that accesses a SQL Server Compact Edition database?

Interestingly enough, nobody seemed to have a simple answer.  Many talked about modifying Crystal to use some of the SQL Server CE libraries, and others said that it couldn’t be done.  Fortunately, there is a very simple way to have Crystal Reports show data from SQL Server Compact Edition (or just about any other source of data you might want to use), and I’m here to show you how.

Say hello to our good friend, the DataSet.

Before going too far into this post, I should mention that I will be posting code that works in Visual Studio 2005 (Version 8.0.50727) with .NET Versions 2 and 3.  Crystal Reports 10 is installed, but this will work for versions 9 through 11. If you’re going to use SQL Server Compact Edition, you will also need to have the .NET Compact Framework installed.  All of my development is now done from an Atom-based netbook, so you don’t need a whole lot of processing power to do any of this.  Why I use a netbook to develop software is a subject that I might discuss in a later post.

I’ll post code for both VB and C#, as these were the two most common requests online.

Disclaimer: This solution is one that I have used numerous times in the past for clients that did not have (or want) a real database in their offices.  The solution that I’m sharing is not the only way to solve this problem, but it’s certainly one of the fastest ones that I picked up back when Crystal Reports was still cool … if such a time ever existed.  If you know of a better way to have CR report from a SQL Server CE data file, I’d love to hear it

How It Works

Because Crystal Reports cannot (easily) connect directly to SQL Server CE’s .sdf files, we need to first retrieve the data for the report in a DataSet (or DataTable if you’re using a flat set).  But before adding any Crystal Report components to the application, we need to first export this DataSet to an XML file.  This only needs to be done while designing the report.

Once we have the XML file made, create a Crystal Report using that XML file as a data source.  This will allow us to position all of the columns just the way we want them.  This can be done like we see in the code below.

C#

[code lang="csharp"]
DataSet myDS = new DataSet();
DataTable dtMyTable = new DataTable("ds");
DataColumn myCol0 = new DataColumn("ColA");
myCol0.DataType = System.Type.GetType("System.String");
myCol0.AllowDBNull = true;DataColumn myCol1 = new DataColumn("ColB");
myCol1.DataType = System.Type.GetType("System.String");
myCol1.AllowDBNull = true;dtMyTable.Columns.Add(myCol0);
dtMyTable.Columns.Add(myCol1);
dtMyTable.AcceptChanges();DataRow myNewRow = dtMyTable.NewRow();
myNewRow["ColA"] = "Hello";
myNewRow["ColB"] = "World!";
dtMyTable.Rows.Add(myNewRow);
dtMyTable.AcceptChanges();myDS.Tables.Add(dtMyTable);
myDS.WriteXml(@"C:\XMLBase.xml");
[/code]

VB

[code lang="vb"]
Dim dt As New DataTable("ds")
dt.Columns.Add("ColA", Type.GetType("System.String"))
dt.Columns.Add("ColB", Type.GetType("System.String"))
dt.AcceptChanges()Dim dtRow As DataRow = dt.NewRow
dtRow.BeginEdit()
dtRow.Item(0) = "Hello"
dtRow.Item(1) = "World!"
dtRow.EndEdit()dt.Rows.Add(dtRow)
dt.AcceptChanges()
dt.WriteXml("C:\XMLBase.xml")
[/code]

Run that code, making changes to the columns and the row values, in order to create a base XML file that we can use inside Crystal. Believe it or not, we’re almost done.

Add a Crystal Reports file to your project and follow the wizard to prepare it.  Choose the “Create New Connection” and “Find Database File”, then select the XML file that you created above.  You should see a screen that looks a little something like the one on the right (Click to enlarge).  At this point, you can create the Crystal Report as you would for a normal database-driven report.  Once you’re done and happy, save the file and let’s get back into the code.

Assigning a DataSet (or DataTable) During Runtime

We’ll need to create a ReportDocument, as this is where we can load the template you just saved.  However, the DataSource will not be the XML file.  Instead, we can feed the file our DataSet.  This allows us to have dynamically created reports each and every time.  From here, we attach the ReportDocument to the Crystal Viewer and watch as everything falls into place.

C#

[code lang="csharp"]
CrystalDecisions.CrystalReports.Engine.ReportDocument myReportDocument;
myReportDocument = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
myReportDocument.SetDataSource(myDS);
myReportDocument.Load(@"C:\Develop\CrystalTest\CrystalReport.rpt");
CrystalReportViewer1.ReportSource = myReportDocument;
CrystalReportViewer1.DataBind();
[/code]

VB

[code lang="vb"]
Dim myReportDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument
myReportDocument.Load(C:\Develop\CrystalTest\CrystalReport.rpt, CrystalDecisions.Shared.OpenReportMethod.OpenReportByDefault)
myReportDocument.SetDataSource(myDS)
Me.crViewer.ReportSource = myReportDocument
Me.crViewer.Show()
[/code]

Give it a try, and it should run fine. Don’t believe it can be this simple? Delete your base XML and run it then. The Crystal Report doesn’t need the XML file after we’ve finished laying out the design. That said, if you want to change the template, you will need to re-create the XML file.

Done and Done

This is just a quick little tip to help you get started on the road to making Crystal Reports in your SQL Server Compact Edition (or any other “unsupported” database)-based application.  By using a DataSet, you could effectively pull data from anywhere and make a usable report.  This post is not an exhaustive explanation by any stretch of the imagination, but it should give you enough to get started.

Have any questions about Crystal Reports or SQL Server Compact Edition?  Just let me know, and I’ll help if I can.