Loading Crystal Report reports which use Stored Proc in C#

• Download source - 27.6 Kb

Introduction

This article focuses on loading Crystal Report reports in C#. The Crystal Report report created here uses a SQL Server stored procedure which takes two parameters and it uses DSN with SQL Server authentication.

Background

When I started working on Crystal Reports, mainly I was wondering about these problems:

1. How to set parameters of stored procedure from C# code using Crystal's APIs.
2. How to avoid popup window which comes when we use DSN with SQL Server authentication.
3. How to avoid these errors:
   • Missing prompting unit
   • The parameter is incorrect

This article gives a solution to all of the above issues and also gives a few notes to avoid unpredictable results.

Using the code

The attached code here loads the "SalseReport.rpt" file. The steps to run the attached application are:

1. Create database say "testDB" in SQL Server and execute the script "SalseData_Table_SP.sql" in the SQL Server Query Analyser which will create a stored procedure "Sel_SalesData" and a table "SalesData" for you.
2. Import the sample data to the table "salseData" from file "SalesData_Data.txt" (data is comma separated).
3. Create a DSN named "TestDB_DSN" with SQL Server authentication. Give valid user name and password.
4. Open "frmSalseData.cs" file and update the below line with your logon information, in the function "btnPreview_Click".

   //The parameters are in the order 
   //- UserName, Password, DSN Name, DatabaseName, Case Sensitive
   reportDocument.SetDatabaseLogon("pchitriv", "Windows2000", 
                              "TestDB_DSN", "testDB", false);

5. In case you have created a DSN with some other name than "TestDB_DSN", then open the "SalseReport.rpt" file from the Reports directory and set the DataSource location to point to the correct DSN and "Sel_SalseData" stored procedure again.
6. The code to load the report looks like this:

   private void btnPreview_Click(object sender, System.EventArgs e) 
   {
       //Instantiate variables
       ReportDocument reportDocument = new ReportDocument();
       ParameterField paramField = new ParameterField();
       ParameterFields paramFields = new ParameterFields();
       ParameterDiscreteValue paramDiscreteValue = new ParameterDiscreteValue();
   
       //Set instances for input parameter 1 -  @vDepartment
       paramField.Name = "@vDepartment";
       //Below variable can be set to any data 
       //present in SalseData table, Department column
       paramDiscreteValue.Value = "South";
       paramField.CurrentValues.Add(paramDiscreteValue);
       //Add the paramField to paramFields
       paramFields.Add(paramField); 
   
       //Set instances for input parameter 2 -  @iSalseYear
       //*Remember to reconstruct the paramDiscreteValue and paramField objects
       paramField = new ParameterField();
       paramField.Name = "@iSalesYear";
       paramDiscreteValue = new ParameterDiscreteValue();
       paramDiscreteValue.Value = "2004";
       paramField.CurrentValues.Add(paramDiscreteValue);
   
       //Add the paramField to paramFields
       paramFields.Add(paramField); 
   
       crystalReportViewer1.ParameterFieldInfo = paramFields;
   
       reportDocument.Load(@"..\..\..\Reports\SalseReport.rpt");
   
       //set the database loggon information. 
       //**Note that the third parameter is the DSN name 
       //  and not the Database or System name
       reportDocument.SetDatabaseLogon("pchitriv", "Windows2000", 
                                  "TestDB_DSN", "testDB", false);
     
       //Load the report by setting the report source
       crystalReportViewer1.ReportSource = reportDocument;
   }

Points of Interest

1. Error - Missing prompting unit:

   I got this error when I was not creating a new “ParameterField" object for each of the input parameters.

2. Error - The parameter is incorrect:

   I got this error when I used paramField.Name = "vDepartment"; instead of paramField.Name = "@vDepartment";. This is because in the report, the parameter field name in the Field Explorer is @vDepartment. So remember to use the exact name in the C# code as that is used in the report (for parameter/input fields).

3. Note:

   Remember to uncheck the option “Save data with Report” under Crystal Reports File menu. Checking this option gives unpredictable results when you call a report in a C# form.