How to: Configure Database Connectivity

This topic discusses how to specify a database connection to the OpenAccessDataSource component that can be used both at design-time and when running the report in production. The provided examples and code snippets assume an existing Telerik Data Access Model of the Product schema from the Adventure Works sample database.

Strictly speaking, it is not necessary to specify a database connection when working with the OpenAccessDataSource component. Simply specifying an OpenAccessContext and a member is enough to connect to the Telerik Data Access Model, because the OpenAccessContext is already configured to access the database. The following code snippet shows the minimum code necessary to setup the OpenAccessDataSource component:

Telerik.Reporting.OpenAccessDataSource openAccessDataSource = 
    new Telerik.Reporting.OpenAccessDataSource();
openAccessDataSource.ObjectContext = typeof(AdventureWorksEntities);
openAccessDataSource.ObjectContextMember = "Products";
Telerik.Reporting.Report report = new Telerik.Reporting.Report();
report.DataSource = openAccessDataSource;
Dim openAccessDataSource As Telerik.Reporting.OpenAccessDataSource = 
    New Telerik.Reporting.OpenAccessDataSource()
openAccessDataSource.ObjectContext = GetType(AdventureWorksEntities)
openAccessDataSource.ObjectContextMember = "Products"
Dim report As Telerik.Reporting.Report = New Telerik.Reporting.Report()
report.DataSource = openAccessDataSource

When running the report in production the above code should work just fine. However, this is not the case when generating a preview of the same report in the designer, for example. The problem is that the OpenAccessContext searches its connection string in the configuration file of the current executing application or web site. When it is your application that is currently running, all that is necessary is to make sure that the connection string is present in the right configuration file. On the other hand, when running the report in the designer, the current executing application is Microsoft Visual Studio, so the connection string is no longer available. To overcome this, you can specify your connection string to the OpenAccessDataSource component. Assign the connection string to the ConnectionString property and the ADO.NET provider to the ProviderName property, as shown in the following code snippet:

Telerik.Reporting.OpenAccessDataSource openAccessDataSource = 
    new Telerik.Reporting.OpenAccessDataSource();
openAccessDataSource.ProviderName = "System.Data.SqlClient";
openAccessDataSource.ConnectionString = "Data Source=.\\SqlExpress;Initial Catalog=AdventureWorks";
openAccessDataSource.ObjectContext = typeof(AdventureWorksEntities);
openAccessDataSource.ObjectContextMember = "Products";
Dim openAccessDataSource As Telerik.Reporting.OpenAccessDataSource = 
    New Telerik.Reporting.OpenAccessDataSource()
openAccessDataSource.ProviderName = "System.Data.SqlClient"
openAccessDataSource.ConnectionString = "Data Source=.\SqlExpress;Initial Catalog=AdventureWorks"
openAccessDataSource.ObjectContext = GetType(AdventureWorksEntities)
openAccessDataSource.ObjectContextMember = "Products"

Instead of specifying an inline connection string, you can assign the name of an existing connection string stored in the configuration file to the ConnectionString property. In that case specifying an ADO.NET provider to the ProviderName property is no longer required, because that information is already present in the configuration. The following sample code demonstrates how to specify a named connection string from a configuration file:

Telerik.Reporting.OpenAccessDataSource openAccessDataSource = 
    new Telerik.Reporting.OpenAccessDataSource();
openAccessDataSource.ConnectionString = "AdventureWorksConnection";
openAccessDataSource.ObjectContext = typeof(AdventureWorksEntities);
openAccessDataSource.ObjectContextMember = "Products";
Dim openAccessDataSource As Telerik.Reporting.OpenAccessDataSource = 
    New Telerik.Reporting.OpenAccessDataSource()
openAccessDataSource.ConnectionString = "AdventureWorksConnection"
openAccessDataSource.ObjectContext = GetType(AdventureWorksEntities)
openAccessDataSource.ObjectContextMember = "Products"

Specifying an inline connection string directly to the ConnectionString property of the OpenAccessDataSource component is not recommended, because it might be difficult to maintain all your reports later, when that connection string changes. The recommended approach is to always specify the name of an existing connection string stored in the configuration file.
When specifying a connection string from a configuration file it is important to understand which configuration file is used at design-time or when running the report in production. For example, let's consider the following simplified structure of a business application:

The above schema assumes that the different parts of the business application are represented as separate projects in the solution, each with its own configuration file. Initially, when you create the Telerik Data Access Model in the Business Logic project, the connection string is automatically stored in the configuration file of that project. Later, when creating new reports in the Report Library project, you need to add the connection string to the configuration file of that project, because this is the place where Report Designer searches for existing connection strings. Finally, when deploying your application or web site in production, you need to add the connection string to the configuration file of your Main Application.

See Also