In this article I will explain how to make Crystal Report work with a dynamic DataSource i.e. without any database connection at design time in ASP.Net using C# and VB.Net.
The idea is to make generic Typed DataSet as a DataSource for Crystal Report and then populate the DataSet with the data from any Table of any Database.
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.
The use of Typed DataSet helps us avoid direct connection of Crystal Report with database, which is very necessary for building a Crystal Report with dynamic columns.
![Crystal Report Dynamic DataSource: Crystal Report without Database connection in ASP.Net using C# and VB.Net]()
2. Adding DataTable to the Typed DataSet
Our next step would be to add a DataTable to the Type DataSet.
![Crystal Report Dynamic DataSource: Crystal Report without Database connection in ASP.Net using C# and VB.Net]()
3. Adding Generic Columns or fields to DataTable
In the DataTable you will need to add as many columns as you need. For this article I am adding 5 columns DataColumn1 to DataColumn5. These generic columns will be used to fetch data from any column of any table.
Note: Since we are adding generic columns, the data type of all columns has to be of String type so that it works for all columns of different data types.
![Crystal Report Dynamic DataSource: Crystal Report without Database connection in ASP.Net using C# and VB.Net]()
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.
![Crystal Report Dynamic DataSource: Crystal Report without Database connection in ASP.Net using C# and VB.Net]()
As soon as you click OK you get the following dialog. You must select Using the Report Wizard option.
![Crystal Report Dynamic DataSource: Crystal Report without Database connection in ASP.Net using 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 Custom DataSet.
![Crystal Report Dynamic DataSource: Crystal Report without Database connection in ASP.Net using C# and VB.Net]()
Next the Wizard will ask for the Columns or Fields from the DataSet you need to display on the Crystal Reports.
Note: You need to choose all columns as we will show and hide Columns in Crystal Report dynamically from the code.
![Crystal Report Dynamic DataSource: Crystal Report without Database connection in ASP.Net using 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. Here the Section 2 acts as the Header and Section 3 will records from the DataTable.
![Crystal Report Dynamic DataSource: Crystal Report without Database connection in ASP.Net using C# and VB.Net]()
All the elements in the Section 2 are Crystal Report TextObject. The Text of these TextObjects will be replaced with the actual names of the Columns present in the Recordset returned by the SQL Query.
![Crystal Report Dynamic DataSource: Crystal Report without Database connection in ASP.Net using C# and VB.Net]()
HTML Markup
The HTML Markup consists of an ASP.Net RadioButton containing names of 2 tables of the Northwind Database and a CrystalReportViewer control.
Tables:
<asp:RadioButtonList ID="rbTables" runat="server" RepeatDirection="Horizontal" AutoPostBack="true"
OnSelectedIndexChanged="Tables_Changed">
<asp:ListItem Text="Customers Table" Value="Customers" Selected="True"></asp:ListItem>
<asp:ListItem Text="Employees Table" Value="Employees"></asp:ListItem>
</asp:RadioButtonList>
<hr />
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true"
Height="400" Width="580" BestFitPage="False" />
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Linq;
using System.Configuration;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
VB.Net
Imports System.Data
Imports System.Linq
Imports System.Configuration
Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine
Crystal Report with Dynamic DataSource without Database connection in ASP.Net
I am populating Crystal Reports from database in the Page Load event of the page using the BindReport function.
This function build a dynamic query based on the Table selected from the RadioButtonList and then passes the SQL Query to the GetData function along with one more parameter i.e. Crystal Report Document.
Inside the GetData function, the records are fetched from the database using SqlDataReader. Once the results are available first a loop is executed over the fields returned and then the names of the Columns are set in the corresponding TextObject in Section 2 discussed earlier.
The above part completes the dynamic header generation of Crystal Report. Now the next task is to populate data from DataReader into the DataTable of the Typed DataSet.
Finally the Typed DataSet is returned by the function and it is supplied as ReportSource property of the Crystal Report.
This BindReport function is also called inside the SelectedIndexChanged event handler of the RadioButtonList.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.BindReport();
}
}
private void BindReport()
{
string query = "SELECT TOP 5 ";
if (rbTables.Items[0].Selected)
{
query += "CustomerId, ContactName, City, Country, PostalCode FROM Customers";
}
else
{
query += "EmployeeId, FirstName, LastName, City, Country FROM Employees";
}
ReportDocument crystalReport = new ReportDocument();
CrystalReportViewer1.DisplayGroupTree = false;
crystalReport.Load(Server.MapPath("~/CrystalReport.rpt"));
CustomDataSet dsCustom = GetData(query, crystalReport);
crystalReport.SetDataSource(dsCustom);
CrystalReportViewer1.ReportSource = crystalReport;
}
private CustomDataSet GetData(string query, ReportDocument crystalReport)
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlCommand cmd = new SqlCommand(query);
using (SqlConnection con = new SqlConnection(conString))
{
CustomDataSet dsCustom = new CustomDataSet();
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
//Get the List of all TextObjects in Section2.
List<TextObject> textObjects = crystalReport.ReportDefinition.Sections["Section2"].ReportObjects.OfType<TextObject>().ToList();
for (int i = 0; i < textObjects.Count; i++)
{
//Set the name of Column in TextObject.
textObjects[i].Text = string.Empty;
if (sdr.FieldCount > i)
{
textObjects[i].Text = sdr.GetName(i);
}
}
//Load all the data rows in the Dataset.
while (sdr.Read())
{
DataRow dr = dsCustom.Tables[0].Rows.Add();
for (int i = 0; i < sdr.FieldCount; i++)
{
dr[i] = sdr[i];
}
}
}
con.Close();
return dsCustom;
}
}
protected void Tables_Changed(object sender, EventArgs e)
{
this.BindReport();
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Me.BindReport()
End If
End Sub
Private Sub BindReport()
Dim query As String = "SELECT TOP 5 "
If rbTables.Items(0).Selected Then
query += "CustomerId, ContactName, City, Country, PostalCode FROM Customers"
Else
query += "EmployeeId, FirstName, LastName, City, Country FROM Employees"
End If
Dim crystalReport As New ReportDocument()
CrystalReportViewer1.DisplayGroupTree = False
crystalReport.Load(Server.MapPath("~/CrystalReport.rpt"))
Dim dsCustom As CustomDataSet = GetData(query, crystalReport)
crystalReport.SetDataSource(dsCustom)
CrystalReportViewer1.ReportSource = crystalReport
End Sub
Private Function GetData(query As String, crystalReport As ReportDocument) As CustomDataSet
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim cmd As New SqlCommand(query)
Using con As New SqlConnection(conString)
Dim dsCustom As New CustomDataSet()
cmd.Connection = con
con.Open()
Using sdr As SqlDataReader = cmd.ExecuteReader()
'Get the List of all TextObjects in Section2.
Dim textObjects As List(Of TextObject) = crystalReport.ReportDefinition.Sections("Section2").ReportObjects.OfType(Of TextObject)().ToList()
For i As Integer = 0 To textObjects.Count - 1
'Set the name of Column in TextObject.
textObjects(i).Text = String.Empty
If sdr.FieldCount > i Then
textObjects(i).Text = sdr.GetName(i)
End If
Next
'Load all the data rows in the Dataset.
While sdr.Read()
Dim dr As DataRow = dsCustom.Tables(0).Rows.Add()
For i As Integer = 0 To sdr.FieldCount - 1
dr(i) = sdr(i)
Next
End While
End Using
con.Close()
Return dsCustom
End Using
End Function
Protected Sub Tables_Changed(sender As Object, e As EventArgs)
Me.BindReport()
End Sub
Screenshot
The following Screenshot describes how the Crystal Report is being populated dynamically from different tables using dynamic DataSource.
![Crystal Report Dynamic DataSource: Crystal Report without Database connection in ASP.Net using C# and VB.Net]()
Demo
Downloads
