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.

Handling QueryStrings in ASP.Net

The simplest way to access query string values in ASP.Net would be -

Request.QueryString["param_name"]
//or simpler
Request["param_name"]

But if we keep accessing it this way throughout our code, soon the whole think would become very messy. What if we needed to change the name of the parameter at a later point. Then we have to dig through the code to replace all the instances where we accessed the parameter.

Another way would be to read and store the value of the parameter to a variable in page load and use the variable everywhere. But this would mean that the query string is parsed in every page load even if the value is not used for some particular post backs.

So, a better way would be to encapsulate the access to a function or property -

private string ParamName
{
   get
   {
       return Request.QueryString["param_name"];
   }
}

This is much better, now we can just call the property when we need it and keep our page load method clean. This does query the QueryString every time we call the property which we can easily avoid by storing the value in a variable during first call of the property-

private string _paramName;
private string ParamName
{
   get
   {
       if(_paramName != null)
           return _paramName;
       else
       {
           _paramName = Request.QueryString["param_name"];
           return _paramName;
       }
   }
} 

But what if the parameter was not passed during page call? Then the code will throw a Null exception. The QueryString's Get method takes care of the. The method returns empty string if the parameter is not passed. So the best way to handle QueryString would be -

private string _paramName;
private string ParamName
{
   get
   {
       if(_paramName != null)
           return _paramName;
       else
       {
           _paramName = Request.QueryString.Get("param_name");
           return _paramName;
       }
   }
} 

Now we can easily use the property in our code without facing any runtime error-

if(!string.IsNullOrEmpty(ParamName))  // use ParamName