Monday, August 9, 2010

Crystal Report in ASP.NET using C# with DataSet and DataTable

To create crystal report in ASP.NET dataSet and dataTable makes the programming so much easy.  In this article i have tried to show how DataSet ease our work and make the report creation flexible.

1. Add a DataSet to your Project and name it as myDataSet  as follows:
Click Yes. It will create a folder named App_Code in the solution window.
Now add a DataTable to myDataSet. Add columns to your DataTable
Your column name and datatype should be the same as that in your database.
Rename the DataTable name into "InfoTable"(without double quotes).

2. Add a Crystal report to the project and name it as myCrystalReport.
    Choose Using the report wizard.
    Choose the data source from project data which is myDataSet in our case.
Click (>) to move the table into 'Selected Tables' section
Choose the columns to be displayed on the report.

After that clicking finish will show the report which we can change the design at our choice.
Now let's see the code behind which will bind the data to the report.

SqlConnection connection = new SqlConnection("server='SERVER-NAME';uid= 'USER-ID'; "pwd='PASSWORD';database=DATABASE_NAME; Connect Timeout=1000000");

        string selectQry = "YOUR-SQL-QUERY-HERE";        
        DataSet myDS = new DataSet();        
        SqlDataAdapter myDataAdapter = new SqlDataAdapter(selectQry, connection);
        myDataAdapter.Fill(myDS, "InfoTable");
        CrystalReportViewer1.ReportSource = myReportDocument;

The main advantage of using DataSet is we can modify the table/Query and get effect from our DataSet. Suppose we may need to add a column in our report. Then we will modify our SQL Query in our program as well as add the new column to the myDataSet DataSet. After that open the Crystal Report and update it by as following way:

Open the Field Explorer -> Right Click on Database Fields -> Click Verify Database. A message will show that the DataSet has changed.
Clicking Ok will update the table field. After that we can use that/those field in our Crystal Report. 
Earlier when i use XML file to make report, that time to update the report is bit difficult. If i had a change in my SQL query then i had to build the XML file again, map to that XML into crystalReport and need to design Crystal Report again.
Using Data Set technique its now so much easy.

Keep coding......... and enjoy programming.............