Developer Express' Persistent Classes wizard does not show views. It'd be cool if we could add views and table-valued functions. However, you can manually define a persistent class based on a view and XPO, the XpoDataSource, and controls like the ASPxGridView can use the view-based persistent class to display your data. There is a small quirk: if the view does not contain a single primary key then running the XpoDataSource in server mode--one of our coolest performance features--doesn't work correctly. (This is a noted limitation and there is a reason for it.) Before I show you how to create persistent class based on a view with and without a primary key (and toss in a couple of neat tricks you might like) let's look at the aforementioned limitation.
Figure 1: Look ma no views or table-valued functions!
Why XPO Needs Primary Keys
The XpoDataSource has a property named ServerMode. ServerMode is important because when the XpoDataSource is in ServerMode it pushes user requests to the back-end database server instead of the DataController. The DataController runs on the web server and generally the SQL Server has (or can have) its own box. Why does this matter? Well, manipulating a huge DataSet like a sort operation or custom objects on the Web server can slow the Web server down. On the other hand SQL Server boxes tend to be beefy and are much better at doing things like sorting and grouping when compared to a separate DataController. Suppose for example you have the ASPxGridView with a SqlDataSource. You click a column header to sort. The sort has to sort all of the data in the result set regardless of whether it is all showed in the grid or not to make the sort meaningful. For the sort to work then the data needs to be in memory and sorted by some code in the .NET framework. Now suppose you have the ASPxGridView with the XpoDataSource with ServerMode = true. When you click on the column header the data is sorted by sending a query with and order by to the database; the work is offload to the database server alleviating pain on the web server and improving performance without loading all data in memory.
The reason a primary key is needed is to support paging. With paging you have an implicit index into the result set based on the page. So when the XpoDataSource is in ServerMode the page and number of items per page act as a collection of indexes for the resultset and XPO has to send unique key references and sort order information to the SQL Server to get the right result set. Without uniqueness duplicate item resolution is impossible. The first listing (Listing 1) is one query sent when XPO is requesting data based on the Northwind.Products table with a sort order indicated. Listing 2 shows how the query is built when the ASPxGridView uses paging in conjunction with a specified sort order.
Listing 1: The XpoDataSource in ServerMode with a sort specified (from an ASPxGridView).
select N0."ProductID" from "dbo"."Current Product List" N0
order by N0."ProductID" asc
Listing 2: XpoDataSource in ServerMode sends a query to reflect the items needed relative to the page index, off loading data intensive work to the database.
exec sp_executesql N'select N0."ProductID",N0."ProductName" from "dbo"."Current Product List" N0
where N0."ProductID" in
(@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,
@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22,@p23,@p24,@p25,@p26,@p27,
@p28,@p29,@p30,@p31,@p32,@p33,@p34,@p35,@p36,@p37,@p38,@p39,@p40,
@p41,@p42,@p43,@p44,@p45,@p46,@p47,@p48,@p49,@p50,@p51,@p52,@p53,
@p54,@p55,@p56,@p57,@p58,@p59,@p60,@p61,@p62,@p63,@p64,@p65,@p66,@p67,@p68,@p69)',N'@p0
int,@p1 int,@p2 int,@p3 int,@p4 int,@p5 int,@p6 int,@p7 int,@p8 int,@p9 int,@p10 int,@p11 int,@p12 int,@p13 int,@p14 int,@p15 int,@p16
int,@p17 int,@p18 int,@p19 int,@p20 int,@p21 int,@p22 int,@p23 int,@p24 int,@p25 int,@p26 int,@p27 int,@p28 int,@p29 int,@p30 int,@p31
int,@p32 int,@p33 int,@p34 int,@p35 int,@p36 int,@p37 int,@p38 int,@p39 int,@p40 int,@p41 int,@p42 int,@p43 int,@p44 int,@p45 int,@p46
int,@p47 int,@p48 int,@p49 int,@p50 int,@p51 int,@p52 int,@p53 int,@p54 int,@p55 int,@p56 int,@p57 int,@p58 int,@p59 int,@p60 int,@p61
int,@p62 int,@p63 int,@p64 int,@p65 int,@p66 int,@p67 int,@p68 int,@p69
int',@p0=25,@p1=26,@p2=27,@p3=30,@p4=31,@p5=32,@p6=33,@p7=34,@p8=35,
@p9=36,@p10=37,@p11=38,@p12=39,@p13=40,@p14=41,@p15=43,
@p16=44,@p17=45,@p18=46,@p19=47,@p20=48,@p21=49,@p22=50,@p23=51,
@p24=52,@p25=54,@p26=55,@p27=56,@p28=57,@p29=58,@p30=59,@p31=60,
@p32=61,@p33=62,@p34=63,@p35=64,@p36=65,@p37=66,@p38=67,@p39=68,
@p40=69,@p41=70,@p42=71,@p43=72,@p44=73,@p45=74,@p46=75,@p47=76,
@p48=77,@p49=78,@p50=23,@p51=22,@p52=21,@p53=20,@p54=19,@p55=18,
@p56=16,@p57=15,@p58=14,@p59=13,@p60=12,@p61=11,@p62=10,@p63=8,@p64=7,@p65=6,@p66=4,@p67=3,@p68=2,@p69=1
Using Persistent Classes with a View with a Primary Key
If you want to use a view and it has a primary key then you can roll your own persistent class. The steps are as follows:
- Add an ASPxGridView to a Web page
- Add an XpoDataSource to a Web page and set ServerMode = true
- Add a class (see Listing 3) to your web project
- Add the startup code to a Global.asax file (which means you need to add a Global.asax file too)(see Listing 4)
- Add the Page_Init code to the default.aspx page (the one containing the ASPxGridView)
- Set the TypeName of the XpoDataSource to the name of your persistent class
- Run the demo
Listing 3: Define a class that inherits from XPLiteObject that represents your underlying view.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DevExpress.Xpo;
[Persistent("Current Product List")]
public class CurrentProductList : XPLiteObject
{
[Key, Persistent("ProductID")]
public int ProductID;
[Persistent("ProductName")]
public string ProductName;
public CurrentProductList(Session session) : base(session) { }
public CurrentProductList() : base(Session.DefaultSession) { }
public override void AfterConstruction() { base.AfterConstruction(); }
}
The class defines ProductID as the key and a persistent column. ProductName is a persistent column, and the PersistentAttribute on the class names the underlying view.
Listing 4: Write the plumbing for the Xpo Persistent Class.
<%@ Application Language="C#" %>
<script runat="server">
void Application_Start(object sender, EventArgs e)
{
string connectionString =
DevExpress.Xpo.DB.MSSqlConnectionProvider
.GetConnectionString(@".\SQLExpress",
"northwind");
DevExpress.Xpo.Metadata.XPDictionary dictionary =
new DevExpress.Xpo.Metadata.ReflectionDictionary();
DevExpress.Xpo.DB.IDataStore store =
DevExpress.Xpo.XpoDefault.GetConnectionProvider(connectionString,
DevExpress.Xpo.DB.AutoCreateOption.SchemaAlreadyExists);
dictionary.GetDataStoreSchema(typeof(CurrentProductList).Assembly);
DevExpress.Xpo.XpoDefault.DataLayer = new DevExpress.Xpo.ThreadSafeDataLayer(dictionary, store);
DevExpress.Xpo.XpoDefault.Session = null;
}
void Application_End(object sender, EventArgs e)
{
// Code that runs on application shutdown
}
void Application_Error(object sender, EventArgs e)
{
// Code that runs when an unhandled error occurs
}
void Session_Start(object sender, EventArgs e)
{
// Code that runs when a new session is started
}
void Session_End(object sender, EventArgs e)
{
// Code that runs when a session ends.
// Note: The Session_End event is raised only when the sessionstate mode
// is set to InProc in the Web.config file. If session mode is set to StateServer
// or SQLServer, the event is not raised.
}
</script>
The XPO wire-up code is pretty boilerplate. Copy and paste this code to your toolbox or better yet create a CodeRush template for it. You can also copy the Page_Init to your toolbox (maybe create an XPO tab) or again, a CodeRush template. Listing 6 and Figure 2 show the Page_Init template and the CodeRush Options dialog, respectively. (pin<space> activates the template as defined.)
Listing 5: Associate a Session object with the XpoDataSource in the Page_Init method.
protected void Page_Init(object sender, EventArgs e)
{
XpoDataSource1.Session = new DevExpress.Xpo.Session();
}
Figure 2: The CodeRush Options dialog shows the template definition information the Page_Init session wire-up.
Watching XPO in Action
If you just think of XPO as another simple competitor in the Data Access Layer well you (are making the same mistake I did originally and) are missing two powerful features in addition to persistent class generation. XPO can generate the database from the classes as well as classes from the database, and XPO uses a server mode which takes advantage of the power of your database server by pushing client queries back to the database server using Ajax. This means the Web server gets a break and the optimized-for-the-job SQL server is pitching where it helps. Its cool to watch this back-end action.
You can see XPO doing its thing by opening the SQL Profiler and selecting File|New Trace. Run the demo application and you will see the queries. Use grouping, sorting or paging and you will see that the data is actually being fetched at the database server level. Figure 3 shows the SQL Profiler responding to a group operation on the client.
Figure 3: Drag a column to the group panel and with the magic of Ajax and the XpoDataSource ServerMode the grid is reconfigured and the data is grouped.
Using XPO with Views without Primary Key Fields
You can also use the XpoDataSource with views that don't have a unique key. Essentially what you have to do is construct a unique key out of other columns. Do this by defining an additional struct with the persistent fields and then define a variable of the struct type in the persistent class. Sorting won't work in ServerMode for the aforementioned reason but you will be able to see your view data in the ASPxGridView. If you want sorting then set the XpoDataSource.ServerMode=false. The necessary revision to the code is shown in Listing 6 and the output is shown in Figure 4.
Listing 6: The revised code; use this approach if you have a view without a unique key in the resultset.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DevExpress.Xpo;
public struct CurrentProductListKey
{
[Persistent("ProductID")]
public int ProductID;
[Persistent("ProductName")]
public string ProductName;
}
[Persistent("Current Product List")]
public class CurrentProductList : XPLiteObject
{
[Key, Persistent]
private CurrentProductListKey Key;
public int ProductID { get { return Key.ProductID; } }
public string ProductName { get { return Key.ProductName; } }
public CurrentProductList(Session session) : base(session) { }
public CurrentProductList() : base(Session.DefaultSession) { }
public override void AfterConstruction() { base.AfterConstruction(); }
}
Figure 4: The output from the Current Product List view.
That's it. Be sure to check out Developer Express' booth at TechEd in Los Angeles, and if you want some demos on our controls on LA drop me a line, I will try to whip some up for you.