Sunday, July 26, 2015

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:


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.

Asp.net: Create RDLC report using Store procedure

Step 2: Give datasource to report
Click on NEW button and data connection window will open.
Asp.net: Create RDLC report using Store procedure

 Click on new connection button and add the new connection if you not create any connection earlier.
Asp.net: Create RDLC report using Store procedure


Asp.net: Create RDLC report using Store procedure


Asp.net: Create RDLC report using Store procedure

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.

Asp.net: Create RDLC report using Store procedure

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.

Asp.net: Create RDLC report using Store procedure


Step 3: Arrange the field
In this step drag the fields from available field to values and click on the next button.
Asp.net: Create RDLC report using Store procedure

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

Asp.net: Create RDLC report using Store procedure


Now you see an App_code folder added to project/website and Dataset created in it.
Asp.net: Create RDLC report using Store procedure

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 :

Asp.net: Create RDLC report using Store procedure

    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. 

No comments:

Post a Comment