In the previous blog post from the series we discussed how to connect Telerik Reporting to a SQL Azure database using Open Access ORM with the OpenAccessDataSource component. Now it is time to demonstrate how to perform this with another very popular ORM – Microsoft ADO.NET Entity Framework and the EntityDataSource 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 ADO.NET Entity Data Model. To connect to the SQL Azure database specify the server name and the login credentials of your SQL Azure account, then choose the AdventureWorksLTAZ2008R2 sample database as shown below:
To create the entity model, choose the following tables from the SalesLT schema of the AdventureWorksLT database: Product, ProductCategory and ProductModel; then specify AdventureWorksEntities for its name.
The newly created entity data model should look like the following illustration:
Next, define a query to retrieve some data for the report. For the sake of this example, we should retrieve data about the products, organized by category and model. To do this, extend the entity data model by adding a new partial class AdventureWorksEntities to the project and define the following method in it:
on product.ProductCategoryID equals category.ProductCategoryID
where category.Name.StartsWith(categoryName)
join model inthis.ProductModels
on product.ProductModelID equals model.ProductModelID
where model.Name.StartsWith(modelName)
select newProductInfo
{
CategoryName = category.Name,
ModelName = model.Name,
ProductName = product.Name,
ProductNumber = product.ProductNumber
};
returnquery.ToArray();
}
}
publicclassProductInfo
{
publicstringCategoryName { get; set; }
publicstringModelName { get; set; }
publicstringProductName { get; set; }
publicstringProductNumber { get; set; }
}
The GetProductInfo method executes a LINQ query against the entity model to search for products by category and model and packs the result into a collection of ProductInfo POCOs. As mentioned in the previous blog post, retrieving the data with a single query can improve the performance by eliminating the additional round-trips to the SQL Azure database for materializing the ProductCategory and ProductModel entities for each Product entity that would normally happen if we simply bind to the corresponding relational properties.
Finally, create a new report and use the EntityDataSource 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 our closing words to these series we might add that connecting and consuming a SQL Azure cloud database from Telerik Reporting requires no additional effort from you in comparison to a local relational database, thanks to the powerful declarative data source components: SqlDataSource, OpenAccessDataSource and EntityDataSource. You can watch the following video below that demonstrates how to create the sample report yourself in few quick steps.