Abstract
This article explains how to extract data into a Crystal Report created outside a C# project using xml data definitions and data sets.
Introduction
This requirement was a part of a main C#.net project, where we wanted to develop reports using crystal reports using field definitions. As we took a great deal of effort in arriving at a solution, and information available in the C# corner helped us to a great extent, we decided to submit this code for the benefit of the fellow programmers.
In this project,
- The report is formatted outside the project via Crystal Reports (the .rpt file)
- The data to the report is extracted from a field definition file created as a .xsd extension (as compatible with ADO.Net)
- The report is called and displayed through a form using the crystal report viewer.
The example overview
We have taken the Authors table in the pubs database where au_id, au_lname, au_fname will be printed on the report.
The main requirement is to create the Xml schema file with .xsd extension and bind the data to the .rpt file.
The example handles this in one form with two buttons: The XSD Button to create the .xsd field definition file and the VIEW button to generate the report.
So here is the step by step procedure to arrive at this.
1. Create the .xsd field definitions file depending on the data that we want to extract from a database.
Insert the following code in the click event of XSD button, to create the .xsd file. In our example this file is called the 'sampledatadef.xsd'. This is created in c:\
By inserting the following code into the start form of your project and clicking the XSD button, you will see the sampledatadef.xsd created in the root directory of your c:\
// References used
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
// These are auto generated
private System.Windows.Forms.Button button1;
private System.Windows.Forms.Button button2;
private System.ComponentModel.Container components = null;
/// <summary>
/// This is to generate the field definition file
/// This code needs to be executed, only when there had been a change to the
/// Data fields that are to be extracted.
/// </summary>
private void button1_Click(object sender, System.EventArgs e)
{
SqlConnection mcon_pub;
SqlDataAdapter mda_fld;
// Remember to change data source as applicable.
string constr = "Data source =SERVER;Initial catalog=pubs;User ID=sa;password=";
string sqlstmt = "select au_id, au_lname, au_fname from Authors";
DataSet ds_fldDef = new DataSet();
mcon_pub = new SqlConnection(constr);
mda_fld = new SqlDataAdapter(sqlstmt,mcon_pub);
ds_fldDef = new DataSet();
mda_fld.Fill(ds_fldDef);
ds_fldDef.WriteXmlSchema(@"c:\sampledatadef.xsd");
MessageBox.Show ("Field Definitions Written Successfully");
}
private void button2_Click(object sender, System.EventArgs e)
{
ViewReport mfrm_view = new ViewReport();
mfrm_view.Show();
}
2. Now create the .rpt file in crystal reports using the .xsd field definitions file.
You need to select 'Create new Connections' and take option - Field Defintions. Select the ADO.NET option. In the dialogue box you will be asked to enter the xsd file name. Select the path and click the 'Finish' button.
We have named this report as crystalsample.rpt (saved in c:\)
If the ADO.Net option is not available, you will have to add new components to the installed crystal reports using 'Add/Remove' programs option and include the necessary component to the Crystal Reports installation.
3. Generate the class to hold the data set for the .xsd datadefiniton
This is an important step. The .xsd definitions do not recognize the datasets generated through the normal DataSet class. The report will be displayed only with the headings, if this step is not accomplished!!! (This was a weird experience to us).
This is done though executing the following command through the command prompt tool of the Visual Studio .Net Tools.
xsd.exe /d /l:C# sampledatadef.xsd
(The /d directive tells the tool to generate DataSets, /l specifies the language to use)
This command generates the DataSet class compatible with the .xsd file you created in the path in which you executed the command. The name of the source file will be sampledatadef.cs.
4. Now add this class to your project.
In the source file, the name of the class will be generated as 'NewDataSet'. Rename this name to a name that you desire in the source file. We have named it as ds_SampleDataSet
5. Now generate the code to extract the data from field definition
In our example we have used a new form for this, which is loaded with the click event of the VIEW button.
This is done in an easy set of steps.
- Define a dataset of the newly generated type. In this example
Private ds_sampledataset mydataset = New ds_sampledataset()
- Fill this dataset with the identical data fields that you created for the xsd definition file. In this example they are au_id, au_lname, au_fname of Authors.
- Open a new ReportDocument type class and call the .rpt file you created into it. (Make sure that the necessary references are added to the project - CrystalDecisions.CrystalReports.Engine CrystalDecisions.Shared )
The form to load the report is named as ViewReport and the CrystalReportViewer is added to the form
The essential coding to the form goes as follows:
// References used
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
// private variables defined
private SqlConnection mcon_pub;
private SqlDataAdapter mda_Rpt;
private ds_SampleDataSet ds_xml = new ds_SampleDataSet();
// These are auto generated
private CrystalDecisions.Windows.Forms.CrystalReportViewer crystalReportViewer1;
private System.ComponentModel.Container components = null;
/// <summary>
/// Connects to the database and extracts data into the new dataset type class
/// </summary>
private void SetConnection()
{string sqlstmt = "select au_id, au_lname, au_fname from Authors";
// Remember to change data source as applicable.
string constr = "Data source =SERVER;Initial catalog=pubs;User ID=sa;password=";mcon_pub = new SqlConnection(constr);
mda_Rpt = new SqlDataAdapter(sqlstmt,mcon_pub);
// Note that the data is filled into the new xml type
datasetmda_Rpt.Fill(ds_xml);
}
private void SetReport()
{
ReportDocument rpt = new ReportDocument();
rpt.Load(@"C:\crystalsample.rpt");
rpt.Database.Tables[0].SetDataSource(ds_xml);
crystalReportViewer1.ReportSource = rpt;
}
/// <summary>
/// The load event of this form is named as Form1_Load
/// </summary>
private void Form1_Load(object sender, System.EventArgs e)
{
SetConnection();
SetReport();
}
References :