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 Northwind database’s Products table and display product names:
'#Region "#reference"
Imports System.Windows.Forms
Imports System
Imports DevExpress.DataAccess.Sql
Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.XtraReports.UI
' ...
'#End Region ' #reference
Namespace RuntimeBindingToMdbDatabase
Public Partial Class Form1
Inherits Form
Public Sub New()
InitializeComponent()
End Sub
'#Region "#code"
Private Function BindToData() As SqlDataSource
' Create a data source with the specified connection parameters.
Dim connectionParameters As Access97ConnectionParameters = New Access97ConnectionParameters("../../nwind.mdb", "", "")
Dim ds As SqlDataSource = New SqlDataSource(connectionParameters)
' Create an SQL query to access the Products table.
Dim query As CustomSqlQuery = 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
End Function
Private Function CreateReport() As XtraReport
' Create a new report instance.
Dim report As XtraReport = 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 DetailBand = New DetailBand()
detailBand.Height = 50
report.Bands.Add(detailBand)
' Create a new label.
Dim label As XRLabel = New XRLabel With {.WidthF = 300}
' Bind the label to the data, dependent on the data binding mode.
Dim DesignerOptions = DevExpress.XtraReports.Configuration.Settings.Default.UserDesignerOptions
If DesignerOptions.DataBindingMode = DataBindingMode.Bindings Then
label.DataBindings.Add("Text", Nothing, "customQuery.ProductName")
Else
label.ExpressionBindings.Add(New ExpressionBinding("BeforePrint", "Text", "[ProductName]"))
End If
' Add the label to the detail band.
detailBand.Controls.Add(label)
Return report
End Function
Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs)
' Invoke the report preview.
Dim printTool As ReportPrintTool = New ReportPrintTool(CreateReport())
printTool.ShowPreview()
End Sub
Private Sub button2_Click(ByVal sender As Object, ByVal e As EventArgs)
' Invoke the End-User Designer and load the report.
Dim designTool As ReportDesignTool = New ReportDesignTool(CreateReport())
designTool.ShowRibbonDesignerDialog()
End Sub
'#End Region ' #code
End Class
End Namespace
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.
Imports System.Windows.Forms
Imports System
Imports DevExpress.DataAccess.Sql
Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.XtraReports.UI
Imports DevExpress.XtraReports.Configuration
Private Function BindToData() As SqlDataSource
' Create a data source with the required connection parameters.
Dim connectionParameters As New Access97ConnectionParameters("../../nwind.mdb", "", "")
Dim ds As 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.
Dim query As SelectQuery = 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
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 = "selectQuery"
' Add a detail band to the report.
Dim detailBand As New DetailBand()
detailBand.Height = 50
report.Bands.Add(detailBand)
' Create new labels.
Dim label1 As New XRLabel()
Dim label2 As 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( { label1, label2 })
Return report
End Function
Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles button1.Click
' Show the report's print preview.
Dim printTool As New ReportPrintTool(CreateReport())
printTool.ShowPreview()
End Sub
Private Sub button2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles button2.Click
' Open the report in an End-User Designer.
Dim designTool As New ReportDesignTool(CreateReport())
designTool.ShowRibbonDesignerDialog()
End Sub