Dynamic Crystal Report with C#

This program shows how to dynamically load data from a database and application into the Crystal Report

Top of Form

• Download source - 57.25 KB

Introduction

This program shows how to dynamically load data from a database and application into the Crystal Report. By using this program, we can customize a Crystal Report to some limit at run time of the application like specifying which field (Columns of a particular table) should be displayed in the report.

Background

This problem arose because a group of students from SLIIT asked me how to dynamically generate a Crystal Report using C# 2.0 (.NET 2005). I tried to find a solution for this by searching many forums and sites, but unfortunately I couldn't find any solution for that. Some forums said that there is no way to create dynamic Crystal Reports using .NET 2005. Finally, I found a way to do that.

Using the Code

1. Create a C# project or add a Form to your existing project.

Now you can add Checkboxes that correspond to columns of a particular table that should be displayed in the Crystal Report and CrystalReportViewer control to the form.

For this demonstration, I have created a database called db1.mdb (in bin\Debug) using Access and created a table called Customer.

2. Add a DataSet (*.xsd file) to your project using add -> New Items in solution explorer. After that, add a DataTable to the DataSet.

Add columns to DataTable and name them Column1, Column2, and so on. The number of columns depends on how many columns should be displayed in the Crystal report.

3. Add a Crystal Report into the project and using the Report Wizard, choose ADO.NET DataSets of the Project data source as the data source of the Crystal Report and select Customer data table of DataSet1 as the selected table of the Crystal Report. Then select fields to be displayed in your report. Then remove Column1…, Column5 objects in Section 2 of the Crystal Report.

4. Now add parameters called col1, col2… col5 (the number of parameters should be equal to the number of columns displayed in the Crystal Report.) using Field Explorer.

5. Add the following method to your Form for Create SQL SELECT query and assign values to parameters of the Crystal Report according to user selected columns that should be displayed on your report.

Collapse

/// <summary>

/// This method is used to

/// 1. create SELECT query according to the selected column names and

/// 2. create parameters and assign values for that parameter

/// that correspond to the crystal report.

/// NOTE: This parameter is used to display Column names of the

/// Crystal Report according to the user selection.

/// </summary>

/// <returns></returns>

private string CreateSelectQueryAndParameters()

{

    ReportDocument reportDocument;

    ParameterFields paramFields;

   

    ParameterField paramField;

    ParameterDiscreteValue paramDiscreteValue;

    reportDocument = new ReportDocument();

    paramFields = new ParameterFields();

              

    string query = "SELECT ";

    int columnNo = 0;               

    if (chbCode.Checked)

    {

        columnNo++;

        query = query.Insert(query.Length, "Code as Column" +

        columnNo.ToString());

        paramField = new ParameterField();

        paramField.Name = "col" + columnNo.ToString();

        paramDiscreteValue = new ParameterDiscreteValue();

        paramDiscreteValue.Value = "Customer Code";

        paramField.CurrentValues.Add(paramDiscreteValue);

        //Add the paramField to paramFields

        paramFields.Add(paramField);

    }

    if (chbFirstName.Checked)

    {

        columnNo++;

        if (query.Contains("Column"))

        {

            query = query.Insert(query.Length, ", ");

        }

        query = query.Insert(query.Length, "FirstName as Column" +

        columnNo.ToString());

       

        paramField = new ParameterField();

        paramField.Name = "col" + columnNo.ToString();

        paramDiscreteValue = new ParameterDiscreteValue();

        paramDiscreteValue.Value = "First Name";

        paramField.CurrentValues.Add(paramDiscreteValue);

        //Add the paramField to paramFields

        paramFields.Add(paramField);

    }

    if (chbLastName.Checked)

    {

        columnNo++; //To determine Column number

        if (query.Contains("Column"))

        {

            query = query.Insert(query.Length, ", ");

        }

        query = query.Insert(query.Length, "LastName as Column" +

        columnNo.ToString());

                       

        paramField = new ParameterField();

        paramField.Name = "col" + columnNo.ToString();

        paramDiscreteValue = new ParameterDiscreteValue();

        paramDiscreteValue.Value = "Last Name";

        paramField.CurrentValues.Add(paramDiscreteValue);

        //Add the paramField to paramFields

        paramFields.Add(paramField);

    }

    if (chbAddress.Checked)

    {

        columnNo++;

        if (query.Contains("Column"))

        {

            query = query.Insert(query.Length, ", ");

        }

        query = query.Insert(query.Length, "Address as Column" +

        columnNo.ToString());

                       

        paramField = new ParameterField();

        paramField.Name = "col" + columnNo.ToString();

        paramDiscreteValue = new ParameterDiscreteValue();

        paramDiscreteValue.Value = "Address";

        paramField.CurrentValues.Add(paramDiscreteValue);

        //Add the paramField to paramFields

        paramFields.Add(paramField);

    }

    if (chbPhone.Checked)

    {

        columnNo++;

        if (query.Contains("Column"))

        {

            query = query.Insert(query.Length, ", ");

        }

        query = query.Insert(query.Length, "Phone as Column" +

        columnNo.ToString());

        paramField = new ParameterField();

        paramField.Name = "col" + columnNo.ToString();

        paramDiscreteValue = new ParameterDiscreteValue();

        paramDiscreteValue.Value = "Phone";

        paramField.CurrentValues.Add(paramDiscreteValue);

        //Add the paramField to paramFields

        paramFields.Add(paramField);

    }

    //if there is any remaining parameter, assign empty value for that

    //parameter.

    for (int i = columnNo; i < 5; i++)

    {

        columnNo++;

        paramField = new ParameterField();

        paramField.Name = "col" + columnNo.ToString();

        paramDiscreteValue = new ParameterDiscreteValue();

        paramDiscreteValue.Value = "";

        paramField.CurrentValues.Add(paramDiscreteValue);

        //Add the paramField to paramFields

        paramFields.Add(paramField);

    }

          

    crystalReportViewer1.ParameterFieldInfo = paramFields;

   

    query += " FROM Customer" ;

    return query;

}

//

6. Add the following method to the button click event to display a report when the user presses the button:

Collapse

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.OleDb;

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.ReportSource;

using CrystalDecisions.Shared;

using CrystalDecisions.Windows.Forms;

namespace app5

{

    public partial class Form1 : Form

    {

        CrystalReport1 objRpt;

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            objRpt = new CrystalReport1();

            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

                "Data Source=|DataDirectory|\\db1.mdb";

          

            //Get Select query String and add parameters to the

            //Crystal report.

            string query = CreateSelectQueryAndParameters();

            //if there is no item select, then exit from the method.

            if (!query.Contains("Column"))

            {

                MessageBox.Show("No selection to display!");

                return;

            }

            try

            {

                OleDbConnection Conn = new OleDbConnection(connString);

                OleDbDataAdapter adepter =

                new OleDbDataAdapter(query, connString);

                DataSet1 Ds = new DataSet1();

                adepter.Fill(Ds, "Customer");

               

                objRpt.SetDataSource(Ds);

                crystalReportViewer1.ReportSource = objRpt;

            }

            catch (OleDbException oleEx)

            {

                MessageBox.Show(oleEx.Message);

            }

            catch (Exception Ex)

            {

                MessageBox.Show(Ex.Message);

            }

        }

Case 2

C#.NET
---------

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

private CrystalReport1 crReportDocument = new
CrystalReport1 ();
private Database crDatabase;
private Tables crTables;
private Table crTable;
private TableLogOnInfo crTableLogOnInfo;
private ConnectionInfo crConnectionInfo = new
ConnectionInfo ();

//Setup the connection information structure
//to log on to the data source for the report.
// If using ODBC, this should be the DSN. If using
// OLEDB, etc, this should be the physical server name


crConnectionInfo.ServerName = "DSN or
Server Name";

// If you are connecting to Oracle there is no
// DatabaseName. Use an empty
// string i.e. crConnectionInfo.DatabaseName = "";

crConnectionInfo.DatabaseName = "DatabaseName";
crConnectionInfo.UserID = "Your UserID";
crConnectionInfo.Password = "Your Password";

// This code works for both user tables and stored
//procedures

//Get the table information from the report
crDatabase = crReportDocument.Database;
crTables = crDatabase.Tables;

//Loop through all tables in the report and apply the
//connection information for each table.
for (int i = 0; i < crTables.Count; i++)
{
crTable = crTables [i];
crTableLogOnInfo = crTable.LogOnInfo;
crTableLogOnInfo.ConnectionInfo =
crConnectionInfo;
crTable.ApplyLogOnInfo(crTableLogOnInfo);

//If your DatabaseName is changing at runtime, specify
//the table location. For example, when you are reporting
//off of a Northwind database on SQL server
//you should have the following line of code:

crTable.Location = "Northwind.dbo." +
crTable.Location.Substring(crTable.Location.LastIndexOf
(".") + 1)
}

//Set the viewer to the report object to
//be previewed.

crystalReportViewer1.ReportSource =
crReportDocument;

Case3

Runtime dynamically bind data into a crystal report using Asp.net C#

Email

Written by shawpnendu on Mar-9-10 4:27pm2010-03-09T02:57:24

From:  shawpnendu.blogspot.com

Reporting is the most sensitive part of any database driven application. Without reporting system never completed. Fortunately we found again crystal report with Asp.net which ease our life as well as reduce a lot of coding. Now we just by drag and drop can create a report within a minute. This is true that from other reporting services, crystal report is the powerful and most popular reporting tools in the IT industry. This is my first article focusing on programming with Crystal Reports with ASP.NET 3.5. In this article, I will focus on adding a Crystal Report to an ASP.NET 2.0 or 3.5 web site, design the report structure and displaying the report data using a CrystalReportViewer server control, that shipped with Visual studio.

I hope this article will cover:
1. An introduction to creating Crystal report in Asp.Net.
2. How to bind runtime data into a crystal report.

To implement a crystal report using asp.net or any other languages keep in mind two things:
1. Design the report.
2. How to bind runtime dynamic data.

1. Design the report:

A lot of way to design a report. Many developers uses many tricks to design a report. As well as i have my own way. Initially when user place a requirement for a report then I think how I can design the report. Definitely the data comes from database. So find out and enlist the database column names. In this regard my own way for simple to complex report is: I always create a dummy database view to design a report. Since any report consists on a set of tabular data, that's why I use a view to design the report. In this article i will show an example how I develop a crystal report to bind dynamic data in runtime. Basically the dummy view that I will create is only to design the report nothing else. After completion of design phase, from code behind I will bind a dynamic dataset which structure is identical as the view. So let's start my example. Suppose my client ABC School & College wants a report which will display all student list. Properties or report fields that I have identified from database are Roll, Name, Email address, Address & Admission date. Let I have a table where column names related to requirement properties are Roll, Name, Email, Address & AdDate. So first create the below table & insert some data:

CREATE TABLE [dbo].[Student]

(

 [Roll] [bigint] NOT NULL,

 [Name] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

 [Email] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

 [Address] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

 [AdDate] [datetime] NOT NULL

)

INSERT INTO Student

Values(1001,'Abul Kalam Azad','akalam@gmail.com','Donia, Dhaka','May 01, 2008')

INSERT INTO Student

Values(1002,'Md. Afsarul Alam','afsar@hotmail.com','Mirpur, Dhaka','May 05, 2008')

INSERT INTO Student

Values(1003,'Md. Jahangir Alam','jalam@yahoo.com','Shonir Akhra, Dhaka','May 07, 2008')

INSERT INTO Student

Values(1004,'Akhtarul Islam','akhtar123@live.com','Savar, Dhaka','May 09, 2008')

INSERT INTO Student

Values(1005,'A.K.M. Parvez','parvez@gmail.com','Uttara, Dhaka','May 11, 2008')

INSERT INTO Student

Values(1006,'Musfiqur Rahaman','musfiq@hotmail.com','Firmgate, Dhaka','May 14, 2008')

INSERT INTO Student

Values(1007,'Golam Rabbani','rabbani@yahoo.com','Dhanmondi, Dhaka','May 15, 2008')

INSERT INTO Student

Values(1008,'PC sarkar','msarkar@gmail.com','Nilkhet, Dhaka','June 01, 2008')

INSERT INTO Student

Values(1009,'ZH Khan','zkhan@tribute.com','Niketon, Dhaka','June 05, 2008')

INSERT INTO Student

Values(1010,'Bimolandu Bikash','bikash@gmail.com','Banani, Dhaka','June 09, 2008')

Run this script will create a table & insert some data which we will display in crystal report. As i told that i need to create a view which will be the exact data structure of reporting requirements. In this case my view will be:

CREATE VIEW vw_ListStudent AS

SELECT 0 Roll,'' Name,'' Email,'' Address,GETDATE() AdDate

Look at the dummy view where i didn't mention any table name just a simple SQL. Keep in mind that the column name must match with your relevant table or tables column name otherwise in runtime crystal report cannot bind data since the report structure based on the view. One another thing note that view's column datatype must be identical with your relevant table columns datatype. Such as for Admission date property i used getdate() as AdDate where getdate() return a datetime value whose column name is AdDate.

Ok now a table with data and also a view is now exist in our database. Our primary task is completed.

Now create a project. Right click on the project and select "Add new item". Select Crystal report from dialog box. The dialog box looks like:



After that select the report as a blank report from the popup like:

Now we need to connect the database where we have added the table student & the dummy view. Now the crystal report is open in your IDE. You found a "Database fields" menu under "Field explorer" window. Right click on "Database fields" menu & then select Database Expert like below:

Now from "Database Expert" window expand "Create new connection" node and double click on "OLEDB" node. A database provider window will open. Now from the provider list select "Microsoft OLEDB Provider for SQL Server" and click Next button. For better understanding look at the below image:

Now from next window you have to provide the server name, username & password. After that select the database name, where you created the student table & the dummy view. The window looks like:

Now click on Finish button. You will go back to the "Database expert" again automatically. Now again expand "OLEDB" node then you found that one node also created which is exactly same as your database name that you have provided earlier. Now expand this node and then expand dbo then expand views and from list choose the dummy view that you have created earlier in this example and move it into the right pane by clicking on ">" command. In my case the scenario looks like:

Now click OK.

Now adding the view into the report object. Look at the Database fields under field explorer you found that your selected view with column list already added. Look:

Now click on the roll column & drag it onto the report details section. Look at the below image:

Same as above, drag other columns into the report details section. Now add a textbox from toolbox into the header section of your report. You can also write something like copyright@.. into the report footer section. Look at the below image how i design the report:

Ok now our report design is completed & we will move forward to bind runtime data into the report.


2. How to bind runtime dynamic data:

In this section i will explain how you can bind data dynamically or runtime into the crystal report. To do that add a crystal report viewer server control into your default aspx page. You will found that control in the VS toolbox like:

Now go to the code behind. Under page load event write the below code:

protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            DataTable dt;

            String SQL = "SELECT Roll,Name,Email,Address,AdDate FROM Student";

            string sConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(sConstr))

            {

                using (SqlCommand comm = new SqlCommand(SQL, conn))

                {

                    conn.Open();

                    using (SqlDataAdapter da = new SqlDataAdapter(comm))

                    {

                        dt = new DataTable("tbl");

                        da.Fill(dt);

                    }

                }

            }

            ReportDocument _rdStudents = new ReportDocument();

            string reportPath = Server.MapPath("Students_CrystalReport.rpt");

            _rdStudents.Load(reportPath);

            _rdStudents.SetDataSource(dt);

            CrystalReportViewer1.ReportSource = _rdStudents;

        }

    }

I think the code is self explanatory no need to describe it. One thing keep in mind that do not forget to add the below namespaces into the default.aspx.cs file:

using System.Data.SqlClient;

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

Now run the project hope you will get the below output report:



That's it. Hope now you can develop or create crystal reports using asp.net C# or even in VB.Net.