Bind a Report to a Microsoft SQL Server Database at Runtime
- 9 minutes to read
DevExpress Reports uses the SqlDataSource component to retrieve data from Microsoft SQL Server databases. This example creates a master-detail report in code at runtime and binds the report to the data source.
The full code is available in the sample application:
Do the following to use data from the Microsoft SQL Server database in your reporting application:
- Declare the SqlDataSource component.
- Create a data connection for the Microsoft SQL Server database.
- Populate the SqlDataSource.Queries collection.
- Bind the report to the data source.
Declare a Data Source Component
The data source component is an instance of the SqlDataSource class. Your project should reference the DevExpress.DataAccess.v23.2.dll and DevExpress.Xpo.v23.2.dll assemblies to declare this class and use API to initialize the data source.
using DevExpress.DataAccess.Sql;
using DevExpress.DataAccess.ConnectionParameters;
// ...
SqlDataSource DataSource { get; set; }
Create a Connection
Specify connection parameters or use a connection name to connect to the database. The SqlDataSource calls built-in or external services to translate the connection name to a connection string.
Connection from Parameters
Parameters are the straightforward way to create a connection. Create an object that is a DataConnectionParametersBase class descendant and contains parameters specific to the selected database. In this example, you create an MsSqlConnectionParameters object that contains parameters specific to the Microsoft SQL Server.
using DevExpress.DataAccess.Sql;
using DevExpress.DataAccess.ConnectionParameters;
// ...
private void CreateConnectionFromParameters()
{
MsSqlConnectionParameters connectionParameters = new MsSqlConnectionParameters()
{
ServerName = "localhost",
DatabaseName = "NorthWind",
UserName = null,
Password = null,
AuthorizationType = MsSqlAuthorizationType.Windows
};
DataSource = new SqlDataSource(connectionParameters);
}
You can handle the SqlDataSource.ConfigureDataConnection event to modify parameters at runtime.
Connection from a String
If connection parameters (DataConnectionParametersBase descendant) specific to the selected database do not exist or you wish to build a new connection string, use a CustomStringConnectionParameters object. The following code creates a connection to the Microsoft SQL Server database file:
using DevExpress.DataAccess.Sql;
using DevExpress.DataAccess.ConnectionParameters;
// ...
private void CreateConnectionFromString()
{
string connectionString = "XpoProvider=MSSqlServer;Data Source=(LocalDB)\\MSSQLLocalDB;" +
"AttachDbFilename=|DataDirectory|\\Test.mdf;" +
"Integrated Security=True;Connect Timeout=30";
CustomStringConnectionParameters connectionParameters =
new CustomStringConnectionParameters(connectionString);
DataSource = new SqlDataSource(connectionParameters);
}
You can handle the SqlDataSource.ConfigureDataConnection event to modify parameters at runtime.
Connection by Name
You can specify a connection name and let the SqlDataSource call the built-in service to obtain a connection string from the application configuration file.
In this example, the App.config file contains the following section:
<connectionStrings>
<add name="TestConnectionString" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Test.mdf;Integrated Security=True;Connect Timeout=30" providerName="System.Data.SqlClient"/>
</connectionStrings>
To use the connection declared in the App.config file, specify its name:
using DevExpress.DataAccess.Sql;
using DevExpress.DataAccess.ConnectionParameters;
// ...
private void CreateConnectionFromAppConfig()
{
DataSource = new SqlDataSource("TestConnectionString");
}
You can implement the IConnectionProviderService, which translates a connection name to a connection string. The service allows you to ignore connections in the App.config file and specify custom connection strings at runtime.
Add a Query
The SqlDataSource uses queries to retrieve data from databases. This section illustrates how to create different queries for the SQL data source.
A Query with a Master-Detail Relationship
Use the SelectQueryFluentBuilder object to create two queries and add a relationship between resulting tables. This data is used to configure a master-detail report.
using DevExpress.DataAccess.Sql;
using DevExpress.DataAccess.ConnectionParameters;
// ...
void AddQueryRelations()
{
SelectQuery categories = SelectQueryFluentBuilder
.AddTable("Categories")
.SelectAllColumns()
.Build("Categories");
SelectQuery products = SelectQueryFluentBuilder
.AddTable("Products")
.SelectAllColumns()
.Build("Products");
DataSource.Queries.AddRange(new SqlQuery[] { categories, products });
DataSource.Relations.Add(
new MasterDetailInfo("Categories", "Products", "CategoryID", "CategoryID"));
}
A Select Query
The following code shows how to use the SelectQueryFluentBuilder to create complex queries:
public static SqlQuery CreateSelectQuery()
{
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("Categories");
query.Name = "Categories with 7 or More Products";
return query;
}
A Custom SQL Query
You can specify a SQL statement to create a custom query. For security reasons, only SELECT statements are allowed.
public static SqlQuery CreateCustomSqlQuery()
{
CustomSqlQuery query = new CustomSqlQuery();
query.Name = "CustomQuery";
query.Sql = "Select top 10 * from Products";
return query;
}
A Stored Procedure Query
Create a StoredProcQuery to retrieve data from a stored procedure:
public static SqlQuery CreateStoredProcedureQuery()
{
StoredProcQuery spQuery =
new StoredProcQuery("StoredProcedure", "SalesByCategory");
spQuery.Parameters.Add(new QueryParameter("@CategoryName", typeof(string), "SeaFood"));
spQuery.Parameters.Add(new QueryParameter("@OrdYear", typeof(string), "1997"));
return spQuery;
}
Create a Report and Bind to the Data Source
The code snippet below creates a master-detail report based on the SqlDataSource configured in the previous steps. The snippet illustrates the general idea, and the full code is available in the sample application.
using DevExpress.DataAccess.Sql;
using DevExpress.XtraPrinting;
using DevExpress.XtraReports.UI;
using System.Drawing;
// ...
public static XtraReport CreateReport(object dataSource)
{
SqlDataSource ds = dataSource as SqlDataSource;
if (ds == null) return new XtraReport();
// Create an empty report.
XtraReport report = new XtraReport();
// Bind the report to a data source.
report.DataSource = ds;
report.DataMember = ds.Queries[0].Name;
// Create a master part.
CreateReportHeader(report, "Products by Categories");
CreateDetail(report);
// Create a detail part.
CreateDetailReport(report, ds.Queries[0].Name + "." + ds.Relations[0].Name);
return report;
}
Modify the Connection at Runtime
You may have different databases for test and production purposes. In this situation, the application should change connections at runtime to allow users to view the data in which they are interested.
If you specify connection parameters for the SqlDataSource, handle the ConfigureDataConnection event to modify parameters. If you specify a connection by name, implement the IConnectionProviderService to resolve a connection name to a connection string at runtime.
Handle the ConfigureDataConnection Event
The following code shows the ConfigureDataConnection event handler that changes the database name in the connection. Note that the ConfigureDataConnection event is raised only if the original connection is specified with the connection parameters, not by the connection name.
using DevExpress.DataAccess.Sql;
using DevExpress.DataAccess.ConnectionParameters;
// ...
private void DataSource_ConfigureDataConnection(object sender,
ConfigureDataConnectionEventArgs e)
{
(e.ConnectionParameters as MsSqlConnectionParameters).DatabaseName = "SouthWind";
}
Use the IConnectionProviderService for Connection Name Resolution
If the original connection is specified by name, create and register a service that implements the IConnectionProviderService interface.
The code snippet below implements a service that connects the data source to the MS SQL Server if the connection name is “MyRuntimeConnection”, and creates a connection to the Microsoft SQL Server database file for any other connection name.
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Sql;
using DevExpress.DataAccess.Wizard.Services;
class CustomConnectionProviderService : IConnectionProviderService
{
public SqlDataConnection LoadConnection(string connectionName)
{
if (connectionName == "MyRuntimeConnection")
{
MsSqlConnectionParameters connectionParameters = new MsSqlConnectionParameters()
{
ServerName = "localhost\\SQLEXPRESS",
DatabaseName = "NorthWind",
UserName = null,
Password = null,
AuthorizationType = MsSqlAuthorizationType.Windows
};
return new SqlDataConnection("MyRuntimeConnection", connectionParameters);
}
string connectionString = "XpoProvider=MSSqlServer;Data Source=(LocalDB)\\MSSQLLocalDB;" +
"AttachDbFilename=|DataDirectory|\\Test.mdf;" +
"Integrated Security=True;Connect Timeout=30";
CustomStringConnectionParameters fallbackConnectionParameters =
new CustomStringConnectionParameters(connectionString);
return new SqlDataConnection(connectionName, fallbackConnectionParameters);
}
}
The following code registers the service for the ReportDesignTool.
using DevExpress.DataAccess.Wizard.Services;
using DevExpress.XtraReports.UI;
using DevExpress.XtraReports.UserDesigner;
using System.ComponentModel.Design;
using System;
// ...
private void OpenReportDesignerWithService()
{
CustomConnectionProviderService connectionProviderService =
new CustomConnectionProviderService();
DataSource = new SqlDataSource("MyRuntimeConnection");
AddQueryRelations();
XtraReport rep = ReportCreator.CreateReport(DataSource);
ReportDesignTool designer = new ReportDesignTool(rep);
ReplaceService(designer.DesignRibbonForm.DesignMdiController,
typeof(IConnectionProviderService),
connectionProviderService);
designer.DesignRibbonForm.DesignMdiController.DesignPanelLoaded +=
DesignMdiControllerOnDesignPanelLoaded;
designer.ShowRibbonDesignerDialog();
}
private void ReplaceService(IServiceContainer container,
Type serviceType,
object serviceInstance)
{
if (container.GetService(serviceType) != null)
container.RemoveService(serviceType);
container.AddService(serviceType, serviceInstance);
}
private void DesignMdiControllerOnDesignPanelLoaded(object sender, DesignerLoadedEventArgs e)
{
ReplaceService(e.DesignerHost, typeof(IConnectionProviderService),
new CustomConnectionProviderService());
}