The requested page is not available for the requested platform. You are viewing the content for Default platform.

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

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 or XRControl.DataBindings property depending on the report's data binding mode specified by the UserDesignerOptions.DataBindingMode option.

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 };
    // Specify the label's binding depending on the data binding mode.
    if (Settings.Default.UserDesignerOptions.DataBindingMode == DataBindingMode.Bindings)
       label.DataBindings.Add("Text", null, "customQuery.ProductName");
    else 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();
}
See Also