In this article I will explain step by step with an example, how to create Crystal Report using Stored Procedure in ASP.Net with C# and VB.Net in Visual Studio 2010 / 2012 / 2013.
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 Crystal Reports we will make use of Typed DataSet to populate the Crystal Reports with data from database.
![Create Crystal 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 Crystal Report using Stored Procedure in ASP.Net with C# and VB.Net]()
3. Adding Columns or fields to DataTable
In the DataTable we need to specify the column names that we want to display in the Crystal Report.
Note: The Column Names of the DataTable must exactly match with the actual Database Table column names.
![Create Crystal 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. Add Crystal Report to the ASP.Net Website
Now you will need to add a Crystal Report to the ASP.Net Application. You can give it name as per your choice.
![Create Crystal Report using Stored Procedure in ASP.Net with C# and VB.Net]()
As soon as you click OK you get the following dialog. You must select Using the Report Wizard option.
![Create Crystal Report using Stored Procedure in ASP.Net with C# and VB.Net]()
Once you press OK in the above dialog, the Report Wizard starts and you get the following dialog where you need to choose the type of Database connection for your Crystal Report. Since we are using DataSet we will choose the Customers DataSet.
![Create Crystal Report using Stored Procedure in ASP.Net with C# and VB.Net]()
Next the Wizard will ask for the Columns or Fields from the Customer DataSet you need to display on the Crystal Reports. You can choose either all or specific fields as per you choice.
![Create Crystal Report using Stored Procedure in ASP.Net with C# and VB.Net]()
Note: There are more steps in the Wizards but those are Optional hence are not included in this article.
Once you click Finish your Crystal Report should look as below
![Create Crystal Report using Stored Procedure in ASP.Net with C# and VB.Net]()
HTML Markup
The HTML Markup is simple and contains a CrystalReportViewer control.
<%@ Register Assembly="CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
Namespace="CrystalDecisions.Web" TagPrefix="CR" %>
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">
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
</form>
</body>
</html>
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine
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
Populating the Crystal Report from Database using Stored Procedure
I am populating Crystal Reports from database using Stored Procedure in the Page Load event of the page using the following code.
Here first the Crystal Report is initialized and then the data is fetched from the database using Stored Procedure and loaded into the Typed DataSet.
Finally the Type DataSet is assigned to the ReportSource property of the Crystal Report.
C#
protected void Page_Load(object sender, EventArgs e)
{
ReportDocument crystalReport = new ReportDocument();
crystalReport.Load(Server.MapPath("~/CustomerReport.rpt"));
Customers dsCustomers = GetData();
crystalReport.SetDataSource(dsCustomers);
CrystalReportViewer1.ReportSource = crystalReport;
}
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
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
Dim crystalReport As New ReportDocument()
crystalReport.Load(Server.MapPath("~/CustomerReport.rpt"))
Dim dsCustomers As Customers = GetData()
crystalReport.SetDataSource(dsCustomers)
CrystalReportViewer1.ReportSource = crystalReport
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
![Create Crystal Report using Stored Procedure in ASP.Net with C# and VB.Net]()
Demo
Downloads
