How to use RDLC with Asp.net?

I just passed thru a situation where my client does require 2 reports to be developed in SSRS ( SQL Server Reporting Service ). It was not good advise to spend extra money behind SSRS on shared hosting as his requirement was very small. I show some example of RDLC (Report Definition Language Client-side) to my client and he was very excited about that.

I thought it’s nice to share this with all my blog readers and my friends.

Introduction

Step : 1 Create a Parameterized Store Procedure.

(I have used table called ProjectDetail for this tutorial )

CREATE PROCEDURE [dbo].[ReportProjectDetail]

(

@ProjectID INT=NULL ,

@FromDate DATE=NULL,

@ToDate DATE=NULL

)

AS

BEGIN

SET NOCOUNT ON;

BEGIN TRY

SELECT ProjectID,

ProjectName,

ClientName,

ProjectReqDate,

ProjectValue,

PartnerID,

PartnerType,

PartnerName,

PartnerEmail,

FundingID,

FundingStatus,

AmountRequested,

AmountRecieved,

SentFundTo,

FROM ProjectDetail

WHERE (@ProjectID =NULL or ProjectID=@ProjectID)

AND (@FromDate IS NULL OR CreatedOn BETWEEN @FromDate AND @ToDate)

END TRY

BEGIN CATCH

PRINT Error_Message()

DECLARE @msg VARCHAR(MAX)

SELECT @msg = ERROR_MESSAGE()

EXECUTE ERR_LogError

RAISERROR('Error in %s: %s', 16, 1, 'ReportProjectDetail',@msg)
END CATCH

END

Step : 2 Create a DataSet using the DataSet Designer

• Start by running Visual Studio and select New Website from the Start page.
• Add ASP.Net folder App_Code
• In Solution Explorer Right Click on App_Code > Add new Item > DataSet
• Now select DataSet & Give name. e.g. ds_ProjectDetail.xsd and Save.
• Now Right Click anywhere in DataSet sceen and select Add from the context menu.
• Select Table Adapter In to Wizard. Now create data table.
• Now choose Existing Store procedures.

Step : 3 Create a report definition

• In Soultion Explorer right click on & select > Add New Item > Reporting > Report
• Now Given report name e.g. rptProjectDetail.rdlc Click Add to your project.
• Now Drag a table from the report designer screen.
• Now Dataset Properties has to be come in pop up now give your dataset name next choose your dataset next select available reports & last click OK.
• The table has in three Bands, first header, second detail, & last footer bands.
• After created your dataset, it will appear on your left panel in Website Data Source window.
• Expand it and drag the file to the report designer page . For example, drag and place it in the Table Fields.
• After you have assigned the attribute, now report creation is done.
  
  
  
  
  
  Step : 4 Add Report Viewer control into your ASPX page
  

  Step:-

  • In Solution Explore Right click > Add new item >Web > Web Form now give name e.g. rdlcDemo.aspx and click on Add.
  • Now Open your Left Side Toolbox and drag the Report Viewer control to your ASPX page (In Design mode).
  • Once your have Drop n drag Control on aspx Webpage , select it and click on the arrow on the top right corner to choose your rdlcReport.rdlc report in dropdown list.
  • Don’t Forget to add namespace Microsoft.Reporting.WebForms to be in your code-behind file.
  
  
  
  using System;

  using System.Configuration;

  using System.Data;

  using System.Data.Common;

  using System.Web.UI;

  using Microsoft.Practices.EnterpriseLibrary.Data;

  using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

  using Microsoft.Reporting.WebForms;

  using System.Collections.Generic;

  using System.Linq;

  using System.Web.UI.WebControls;

  using System.Data;

  using DataAccessLayer;

  
  

  protected void GenerateReportButton_Click(object sender, EventArgs e)

  {

  ReportViewer1.Visible = true;

          string connectionString = ConfigurationManager.ConnectionStrings

  ["ConnectionStringName"].ConnectionString;

  
  

  Database db = new SqlDatabase(connectionString);

  DbCommand command = db.GetStoredProcCommand("ReportProjectDetail");

  db.AddInParameter(command, "@ProjectID", DbType.String, ddlProject.SelectedValue);

  if (calFromDate.Text.ToString() != "")

  {

  db.AddInParameter(command, "@FromDate", DbType.String, calFromDate.Text.ToString());

  }

  if (calToDate.Text.ToString() != "")

  {

  db.AddInParameter(command, "@ToDate", DbType.String, calToDate.Text.ToString());

  }

  DataSet dataset = db.ExecuteDataSet(command);

  ReportDataSource datasource = new ReportDataSource("DataSet1", dataset.Tables[0]);

  
  

  ReportViewer1.LocalReport.DataSources.Clear();

  ReportViewer1.LocalReport.DataSources.Add(datasource);

  
  

  ReportViewer1.LocalReport.Refresh();

  
  

  }

  }
  Bind and Run The Report
  
  

  Now  Press F5 to Run the .aspx page.