Effective EF with Oracle – RIA & Silverlight 3

Over the past few months we received numerous how-to requests on how best to wire up a Silverlight 3 application with ADO.NET EF, ADO.NET Data Services and Oracle for use in an RIA application. To meet that need, we've put together how-to that we are now publishing on our .NET Connections blog. Full credit goes to our team over at PSI, specifically Avadhoot Kulkarni for doing the majority of leg work here.

First make sure you have all the prerequisites, such as Microsoft Visual Studio 2008 SP1, Microsoft .NET framework 3.5 SP1, Microsoft Silverlight 3, Microsoft Silver-light plug-in for Visual Studio 2008 SP1 and of course Progress DataDirect Connect for ADO.NET 3.3 to connect to Oracle.

Let's start with creating database schema on our Oracle database. I will create a single table called Categories. [cc lang="sql" line_numbers="false"] CREATE TABLE "Categories" ( "CategoryID" NUMBER (10,0) NOT NULL, "CategoryName" VARCHAR2 (15) NOT NULL, "Description" LONG NULL, "Picture" BLOB NULL, CONSTRAINT "PK_Categories" PRIMARY KEY("CategoryID") ) [/cc] And we'll populating the table much as you'd expect. [cc lang="sql" line_numbers="false"] INSERT INTO "Categories" ("CategoryID", "CategoryName", "Description", "Picture") VALUES(1,'Beverages','Soft drinks, coffees, teas, beers, and ales') INSERT INTO "Categories" ("CategoryID", "CategoryName", "Description", "Picture") VALUES(2,'Condiments','Sweet and savory sauces, relishes, spreads, and seasonings') INSERT INTO "Categories" ("CategoryID", "CategoryName", "Description", "Picture") VALUES(3,'Confections','Desserts, candies, and sweet breads') INSERT INTO "Categories"("CategoryID","CategoryName","Description","Picture") VALUES(4,'DairyProducts','Cheeses') INSERT INTO "Categories"("CategoryID","CategoryName","Description","Picture") VALUES(5,'Grains/Cereals','Breads, crackers, pasta, and cereal') INSERT INTO "Categories"("CategoryID","CategoryName","Description","Picture") VALUES(6,'Meat/Poultry','Prepared meats') INSERT INTO "Categories"("CategoryID","CategoryName","Description","Picture") VALUES(7,'Produce','Dried_fruit and bean curd') [/cc]

Next up, we'll create a new ASP.NET Application project in Visual Studio.

1.      Add a new Entity Model for our Categories using the Entity Data Model wizard in Visual Studio 2008. Check MSDN for more details here.

2.      Next, add new ADO.NET data Services component to the ASP.NET project name it as 'CategoriesDataService1'. Modify CategoriesDataService1.svc file with the required Initialization rules as follows. [cc lang="csharp" line_numbers="false"] public class CategoriesDataService1 : DataService { // This method is called only once to initialize service-wide policies. public static void InitializeService(IDataServiceConfiguration config) { // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc. config.SetEntitySetAccessRule("*", EntitySetRights.All); config.SetServiceOperationAccessRule("*", ServiceOperationRights.All); } } [/cc]

Configure your ASP.NET application to run on Specific port 50000 (for example) in the project propertiesWeb

Run the ASP.NET Application and browse our CategoriesDataService1.svc through browser. Now we can browse data through URLs like all Categories, as you'd expect.

Or some specific category identified by its Entity key property. [In this case CategoryID]

Now, let's spin up a Silverlight application in a new Visual Studio instance, while our new Data Service application runs. We'll add the Data Service reference to Silverlight app using "Add Service Reference" wizard.

You'll next need to modify the Mainpage.xaml which defines our Silverlight UI. Notice it has a "Get Categories" button and a Data Grid with title "Categories". [cc lang="xml" line_numbers="false"]

[/cc]

To ensure compile nicely, we need to add reference to the System.Windows.controls.Data to our Silverlight project and a namespace declaration in User Control block. [cc lang="xml" line_numbers="false"] xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data" [/cc]

Next up, open MainPage.xaml.cs to implement the "Button_Click" event handler. Add following lines to the .cs file.

[cc lang="csharp" line_numbers="false"] private void Button_Click(object sender, RoutedEventArgs e) { DataServiceQuery query = (DataServiceQuery) from c in entities.Categories where c.CategoryID > 5 select c; DataGrid1.ItemsSource = null; query.BeginExecute(new AsyncCallback(c => { IEnumerable results = query.EndExecute(c); loadedProducts = results.ToList(); DataGrid1.ItemsSource = loadedProducts; }), query); } [/cc]

Now you're ready to run and enjoy your ADO.NET EF aware Silverlight application!.