Bind a Report to an MDB Database (Runtime Sample)
- 7 minutes to read
This example demonstrates how to bind a report to an MDB file and specify the report layout at runtime.
Create a SqlDataSource class instance with the required connection parameters and construct an appropriate query to access data to bind a report to a 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.
Depending on your particular requirements, you can create a query using the CustomSqlQuery or SelectQuery class.
Creating a CustomSqlQuery
The CustomSqlQuery class allows you 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.
Tip
Online Example: How to programmatically bind a report to an MDB file using a CustomSqlQuery
The following code example illustrates how to bind a report to the Northwind database’s Products table and display product names:
using System.Windows.Forms;
using System;
using DevExpress.DataAccess.Sql;
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.XtraReports.UI;
// ...
private SqlDataSource BindToData() {
// Create a data source that retrieves data from an XML file.
XmlFileConnectionParameters connectionParameters =
new XmlFileConnectionParameters(@"C:\Users\Public\Documents\DevExpress Demos 21.2\Components\Data\nwind.xml");
SqlDataSource dataSource = new SqlDataSource(connectionParameters);
// Build a query that retrieves product names.
SelectQuery query = SelectQueryFluentBuilder
.AddTable("Products")
.SelectColumn("ProductName")
.Build("Products");
// Add this query to the data source.
dataSource.Queries.Add(query);
// Populate the data source with data.
dataSource.Fill();
return dataSource;
}
private XtraReport CreateReport() {
// Create a new report instance.
XtraReport report = new XtraReport();
// Assign a data source to the created report.
report.DataSource = BindToData();
report.DataMember = "Products";
// 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 created label to the ProductName data field.
label.ExpressionBindings.Add(new ExpressionBinding("Text", "[ProductName]"));
// Add the label to the detail band.
detailBand.Controls.Add(label);
return report;
}
private void exportButton_Click(object sender, EventArgs e) {
// Export the report to DOCX and save the exported file to the application folder.
XtraReport report = CreateReport();
report.ExportToDocx(report.Name + ".docx");
}
private void printButton_Click(object sender, EventArgs e) {
// Send the report to the default printer.
XtraReport report = CreateReport();
report.Print();
}
Creating a 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 also demonstrates how to sort, group and filter data at the data source level as well as apply an aggregate function.
Tip
Online Example: How to programmatically bind a report to an MDB file using a SelectQuery
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 in them.
// The included categories must contain a specific number of products.
// The chain ends with calling the Build method accepting the query name as a parameter.
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.
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();
}