Friday, July 5, 2013

How to create crystal report in using Dataset

Introduction: In this post I will explain how we can create crystal report in using Dataset.

In the last articles I have explained How to create Crystal report in using Store Procedure.
Take a New project. Here I take project name Salary_Details. After go to Solution Explorer and right click on Project >> Add >>New Item as mention in below attached image:

Add new item window will be open. Click on Data and select Dataset. As shown in snapshot:

Here I keep its name Employee_Detail.xsd. After that right click Add>>Datatable as shown in below attached snapshot:

Datatable will be created as shown below. After that again Right click Add>>Column. See below
attached snapshot:

Add the fields to Datatable of table from which you want to show detail. See below attached snapshot:

After that goes to Solution Explorer and Right clicks on Project >> Add >>New Item.
Add new item window will be open. Click on Reporting >> Crystal reports. Here i keep its name Employee_Salary.rpt. Now you see Crystal reports wizard will be open. See below attached snapshot:

Select As a Blank report. Now you see Employee_Salary.rpt page will open.
After that Right click (any where in reports file) >> Database >> Database Expert as show in below attached image:

Note: if connection was not made already than Click on Create New Connection.
New OLE DB (ADO) window will be open. Select Sql server Native as mention in snapshot:

After click on Next button which will redirect you to next page. Here enter your Sql server information. Here I enter sql server name and select Database name. See the attached snapshot:

Note: Select integrated Security if your Authentication mode is Window Authentication.
Click on Next button again and on next page click on Finish button.
New Data window will be open. Select Project Data>>ADO.NET Datasets >>Datatable as shown in image:

After you will be on Reports page again. Now go to Field Explorer>> Database Fields. Drag and drop the fields from Database fields to Reports Details section.

Note: Don’t forget to add Colum headline in Page Header section. See the attached snapshot:

You can also check the layout of Report. Click on Main Report Preview tab.Now add a new webform to Project. Drag and drop the CrystalReportViewer control from Toolbox >> Reporting. See attached snapshot:

Now go to .aspx.cs page and write the below mention code:

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

protected void Page_Load(object sender, EventArgs e)
                ReportDocument Report = new ReportDocument();
                Employee_Detail dsNew = Fillreport("select * from EMPLOYEE_INFORMATION");
                CrystalReportViewer1.ReportSource = Report;

        private Employee_Detail Fillreport(string Query)
                string Connection = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
                SqlConnection con = new SqlConnection(Connection);
                SqlCommand cmd = new SqlCommand(Query, con);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                Employee_Detail dsSalary = new Employee_Detail();
                adp.Fill(dsSalary, "DataTable1");
                return dsSalary;            

In VB (.aspx.vb)

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

Private con As New SqlConnection("Data Source=SYS-1F78031ED0A;Initial Catalog=TestBlog;Integrated Security=True")
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Dim cmd As New SqlCommand("CRYSTAL_REPORT", con)
            cmd.CommandType = CommandType.StoredProcedure
            Dim adp As New SqlDataAdapter(cmd)
            Dim ds As New DataSet()
            Dim report As New ReportDocument()
            CrystalReportViewer1.ReportSource = report
        Catch ex As Exception
        End Try
    End Sub

Now run the project and check the result.

Is it helpful?

If yes post your comment to admire my work. You can like me on Facebook, Google+, Linkedin and Twitter via hit on Follow us Button and also can get update follow by Email.

No comments:

Post a Comment