Telerik blogs

Connecting Telerik Reporting to SQL Azure via Open Access

by Stefan Tsokev
December 16, 2010 Productivity, Reporting 0 Comments


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:

SqlAzureOpenAccess1

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.

SqlAzureOpenAccess2

The newly created Open Access domain model should look similar to the following illustration:

SqlAzureOpenAccess3

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.

OpenAccess
About the Author

Stefan Tsokev

Stefan’s main interests outside the .NET domain include rock music, playing the guitar and swimming.

Comments

Comments are disabled in preview mode.