Creating Reports in C# using Crystal Reports with Xml data Definitions

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.