Crystal Reports Using a DataSet DataTable
Crystal Reports - Dynamic WebForms Using a DataSet and SQL DataReader In Custom Class
This article describes how to retrieve data dynamically into a DataSet DataTable. The DataTable (not the TableAdapter Object) in the DataSet does not have a connection to the database. The DataTable columns represent the data fields that will appear on the Crystal Report. The DataSet will be populated with data from the AdventureWorks database using a SQLDataReader created in a Class.
Create the SQL Server Data Source for Crystal Reports
This project will use a SQL stored procedure called Sales.usp_SalesCustomers from the Adventure Works database. The View in the Stored Procedure code calls a custom view called dbo.vw_SalesCustomers.
CREATE
PROCEDURE [Sales].[usp_SalesCustomers]
AS
SELECT CustomerID, CustomerName, AccountNumber, Territory, TotalYTD from
[dbo].[vw_SalesCustomers]
Code for the View.
ALTER VIEW [dbo].[vw_SalesCustomers]
AS
select Sales.Individual.CustomerID,FirstName + ' ' + LastName as CustomerName, Customer.AccountNumber,
SalesTerritory.Name as Territory,
Demo.ref.value('declare namespace dr="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
(dr:TotalPurchaseYTD)[1]','varchar(50)') as TotalYTD
from
Person.Address INNER JOIN
Sales.CustomerAddress
ON Person.Address.AddressID = Sales.CustomerAddress.AddressID
INNER JOIN
Sales.Customer ON Sales.CustomerAddress.CustomerID = Sales.Customer.CustomerID
AND
Sales.CustomerAddress.CustomerID = Sales.Customer.CustomerID
INNER JOIN
Sales.Individual ON Sales.Customer.CustomerID = Sales.Individual.CustomerID
INNER JOIN
Person.Contact ON Sales.Individual.ContactID = Person.Contact.ContactID
INNER JOIN
Sales.SalesTerritory ON Sales.Customer.TerritoryID = Sales.SalesTerritory.TerritoryID
AND Sales.Customer.TerritoryID = Sales.SalesTerritory.TerritoryID
outer apply
Demographics.nodes('declare namespace dr="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
/dr:IndividualSurvey') as Demo(ref)
Create an ASP .NET WebSite in Visual Studio.
Adding the DataSet and SQL Server Connection Class
Right click in the Solution Explorer on the project and click Add New Item. Select DataSet from the templates listed and click Add.
Add a DataTable to the empty DataSet. Change the Name of the DataTable by clicking in the top portion. Right click on the DataTable to Add the Columns (field names) to the DataTable.
Add a class to the Project. This will be the class that will return a populated DataSet to the Default.aspx WebPage to be used as the ReportDataSource for a CrystalReport.
Add using statements to include SQL classes and objects in the new class.
using System.Data.SqlClient;
using System.Data;
Add the code to the class that will create a SqlConnection object and SqlCommand object to connect to the database and execute the stored procedure into a SqlDataReader. The code will create a new DataSet with the same schema as the DataSet1 created earlier and then load the DataSet's DataTable with the data from the SqlDataReader. Finally, it will return the populated DataSet to the Default webpage. Important: Add the namespace attribute above the class name. It will be used in the webpage to access the method created to return the DataSet.
namespace ReportTest
{
public static DataSet sqlReportData()
{
try
{
string scmd = "Sales.usp_SalesCustomers";
string scon = @"Data Source=YourSQLServer;Initial Catalog=AdventureWorks;Integrated Security=SSPI;";
SqlConnection cn = new SqlConnection(scon);
cn.Open();
SqlCommand cmd = new SqlCommand(scmd, cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
DataSet ds = new DataSet();
ds = new DataSet1();
ds.Tables["SalesCustomers"].Load(dr);
return ds;
}
catch (Exception ex)
{
throw ex;
}
}
}
Adding the Crystal Report
Create the CrystalReport by clicking Add New Item.
The wizard will appear and by clicking Using the Report Wizard, you can step through to select the DataSet and the fields for the report.
Select the DataSet1 object and click the > button to add it to the Selected Tables box.
Click on the >> button to add all of the fields to the report.
Select the Style of the report and click Finish.
Open the FieldExplorer to Expand the DataSet and drag and drop fields onto the report and format them as needed.
Adding the Crystal Report to the ASP .Net Web Page
Double click the Default.aspx page in the Solution Explorer to open it. Select Split or Design View to get the Toolbox to appear. Drag and drop a CrystalReportViewer onto the Design portion of the page.
In the Solution Explorer, double click the Default.aspx.cs page to open it. Add these lines of code to the top of the page under the Using statements.
using ReportTest;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.ReportSource;
using CrystalDecisions.Web;
using System.Data.Common;
Add this code within the page class. This code will create a new DataSet and populate it with the data from our custom class by calling the static method sqlreportData(). Using the ReportDocument object, the data source for the report can be set and then loaded dynamically to the Viewer at run time.
ReportDocument rd = new ReportDocument();
protected void CrystalReportsLoad()
{
try
{
DataSet ds = new DataSet();
ds = SQL_Class.sqlReportData();
reportPath = Server.MapPath("CrystalReportSales.rpt");
rd.Load(reportPath);
rd.SetDataSource(ds);
rd.PrintOptions.PaperOrientation = CrystalDecisions.Shared.PaperOrientation.Landscape;
CrystalReportViewer1.ReportSource = rd;
CrystalReportViewer1.DataBind();
}
catch (Exception ex)
{
throw ex;
}
}
Finally, add code to detect the Page_Init event and call the code to run & databind the report here.
protected void Page_Init(object sender, EventArgs e)
{
try
{
CrystalReportsLoad();
}
catch (Exception ex)
{
ex.Message.ToString();
}
}
Run the program to verify that the report runs without any problems or errors.
