XPO – 11.1 Sneak Peek – Data caching improvements

Introduction

First, we would like to refresh your memory and return to the distant past and our old posts about data caching in XPO:

·        XPO is good for distributed applications

·        XPO Beta feature: SQLDependency support

Today is 2011, and we are excited to announce that in v2011 vol1, we greatly improved data caching support in XPO, and added additional functionality to it, allowing this feature to leave the “beta” state.

Want data caching via WCF Services? – Now, it’s easy!

In the previous blog, we already demonstrated how easy it is in 11.1 to create and use WCF Services for IDataStore. If you want to take advantages of XPO data caching using this solution, then the only difference is in using the ICachedDataStore interface and a respective base service class that implements this interface – CachedDataStoreService.

Let’s demonstrate it in action (the exact steps on how to add a data store service can be taken from the previous blog):

  1: using DevExpress.Xpo;
  2: using DevExpress.Xpo.DB;
  3: …
  4: public class Service1 : CachedDataStoreService {
  5:     public static ICachedDataStore MainDataStore;
  6:     static Service1() {
  7:         string connectionString = MSSqlConnectionProvider.GetConnectionString("localhost", "ServiceDB");
  8:         IDataStore dataStore = XpoDefault.GetConnectionProvider(connectionString, AutoCreateOption.DatabaseAndSchema);
  9:         MainDataStore = new DataCacheRoot(dataStore);
 10:     }
 11:     public Service1()
 12:         : base(MainDataStore) {
 13:     }
 14: }

Of course, some modifications of the service binding in the web.config file are necessary as well:

  1: <system.serviceModel>
  2:  <services>
  3:    <service name="WcfService1.Service1" behaviorConfiguration="WcfService1.Service1Behavior">
  4:      <!-- Service Endpoints -->
  5:      <endpoint address="" binding="basicHttpBinding" contract="DevExpress.Xpo.DB.ICachedDataStoreService">
  6:        <identity>
  7:          <dns value="localhost"/>
  8:        </identity>
  9:      </endpoint>
 10:    </service>
 11:  </services>
 12:  <behaviors>
 13:    <serviceBehaviors>
 14:      <behavior name="WcfService1.Service1Behavior">
 15:        <serviceMetadata httpGetEnabled="true"/>
 16:        <serviceDebug includeExceptionDetailInFaults="false"/>
 17:      </behavior>
 18:    </serviceBehaviors>
 19:  </behaviors>
 20: </system.serviceModel>
 21: 

Fortunately, the client part does not need to be modified, because in our example, the service’s name did not change, and in addition, the “data caching domain” of our service is automatically detected by XPO.

Data caching configuration

After looking at how easy it is to implement, you may naturally ask how flexible it really is. Of course we did not forget about flexibility!

For example, it is possible for you to configure which tables need to be cached and which ones don’t. This option can be useful for tables, which are frequently changed – if they are changed a lot, it makes no sense to cache them. The code below demonstrates how the service above can be modified to configure the DataCacheRoot, to cache only the “Customer” table:

  1: using DevExpress.Xpo;
  2: using DevExpress.Xpo.DB;
  3: using DevExpress.Xpo.DB.Helpers;
  4: …
  5: public class Service1 : CachedDataStoreService {
  6:     public static ICachedDataStore MainDataStore;
  7:     static Service1() {
  8:         string connectionString = MSSqlConnectionProvider.GetConnectionString("localhost", "ServiceDB");
  9:         IDataStore dataStore = XpoDefault.GetConnectionProvider(connectionString, AutoCreateOption.DatabaseAndSchema);
 10:         DataCacheRoot dataCacheRoot = new DataCacheRoot(dataStore);
 11:         dataCacheRoot.Configure(
 12:             new DataCacheConfiguration(DataCacheConfigurationCaching.InList, "Customer")
 13:         );
 14:         MainDataStore = dataCacheRoot;
 15:     }
 16:     public Service1()
 17:         : base(MainDataStore) {
 18:     }
 19: }
 20: 

Leveraging SQLDependency

Another configuration option for data caching we would like to tell you about is using SqlDependency feature of MS SQL Server (see here for the background on that). Again, a modified service class demonstrates how this can be done:

  1: using DevExpress.Xpo;
  2: using DevExpress.Xpo.DB;
  3: using DevExpress.Xpo.DB.Helpers;
  4: …
  5: public class Service1 : CachedDataStoreService {
  6:     public static ICachedDataStore MainDataStore;
  7:     static Service1() {
  8:         string connectionString = MSSqlConnectionProvider.GetConnectionString("localhost", "ServiceDB");
  9:         MSSqlConnectionProvider dataStore = (MSSqlConnectionProvider)XpoDefault.GetConnectionProvider(
 10:             connectionString,
 11:             AutoCreateOption.DatabaseAndSchema
 12:         );
 13:         MainDataStore = (ICachedDataStore)MSSql2005SqlDependencyCacheRoot.CreateSqlDependencyCacheRoot(
 14:             dataStore,
 15:             new DataCacheConfiguration(DataCacheConfigurationCaching.InList, "Customer"),
 16:             out objectsToDispose
 17:         );
 18:     }
 19:     public Service1()
 20:         : base(MainDataStore) {
 21:     }
 22: }

That’s it for this blog. Do you like what you see? Please let us know with your comments!

And happy XPOingWinking smile, as always!;-)

XPO – 11.1 Sneak Peek - WCF services for IDataStore

In XPO v2011.1 vol 1, we added support for WCF Services. These improvements provide the capability to create service and client for IDataStore as easy as it can only be.

In order to support this feature, the DevExpress.Xpo assembly will reference assemblies from .NET 3.0. Unfortunately, Visual Studio 2010 does not allow building projects whose Target Framework is set to 2.0, if they reference .NET 3.0 assemblies. To bypass this, it is necessary to set Target Framework to 3.0. Quite surprisingly, other Visual Studio versions do not demonstrate the above problem.
This does not mean that an application will not work on a machine that has only .NET 2.0 installed on it. If the project does not use the functionality of .NET 3.0, it will work just as before. Therefore, it should not affect your clients’ installations at all.

Let’s learn more about the implemented feature from a simple example.
For the beginning, we will create a new WCF Service Application project. Then, we will add references to the DevExpress.Data and DevExpress.Xpo assemblies and remove files with auto-generated interfaces for the service.

The next step is modifying the service class as follows: 

using DevExpress.Xpo;

using DevExpress.Xpo.DB;

namespace WcfService1 {

    public class Service1 : DataStoreService {

        public static IDataStore DataStore;

        static Service1() {

            string connectionString = MSSqlConnectionProvider.GetConnectionString("localhost", "ServiceDB");

            DataStore = XpoDefault.GetConnectionProvider(connectionString, AutoCreateOption.DatabaseAndSchema);

        }

        public Service1()

            : base(DataStore) {

        }

    }

}

To finish with the service, we will need to change some binding properties in its web.config file:

<system.serviceModel>

    <services>

        <service name="WcfService1.Service1" behaviorConfiguration="WcfService1.Service1Behavior">

            <!-- Service Endpoints -->

            <endpoint address="" binding="basicHttpBinding" contract="DevExpress.Xpo.DB.IDataStoreService">

                <identity>

                    <dns value="localhost"/>

                </identity>

            </endpoint>

        </service>

    </services>

    <behaviors>

        <serviceBehaviors>

            <behavior name="WcfService1.Service1Behavior">

                <serviceMetadata httpGetEnabled="true"/>

                <serviceDebug includeExceptionDetailInFaults="false"/>

            </behavior>

        </serviceBehaviors>

    </behaviors>

</system.serviceModel>

Now our service is ready, and it is time to implement the client part.
 
For the client, we will add a Console Application into our existing solution. Then, we will use the Persistent Object item template for a  Customer class:

using DevExpress.Xpo;

namespace ConsoleApplication1 {

    public class Customer : XPObject {

        public Customer(Session session) : base(session) { }

        string _CompanyName;

        public string CompanyName {

            get { return _CompanyName; }

            set { SetPropertyValue("CompanyName", ref _CompanyName, value); }

        }

        string _CompanyAddress;

        public string CompanyAddress {

            get { return _CompanyAddress; }

            set { SetPropertyValue("CompanyAddress", ref _CompanyAddress, value); }

        }

        string _ContactName;

        public string ContactName {

            get { return _ContactName; }

            set { SetPropertyValue("ContactName", ref _ContactName, value); }

        }

        string _Country;

        public string Country {

            get { return _Country; }

            set { SetPropertyValue("Country", ref _Country, value); }

        }

        string _Phone;

        public string Phone {

            get { return _Phone; }

            set { SetPropertyValue("Phone", ref _Phone, value); }

        }

    }

}

The final step is to modify the Main() of our console application as shown in the code below:

using System;

using DevExpress.Xpo;

using DevExpress.Xpo.DB;

namespace ConsoleApplication1 {

    class Program {

        static void Main(string[] args) {

            XpoDefault.DataLayer = XpoDefault.GetDataLayer("http://localhost:64466/Service1.svc",

            AutoCreateOption.DatabaseAndSchema);

            XpoDefault.Session = null;

            using (UnitOfWork uow = new UnitOfWork()) {

                using (XPCollection<Customer> customers = new XPCollection<Customer>(uow)) {

                    foreach (Customer customer in customers) {

                        Console.WriteLine("Company Name = {0}; ContactName = {1}", customer.CompanyName, customer.ContactName);

                    }

                }

            }

            Console.WriteLine("Press any key...");

            Console.ReadKey();

        }

    }

}

As you can see, we pass the address of our service into the GetDataLayer method of the XpoDefault class. It is very similar to what was already supported for Web Services.

Now if we run the service and client parts, we will see the following output:
screenshot
Take special note that the port number in the connection string may be different. You can check it in the properties of the service project in the Solution Explorer:
screenshot

Happy XPOing!

XPO – 11.1 Sneak Peek – Support for the OUTER APPLY operator in SQL queries

We have some good news for our XPO users. In v2011 vol1 we have improved our sql-generator by supporting the OUTER APPLY operator. This SQL operator enables you to create an aggregate’s sub queries outside the WHERE clause and the SELECT expressions. For instance, it provides the capability to perform nested aggregates and aggregates in the group by list of the GROUP BY clause.

 

How this support is useful for you? It is useful because Microsoft SQL Server and SQL Server Compact are limited  in performing aggregates in these scenarios. For instance, the following queries will throw an exception without the OUTER APPLY operator:

 

1) Nested aggregate:

 

select N0.Oid, (select max(N1.Date) from [Orders] N1

where N1.EmployeeId = N0.EmployeeId

AND (select count(*) from [Orders] N2 where N2.CustomerId = N1.CustomerId) <= 2)

from [Employees] N0

 

2) Aggregate in the group by list of a GROUP BY clause:

 

select (select count(*) as Res from "dbo"."FreeClass" N2

where (N0."Data" = N2."Data"))

from "dbo"."FreeClassSub" N0

group by (select count(*) as Res from "dbo"."FreeClass" N3

where (N0."Data" = N3."Data"))

 

However, if we modify the above queries to use the OUTER APPLY operator, they will perform correctly:

 

1) Nested aggregate:

 

select N0.Oid, OA0.Res

from [Employees] N0

outer apply

(select max(N1.Date) as Res from [Orders] N1

outer apply

(select count(*) as Res from [Orders] N2 where N2.CustomerId = N1.CustomerId) OA1

where N1.EmployeeId = N0.EmployeeId AND OA1.Res <= 2)) OA0

 

 

2) Aggregate in the group by list of a GROUP BY clause:

 

select OA0.Res from "dbo"."FreeClassSub" N0

outer apply

(select count(*) as Res from "dbo"."FreeClass" N3 where (N0."Data" = N3."Data")) OA0

group by OA0.Res

 

The OUTER APPLY operator is supported by Microsoft SQL Server and SQL Server Compact. So starting from version 11.1, criteria and group properties (like “[Orders][ [Customers][City = ‘London’].Count() > 10 ].Min(ShippedDate)”) can be performed on those DBMS without any problems. BTW, I recently saw one more practical example from a customer, who incurred the limit of the queries described above. I will post it here, just in case you have similar structures in your projects. So, here are his business classes:

 

public class ParentObject : XPObject {
    ...
    [
Aggregated]

    [Association("Parent-Children")]

    public XPCollection<ChildObject> ChildObjects { ... }

    [PersistentAlias("ChildObjects[].Max(LastChange)")]

    public DateTime LastChangeOfChildObjects {

        get { return (DateTime)EvaluateAlias("LastChangeOfChildObjects"); }

    }

    ...

}

public class ChildObject : XPObject {
    ...

    [Association("Parent-Children")]

    public ParentObject Parent { ... }

    public DateTime LastChange { ... }

    ...

}


The ParentObject was bound to a grid control working in the server mode. If you try to group by the LastChangeOfChildObjects column (take special note of the expression in its PersistentAlias attribute) in 10.2, it will not work, because of the described limitation. There are of course many other examples.

 

Finally, I should note that support for the OUTER APPLY operator is an internal improvement, and that means that XPO users will not require any additional actions to turn it on.

XPO to Database Connectivity: Mastering Fork Etiquette

UPDATED:

The DataStoreFork API is internal and undocumented and was described in this post before the official XPO solution for creating pooled connections was implemented.

Use the DevExpress.Xpo > XpoDefault > GetConnectionPoolString API in the latest XPO versions for this task.

=======================

Check out this how-to article that shows a new way of setting up XPO to work with websites, where a large number of simultaneously connected users is a common occurrence. It may help you reduce server response latency for pages that depend on data supplied by eXpress Persistent Objects.
Mastering Fork Etiquette
Let's consider a standard approach to XPO data layer creation in an ASP.NET website:

// C#
using System.Configuration;
using System.Data.SqlClient;
using DevExpress.Xpo;
using DevExpress.Xpo.DB;
using DevExpress.Xpo.Metadata;

IDataLayer GetDataLayer() {
    ReflectionDictionary dict = new ReflectionDictionary();
    dict.CollectClassInfos(typeof(Person).Assembly);

    string connStr = ConfigurationManager.ConnectionStrings["PrimaryConnection"].ConnectionString;
    SqlConnection conn = new SqlConnection(connStr);
    IDataStore store = new MSSqlConnectionProvider(conn, AutoCreateOption.SchemaAlreadyExists);

    return new ThreadSafeDataLayer(dict, store);
}

' VB.NET
Imports System.Data.SqlClient
Imports DevExpress.Xpo
Imports DevExpress.Xpo.DB
Imports DevExpress.Xpo.Metadata

Private Function GetDataLayer() As IDataLayer
    Dim dict As New ReflectionDictionary()
    dict.CollectClassInfos(GetType(Person).Assembly)

    Dim connStr As String = Configuration.ConfigurationManager.ConnectionStrings("PrimaryConnection").ConnectionString
    Dim conn As New SqlConnection(connStr)
    Dim store As IDataStore = New MSSqlConnectionProvider(conn, AutoCreateOption.SchemaAlreadyExists)

    Return New ThreadSafeDataLayer(dict, store)
End Function

A single database connection object is created in the code snippet above, which is then used by XPO for data access. 

Generally, an ASP.NET application is a multi-user application. The situation when two users are requesting a webpage simultaneously is quite common. IIS and ASP.NET support multithreading. Said differently, two user requests can be processed in parallel. However, when it comes to querying the database, the second user is kept waiting while the first user's query is being processed, because a single database connection object has been created for the XPO data layer.

Creating a separate XPO data layer for each user or even for each page request is one possible solution here. This solution, however, is not perfect: XPO layer creation and establishing a database connection is a slow and resource expensive operation. Moreover, a ThreadSafeDataLayer, as it follows from its name, already supports multithreading. If one could create a pool of multiple connection objects and teach the XPO layer to select the first free available connection for the next database query, that would be really cool.

Fortunately, ThreadSafeDataLayer already can handle a connection pool called DataStoreFork. That is to say, you create an IDataStore object array, instantiate a DataStoreFork, which is then passed to the ThreadSafeDataLayer constructor:

// C#
IDataStore GetDataStore() {
    string connStr = ConfigurationManager.ConnectionStrings["PrimaryConnection"].ConnectionString;
    SqlConnection conn = new SqlConnection(connStr);
    IDataStore store = new MSSqlConnectionProvider(conn, AutoCreateOption.SchemaAlreadyExists);
    return store;
}

IDataLayer GetDataLayer() {
    ReflectionDictionary dict = new ReflectionDictionary();
    dict.CollectClassInfos(typeof(Person).Assembly);

    const int maxConnections = 3;
    IDataStore[] stores = new IDataStore[maxConnections];
    for(int i = 0; i < maxConnections; i++)
        stores[i] = GetDataStore();

    return new ThreadSafeDataLayer(dict, new DataStoreFork(stores));
}

' VB.NET
Private Function GetDataStore() As IDataStore
    Dim connStr As String = Configuration.ConfigurationManager.ConnectionStrings("PrimaryConnection").ConnectionString
    Dim conn As New SqlConnection(connStr)
    Dim store As IDataStore = New MSSqlConnectionProvider(conn, AutoCreateOption.SchemaAlreadyExists)
    Return store
End Function

Private Function GetDataLayer() As IDataLayer
    Dim dict As New ReflectionDictionary()
    dict.CollectClassInfos(GetType(Person).Assembly)

    Const maxConnections As Integer = 3
    Dim stores(maxConnections - 1) As IDataStore
    For i As Integer = 0 To maxConnections - 1
        stores(i) = GetDataStore()
    Next i

    Return New ThreadSafeDataLayer(dict, New DataStoreFork(stores))
End Function

How many connections should one create for DataStoreFork? We dare say that the number of connections must not exceed the number of processors (CPUs) installed on a given server, where the database is running.

We also want you to be aware that the use of DataStoreFork may result in not any noticeable effect at all. Usually, the most common database queries in ASP.NET - SELECT and UPDATE - are processed with lightning speed, while data is transferred via fast LAN, to say nothing about situations where IIS and a database server are running on one and the same system. If users of your website experience performance issues, do not immediately introduce DataStoreFork, but execute thorough performance profiling to discover any bottlenecks.

The use of DataStoreFork may make sense in other multiuser environments with a shared XPO data layer besides ASP.NET, e.g. in Web services or in 3-tier applications with XPO on the middle layer.

 


ANN: DXCore XPO plugin 1.2.0.4

Version 1.2.0.4 of the XPO plugin is now available, compiled against DXCore 9.2.6. Please download it here:

CR_XPOFieldSync-1.2.0.4.zip (22563 bytes)

As always, if you're not familiar with the purpose of the XPO plugin, please read this description of the "Simplified Criteria Syntax" feature.

This version fixes two issues:

  • Changed undo unit handling for compatibility with DXCore 9.2.6 (fixes B140294)
  • Fixed an issue where an exclusion created through the "red cross" UI in the editor wasn't persisted correctly.
More Posts