Dynamically Runtime Bind Data into Crystal Report using Asp.net C# VB.Net

In my first Crstal Report Article I have shown “How to Prepare the Environment“. Now in action. Reporting is the most sensitive part of any database driven application. Without reporting system an application never completed. Fortunately we found again crystal report with Asp.net which ease our life as well as reduce a lot of coding. Now simply using drag and drop we can create a report within a minute. This is true, that from other reporting services, crystal report is the most powerful, sophisticated and most popular reporting tools. This is my first article focusing on programming with Crystal Reports with ASP.NET 3.5, 4.0. In this article, I will focus on adding a Crystal Report to an ASP.NET 3.5, 4.0 web site, design the report structure and displaying SQL Server Database data using a CrystalReportViewer server control.

This article will cover:
1. An introduction to creating Crystal report in Asp.Net C# & VB.Net
2. How to runtime bind Sql Server Database data into a Crystal Report CrystalReportViewer

keep in mind 2 things while implementing Crystal Report using asp.net C# or VB.Net:
1. Design the report
2. How to runtime bind 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 first. 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 to reduce the complexity. In this article i will show an example how I develop a crystal report to bind dynamic data in runtime. Again basically the dummy view that I will create is only for design purpose nothing else. After completion of design phase, from code behind I will bind a dynamic dataset which structure will be identical as like 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. To do the example lesson first create the below table & insert some data:

SQL Server Table Structure:

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

Some Sample Data to Insert:

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

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

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

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

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

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

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

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

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

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

As i told that i need to create a dummy view which will be the exact data structure of the reporting requirements. In this case the DB view will be:

CREATE VIEW vw_ListStudent AS
SELECT 0 Roll,'' Name,'' Email,'' Address,GETDATE() AdDate

Note: 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. If any confusion look at the below image:


Now from next window you have to provide the server name, username & password. For Windows integrated authentication tick the “Integrated Security” checkbox. After that, select the database name which contains 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 one can 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 the control under the VS toolbox like below:


Now go to the code behind. Under Page_Load event write the below code:
C# Code:

        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))
                    using (SqlDataAdapter da = new SqlDataAdapter(comm))
                        dt = new DataTable("tbl");

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


            CrystalReportViewer1.ReportSource = _rdStudents;


Note: Dont forget add below namespaces:

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

VB.Net Code:

        If Not IsPostBack Then
            Dim dt As DataTable
            Dim SQL As String = "SELECT Roll,Name,Email,Address,AdDate FROM Student"

            Dim sConstr As String = ConfigurationManager.ConnectionStrings("TestConnection").ConnectionString

            Using conn As New SqlConnection(sConstr)
                Using comm As New SqlCommand(SQL, conn)
                    Using da As New SqlDataAdapter(comm)
                        dt = New DataTable("tbl")
                    End Using
                End Using
            End Using

            Dim _rdStudents As ReportDocument = New ReportDocument()
            Dim reportPath As String = Server.MapPath("Students_CrystalReport.rpt")


            CrystalReportViewer1.ReportSource = _rdStudents

        End If

Note: Dont forget add below namespaces:

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

I think the code is self explanatory no need to describe it. Please don’t forget to modify the ConnectionString from web.config file.

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# even in VB.Net. Still confusion?? Download full example from below:

Download Code Example C#        Download Code Example VB.Net

Posted in .Net, Asp.net, C#, Crystal Report, VB.Net
One comment on “Dynamically Runtime Bind Data into Crystal Report using Asp.net C# VB.Net
  1. sharad says:

    excellent!!! the dynamic database is working fine…thanks

Leave a Reply

Your email address will not be published. Required fields are marked *