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 or XRControl.DataBindings property depending on the report’s data binding mode.
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
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 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 and return the data source.
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)
' Create a new label.
Dim label As XRLabel = New XRLabel With {.WidthF = 300}
' Specify the label's binding depending on the data binding mode.
If Settings.Default.UserDesignerOptions.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) 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
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
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 depending on the report's data binding mode.
If Settings.Default.UserDesignerOptions.DataBindingMode = DataBindingMode.Bindings Then
label1.DataBindings.Add("Text", Nothing, "selectQuery.CategoryName")
label2.DataBindings.Add("Text", Nothing, "selectQuery.ProductCount")
Else
label1.ExpressionBindings.Add(New ExpressionBinding("BeforePrint", "Text", "[CategoryName]"))
label2.ExpressionBindings.Add(New ExpressionBinding("BeforePrint", "Text", "[ProductCount]"))
End If
' 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