Asp.net: Create RDLC report using Store procedure

In this tutorial I am going to explain how to create RDLC report using Store procedure in asp.net

Description:

In the previous article I have explained 5 simple steps to Create RDLC Report inasp.net, How to split the string in Asp.net  and Display records in Gridview according to DropDown Selection in Asp.net.

I have a table Tb_Student and contain the information of student i.e. name, Address(City) and Roll number. In this example I am going to display the all student information in RDLC report.

Implementation:

I have created a table Tb_Student.

Create a store procedure to get data from database table

Create Proc Sp_GetStudentData

As begin

Select * from Tb_Student

end

Follow the below given steps to Create RDLC report:

Step 1: Add RDLC report to project

Go to add new item, select Report wizard and click on add button.

Step 2: Give datasource to report

Click on NEW button and data connection window will open.

 Click on new connection button and add the new connection if you not create any connection earlier.

After that click on Next button. In next step all database object will be visible i.e. tables, views, Store procedure and functions. Go to store procedure and select the procedure that you create to fetch data from table (Sp_GetStudentData). You can also change the name of Dataset if you want.

After that hit the finish button. Now you see the data source name and Available dataset are defined. Click on the next button to go next step.

Step 3: Arrange the field

In this step drag the fields from available field to values and click on the next button.

Step 4: Click on next button in this step if you don’t want to show grand total and subtotal.

Step 5: Choose the style in this step and hit the finish button

Now you see an App_code folder added to project/website and Dataset created in it.

Step 6: Add a webform to project/website

Add a webform to project/website. drag and drop the required control from toolbox to webform i.e. ReportViwer and ScriptManager.

HTML Markup of Webform:

  <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>

        <rsweb:ReportViewer ID="ReportViewerEmployee" runat="server"></rsweb:ReportViewer>

Add the namespace

C#:

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using Microsoft.Reporting.WebForms;

VB:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Imports Microsoft.Reporting.WebForms

Binding the RDLC report to Reportviewer

Write the below given to bind the repost to reportviewer

C#:

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            BindReport();

        }

    }

    private void BindReport()

    {

        try

        {

            ReportViewerEmployee.ProcessingMode = ProcessingMode.Local;

            //report path

            ReportViewerEmployee.LocalReport.ReportPath = Server.MapPath("~/RptEmployee.rdlc");           

            SqlDataAdapter adp = new SqlDataAdapter("Sp_GetStudentData", con);

            adp.SelectCommand.CommandType = CommandType.StoredProcedure;

            //object of Dataset DemoDataSet

            DemoDataSet ds = new DemoDataSet();

            adp.Fill(ds,"Sp_GetStudentData");

            //Datasource for report

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

            ReportViewerEmployee.Width = 600;

            ReportViewerEmployee.LocalReport.DataSources.Clear();

            ReportViewerEmployee.LocalReport.DataSources.Add(datasource);

        }

        catch (Exception ex)

        {

        }

    }

VB:

Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

        If Not IsPostBack Then

            BindReport()

        End If

    End Sub

    Public Sub BindReport()

        Try

            ReportViewerEmployee.ProcessingMode = ProcessingMode.Local

            'report path

            ReportViewerEmployee.LocalReport.ReportPath = Server.MapPath("~/RptEmployee.rdlc")

            Dim adp As New SqlDataAdapter("Sp_GetStudentData", con)

            adp.SelectCommand.CommandType = CommandType.StoredProcedure

            'object of Dataset DemoDataSet

            Dim ds As New DemoDataSet()

            adp.Fill(ds, "Sp_GetStudentData")

            'Datasource for report

            Dim datasource As New ReportDataSource("DataSet1", ds.Tables(0))

            ReportViewerEmployee.Width = 400

            ReportViewerEmployee.LocalReport.DataSources.Clear()

            ReportViewerEmployee.LocalReport.DataSources.Add(datasource)

        Catch ex As Exception

        End Try

    End Sub

Build, run the project and check the result.

Result :

    In this article we have learn how to create RDLC report using Store Procedure in Asp.net (C#, VB). I hope you enjoyed this article.