Working with Crystal Report and ASP.Net

How to create reports using Crystal Reports in ASP.Net?

Introduction

Integrating Crystal Reports with .Net can be a real challenge if you don't know number
of things required for it. I am going to explain you here those things with a very simple example.

Before explaining about implementing crystal report in ASP.Net, i assume you have the knowledge of following:
- ASP.Net 2.0
- C# or VB.Net [I will use C# for this tutorial, you can use VB.Net if you wish.]
- SQL Server 2005
- XML Schema

Now follow below simple steps to impletment crystal reports:
1) Create a Dataset DS which has all the data to be displayed in Report.

• you can create dataset by fetching data from Database or by creating static dataset in your sample application.

2) Create an XML Schema definition.

•   Add an XML Schema by Right Clicking your application -> Add New Item -> DataSet (.xsd File) and create all the tables in schema as per in the Dataset.

3) Add a crystal report viewer in your web form and call it crystalreportviewer1 for example.
4) Add a Crystal Report in your application and select "Using the Report Wizard" in the "Crystal Report Gallery" popup.

Crystal Reports Gallery

5) Now Standard Report Creation Wizard will be shown. 

Select Create New Connection -> ADO.Net -> Make New Connection(if displayed). It will display a Connection Popup.
   - Select  the XML Schema Definition we have created in the File Path and Finish.
   - Now it will display XSD name as a Connection and the Table names under it as per created in XSD. Select all the tables displayed under connection in the "Available DataSource" to the right side in "Selected tables".

Report Creation Wizard

- Now follow the wizard steps.
- select the fields to be displayed in the "Fields" Box. Click Next.
- if you need any grouping in your report, select the Group fields on which grouping will be applied.Click Next.
- if you need any Summary to be displayed in reports, select the summary fields.
- follow the report steps and finish.
6) Format your Crystal Report as per your requirement. You can add Group Section by Right Click in the Report -> Insert -> Group, if you have selected group fields in above steps.
7) Now Turn to your Web page in which you want to display report, where your code will be looked like below.

Declare 2 public variable for ReportDocument and Dataset of type created XSD.

ReportSchema dsschema = new ReportSchema();
ReportDocument rpt = new ReportDocument();

Now write down below code in your button click event where you want to display the report.

//Fetch data from the records dataset to the dataset object of type Schema
dsschema.Tables["Table1"].Load(ds.Tables["Table1"].CreateDataReader());
dsschema.Tables["Table2"].Load(ds.Tables["Table2"].CreateDataReader());

rpt.Load(Server.MapPath("Report.rpt"), OpenReportMethod.OpenReportByTempCopy);

CrystalReportViewer1.ReportSource = rpt;
//CrystalReportViewer1.RefreshReport();
CrystalReportViewer1.DataBind();

CrystalReportViewer1.DisplayGroupTree = false;
//Display the Toolbar in Crystal Report Viewer Control
CrystalReportViewer1.DisplayToolbar = true;
//Hide Business Objects Logo on Crystal Report Viewer Control
CrystalReportViewer1.HasCrystalLogo = false;

There you go...You are now ready to execute above script to display your crystal report.

if you have any query or concerns, then please write down a comment or mail me at shahbrijal@gmail.com

Export Excel File into Dataset

Sometimes it happens that you have substantial amount of data in an excel file and you need to fetch all the data into the database. it's a very labour task if you have to insert data into database manually one by one( just imagine what will happen if you have 1 million rows in an excel file.!!!!)

Below code snippet, written in C# language, will help you overcome these situation.

NOTE: This code snippet is written with consideration that the user has enough knowledge about ASP.Net, ADO.Net and Excel File

DataSet ExcelDataset = new DataSet();
string filePath = @"D:\TestData.xls";
string sConnectionString = string.Empty;
sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath +

           ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;MAXSCANROWS=0\"";
    try
    {
         using (OleDbConnection ExcelConnection = new OleDbConnection(sConnectionString))
         {
                 ExcelConnection.Open();
                 using (OleDbDataAdapter ExcelDataAdapter = new OleDbDataAdapter("SELECT * FROM [Query$]", ExcelConnection))
                 {
                         ExcelDataAdapter.Fill(ExcelDataset);
                 }
         }
    }
    Catch(Exception e)
    {

         throw;
    } 

    finally

    {

    }

Explanation:
In above code snippet, i have used OleDBConnection and OleDBDataAdapter to connect and fetch data from excel file.

Let's understand the ConnectionString:
As you can observe, i have used Microsoft.Jet.OLEDB.4.0 as a Provider here. You can use this provider for excel files of Versions Excel 97 and Excel 2000, where as you can use Microsoft.ACE.OLEDB.12.0 as a provider for excel files of Excel 2003 and later versions.

The Datasource contains the physical location of the excel file you want to import.
Now the most important part of the connectionstring Extended Properties must be specified as Excel 8.0 or Excel 12.0 depending on the version of the MS Excel you are using.
HDR=Yes specifies that there is a header row in the cell range, so the provider will not include first row in the recordset.
IMEX=1 specifies the mixed datatypes are accepted as a TEXT in the cells.