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

Please visit my new Web Site https://coderstechzone.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.