Bind a Report to a DataSet

• Oct 29, 2020
• 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.

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.

   

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

   

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

   

5. On the next page, click New Connection.

   

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.

   

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.

   

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.

    

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.

    

Runtime

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

C#

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;

    }
}

VB.NET

Imports DevExpress.XtraReports.UI
' ...
Partial Public Class Form1
    Inherits Form

    Public Sub New()
        InitializeComponent()
        Dim designTool As New ReportDesignTool(CreateReport())
        designTool.ShowRibbonDesignerDialog()
    End Sub

    Private Function CreateReport() As XtraReport
        ' Create a dataset.           
        Dim ds As DataSet = FillDataset()
        ' Define a report
        Dim report As New XtraReport() With {
            .DataSource = ds, .DataMember = ds.Tables(0).TableName, .Bands = {
                New DetailBand() With {
                    .Controls = { CreateLabel() }
                }
            }
        }
        Return report
    End Function

    Private Function CreateLabel() As XRLabel
        Dim eb As New ExpressionBinding("BeforePrint", "Text", "[ProductId] + ' | ' + [ProductName] + ' | ' + [SupplierId] + ' | ' + [Category]")
        Dim lb As XRLabel = New XRLabel With {.Left = 30, .WidthF = 300}
        lb.ExpressionBindings.Add(eb)
        Return lb
    End Function

    Public Function FillDataset() As DataSet
        Dim dataSet1 As New DataSet()
        dataSet1.DataSetName = "nwindDataSet1"
        Dim dataTable1 As New DataTable()

        dataSet1.Tables.Add(dataTable1)

        dataTable1.TableName = "Products"
        dataTable1.Columns.Add("ProductId", GetType(Integer))
        dataTable1.Columns.Add("ProductName", GetType(String))
        dataTable1.Columns.Add("SupplierId", GetType(Integer))
        dataTable1.Columns.Add("Category", GetType(Integer))


        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
    End Function
End Class

The following image demonstrates the resulting Field List:

Convert DataSet to SQL Data Source

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

• Data sources are serialized together with report layouts.
• You can edit data source queries in the Query Builder.
• You can bind query parameters to report parameters.

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.

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.

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.