Create RDLC Report using Stored Procedure in ASP.Net with C#
1. Add Typed DataSet to the ASP.Net Website
Go Add->Add New Item->DataSet
Name the DataSet "Employee.xsd"
2. Adding DataTable to the Typed DataSet
Right Click Employee.xsd page
Go Add->DataTable
Name the DataTable "DataTable1"
3. Adding Columns or fields to DataTable1
In the DataTable1 we need to specify the column names that
we want to display in the RDLC Report.
Right Click on DataTable1
Go Add->Column
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 make easier to configure the Report.
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.
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
7. Choose the Layout
The next dialog will ask us to choose the layout
8. Choose the Style
Finally we need to choose the style, i.e. color and theme of the Report.
9. Adding Report Viewer to the page
Now we need to add ReportViewer control
to the page from the Toolbox. The ReportViewer controls requires
ScriptManager on the page.
10. Add the code in ASPX Page
<%@ 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>My First RDLC Report</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>
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
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.Reporting.WebForms;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ReportViewer1.ProcessingMode = ProcessingMode.Local;
ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc");
Customers dsCustomers = GetData();
ReportDataSource datasource = new ReportDataSource("Employee", 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("GetEmployee_SP");
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;
}
}
}
}
1. Add Typed DataSet to the ASP.Net Website
Go Add->Add New Item->DataSet
Name the DataSet "Employee.xsd"
2. Adding DataTable to the Typed DataSet
Right Click Employee.xsd page
Go Add->DataTable
Name the DataTable "DataTable1"
3. Adding Columns or fields to DataTable1
In the DataTable1 we need to specify the column names that
we want to display in the RDLC Report.
Right Click on DataTable1
Go Add->Column
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 make easier to configure the Report.
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.
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
7. Choose the Layout
The next dialog will ask us to choose the layout
8. Choose the Style
Finally we need to choose the style, i.e. color and theme of the Report.
9. Adding Report Viewer to the page
Now we need to add ReportViewer control
to the page from the Toolbox. The ReportViewer controls requires
ScriptManager on the page.
10. Add the code in ASPX Page
<%@ 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>My First RDLC Report</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>
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
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.Reporting.WebForms;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ReportViewer1.ProcessingMode = ProcessingMode.Local;
ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc");
Customers dsCustomers = GetData();
ReportDataSource datasource = new ReportDataSource("Employee", 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("GetEmployee_SP");
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;
}
}
}
}
