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:
- A select * from customers
- 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
{
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()
{
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();
DataSet ds = new DataSet();
ds = objBll.CustomersDataSet();
string path = Server.MapPath(@"Reports\crAllCustomers.rpt");
rd.Load(path);
rd.SetDataSource(ds);
crAllCustomers.ReportSource = rd;
crAllCustomers.DataBind();
crAllCustomers being the Crystal Report Viewer control.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.