DOT NET SOLUTIONS: October 2014

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:

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')

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:
Blank_CrystalReport

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:
DropColumn

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))

        {

            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;

}

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)

            conn.Open()

            Using da As New SqlDataAdapter(comm)

                dt = New DataTable("tbl")

                da.Fill(dt)

            End Using

        End Using

    End Using

    Dim _rdStudents As ReportDocument = New ReportDocument()

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

    _rdStudents.Load(reportPath)

    _rdStudents.SetDataSource(dt)

    CrystalReportViewer1.ReportSource = _rdStudents

End If

Note: Dont forget add below namespaces:

1

2

3

4

5

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:
LiveReport

DOT NET SOLUTIONS

Tuesday 28 October 2014

Pivot in sql

Saturday 18 October 2014

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