Tip #53 – Dynamically Assigning a DataSet to a Report

You can use an existing DataSet as the data source for the a Crystal Report from Visual Studio. First you must add a DataSet to you project. This DataSet can contain one or more tables and can be used by several reports.

In order to use the DataSet you will need to create a Crystal Report that uses it as a data source. This is done using the Database Expert or when creating the report using the new report wizard. All of the DataSets in your project will be listed under Project Data –> ADO.NET DataSets. In my case I created a DataSet called SampleDB which contains the CustomerDetails view. By using a table from the existing DataSet I can now pass any table that matches the structure defined in the DataSet.

With the DataSet and report created we can now use an SQL command to filter the data for the report. For this example we create a new AdsConnection, AdsDataAdapter and DataSet objects. The DataAdapter will use a command object to retrieve the data from the database. The DataAdapter is then used to fill the DataSet and finally this new DataSet is passed as the data source for the report.

   1: DynamicReport myReport = new DynamicReport();

   2: AdsCommand cmd;

   3: AdsDataAdapter da;

   4: DataSet dsCust;

   5:  

   6: try 

   7: {

   8:     // Open the connection 

   9:     cnAds.Open();

  10:     

  11:     // Create the command, dataadapter and fill the dataset

  12:     cmd = cnAds.CreateCommand();

  13:     cmd.CommandText = "SELECT * FROM CustomerDetails WHERE CustNum < 10020";

  14:     da = new AdsDataAdapter(cmd);

  15:     dsCust = new DataSet();

  16:     da.Fill(dsCust);

  17:     

  18:     // Open the report using the current dataset

  19:     myReport.SetDataSource(dsCust.Tables[0]);

  20:     crvTest.ReportSource = myReport;

  21:     crvTest.RefreshReport();

  22:     

  23:     // Close the connection

  24:     cnAds.Close(); 

  25: }

  26: catch (AdsException aex)

  27: {

  28:     MessageBox.Show(aex.Message, "Advantage Error");

  29: } 

I would recommend using this technique which allows you more flexibility. For example you could use this method to allow users to print a set of data which they have filtered.