How to Create Dynamic Crystalreport with Parameter Passing in C# Windows Application

Step 1 : Create a blank Solution "testParameterReport"

Step 2 : Add new project Windows Forms Application name "testParameterReport"

Step 3 : Run this Script for create Table and Storedprocedure.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CITY]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CITY]( [CITYID] [int] IDENTITY(1,1) NOT NULL,[CITYNAME] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_CITY] PRIMARY KEY CLUSTERED
(
[CITYID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EMPLOYEE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[EMPLOYEE](
[EMPID] [int] IDENTITY(1,1) NOT NULL,
[EMPNAME] [nvarchar](max) NOT NULL,
[DESIGNATION] [nvarchar](max) NOT NULL,
[SALARY] [numeric](18, 2) NOT NULL,
[CITYID] [int] NOT NULL,
CONSTRAINT [PK_EMPLOYEE] PRIMARY KEY CLUSTERED
(
[EMPID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_EMPLOYEE]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[usp_EMPLOYEE]
(
@CITYID int = null
)
AS
BEGIN
SELECT EMPLOYEE.EMPNAME, EMPLOYEE.DESIGNATION, EMPLOYEE.SALARY, CITY.CITYNAME
FROM EMPLOYEE INNER JOIN
CITY ON EMPLOYEE.CITYID = CITY.CITYID
WHERE (EMPLOYEE.CITYID = @CITYID)
END

-
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_CITY]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[usp_CITY]
(
@Task int = 0,
@CITYID int = null,
@CITYNAME nvarchar(MAX) = ''''
)
AS

--- select name of the city from city table order by cityname. -----

IF(@Task = 1)

SELECT CITYNAME
FROM CITY
ORDER BY CITYNAME

IF(@Task = 2)

SELECT CITYID
FROM CITY
WHERE CITYNAME = @CITYNAME

'
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_EMPLOYEE_CITY]') AND parent_object_id = OBJECT_ID(N'[dbo].[EMPLOYEE]'))
ALTER TABLE [dbo].[EMPLOYEE] WITH CHECK ADD CONSTRAINT [FK_EMPLOYEE_CITY] FOREIGN KEY([CITYID])
REFERENCES [dbo].[CITY] ([CITYID])
GO
ALTER TABLE [dbo].[EMPLOYEE] CHECK CONSTRAINT [FK_EMPLOYEE_CITY]

Step 4 : Create from name "Employeereport.cs"



Step 5 : Create Crystalreport "EmployeeReport.rpt" and Bind with Storedprocedure "usp_EMPLOYEE".



Step 6 : Your code behind file like

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using CrystalDecisions;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Windows.Forms;

namespace testParameterReport
{
    public partial class Employeereport : Form
    {
        CrystalReportViewer crystalReportViewer1 = new CrystalReportViewer();
        public Employeereport()
        {
            InitializeComponent();
        }

        private void Employeereport_Load(object sender, EventArgs e)
        {
            BindCitycmb();
        }

        private void BindCitycmb()
        {
            using(EmployeeDataContext db = new EmployeeDataContext())
            {
                var info = db.usp_CITY(1, null, "");

                if(info != null)
                {
                    foreach (var mycity in info)
                    {
                        cmbcity.Items.Add(mycity.CITYNAME.ToString());
                    }                      
                }
            }
        }       

        private int selectcityid()
        {
            int id = 0;
            if (cmbcity.SelectedItem.ToString() != "")
            {
                using (EmployeeDataContext db = new EmployeeDataContext())
                {
                    var info = from p in db.CITies
                               where p.CITYNAME == cmbcity.SelectedItem.ToString()
                               select new {
                                   p.CITYID
                               };

                    foreach(var cid in info)
                    {
                        id = cid.CITYID;
                    }                   
                }
            }

            return id;
        }

        private void btnreport_Click(object sender, EventArgs e)
        {
            if (dockPanel1.Controls.Contains(crystalReportViewer1))
            {
                dockPanel1.Controls.Remove(crystalReportViewer1);
                createreportviewer();
            }
            else
            {
                createreportviewer();
            }
        }

        private void createreportviewer()
        {
            crystalReportViewer1.Refresh();
            crystalReportViewer1.Height = 600;
            crystalReportViewer1.Width = 900;
            crystalReportViewer1.ShowRefreshButton = false;                       
            Createreport();
            dockPanel1.Controls.Add(crystalReportViewer1);
        }

        private void Createreport()
        {
            int cityid = selectcityid();
           //Add your Crystalreport file path.
            crystalReportViewer1.ReportSource = @"D:\testParameterReport\testParameterReport\testParameterReport\EmployeeReport.rpt";
            CrystalDecisions.CrystalReports.Engine.ReportDocument objReport
            = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
            if (cityid != 0)
            {
                try
                {
                    //Add your Crystalreport file path.
                    objReport.Load(@"D:\testParameterReport\testParameterReport\testParameterReport\EmployeeReport.rpt");                   
                    ParameterField f1 = crystalReportViewer1.ParameterFieldInfo[0];
                    ParameterDiscreteValue v1 = new ParameterDiscreteValue();                  
                    v1.Value = cityid;
                    f1.CurrentValues.Add(v1);                   
                }
                catch
                {
                    crystalReportViewer1.Refresh();
                    Createreport();
                }
                finally
                {

                    objReport.Dispose();
                    objReport.Close();
                }
            }
            else
            {
                crystalReportViewer1.Visible = false;
            }           
        }
 
    }
}