In the previous blog post from the series we discussed how to connect Telerik Reporting to a SQL Azure database using ADO.NET with the help of the SqlDataSource component. Now we will demonstrate how to accomplish the same task with Open Access ORM utilizing the OpenAccessDataSource component.
For the sake of this example we assume an existing SQL Azure account with the Adventure Works sample database already installed. Let’s start with a new Open Access domain model – the Open Access ORM Data Wizard starts. Choose Microsoft SQL Azure as a backend for the database connection and specify the connection string to your SQL Azure database as shown in the screenshot below:
Choose the following tables from the SalesLT schema of the AdventureWorksLT database for the domain model: Product, ProductCategory and ProductModel. Specify AdventureWorksEntities as a name for the new model and complete the wizard.
The newly created Open Access domain model should look similar to the following illustration:
Next, define a query to retrieve data for the report. For the sake of this example, we should retrieve information about the products by category and model. To accomplish this, extend the Open Access domain model by adding a new partial class AdventureWorksEntities to the project and define the following method:
partial
class
AdventureWorksEntities
{
public
IEnumerable<ProductInfo> GetProductInfo(
string
categoryName,
string
modelName)
{
var query = from product
in
this
.Products
join category
in
this
.ProductCategories
on product.ProductCategoryID equals category.ProductCategoryID
where category.Name.StartsWith(categoryName)
join model
in
this
.ProductModels
on product.ProductModelID equals model.ProductModelID
where model.Name.StartsWith(modelName)
select
new
ProductInfo
{
CategoryName = category.Name,
ModelName = model.Name,
ProductName = product.Name,
ProductNumber = product.ProductNumber
};
return
query.ToArray();
}
}
public
class
ProductInfo
{
public
string
CategoryName {
get
;
set
; }
public
string
ModelName {
get
;
set
; }
public
string
ProductName {
get
;
set
; }
public
string
ProductNumber {
get
;
set
; }
}
The GetProductInfo method executes a LINQ query against the domain model to search for products by category and model and packs the result into a collection of ProductInfo POCOs. This approach guarantees maximal performance of data access because all necessary data about the products and their corresponding categories and models is retrieved in a single round-trip to the SQL Azure database, eliminating the need to execute additional queries for materializing the ProductCategory and ProductModel entities of each Product entity that would normally happen when binding directly to the corresponding relational properties.
Finally, create a new report and use the OpenAccessDataSource component to bind it to the GetProductInfo method of the AdventureWorksEntities class.
Attached to this blog post you can find a complete sample project including a report that demonstrates the discussed technique. In order to run the sample web application you need to modify the connection string in the web.config file with your existing SQL Azure server name and login credentials.
In conclusion, you can see that connecting Telerik Reporting to SQL Azure with Open Access is seamless and completely transparent to the application when using the flexible OpenAccessDataSource component. The next video demonstrates how to create the sample report yourself in a few quick steps.
The video is also available on Telerik TV: Connecting Telerik Reporting to SQL Azure via Open Access.
Stefan’s main interests outside the .NET domain include rock music, playing the guitar and swimming.