Parameters and Stored Procedures

A common hurdle that developers have is figuring out how to print reports connected to stored procedures using parameters. It really isn’t hard once you understand how it works. Reports that use stored procedures as their data source are no different than reports that use any other data source. When you open the report, it automatically calls the stored procedure, retrieves the data, and populates the report with this data. The difference between using a stored procedure and using a table is that stored procedures accept parameters as input.

When a report is designed with a stored procedure, Crystal Reports examines the stored procedure to see if it uses parameters. If so, the designer automatically creates a report parameter for each parameter in the stored procedure. There is a one-to-one mapping of report parameters to stored procedure parameters. When the report runs, the report engine takes the value of each of these parameters and automatically passes them to the stored procedure.

As you saw in Chapter 16, the user is always prompted to enter the parameters before the report can execute. Of course, you probably don’t want to prompt the user for this information because your application has already done so via the user interface. To prevent this from happening, manually populate the parameter(s) via code with the information the user has already provided. After the parameters are filled, the report connects to the database, passes the parameters to the stored procedure and previews the report.

Listing 17-4 demonstrates how to connect to a report that uses a stored procedure with a parameter.

Listing 17-4. Connect to a stored procedure using a parameter.
[VB.NET]
Private Sub LogonToTables(ByVal myUserId As String, ByVal myPassword As String, _
ByVal myServerName As String, ByVal myDatabaseName As String)
Dim myReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument
myReport.Load("report.rpt")
Dim myTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo
'Set the database properties and security credentials
Dim myConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo
myConnectionInfo.ServerName = myServerName
myConnectionInfo.DatabaseName = myDatabaseName
myConnectionInfo.UserID = myUserId
myConnectionInfo.Password = myPassword
'Apply the ConnectionInfo to the report tables
Dim myTables As CrystalDecisions.CrystalReports.Engine.Tables
myTables = myReport.Database.Tables
For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In myTables
myTableLogonInfo = myTable.LogOnInfo
myTableLogonInfo.ConnectionInfo = myConnectionInfo
myTable.ApplyLogOnInfo(myTableLogonInfo)
Next
‘Set the parameter value
myReport.SetParameterValue("@ParameterName", "value")
CrystalReportViewer1.ReportSource = myReport
End Sub
[C#]
private void LogonToTables(string UserId, string Password, string ServerName, string DatabaseName, CrystalDecisions.CrystalReports.Engine.ReportDocument myReport)
{
CrystalDecisions.Shared.TableLogOnInfo myTableLogonInfo;
//Set the database properties and security credentials
CrystalDecisions.Shared.ConnectionInfo myConnectionInfo = new CrystalDecisions.Shared.ConnectionInfo();
myConnectionInfo.ServerName = ServerName;
myConnectionInfo.DatabaseName = DatabaseName;
myConnectionInfo.UserID = UserId;
myConnectionInfo.Password = Password;
//Apply the ConnectionInfo to the report tables
CrystalDecisions.CrystalReports.Engine.Tables myTables;
myTables = myReport.Database.Tables;
foreach (CrystalDecisions.CrystalReports.Engine.Table myTable in myTables)
{
myTableLogonInfo = myTable.LogOnInfo;
myTableLogonInfo.ConnectionInfo = myConnectionInfo;
myTable.ApplyLogOnInfo(myTableLogonInfo);
}
//Set the parameter value
myReport.SetParameterValue("@ParameterName", "value");
crystalReportViewer1.ReportSource = myReport;
}

If you want to pass a NULL value to a stored procedure parameter, set the parameter’s Value property to Nothing in VB.Net and null in C#.