In this article I will explain with an example, how to create
RDLC Report using Stored Procedure in
ASP.Net using C# and VB.Net.
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
1. Add Typed DataSet to the ASP.Net Website
Since I am using disconnected RDLC Reports we will make use of Typed DataSet to populate the RDLC Reports with data from database.
![Create RDLC Report using Stored Procedure in ASP.Net with C# and VB.Net]()
2. Adding DataTable to the Typed DataSet
Our next step would be to add a DataTable to the Type DataSet.
![Create RDLC Report using Stored Procedure in ASP.Net with C# and VB.Net]()
3. Adding Columns or fields to DataTable
Note: The Column Names of the
DataTable must exactly match with the actual
Database Table column names.
![Create RDLC Report using Stored Procedure in ASP.Net with C# and VB.Net]()
By default, all the columns are of String Data Type but you can also change the data type as per your need.
4. Adding the RDLC Report
Using the
Add New Item option in
Visual Studio you need to add new
RDLC Report. I am making use of Report Wizard so that it makes easier to configure the Report.
![Create RDLC Report using Stored Procedure in ASP.Net with C# and VB.Net]()
5. Choose the DataSet
Now we need to choose the
DataSet that will act as the DataSource for the
RDLC Report. Thus we need to select the Customers
DataSet that we have created earlier.
![Create RDLC Report using Stored Procedure in ASP.Net with C# and VB.Net]()
6. Choose the Fields to be displayed in the RDLC Report
Next we need to choose the fields that we need to display, we need to simply drag and drop each fields into the Values Box as shown in the screenshot below.
![Create RDLC Report using Stored Procedure in ASP.Net with C# and VB.Net]()
7. Choose the Layout
The next dialog will ask us to choose the layout, we can simply skip it as of now as this is a simple Report with no calculations involved.
![Create RDLC Report using Stored Procedure in ASP.Net with C# and VB.Net]()
`
Once you press
Finish button on the above step, the Report is ready and is displayed in the
Visual Studio as shown below.
![Create RDLC Report using Stored Procedure in ASP.Net with C# and VB.Net]()
9. Adding Report Viewer to the page
In order to display the Report, we will need to add ReportViewer control to the page from the Toolbox. The ReportViewer controls requires ScriptManager on the page.
![Create RDLC Report using Stored Procedure in ASP.Net with C# and VB.Net]()
Once you add the ReportViewer control to the page, your page must look as below.
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<rsweb:reportviewer id="ReportViewer1" runat="server" width="600">
</rsweb:reportviewer>
</form>
</body>
</html>
10. The Stored Procedure
Below is the stored procedure which will be used to populate the records from the Customers table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Customers_GetCustomers
AS
BEGIN
SET NOCOUNT ON;
SELECT [CustomerID]
,[ContactName]
,[City]
,[Country]
FROM [dbo].[Customers]
END
GO
11. Populating the RDLC Report from Database
Below is the code to populate the
RDLC Report from database using Stored Procedure. The first statement notifies the
ReportViewer control that the Report is of type Local Report.
Then the path of the Report is supplied to the ReportViewer, after that the Customers DataSet is populated with records from the Customers Table and is set as ReportSource to the Report.
C#
Namespaces
You will need to import the following namespaces.
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.Reporting.WebForms;
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
ReportViewer1.ProcessingMode = ProcessingMode.Local;
ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc");
Customers dsCustomers = GetData();
ReportDataSource datasource = new ReportDataSource("Customers", dsCustomers.Tables[0]);
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(datasource);
}
}
private Customers GetData()
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlCommand cmd = new SqlCommand("Customers_GetCustomers");
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
sda.SelectCommand = cmd;
using (Customers dsCustomers = new Customers())
{
sda.Fill(dsCustomers, "DataTable1");
return dsCustomers;
}
}
}
}
VB.Net
Namespaces
You will need to import the following namespaces.
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports Microsoft.Reporting.WebForms
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
ReportViewer1.ProcessingMode = ProcessingMode.Local
ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc")
Dim dsCustomers As Customers = GetData()
Dim datasource As New ReportDataSource("Customers", dsCustomers.Tables(0))
ReportViewer1.LocalReport.DataSources.Clear()
ReportViewer1.LocalReport.DataSources.Add(datasource)
End If
End Sub
Private Function GetData() As Customers
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim cmd As New SqlCommand("Customers_GetCustomers")
Using con As New SqlConnection(conString)
Using sda As New SqlDataAdapter()
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
sda.SelectCommand = cmd
Using dsCustomers As New Customers()
sda.Fill(dsCustomers, "DataTable1")
Return dsCustomers
End Using
End Using
End Using
End Function
Screenshot
![Create RDLC Report using Stored Procedure in ASP.Net with C# and VB.Net]()
Demo
Downloads
