OpenAccess, WCF, and a RadGrid (Oh My!)

I’ve been working on a project that uses OpenAccess ORM as the persistence layer between my app and the database. Since the app is going to be used on both web and client platforms I wanted to add a services layer to serve up the data. Turns out it’s pretty easy to do. So I’ll walk you through a quick demo of how to hook OpenAccess up to WCF services and then consume the data with a RadGrid.

Set up the project structure

First, create a class library project called Data to hold the data access code. This is where you’ll use OpenAccess to generate an ObjectScopeProvider (for those of you familiar with LINQ to SQL, think DataContext). You’ll also store our queries here.

Once the first project is set up, create a new class library project in Visual Studio. Call this one Entities as it will hold all of the persistent classes. These classes are models of the database tables (and columns) the app will be interacting with. A cool feature of OpenAccess is that it can use an existing schema to generate these classes automatically (reverse mapping), or you can create the classes first and then have OpenAccess do the dirty work of creating the database (forward mapping).

Next, create two additional projects. For the first, use the built-in WCF Service Application template in Visual Studio and call it Services. This, as you might guess, will be the home of your WCF service, which will consume the data from the data access layer. The second will be an ASP.NET web application project and you can call it Site. This is UI layer of the app and you’ll create a new page for a RadGrid and use it to consume the service and display its data.

Finally, you need to ensure that each project references the class libraries it needs in order to do its job. So go ahead and add a reference to Entities in the Data and Services library. Then, add an additional reference to Data in the Services library. That should do it.

Go ahead and delete all of the files that are generated by each project template. Since you’ll be creating everything from scratch, you won’t need any of these. The only exception might be for the web application project. The page and configuration files generated there are good enough to host the RadGrid that will consume your WCF service.

If you plan on modifying this app for your own needs, you could add additional (optional) layers to handle any business logic of your application or any unit tests you need to write. Of course, you don’t have to break up your project how I’ve described here, but I like this project structure in order to keep a separation of concerns so that I can easily isolate any issues that may arise in the app.

Create the persistent classes

Now that the project structure is in place, you should go ahead and use OpenAccess to create our persistent classes. Right-click on the Entities project, select the OpenAccess sub-menu, and click Enable Project.

This will launch the Telerik.OpenAccess Enable Project Wizard. Click Next to continue to the second page of the wizard. Here you’ll select the Persistent classes? checkbox and click Next.

On the next screen you’ll configure the connection to the database. In this case you’ll be connecting to the Northwind database, which I assume is hosted in SQL Server Express. Add NorthwindConnection to the Connection Id field, ensure Microsoft SQL Server is selected from the Backend dropdown, and enter the name of your server in the Server Name field. If you use integrated security to authenticate your SQL Server connections, go ahead and leave the Use integrated security checkbox selected. Otherwise, provide your username and password details. Finally, type in the name of the database, Northwind, and click Test Connection to ensure you’ve set the connection up right. Click Next.

Click Finish on the final page of the wizard, and you should notice a new App.Config file has been added to the project. This file contains all of the connection information you just set up and will later hold all of the mappings from the database tables to your persistent classes.

Now that the connection to the database has been set up and the project has been enabled, it’s time to launch the reverse mapping wizard. With your project selected, select OpenAccess | Reverse Mapping (Tables to Classes)… from the Visual Studio menu.

You’ll be greeted with the Reverse Engineering – Entities wizard, which will allow you to specify which database tables you want to map. In this example you only need to map the Products tables, so deselect all but that table in the Generate column.

Before you generate the classes, click on the Advanced View (TreeView) tab in order to modify the Product class that will be generated. Because the Products table is related to the Suppliers and Categories tables, OpenAccess will automatically generate properties that return a Supplier and Category object. However, since you are not generating a persistent class for those tables, those objects do not exist and you will get a compile error. To fix this, simply go to the advanced view, expand the Products node, click on the SupplierID node and deselect the Map column checkbox. Do the same for the CategoryID node. This will prevent OpenAccess from mapping this column and generating the properties in our Product class.

Once this is done, click Generate & Save Config. A dialog will pop up with a summary of the actions that OpenAccess is about to take. Click Yes to confirm. You’ll notice that a couple of new files have been added to our project. First, you’ll see a new class called Product. This is your persistent class. Also, a file called reversemapping.config was added to the project. This file contains metadata that OpenAccess uses to generate our classes.

Now that you’re done creating the persistent classes, it’s time to move on to the data layer.

Set up the data access layer

The first thing you’ll need in the data access layer is an ObjectScopeProvider, which OpenAccess uses to manage the connection to the database. To generate this class, right-click on the Data class library project and select OpenAccess | Enable Project. you saw the Enable Project wizard when setting up your persistent classes, but this time you need to select the Data access code (DAL)? checkbox before setting up our connection to the Northwind database.

Once you’ve gotten through the wizard, you’ll have a new ObjectScopeProvider1 class in your project. There will also be two new references to the Telerik.OpenAccess and Telerik.OpenAccess.Query assemblies, which were installed in the GAC when you installed OpenAccess. While it would be perfectly okay to use the new ObjectScopeProvider1 class to query the database, I prefer to give the class a name that better describes its purpose. Lucky for us its easy to rename this class. Simply highlight the class name in Visual Studio, right-click and select Refactor | Rename… and rename the class to something more appropriate, like NorthwindScopeProvider. Don’t forget to rename the file as well!

Now that you have a scope provider, you’ll need to set up a class to query the database and return the persistent objects. Right-click the Data project and click Add | Class… Create a new class called ProductDataProvider. In this class you’ll add a single method to query the Northwind database for all products, and return a strongly-typed List of Product objects. The method should take two arguments, startRow and count, which will be used to return only a subset of the Product records from the database.

Once the method has been created, grab an instance of the NorthwindScopeProvider class.

var northwindScope = NorthwindScopeProvider.ObjectScope();

Next, write a LINQ query against the Product table in the database by calling the NorthwindScopeProvider’s Extent<T> method. In order to use LINQ to query in OpenAccess, you must add a using directive to the class file to import the Telerik.OpenAccess.Query namespace.

var products = (from p in northwindScope.Extent<Product>()

                select p)

               .Skip(startRow)

               .Take(count)

               .ToList<Product>();

Finally, simply return the strongly-typed list of Products. In addition, you’ll need to add a method that returns the number of products in the database. A simply LINQ query for this method will do the trick. Here is the entire class:

using System.Collections.Generic;

using System.Linq;

using Telerik.Examples.Entities;

using Telerik.OpenAccess.Query;

namespace Telerik.Examples.Data

{

 public class ProductDataProvider

    {

 public List<Product> GetProducts(int startRow, int count)

        {

            var northwindScope = NorthwindScopeProvider.ObjectScope();

            var products = (from p in northwindScope.Extent<Product>()

                            select p)

                           .Skip(startRow)

                           .Take(count)

                           .ToList<Product>();

 return products;

        }

 public int GetProductCount()

        {

            var northwindScope = NorthwindScopeProvider.ObjectScope();

            var count = (from p in northwindScope.Extent<Product>()

                            select p)

                            .Count();

 return count;

        }

    }

}

Create and configure the WCF service

Now that you’ve used OpenAccess to generated the persistent classes and create the data access layer, create a WCF service to serve up the data. To add a new service to the Services project, right-click on the project and select Add | New Item… Choose the WCF Service template, name it NorthwindProductsService and click Add. This generates the following four files: INorthwindProductsService.cs, NorthwindProductsService.svc, NorthwindProductsService.svc.cs, and Web.config.

First, the INorthwindProductsService interface will be the service contract. You need to decorate it with a ServiceContract attribute and add a couple of methods. The first method, GetProducts, will return a list of products; the second, GetProductCount, will return the total number of products in the database.

using System.Collections.Generic;

using System.ServiceModel;

using Telerik.Examples.Entities;

namespace Telerik.Examples.Services

{

    [ServiceContract]

 public interface INorthwindProductsService

    {

        [OperationContract]

        List<Product> GetProducts(int startIndex, int rowCount);

        [OperationContract]

 int GetProductCount();

    }

}

Next you need to implement this interface, so open the NorthwindProductsService class and add the implementation for those two methods. Notice that you only need to make a call to the data access layer. No logic or other processing is done here, keeping the code simple and easy to maintain.

using System.Collections.Generic;

using Telerik.Examples.Data;

using Telerik.Examples.Entities;

namespace Telerik.Examples.Services

{

 public class NorthwindProductsService : INorthwindProductsService

    {

 public List<Product> GetProducts(int startIndex, int rowCount)

        {

            var productProvider = new ProductDataProvider();

            var products = productProvider.GetProducts(startIndex, rowCount);

 return products;

        }

 public int GetProductCount()

        {

            var productProvider = new ProductDataProvider();

            var count = productProvider.GetProductCount();

 return count;

        }

    }

}

Since you’re using OpenAccess classes with the WCF service, you need to add a reference to the Telerik.OpenAccess namespace to the project. Otherwise, the project will not compile. Right-click on the project’s References folder and select Add Reference…, select the .NET tab and choose Telerik.OpenAccess.

The next step is to configure the WCF service in Web.config. For this example you can leave the address empty and let it default to the service file (NorthwindProductsService.svc). Use webHttpBinding for the binding and the INorthwindProductsService interface as the contract. Here’s the entire web configuration file:

<?xml version="1.0"?>

<configuration>

 <system.serviceModel>

 <behaviors>

 <endpointBehaviors>

 <behavior name="webHttpBehavior">

 <enableWebScript />

 </behavior>

 </endpointBehaviors>

 </behaviors>

 <services>

 <service name="Telerik.Examples.Services.NorthwindProductsService">

 <endpoint address="" 

 binding="webHttpBinding"

 behaviorConfiguration="webHttpBehavior"

 contract="Telerik.Examples.Services.INorthwindProductsService" />

 </service>

 </services>

 </system.serviceModel>

 <system.web>

 <compilation debug="true"/>

 </system.web>

</configuration>

Finally, you need to go back into the Entities project and modify the persistent class Product. In order for the class to be serialized and passed down to the caller when the web service is invoked, a DataContract attribute needs to be added to the Product class and a DataMember attribute added to each of its properties. In order to use the DataContract and DataMember attributes, you must add a reference to the System.Runtime.Serialization namespace to the project.

[DataContract]

public partial class Product

{

    [DataMember]

    [Telerik.OpenAccess.FieldAlias("productID")]

 public int ProductID

    {

        get { return productID; }

        set { this.productID = value; }

    }

    ...

}

Consume the WCF service

Once you’ve created the persistent classes, the data access layer, and set up a WCF service, you are finally ready to see the fruits of your labor by consuming and displaying data from the service on a web page. For this example, use the RadGrid for ASP.NET AJAX so you can see how easy it is to hook it up to your service.

To get started, open the Default.aspx page generated when you created the web application project. Drag a RadScriptManager control onto the top of the page from the toolbox. In Design View, click the control’s smart tag and select the Register Telerik.Web.UI.WebResource.axd link. Next, drag a RadGrid control onto the page. I like to configure ASP.NET pages in Source View, but you should know that all the settings can easily be configured from the Visual Studio designer.

To configure the RadGrid, first set the AllowPaging property to true, the AutoGenerateColumns property to false, and the PageSize property to 10.

<telerik:RadGrid ID="RadGrid1" runat="server"

 AllowPaging="true"

 AutoGenerateColumns="false"

 PageSize="10">

</telerik:RadGrid>

Since you don’t the RadGrid to generate columns automatically, you’ll need to define them. Add a <MasterTableView> section to the RadGrid and define the columns there. For this example, add 4 columns for the ProductName, UnitPrice, UnitsInStock, and Discontinued data fields.

<MasterTableView>

 <Columns>

 <telerik:GridBoundColumn DataField="ProductName" 

 HeaderText="Product" />

 <telerik:GridBoundColumn DataField="UnitPrice" 

 DataFormatString="{0:c}"

 HeaderText="Price" />

 <telerik:GridBoundColumn DataField="UnitsInStock" 

 HeaderText="Units" />

 <telerik:GridCheckBoxColumn DataField="Discontinued" 

 HeaderText="Discontinued?" />

 </Columns>

</MasterTableView>

Finally, it’s time to hook the RadGrid up to the WCF service. The cool thing about the RadGrid is that it now (as of the Q3 2008 release) supports codeless client-side data binding to web services. This means that you can configure it to talk to a WCF service without writing a single bit of JavaScript. To do this, add a <ClientSettings> section to the RadGrid, inside of which you need to add a <DataBinding> section. It’s in this section that all the magic happens. First set the location of the web service using the Location property. Then define a SelectMethod and a SelectCountMethod. Lastly, tell the RadGrid the names of the parameters for our WCF service (startIndex and rowCount).

<ClientSettings>

 <DataBinding

 Location="http://localhost/Services/NorthwindProductsService.svc"

 SelectMethod="GetProducts"

 SelectCountMethod="GetProductCount"

 StartRowIndexParameterName="startIndex"

 MaximumRowsParameterName="rowCount">

 </DataBinding>

</ClientSettings>

You’re done. By setting a few simple properties you’ve enabled the RadGrid to consume and display the data from your WCF service, making it much more performant than if a full- or partial-page postback was required to update the data in the grid. Here is the markup for the entire page. Note that you didn’t have to write any code in the code-behind of the page.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Telerik.Examples.Site._Default" %>

<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

 <title>Example - Consuming WCF Services with the RadGrid for ASP.NET AJAX</title>

</head>

<body>

 <form id="form1" runat="server">

 <telerik:RadScriptManager ID="RadScriptManager1" runat="server" /> 

 <telerik:RadGrid ID="RadGrid1" runat="server"

 AllowPaging="true"

 AutoGenerateColumns="false"

 PageSize="10">

 <MasterTableView>

 <Columns>

 <telerik:GridBoundColumn DataField="ProductName" 

 HeaderText="Product" />

 <telerik:GridBoundColumn DataField="UnitPrice" 

 DataFormatString="{0:c}"

 HeaderText="Price" />

 <telerik:GridBoundColumn DataField="UnitsInStock" 

 HeaderText="Units" />

 <telerik:GridCheckBoxColumn DataField="Discontinued" 

 HeaderText="Discontinued?" />

 </Columns>

 </MasterTableView>

 <ClientSettings>

 <DataBinding

 Location="http://localhost/Services/NorthwindProductsService.svc"

 SelectMethod="GetProducts"

 SelectCountMethod="GetProductCount"

 StartRowIndexParameterName="startIndex"

 MaximumRowsParameterName="rowCount">

 </DataBinding>

 </ClientSettings>

 </telerik:RadGrid>

 </form>

</body>

</html>

Running the app

Normally with examples like these the web service is hosted within the same project as the site that consumes it. So what do you do when the service and site are running in separate projects? Well, there are several options. You can run each project in a separate instance of Visual Studio’s built-in web server. The problem with this is that Visual Studio assigns random ports to the projects when it runs them, so unless you define a static port ahead of time there’s no way to know to which address you will need to point the web site in order for it to consume the service.

The approach I like to take is to have Visual Studio add the project to my local instance of IIS as a virtual directory of the default web site. This method ensures that I will always know ahead of time the address at which both the site and web service will be hosted. I also get the added bonus of not being able to run the app even when Visual Studio is closed. To configure this setting, right-click on the project and select Properties. Select the Web tab and click the Use Local IIS Web server radio button. Give your project a URL and click the Create Virtual Directory button.

Now you can hit F-5 to launch the web site from Visual Studio. You’ll see the RadGrid render to the page and load up with all the data from the WCF service.

If you use an add-in like Firebug for Firefox you can monitor the traffic going across the network as you page through the records in the RadGrid.

As you can see, only a minimal amount of data is passed over the network. Compared to the amount of data required to make a full- or partial-page postback, this is a huge improvement (especially considering the small amount of extra effort required).

Gotchas

There are always lessons to be learned when working with new technologies for the first time. Here are a couple of lessons I had to figure out the hard way while using these techniques:

1. When you installed OpenAccess, it installed several assemblies into the GAC. These assemblies are referenced in the configuration files when you use OpenAccess in your projects. When it’s time to deploy your project to a server environment, don’t forget that these references will no longer work if OpenAccess is not installed on your server. In order to ensure that your application will work when deployed, you either need to install OpenAccess on the server or add the following assemblies to your project bin folder and update the references in your configuration files.

• Telerik.OpenAccess
• Telerik.OpenAccess.Adonet2
• Telerik.OpenAccess.Config
• Telerik.OpenAccess.Query
• Telerik.OpenAccess.RunTime

You can find these assemblies in the OpenAccess installation directory of your development machine, in the bin folder.

2. If you launch the OpenAccess reverse engineering wizard to regenerate your persistent classes, don’t forget to go back and decorate the classes and their properties with DataContract and DataMember attributes. If you forget to add these attributes your application will fail and all you’ll get is this helpful message (please not the sarcasm):

WCF is unable to serialize the objects and simply fails without returning any helpful information to indicate what the problem might be. This issue caused me to pull my hair out for about an hour until I stumbled upon a great post by Rick Strahl, who experienced a similar problem.

3. Sometimes when you separate your persistent classes and ObjectScopeProvider classes in different projects, things get out of sync. If you receive an error when compiling that says no persistent classes were found, simply select your project and click OpenAccess | Configuration | Update Config References from the Visual Studio menu.

Wrap up

Telerik makes it so easy for developers to take advantage of technologies like WCF to serve their data. Full-featured controls like the RadGrid for ASP.NET AJAX make consuming data from web services painless. And now that Telerik provides a more complete development toolset with the addition of OpenAccess ORM to their developer toolbox, there is even less code to write as you take your applications from concept to complete.