Using Crystal Reports in ASP.Net Making use of strongly typed datasets

• Download CrystalReportAndASPNET - 175.68 KB

Introduction

This simple project will demonstrate how to report using crystal reports. In this example strongly typed datasets are used.

To start using the example you must have the Northwinds database. The example uses the customers table from the Northwinds database. Before starting you should start a new web site from visual studio.

Using the Code

Create the Dataset

To create the dataset right click the web site and select “Add New Item” from the menu. From the list select Dataset.(I have named m y dataset “CustomersDS”. Visual studio will automatically put this and all resulting code under the “App_Code” folder.

After the wizard has added the dataset to the App_Code folder, drag the TableAdapter from the toolbox. On the resulting Wizard, create a SQl connection that will point to the Northwind database. (Note: This connection will be saved in your web.config).. Use the wizard to create either sql statements or Stored procedures to select customers..Create the following:

1. A select * from customers
2. A select * from customers where Country=@Country

Create another dataset that will select distinct Country from the customers table (this will be used for the drop down for the parameterized report).

Accessing the Data

The next thing is to access the data exposed by the dataset in a typical business logic layer. Right click App_Code and select “Add New item” . select class and add a new class and name it accordingly(in my case I named mine “CustomersBLL”..The next thing will be the logic to access this data, I used properties:

private CustomersTableAdapter _Adapter = null;
public CustomersTableAdapter Adapter //this will expose the customersTableAdapter
                                     // which is in the Customers Dataset
{
    get
    {
        if (_Adapter == null)
        {
            _Adapter = new CustomersTableAdapter();
        }
        return _Adapter;
    }
}

The next thing will be to right the different methods to return the data as a datatable and as a dataset:

public CustomersDS.CustomersDataTable GetAllCustomers() //this method will return all
                                                        //customers
{
    return Adapter.CustomersGet(); 
}
public DataSet CustomersDataSetByCountry(string Country)
{
    DataSet ds = new DataSet();
    DataTable dt = GetCustomersByCountry(Country);
    ds.Tables.Add(dt);
    return ds;
}

Create the Report

Add the reports folder and right click it and select “Add New Item”.. then select Crystal Report”. On the resulting dialog select “As a blank report”. On the menus select Crystal Report >>Database>>Database Expert..On the resulting dialog select “Create New Connection” then select “ADO.Net”. An Ado.Net Dialog will open, On File Path navigate to where your dataset is located

After clicking Finish, you must be able to see your dataset. Select the Customers dataset. Click the customers dataset and select it so that it’s on the right panel.

On the field Explorer select Database Fields, this will display all the fields from the dataset. You can then drag the necessary fields to the report area. Save the report. Your report now has the correct schema all you need is to supply the data to the report.

Pass data to the Report

On the necessary aspx page, you can then have the code below to get data to the report.

CrystalDecisions.CrystalReports.Engine.ReportDocument rd =
     new CrystalDecisions.CrystalReports.Engine.ReportDocument();
CustomersBLL objBll = new CustomersBLL(); //instantiate the customersBLL
DataSet ds = new DataSet();
ds = objBll.CustomersDataSet(); //get all customer information into a dataset
string path = Server.MapPath(@"Reports\crAllCustomers.rpt"); //path of the crystal report
rd.Load(path); //load the report to the ReportDocument
rd.SetDataSource(ds);
crAllCustomers.ReportSource = rd;
crAllCustomers.DataBind();

crAllCustomers being the Crystal Report Viewer control.