How to use RDLC with Asp.net?

How to use RDLC with Asp.net?

Sql server reporting service without instilling SSRS

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.

  Thanks,

  Bhavik 

   
  

Posted in: Asp.Net, SQL