How to create Crystal report in Asp.net using Store Procedure

Introduction: In this post I try to explain how we can generate Crystal Reports in Asp.net using Store Procedure.

Description:

Firstly I created a Store Procedure to display records CRYSTAL_REPORT.

CREATE PROCEDURE [dbo].[CRYSTAL_REPORT]

AS

BEGIN

     

      SET NOCOUNT ON;

   SELECT * FROM dbo.STUDENT_DETAIL

END

Take a New project. Here I take project name Example. 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 Reporting >> Crystal reports as mention in snapshot:

Here i keep its name Example.rpt. Now you see Crystal reports wizard will be open. See below attached snapshot:

Select As a Blank report. Now you see Example.rpt page will open as shown in attached snapshot:

After that Right click (any where in reports file) >> Database >> Database Expert as show in below attached image:

New Data window will be open. Select OLE DB (ADO) as shown in image:

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. See the below Attached image:

Now you see connection has been added to your Data. Click on Connection >> Database Name (TestBlog) >> Select Store Procedure as shown in below attached snapshot:

Select Store Procedure that for you to create Crystal Reports. Here select the CRYSTAL_REPORT and click on ok button. See the attached snapshot:

After you will be on Reports page again. Now go to Field Explorer>> Database Fields>> CRYSTAL_REPORT as show in image:

Drag and drop the fields from Database fields to Reports Details section as shown in below attached snapshot:

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

You can also check the layout of Report. Click on Main Report Preview tab. See snapshot:

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;

SqlConnection con = new SqlConnection("Data Source=SYS-1F78031ED0A;Initial Catalog=TestBlog;Integrated Security=True");

        protected void Page_Load(object sender, EventArgs e)

        {

            try

            {

                SqlCommand cmd = new SqlCommand("CRYSTAL_REPORT", con);

                cmd.CommandType = CommandType.StoredProcedure;

                SqlDataAdapter adp = new SqlDataAdapter(cmd);

                DataSet ds = new DataSet();

                adp.Fill(ds);

                ReportDocument report = new ReportDocument();

                report.Load(Server.MapPath("Example.rpt"));

                report.SetDataSource(ds.Tables["report"]);

                CrystalReportViewer1.ReportSource = report;

                CrystalReportViewer1.DataBind();

            }

            catch (Exception ex)

            {

            }

        }

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

        Try

            Dim cmd As New SqlCommand("CRYSTAL_REPORT", con)

            cmd.CommandType = CommandType.StoredProcedure

            Dim adp As New SqlDataAdapter(cmd)

            Dim ds As New DataSet()

            adp.Fill(ds)

            Dim report As New ReportDocument()

            report.Load(Server.MapPath("Example.rpt"))

            report.SetDataSource(ds.Tables("report"))

            CrystalReportViewer1.ReportSource = report

            CrystalReportViewer1.DataBind()

        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.