Bind a Report to a DataSet

  • 5 minutes to read

This topic explains how to bind a report to a DataSet.

If you use the End-User Report Designer or store reports in REPX files, be aware that non-serializable objects (for instance, DataSet) are lost when you save a report. DataSets are not available in the End-User Report Designer's Data Source property drop-down list.

No DataSet in the Data Source Drop-Down

We recommend that you use one of the built-in serializable data components instead. Use the Data Source Wizard to create them.

Additionally, you can convert DataSets in your reports to SQL data sources.

Design Time

Do the following to bind a report to a DataSet object at design time in Visual Studio:

  1. Create a new application or open an existing application.

  2. Select Add New Data Source on the Project menu.

    bind-a-report-to-dataset-01

  3. In the invoked Data Source Configuration Wizard, select Database and click Next.

    bind-a-report-to-dataset-02

  4. Choose the Dataset as the database model. Click Next.

    bind-a-report-to-dataset-03

  5. On the next page, click New Connection.

    bind-a-report-to-dataset-04

  6. In this example, the report is bound to a sample Northwind database hosted on a Microsoft SQL Server. Specify the connection options and click OK.

    bind-a-report-to-dataset-05

  7. Click Next to connect to the database.

  8. On the next page, choose an object to add to the dataset. Expand the Tables category, select the Categories item, and click Finish to exit the wizard.

    bind-a-report-to-dataset-06

  9. Add a new blank report to the application.

  10. Expand the report's smart tag. In the invoked actions list, choose DataSource - Project Options - NWindDataSet.

    bind-a-report-to-dataset-07 bind-a-report-to-dataset-08

  11. The report is now bound to data. The Report Explorer displays the data source in the Components node. The Field List reflects the data source's hierarchy.

    bind-a-report-to-dataset-09

Runtime

The following example binds a report to a DataSet object at runtime:

using DevExpress.XtraReports.UI;
//...
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
        ReportDesignTool designTool = new ReportDesignTool(CreateReport());
        designTool.ShowRibbonDesignerDialog();
    }

    XtraReport CreateReport()
    {
        // Create a dataset.           
        DataSet ds = FillDataset();
        // Define a report
        XtraReport report = new XtraReport()
        {
            DataSource = ds,
            DataMember = ds.Tables[0].TableName,
            Bands = {
                new DetailBand()
                {
                    Controls = {
                        CreateLabel()
                        }
                }
            }
        };
        return report;
    }

    XRLabel CreateLabel()
    {
        ExpressionBinding eb = new ExpressionBinding("BeforePrint", "Text", "[ProductId] + ' | ' + [ProductName] + ' | ' + [SupplierId] + ' | ' + [Category]");
        XRLabel lb = new XRLabel { Left = 30, WidthF = 300 };
        lb.ExpressionBindings.Add(eb);
        return lb;
    }


    public DataSet FillDataset()
    {
        DataSet dataSet1 = new DataSet();
        dataSet1.DataSetName = "nwindDataSet1";
        DataTable dataTable1 = new DataTable();

        dataSet1.Tables.Add(dataTable1);

        dataTable1.TableName = "Products";
        dataTable1.Columns.Add("ProductId", typeof(int));
        dataTable1.Columns.Add("ProductName", typeof(string));
        dataTable1.Columns.Add("SupplierId", typeof(int));
        dataTable1.Columns.Add("Category", typeof(int));


        dataSet1.Tables["Products"].Rows.Add(new Object[] { 1, "Chai", 1, 1 });
        dataSet1.Tables["Products"].Rows.Add(new Object[] { 2, "Chang", 1, 1 });
        dataSet1.Tables["Products"].Rows.Add(new Object[] { 3, "Aniseed Syrup", 1, 2 });
        dataSet1.Tables["Products"].Rows.Add(new Object[] { 4, "Chef Anton's Cajun Seasoning", 2, 2 });
        dataSet1.Tables["Products"].Rows.Add(new Object[] { 5, "Chef Anton's Gumbo Mix", 2, 2 });
        return dataSet1;

    }
}

The following image demonstrates the resulting Field List:

Result

Convert DataSet to SQL Data Source

SQL data sources offer extra capabilities that DataSets do not have:

To use these advantages, convert the DataSet to an SQL data source. Right-click the DataSet in the Report Explorer and select Convert to SqlDataSource from the context menu. Click Yes in the invoked dialog to confirm the selected action.

Convert DataSet to SqlDataSource

The Convert to SqlDataSource menu item is available when the connection string is retrieved from the DataSet and Data Adapters are available for all tables.

The converted SQL data source replaces the DataSet in the Report Explorer. The DataSet and all satellite Data Adapters are removed from the report. All controls that referenced the DataSet now reference the newly created SQL data source.

The Converted SqlDataSource

If the DataSet's SQL query was not validated (for instance, the query is not a SELECT statement), the query is not added to the newly created data source's queries collection.