Bind a Report to a Microsoft SQL Server Database (Runtime Sample)

• Aug 21, 2020
• 3 minutes to read

This example demonstrates how to bind a report to a Microsoft SQL Server database and specify the report layout at runtime.

To bind the report to the Microsoft SQL Server, create a SqlDataSource class instance with the MsSqlConnectionParameters object, which provides parameters required for establishing a connection.

In this example, a CustomSqlQuery object is created to select all elements of all rows of a specified table. You can also use the SelectQuery class to construct a query that selects a set of columns from a single table or multiple joined tables. For an example of using this class, refer to How to: Bind a Report to an MDB Database.

Use the XtraReportBase.DataSource and XtraReportBase.DataMember properties to assign the created data source to the report.

To provide data to report controls, use the XRControl.ExpressionBindings property.

C#

using System;
using System.Windows.Forms;
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Sql;
using DevExpress.XtraReports.UI;
using DevExpress.XtraReports.Configuration;
// ...

private SqlDataSource BindToData() {
    // Create a data source with the required connection parameters.  
    MsSqlConnectionParameters connectionParameters = new MsSqlConnectionParameters(
        "localhost", "nwind.mdf", "username", "password", MsSqlAuthorizationType.SqlServer);
    SqlDataSource ds = new SqlDataSource(connectionParameters);

    // Create an SQL query to access the Products table.
    CustomSqlQuery query = new CustomSqlQuery();
    query.Name = "customQuery";
    query.Sql = "SELECT * FROM Products";

    // Add the query to the collection. 
    ds.Queries.Add(query);

    // Make the data source structure displayed  
    // in the Field List of an End-User Report Designer. 
    ds.RebuildResultSchema();

    return ds;
}

private XtraReport CreateReport() {
    // Create a new report instance.
    XtraReport report = new XtraReport();

    // Assign the data source to the report.
    report.DataSource = BindToData();
    report.DataMember = "customQuery";

    // Add a detail band to the report.
    DetailBand detailBand = new DetailBand();
    detailBand.Height = 50;
    report.Bands.Add(detailBand);

    // Create a new label.
    XRLabel label = new XRLabel { WidthF = 300 };
    // Bind the label to data.
    label.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[ProductName]"));
    // Add a label to the detail band. 
    detailBand.Controls.Add(label);

    return report;
}

private void button1_Click(object sender, EventArgs e) {
    // Show the report's print preview.
    ReportPrintTool printTool = new ReportPrintTool(CreateReport());
    printTool.ShowPreview();
}

private void button2_Click(object sender, EventArgs e) {
    // Open the report in an End-User Designer.
    ReportDesignTool designTool = new ReportDesignTool(CreateReport());
    designTool.ShowRibbonDesignerDialog();
}

VB.NET

Imports System
Imports System.Windows.Forms
Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.DataAccess.Sql
Imports DevExpress.XtraReports.UI
Imports DevExpress.Xpo
' ...

Private Function BindToData() As SqlDataSource
    ' Create a data source with the required connection parameters.  
    Dim connectionParameters As New MsSqlConnectionParameters(
            "localhost", "nwind.mdf", "username", "password", MsSqlAuthorizationType.SqlServer)
    Dim ds As New SqlDataSource(connectionParameters)

    ' Create an SQL query to access the Products table.
    Dim query As New CustomSqlQuery()
    query.Name = "customQuery"
    query.Sql = "SELECT * FROM Products"

    ' Add the query to the collection. 
    ds.Queries.Add(query)

    ' Make the data source structure displayed  
    ' in the Field List of an End-User Report Designer. 
    ds.RebuildResultSchema()

    Return ds
End Function

Private Function CreateReport() As XtraReport
    ' Create a new report instance.
    Dim report As New XtraReport()

    ' Assign the data source to the report.
    report.DataSource = BindToData()
    report.DataMember = "customQuery"

    ' Add a detail band to the report.
    Dim detailBand As New DetailBand()
    detailBand.Height = 50
    report.Bands.Add(detailBand)

    ' Add a label to the detail band.
    Dim label As New XRLabel() With { 
        .WidthF = 300 
    }
    label.ExpressionBindings.Add(New ExpressionBinding("BeforePrint", "Text", "[ProductName]"))
    detailBand.Controls.Add(label)

    Return report
End Function

Private Sub button1_Click(sender As Object, e As EventArgs)
    ' Show the report's print preview.
    Dim printTool As New ReportPrintTool(CreateReport())
    printTool.ShowPreview()
End Sub

Private Sub button2_Click(sender As Object, e As EventArgs)
    ' Open the report in an End-User Designer.
    Dim designTool As New ReportDesignTool(CreateReport())
    designTool.ShowRibbonDesignerDialog()
End Sub