Binding parameters to a Crystal Report using SqlDataSource control � A reduced code approach

Introduction

There are many articles on the internet about data binding using CrystalReportViewer, CrystalReportSource and Crystal Report designer. Some examples used the reduced code approach where as others have shown how to work with the CR object model to do the same. I show one reduced code approach to bind UI elements to CrystalReportViewer to display the underlying report using SqlDataSource control.

Background

To create a Crystal Report that displays data from a database using filter values from UI elements on the presentation-tier we can use the following methods:

1. Create a CR using a stored procedure in the database; create a datatable in the code behind for an ASPX page that displays the CR using a CrystalReportViewer control and bind the datatable to the report.
2. Create a CR using a stored procedure in the database; bind the report parameters to values from UI elements programmatically using the parameters collection in the report object model.
3. Another approach to bind parameters to a CR is the reduced code approach using a SqlDataSource control as a datasource for the underlying report.

Using the Code

To demonstrate this approach we will use CustOrderHist stored procedure that comes with Northwind database.

Create a new CR to display data using CustOrderHist stored procedure.

1. Create CustOrderHistory.rpt in Visual Studio 2005 and follow the wizard to choose CustOrderHist stored procedure after you create a new database connection.
2. Choose ProductName and Total as the report fields to display.

Create an ASPX page and bind CrystalReportViewer control to the Crystal Report

1. Drag and drop a SqlDataSource control onto the ASPX page, switch to the page designer and use the smart tag to configure the data source. You will see markup generated as shown below after step 1 and 2
2. Use the query builder and select CustomerID and CompanyName columns from Customers table.

   <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
   ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" 
   SelectCommand="SELECT [CustomerID], [CompanyName] FROM [Customers]" 
   SelectCommandType="Text"> 
   </asp:SqlDataSource>

3. Drag and drop a DropDownList control onto the ASPX page, switch to the page designer and use the smart tag to select SqlDataSource created in the previous step as the datasource. Choose CustomerID as DataValueField and CompanyName as DataTextField.

   You will see markup generated as shown below after step 3.

   <asp:DropDownList ID="DropDownList1" runat="server" 
   AutoPostBack="True" DataSourceID="SqlDataSource1" 
   DataTextField="CompanyName" DataValueField="CustomerID"
       AppendDataBoundItems="true"> 
   <asp:ListItem Text="-- Customer Order History --" Value="NULL" /> 
   </asp:DropDownList> 
   

4. Drag and Drop a CrystalReportSource control and CrystalReportViewer control, switch to the page designer and configure CRS to select CustOrderHistory.rpt as the report source. Then configure CRV to select CrystalReportSource1 control created as the report source.
5. Open properties window for CrystalReportSource1 control and choose SqlDataSource1 control as one of the datasources. You will see markup generated as shown below after steps 4 and 5.

   <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" 
       AutoDataBind="true" ReuseParameterValuesOnRefresh="True" 
       DisplayGroupTree="False" ReportSourceID="CrystalReportSource1" />
   
   <CR:CrystalReportSource ID="CrystalReportSource1" runat="server" 
       OnDataBinding="CrystalReportSource1_DataBinding" 
       OnInit="CrystalReportSource1_Init" OnLoad="CrystalReportSource1_Load"
       OnPreRender="CrystalReportSource1_PreRender" 
       OnUnload="CrystalReportSource1_Unload"> 
   <Report FileName="Reports\CustOrderHistory.rpt"> 
   <DataSources> 
   <CR:DataSourceRef DataSourceID="SqlDataSource2" 
       TableName="CustOrderHist;1" /> 
   </DataSources> 
   </Report> 
   </CR:CrystalReportSource>
   
   <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
   ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" 
   SelectCommand="CustOrderHist" SelectCommandType="StoredProcedure"
       OnSelecting="SqlDataSource2_Selecting"> 
   <SelectParameters> 
   <asp:ControlParameter ControlID="DropDownList1" Name="CustomerID"
       PropertyName="SelectedValue" /> 
   </SelectParameters> 
   </asp:SqlDataSource>

6. Go into the code behind file and the ASPX page and add the following code.

   using System; 
   using System.Data; 
   using System.Configuration; 
   using System.Collections; 
   using System.Web; 
   using System.Web.Security; 
   using System.Web.UI; 
   using System.Web.UI.WebControls; 
   using System.Web.UI.WebControls.WebParts; 
   using System.Web.UI.HtmlControls; 
   using CrystalDecisions.CrystalReports.Engine; 
   using CrystalDecisions.Enterprise; 
   using CrystalDecisions.ReportSource; 
   using CrystalDecisions.Shared; 
   using CrystalDecisions.Web; 
   public partial class CRParameters : System.Web.UI.Page 
   { 
       protected void Page_Load(object sender, EventArgs e) 
       { 
       } 
       protected void SqlDataSource2_Selecting(object sender, 
           SqlDataSourceSelectingEventArgs e) 
       { 
           if (Page.IsPostBack == false) 
           { 
               e.Command.Parameters["@CustomerID"].Value = DBNull.Value; 
           } 
       }