ADO.NET vNext Part 2: Using the Object Services Layer

he first installment of this three-part article series introduced the core concepts of ADO.NET vNext, including the Entity Data Model (EDM), Entity SQL, and the Mapping Provider. It also discussed the steps involved in executing entity SQL queries against the EDM and retrieving output in the form of rows and values (contained in a DbDataReader object). This installment builds on Part 1, focusing on a layer named the Object Services Layer that lets you work with database tables and columns as objects, and properties. The object services layer is an abstraction on top of EDM that leverages the same EDM infrastructure to execute queries against a database.

The object services layer provides a strongly typed object abstraction, letting you work with the database layer in terms of programming language objects rather than only the tabular “rows-and-columns” model thereby handling the impedance mismatch problem. The object services layer provides a cache of objects and the ability to perform CRUD operations on these objects. There are two ways to execute queries through the object services layer?via LINQ or Entity SQL. This installment focuses on query execution using Entity SQL and the next installment discusses using of LINQ for query execution in detail.

To follow along you’ll need to have Visual Studio Professional, and you’ll need to download and install these three items:

As you can see in Figure 1, the object service layer leverages the combination of EDM and mapping provider and exposes a set of strongly typed objects to the client application.

?
Figure 1. ADO.NET Object Services Layer: The Object Services layer allows each client application to have an object representation of the entities in the EDM.

As Figure 1 shows, the object services layer integrates seamlessly with the rest of the stack, providing developers with the choice of exposing entities as .NET objects. In addition, the object services layer also handles object identity resolution, change tracking, and persistence.

Retrieving Data as Objects
In Part 1 of this series, you saw the code required to retrieve data in the form of a DbDataReader object (as rows and columns). One shortcoming of this approach is its late bound nature, which is not only error-prone but also difficult to program with. The object services layer lets you shift away from late binding to an early bound implementation where you can work with database entities as traditional .NET objects.

Here’s an example that uses the object services layer to retrieve data from the ProductSubcategory entity in EDM.

   private void btnGetCategories_Click(object sender, EventArgs e)   {     string connectionString =       Properties.Settings.Default.AdventureWorksConnectionString;     ObjectContext context = new ObjectContext(connectionString,        "AdventureWorksModel.AdventureWorks");                 Query categories =        context.GetQuery(       "SELECT VALUE p FROM AdventureWorksModel.AdventureWorks." +                 "ProductSubcategory AS p ");     lstCategories.Items.Clear();     foreach (ProductSubcategory category in categories)     {       lstCategories.Items.Add(category.Name);     }   }

The preceding code begins by retrieving a connection string from the custom settings originally stored using the property pages of the project (Settings tab). These custom settings are stored in the app.config file and available at runtime.

For the purposes of this example I created a setting named AdventureWorksConnectionString in the Property pages. Visual Studio adds strongly typed properties to a Settings class created by the designer so that you can access those settings programmatically with code like this:

   string connectionString =       Properties.Settings.Default.AdventureWorksConnectionString;

Next, to execute queries using the object services layer, you need to create an instance of the ObjectContext object that acts as the entry point to the object services layer.

     ObjectContext context = new ObjectContext(connectionString,        "AdventureWorksModel.AdventureWorks");               

Author’s Note: The above code uses Entity SQL with the object services layer to retrieve data, but it’s equally possible to use LINQ queries in conjunction with object services layer. I’ll cover that in the next installment.

The results of the execution is a Query object, which is returned as an output from the call to ObjectContext.GetQuery().

   Query categories =        context.GetQuery(       "SELECT VALUE p FROM AdventureWorksModel.AdventureWorks." +           "ProductSubcategory AS p ");

Note that the object services layer uses the same map connection (pointing to the same EDM schema and mappings) as well as the same query language; however, it now returns the results in object form. The query result is an object of type ProductSubcategory?a type generated during the creation of the EDM. For more information on the creation of the EDM, see Part 1 of this article series. When it creates the entity data model, the entity data model wizard also generates an object representation for each table belonging to entity data model. If you open up the AdventureWorksModel.Model.cs file (which contains all the generated classes), you will find the ProductSubcategory class declared as follows:

   [System.Data.Objects.EntityTypeAttribute      (SchemaName="AdventureWorksModel",      TypeName="ProductSubcategory")]   public partial class ProductSubcategory :      global::System.Data.Objects.Entity   {     public ProductSubcategory()     {       if ((((object)(this)).GetType() ==          typeof(global::AdventureWorksModel.ProductSubcategory)))       {         this.DoFinalConstruction();       }     }         public ProductSubcategory(int productSubcategoryID,         string name, global::System.Guid rowguid,         global::System.DateTime modifiedDate)      {        this.ProductSubcategoryID = productSubcategoryID;        this.Name = name;        this.rowguid = rowguid;        this.ModifiedDate = modifiedDate;        if ((((object)(this)).GetType() ==           typeof(global::AdventureWorksModel.ProductSubcategory)))        {          this.DoFinalConstruction();        }      }          [System.Data.Objects.ScalarFieldAttribute()]       [System.Data.Objects.NullableConstraintAttribute(false)]       public int ProductSubcategoryID       {         get{return this._ProductSubcategoryID;       }         set         {           -----         }       }       private int _ProductSubcategoryID;
?
Figure 2. Output Generated by Executing Queries through the Object Services Layer: The ListBox displays the names of all the products sub categories contained in the AdventureWorks database.
---- ---- }

Note that ProductSubcategory is a partial class that makes it possible for you to add custom business logic in a separate class file without having to modify the tool-generated class. For the complete code of the ProductSubcategory class, please refer to the downloadable code for this article.

After retrieving the collection of ProductSubcategory objects, you can then loop through the collection to display the results in the ListBox, as shown in Figure 2.

       foreach (ProductSubcategory category in categories)       {         lstCategories.Items.Add(category.Name);       }

Note the ease with which you can retrieve the values from the collection, once you have the results available in a strongly typed object.

Key Capabilities of ObjectContext
As mentioned before, the ObjectContext is a key object in the object services layer that acts as the single point of contact for the client applications to interact with the data store. Basically, ObjectContext acts as a higher level container that manages and tracks instances. Specifically, it:

  • Keeps track of every object retrieved through a query.
  • Tracks the original versions of the values used to populate each object. This allows the system to perform optimistic concurrent checks against the database during updates.
  • Tracks objects that have been modified so that it knows which entities need to be updated in the store when you invoke the SaveChanges() method.

Passing Parameters to Entity SQL
In this section, I’ll build on the previous example to demonstrate how to pass parameters to the Entity SQL through the object services layer. For example, suppose you want to query the Product table in the AdventureWorks, supplying the ProductID as an input parameter to retrieve a specific product.

   private void btnGetProductDetails_Click     (object sender, EventArgs e)   {     string connectionString =       Properties.Settings.Default.AdventureWorksConnectionString;     ObjectContext context = new ObjectContext(connectionString,        "AdventureWorksModel.AdventureWorks");                 Query products = context.GetQuery       ("SELECT VALUE p FROM " +       " AdventureWorksModel.AdventureWorks.Product "       " AS p Where p.ProductID = @ProductID",       new QueryParameter("ProductID",        Convert.ToInt32(txtProductID.Text)));     lstProductDetails.Items.Clear();     foreach (Product prod in products)     {       lstProductDetails.Items.Add(prod.ProductID);       lstProductDetails.Items.Add(prod.Name);       lstProductDetails.Items.Add(prod.ProductNumber);      }   }   
?
Figure 3. Selecting Details of a Specific Product using Parameters: You can execute Entity SQL query with parameters by creating an instance of the QueryParameter object with appropriate arguments passed to its constructor.

The use of the QueryParameter object is a key point to note in the above code. You populate it with the name of the parameter and the parameter value through its constructor. After creating a QueryParameter object, you pass it to the ObjectContext.GetQuery() method as the second argument.

The user enters the value for the ProductID parameter in the TextBox named txtProductID. Once you execute the query, the code stores the results in a variable of type Query, which is a collection the last part of the code loops through to display the results in the list box (see Figure 3).

You can see that most of the plumbing code has been eliminated, and only the code that represents the application intent remains. At this point most of the impedance mismatch problem mentioned earlier has been eliminated. This is a key goal of the ADO.NET Entity Framework.

Updating Data
Usually when you want to make a change to the database, you issue INSERT, UPDATE and DELETE statements against the database. In most modern applications, a custom object layer that maps nicely to the entities in the database abstracts these statements. Now; however, the object services layer handles all the underlying complexities for you. The object services layer makes this possible by leveraging the entity metadata stored by the ADO.NET entity framework.

?
Figure 4. Updating the List Price of All Products: To perform batch updates, you loop through all the products, update each of them and finally commit changes through the ObjectContext.SaveChanges() method call.

As an example, consider a scenario where you want to increase the price of all the products in the Product table by a specific amount entered by the user (see Figure 4).

   private void btnIncreaseThePrice_Click(      object sender, EventArgs e)   {     string connectionString =       Properties.Settings.Default.       AdventureWorksConnectionString;     ObjectContext context = new ObjectContext(        connectionString,        "AdventureWorksModel.AdventureWorks");                 Query products = context.GetQuery(       "SELECT VALUE p FROM " +         "AdventureWorksModel.AdventureWorks." +       "Product AS p");     foreach (Product prod in products)     {       prod.ListPrice += Convert.ToInt32(txtPrice.Text);     }     context.SaveChanges();     MessageBox.Show("Product Price successfully updated");         }   

The first few lines of the preceding code are very similar to the previous example?it executes a simple Entity SQL to get a list of products from the AdventureWorks database. However, after retrieving the list, the code becomes more interesting when it updates the list price of all the products.

   Query products = context.GetQuery(         "SELECT VALUE p FROM AdventureWorksModel.AdventureWorks." +         "Product AS p");

To update the list price of all the products, you loop through all the products contained in the Query object and increase the list price of each of the products by the value entered in the txtPrice text box.

       foreach (Product prod in products)       {         prod.ListPrice += Convert.ToInt32(txtPrice.Text);       }

Now you need to commit the latest changes in the database, which you can accomplish with one line of code by simply calling the ObjectContext.SaveChanges() method.

     context.SaveChanges();

When you work with objects through the object services layer, it retrieves object instances from the cache and makes changes to them in-memory. When you invoke the SaveChanges() method, it pushes the in-memory changes to the database store.

Inserting Data
Now that you have looked at the code required to perform read and update operations through the object services database, here’s an example showing how to insert data that uses the ProductCategory table in the AdventureWorks database.

   private void btnAddCategory_Click(object sender, EventArgs e)   {     string connectionString =       Properties.Settings.Default.AdventureWorksConnectionString;     ObjectContext context = new ObjectContext(connectionString,        "AdventureWorksModel.AdventureWorks");                 ProductCategory category = new ProductCategory();     category.Name = txtCategoryName.Text ;     category.ModifiedDate = DateTime.Now;     category.rowguid = System.Guid.NewGuid();     context.AddObject(category);     context.SaveChanges();                     MessageBox.Show("Product Category successfully added");   }

The preceding code creates a new ProductCategory object instance, setting its properties to appropriate values. Then it invokes the ObjectContext.AddObject() method to add the ProductCategory object to the current object context.

     context.AddObject(category);

To persist the inserted values to the database, you then invoke the ObjectContext.SaveChanges() method.

     context.SaveChanges();  

Figure 5 shows the output generated by the Windows form in the downloadable sample application.

?
Figure 5. Inserting a New Product Category: To insert a new product category, you simply create a ProductCategory object, populate its properties, invoke the ObjectContext.AddObject() method and finally persist the changes through the ObjectContext.SaveChanges() method.

A Missing Operation
Astute readers will have noticed that I haven’t covered the DELETE operation. There is a DeleteObject method available (seen through Intellisense) but it requires a bunch of arguments (such as a MetadataWorkspace object and so on). I tried passing various combinations of values to get that to work, but it never did work. Unfortunately, there is no documentation on that method to see exactly what needs to be passed and in what format. Perhaps DELETE operations will become clearer in a later CTP; if so, I will cover that in a future article.

Even without that, however, you’ve seen how the object services layer simplifies working with data, letting you make modifications to .NET objects rather than performing detailed operations against tabular data. To recap, the main advantages are:

  • By using the object services layer, you can delegate the management of persistence, and to work at state management to the ADO.NET entity framework, and focus mainly on the core business logic of your application.
  • By representing entities as a set of objects, you work with objects that directly represent the domain model, and not with data structures in the same format as the relational database (like DataReaders).
  • The object services layer provides a high quality, flexible data access layer that reduces testing and debugging efforts.

As examples, you saw how to perform READ, INSERT, and UPDATE operations through the object services layer. The next installment in this series will focus on the intersection between LINQ (Language INtegrated Query) and ADO.NET vNext Framework.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Related Posts