Bind a Report to an MDB Database (Runtime Sample)
- 8 minutes to read
This example demonstrates how to bind a report to an MDB file and specify the report layout at runtime.
If your project in Visual Studio 2022 has to connect to Microsoft Access databases, note that Visual Studio 2022 is a 64-bit process, and Microsoft OLE DB Provider for Jet and the Jet ODBC driver are only available in 32-bit versions. It is recommended that you use the 64-bit Microsoft Access Database Engine (Access Connectivity Engine, ACE), or use an earlier version of Visual Studio (VS 2019) that supports 32-bit database providers. For more information, review the following article: Connect to data in an Access database.
To bind a report to an MDB database, do the following:
Create a SqlDataSource class instance, specify connection parameters, and add a query to retrieve data for the report.
You can enter the query string manually to create the CustomSqlQuery query or use the Query Builder UI to create the SelectQuery query. In the code, you can create any type of query.
Use the XtraReportBase.DataSource and XtraReportBase.DataMember properties to assign the created data source to the report.
Specify the ExpressionBindings property to bind a control to data.
Use CustomSqlQuery
The CustomSqlQuery class allows you to specify a custom query string. The following example demonstrates how to use this class to select all elements of all rows of a specified table.
The following code example illustrates how to bind a report to the Microsoft Access Northwind database’s Products table and display product names:
#region #reference
using System.Windows.Forms;
using System;
using DevExpress.DataAccess.Sql;
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.XtraReports.UI;
using DevExpress.XtraReports.Configuration;
// ...
#endregion #reference
namespace RuntimeBindingToMdbDatabase {
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
}
#region #code
private SqlDataSource BindToData() {
// Create a data source with the specified connection parameters.
Access97ConnectionParameters connectionParameters =
new Access97ConnectionParameters("../../nwind.mdb", "", "");
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);
// Update the data source schema.
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 the data, dependent 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 the label to the detail band.
detailBand.Controls.Add(label);
return report;
}
private void button1_Click(object sender, EventArgs e) {
// Invoke the report preview.
ReportPrintTool printTool = new ReportPrintTool(CreateReport());
printTool.ShowPreview();
}
private void button2_Click(object sender, EventArgs e) {
// Invoke the End-User Designer and load the report.
ReportDesignTool designTool = new ReportDesignTool(CreateReport());
designTool.ShowRibbonDesignerDialog();
}
#endregion #code
}
}
Use SelectQuery
Use the SelectQuery class to construct a query that selects a set of columns from a single table or multiple joined tables. In the example below, a query uses an inner join to select columns from separate data tables sharing a common column key. The code shows how to sort, group, and filter data at the data source level, and how to apply an aggregate function.
using System.Windows.Forms;
using System;
using DevExpress.DataAccess.Sql;
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.XtraReports.UI;
using DevExpress.XtraReports.Configuration;
// ...
private SqlDataSource BindToData() {
// Create a data source with the required connection parameters.
Access97ConnectionParameters connectionParameters = new Access97ConnectionParameters("../../nwind.mdb", "", "");
SqlDataSource ds = new SqlDataSource(connectionParameters);
// Create a SELECT query.
// Join the Categories and Products table by the CategoryID column.
// Return the list of categories and the number of products in each category.
// Sort the categories by the number of products they contain.
// The filtered categories contain a specific number of products.
// Call the Build method with the query name as a parameter
// to end the chain of methods.
SelectQuery query = SelectQueryFluentBuilder
.AddTable("Categories")
.SelectColumn("CategoryName")
.GroupBy("CategoryName")
.Join("Products", "CategoryID")
.SelectColumn("ProductName", AggregationType.Count, "ProductCount")
.SortBy("ProductName", AggregationType.Count, System.ComponentModel.ListSortDirection.Descending)
.GroupFilter("[ProductCount] > 7")
.Build("selectQuery");
// Add the query to the collection and return the data source.
ds.Queries.Add(query);
ds.Fill();
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 = "selectQuery";
// Add a detail band to the report.
DetailBand detailBand = new DetailBand();
detailBand.Height = 50;
report.Bands.Add(detailBand);
// Create new labels.
XRLabel label1 = new XRLabel();
XRLabel label2 = new XRLabel();
label2.Location = new System.Drawing.Point(200, 0);
// Specify labels' bindings depending on the report's data binding mode.
if (Settings.Default.UserDesignerOptions.DataBindingMode == DataBindingMode.Bindings) {
label1.DataBindings.Add("Text", null, "selectQuery.CategoryName");
label2.DataBindings.Add("Text", null, "selectQuery.ProductCount");
} else {
label1.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[CategoryName]"));
label2.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[ProductCount]"));
}
// Add labels to the detail band.
detailBand.Controls.AddRange(new[] { label1, label2 });
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();
}