Crystal Report with ADO.NET(XML)

Crystal Report is a great tool for reporting. I have used it couple of times to create reports. When I use crystal reports though, I like to keep presentation layer (that is the report file) and the data access layer separate. Therefore I use DataTable to fetch the data from database and load it to crystal report.

The code for fetching the data and loading to crystal report looks someting like this -

DataTable dt = GetDataFromDB(); 
using(ReportDocument rdoc = new ReportDocument())
{
    rdoc.Load("/path/to/rpt/file.rpt");
    //set datasource
    rdoc.SetDataSource(dt);
    //set parameters
    rdoc.SetParameterValue("","");
    //export to pdf
    rd.Export(ExportFormatType.PortableDocFormat);
}

This does separate the data layer from presentation layer at runtime. But during crystal report design the report needed database as source to get the fields. This can be overcome by using XML as datasource. For that, we first need to convert our data to XSD. I have written a code snippet that does just that.

DataTable dt = new DataTable();
using(SqlCommand com = new SqlCommand())
{
    using(com.Connection = new SqlConnection(_connectionString))
    {
        com.CommandText = "your sqlcommand";
        using(SqlDataReader dr = com.ExecuteReader())
        dt.Load(dr);
    }
}
dt.WriteXmlSchema("xsd file name");

This code can easily be run using Linqpad or Notepad++ plugin cs-script

Once I have the XSD, I just need to add the file as data source by selecting ADO.NET (XML) and selecting the file.

Note: Sometimes the XSD file contains complex data types which can be edited to simple types by opening up the file in notepad.

Now, if the data type of any column of the query changes, it can be easily changed in the XSD file and the datasource in crystal report file can be refreshed. Even if the source database changes in future (like form MS Sql to Oracle or anything else), no changes are needed in the crystal report file.