The holiday season is a time for rich food, the warmth of family and friends, and festive fun and good times. And, if you are like me, you were able to squeeze in some computer time when the kiddies were playing their XBoxes or watching their new DVDs in between sledding and ice skating.
Of course, if you are going to play with technology during the holidays, you might as well play with fun technology. When I wasn’t fighting the Lich King, I was working with Devexpress controls for Windows and the web. The Windows controls are part of an application I am helping a friend with, and the web controls because Devexpress put out a new release 8.3. Naturally, I wanted to take Devexpress’ new controls for a spin.
One of the most popular web controls is Devexpress’ ASPxGridView. To help you explore, I have included several graphics and some code that show you how to use the new filter feature, grid sorting, and grouping, and how to use the XPO Persistent Classes to help you generate your custom entity classes.
The article is a little on the long side, so if you want to go refill your eggnog from whatever’s left over, I’ll wait. Already back, I see. Good.
Binding Persistent Classes to an ASPxGridView
An entity class is a class that represents a database table, generally. It’s not worth being dogmatic about where you get your entity classes. Sometimes, you might roll your own, you might use something like Microsoft’s LINQ to SQL, or you can use Devexpress’ XPO Persistent Classes. The Express Persistent Classes (XPO) are designed to work with Devexpress’ XpoDataSource and controls; if you are creating an application that leverages Devexpress’ professional looking controls, the XPO Persistent Classes may be the way to go.
Start by creating a new web site project with Visual Studio 2008. To that project, you need to add an XPO class. XPO classes are code-generated in Visual Studio by selecting Project|Add New Item|Persistent Classes 8.3. The wizard will display a dialog labeled “Generating Persistent Classes for an Existing Database” (see Figure 1). The first step lets you pick the provider and the database connection. After you pick the database, click Next and select the table and the columns (see Figure 2). After you click Finish, the Persistent Classes will code-generate entity classes based on your selections. For the demonstration, select the Northwind Traders database and the Products table.
Figure 1: The XPO Persistent Classes item starts a wizard that will code generate entity classes based on your selections.
Figure 2: For the demo, pick the Northwind Traders Products table and all of the columns.
After you click Finish, the Devexpress’ XPO technology will generate the classes and properties that represent the tables and columns you selected. Each source file will have a namespace that reflects the database and a class that reflects the tables.
Listing 1: The code generated from the XPO Persistent Classes item installed when you install Devexpress controls.
Imports System Imports DevExpress.Xpo Namespace northwind Public Class Products Inherits XPLiteObject Dim fProductID As Integer <Key(true)> _ Public Property ProductID() As Integer Get Return fProductID End Get Set(ByVal value As Integer) SetPropertyValue(Of Integer)("ProductID", _ fProductID, value) End Set End Property Dim fProductName As String <Size(40)> _ Public Property ProductName() As String Get Return fProductName End Get Set(ByVal value As String) SetPropertyValue(Of String)("ProductName", fProductName, value) End Set End Property Dim fSupplierID As Integer Public Property SupplierID() As Integer Get Return fSupplierID End Get Set(ByVal value As Integer) SetPropertyValue(Of Integer)("SupplierID", fSupplierID, value) End Set End Property Dim fCategoryID As Integer Public Property CategoryID() As Integer Get Return fCategoryID End Get Set(ByVal value As Integer) SetPropertyValue(Of Integer)("CategoryID", fCategoryID, value) End Set End Property Dim fQuantityPerUnit As String <Size(20)> _ Public Property QuantityPerUnit() As String Get Return fQuantityPerUnit End Get Set(ByVal value As String) SetPropertyValue(Of String)("QuantityPerUnit", fQuantityPerUnit, value) End Set End Property Dim fUnitPrice As Decimal Public Property UnitPrice() As Decimal Get Return fUnitPrice End Get Set(ByVal value As Decimal) SetPropertyValue(Of Decimal)("UnitPrice", fUnitPrice, value) End Set End Property Dim fUnitsInStock As Short Public Property UnitsInStock() As Short Get Return fUnitsInStock End Get Set(ByVal value As Short) SetPropertyValue(Of Short)("UnitsInStock", fUnitsInStock, value) End Set End Property Dim fUnitsOnOrder As Short Public Property UnitsOnOrder() As Short Get Return fUnitsOnOrder End Get Set(ByVal value As Short) SetPropertyValue(Of Short)("UnitsOnOrder", fUnitsOnOrder, value) End Set End Property Dim fReorderLevel As Short Public Property ReorderLevel() As Short Get Return fReorderLevel End Get Set(ByVal value As Short) SetPropertyValue(Of Short)("ReorderLevel", fReorderLevel, value) End Set End Property Dim fDiscontinued As Boolean Public Property Discontinued() As Boolean Get Return fDiscontinued End Get Set(ByVal value As Boolean) SetPropertyValue(Of Boolean)("Discontinued", fDiscontinued, value) End Set End Property Public Sub New(ByVal session As Session) MyBase.New(session) End Sub Public Sub New() MyBase.New(Session.DefaultSession) End Sub Public Overrides Sub AfterConstruction() MyBase.AfterConstruction() End Sub End Class End Namespace
The generated code is straightforward. The class represents the table, and the properties represent the table columns. The setters have a call to a generic SetPropertyValue method that, if I had to guess, I would say it contains a unified dirty-state tracking mechanism. A quick scan of Reflector says it looks like SetPropertyValue checks to see whether the new value is identical to the old value and raises some events and updates the Session’s version of the property state.
Adding a Global Application Class
You need very little code to get data from your database into an ASPXGridView with XPO. Add a Global Application Class—global.asax—file to your web site. Select Website|Add New Item. From the Templates dialog, select the Global Application Class template. Click OK. This will add a global.asax file to your web site project (see Figure 3). After you add the needed code to the global.asax file, it will look like the code in Listing 2.
Figure 3: Add a global.asax file to your web site; this will let you add some code for application level events.
Listing 2: Startup code that establishes plumbing for using the XPO Data Source and generated code.
Imports System.Web.SessionState PublicClass Global_asax Inherits System.Web.HttpApplication Sub Application_Start(ByVal sender As Object, _ ByVal e As EventArgs) Dim connectionString As String = _ DevExpress.Xpo.DB.MSSqlConnectionProvider. _ GetConnectionString( _ "casparSQLExpress", "northwind") Dim dictionary As DevExpress.Xpo.Metadata.XPDictionary = _ New DevExpress.Xpo.Metadata.ReflectionDictionary() Dim store As DevExpress.Xpo.DB.IDataStore = _ DevExpress.Xpo.XpoDefault. _ GetConnectionProvider(connectionString, _ DevExpress.Xpo.DB.AutoCreateOption.SchemaAlreadyExists) dictionary.GetDataStoreSchema(GetType _ (northwind.Products).Assembly) DevExpress.Xpo.XpoDefault.DataLayer = _ New DevExpress.Xpo. _ ThreadSafeDataLayer(dictionary, store) DevExpress.Xpo.XpoDefault.Session = Nothing End Sub Sub Session_Start(ByVal sender As Object, _ ByVal e As EventArgs) ' Fires when the session is started End Sub Sub Application_BeginRequest(ByVal sender As Object, _ ByVal e As EventArgs) ' Fires at the beginning of each request End Sub Sub Application_AuthenticateRequest(ByVal sender As Object, _ ByVal e As EventArgs) ' Fires upon attempting to authenticate the use End Sub Sub Application_Error(ByVal sender As Object, _ ByVal e As EventArgs) ' Fires when an error occurs End Sub Sub Session_End(ByVal sender As Object, ByVal e As EventArgs) ' Fires when the session ends End Sub Sub Application_End(ByVal sender As Object, ByVal e As EventArgs) ' Fires when the application ends End Sub End Class
The code in Listing 2 is boilerplate code that I got from tv.devexpress.com. The code contains helper methods for obtaining connection strings dynamically and creating a dictionary and datastore. You can copy the code in the Application_Start event and substitute the arguments to GetConnectionString for your database server and your database’s name. You also need to provide the namespace and class name for the GetType argument of GetDataStoreSchema. This is the namespace and name of the generated XPO persistent classes. In the example, this is northwind.Products.
Using the ASPxGridView
You have written all the code you need for the demonstration—except one line of code. (After you get the grid view and XpoDataSource on the default.aspax page, you’ll add the line of code.)
Next, open the design mode of the Default.aspx page. Drop an ASPxGridView control from the Toolbox window’s DX.8.3:Data tab. Follow these steps to configure the ASPxGridView:
- After dropping the ASPxGridView control on the default.aspx page, use the same Toolbox tab (DX.8.3: Data) and drop an XpoDataSource on the default.aspx page.
- Using Figure 4 as a visual guide, configure the XpoDataSource by setting the ServerMode property to True and the TypeName to the name of the generated persistent class (which is northwind.Products on my PC and may include the project’s namespace first).
- Next, select the ASPxGridView and set the DataSourceID property to be the XpoDataSource (in this example, the DataSourceID will be XpoDataSource1; see Figure 5).
- The final step is to add a Page_Init method that establishes session information for the XpoDataSource (see Listing 3.)
Figure 4: Configure the XpoDataSource to include ServerMode and the complete name of the persistent class generated earlier.
Figure 5: Indicate that the grid is getting its data from the XpoDataSource by setting the DataSourceID property as shown.
Listing 3: Add this code to the default.aspx.vb source file.
Protected Sub Page_Init(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Init XpoDataSource1.Session = New DevExpress.Xpo.Session End Sub
Page_Init has the same signature as the Page_Load, so you can copy and paste Page_Load and change the subroutine name and the handles clause to indicate that this subroutine handles the Page_Init behavior. This very simple statement establishes session storage for the XpoDataSource. Run the solution and you should see a web page that looks similar to the result shown in Figure 6.
Figure 6: About eight lines of code and a few minutes of drag and drop practice and you can get a result similar to that shown using the ASPxGridView and XpoDataSource from Developer Express.
The XpoDataSource ServerMode property pushes managing how much data is returned to the client back to the server. As a result, if you returned a table that contains thousands of rows, only those displayed in the grid at a given time will be returned to the client. ServerMode means that managing how and when subsets of data are retrieved from the database is managed by XPO, so you and I don’t have to write that code.
Sorting the ASPxGridView at Runtime
The ASPxGridView supports sorting by default, and sorting is enabled by default. All the user has to do to use the sort behavior is click a column header. Clicking a column header will cause the data to be sorted on that column. Click the column a subsequent time and the sort order will be reversed. Hold the Shift key and click another column, and you will create a secondary sort on that column. A small icon on the right-hand side of the column header will indicate which column is being sorted and the triangular icon indicates the direction. (The triangle pointing up in Figure 6 in the Product Name column indicates that the grid is sorted on the Product Name column in ascending order.)
Implementing a Custom Sort
The default sort order uses comparisons based on the data type. For example, sorting on a string column will sort on the spelling of the text in the columns. If you wanted to deviate from the default sorting behavior, you can implement custom sorting for individual columns.
Custom sorting works by setting some properties, implementing a CustomColumnSort event handler, checking the field name, and then manually defining the sort. (Borrowed from one of the many great videos on tv.devexpress.com) the following steps set up sorting the Product Name column by length instead of spelling—shorter words appear earlier and longer words appear later.
- Open the Default.aspx page in the designer and click the Tasks button (on the upper right-hand side of the ASPxGridView).
- Select the Columns option to open the Columns Editor Form.
- Expand the Settings property and set the SortMode sub-property to Custom, and change the SortMode to 0. (Use Figure 7 as a visual guide).
Figure 7: Change the Settings.SortMode to Custom and the SortIndex to 0 to get the ASPxGridView to call the CustomerColumnSort event.
Unfortunately, the code in Listing 4 won’t get called with an XpoDataSource.ServerMode set to True. You will have to make a choice: advanced server management of dataset sizes or custom sorting.
Listing 4: Turn XpoDataSource.ServerMode to False and add the following code to sort the Product Name column by length and spelling.
Protected Sub ASPxGridView1_CustomColumnSort( _ ByVal sender As Object, _ ByVal e As DevExpress.Web.ASPxGridView. _ CustomColumnSortEventArgs) _ Handles ASPxGridView1.CustomColumnSort ' Sort by length first, spelling second If (e.Column.FieldName = "ProductName") Then e.Handled = True Dim s1 As String = e.Value1 Dim s2 As String = e.Value2 If (s1.Length > s2.Length) Then e.Result = 1 ElseIf (s1.Length = s2.Length) Then e.Result = String.Compare(s1, s2) Else e.Result = -1 End If End If End Sub
Can you spot what is missing in the code? If you guessed that the sort in Listing 4 will always sort in ascending-length order, you are right. Checking the event argument’s e.SortOrder property and figuring out how to sort in ascending and descending order is left to the reader as an exercise.
Another great feature of the ASPxGridView from Devexpress is the ability to group data using a drag and drop feature. Enable grouping and all the user has to do at runtime is drag a column header to the group panel and the ASPxGridView is re-arranged by that group. Drag a second column header to the group panel and a secondary grouping will be created. To enable grouping, click the ASPxGridView’s Task button and check the Show Group Panel checkbox.
Run the application and you will now see the group panel with the hint about dragging a column header (see Figure 8 to see me dragging the Product Name column header) and Figure 9 shows how the grid data is re-structured to show products grouped by Product Name.
Figure 8: This figure is intended to show the column header in motion being dragged to the group panel.
Figure 9: Drop the column header in the group panel and the grid is sorted.
You can drag multiple column headers to the group panel to create sub-groups. Drag the column headers from the group panel back to the grid to un-group, and you can sort by clicking on column headers in the group panel. (That might take you a while to implement from scratch.)
Using the New ASPxGridView Filter Feature
Another cool feature is the new filter feature. The new filter feature shows up as a link at the bottom of the grid when enabled and pops up a dialog that lets you pick options based on the intuitive operators, field names from the data’s schema, and even shows the “correct” user control based on the field’s data type. To enable the Create Filter link shown in Figure 10, select the ASPxGridView at design time, show the Properties window, expand the Settings property, and set Show Filter Bar to True.
Figure 10: The new filter feature shows up as a link at the bottom of the grid; click the link to display a user-friendly modal dialog.
To change the filter, click on the Create Filter link (see Figure 11). Click the And link to change the operator between clauses. Click the (+) button to add filter options. When you add a filter, the first link is a field, the second is an operator, the last link becomes a dynamic control that facilitates input for the field’s data type. To remove a clause, click the cancel button (x) to remove any filter clause. Figure 11 shows a filter on the Discontinued column.
Figure 11: A single filter on the Discontinued column; the result set will show only discontinued items.
After you create a filter, the text of the filter will appear in the link at the bottom of the grid. The link will include a preceding checkbox. Uncheck the checkbox to immediately disable and the filter and re-check to the checkbox to re-enable the filter. Clicking the filter will re-open the filter builder dialog.
You probably won’t be surprised that the ASPxGridView supports a lot of other features, such as grid nesting. A lot of time and energy has gone into making the ASPxGirdView pop with features. (There are enough features in this one control to support a couple chapters in a book; someone ought to tackle that.)
I delivered the bits and technical information to you straight, but I think you will enjoy playing with the grid and adopting it for your web solutions enough that you won’t have missed some of my usual banter. If you really want to have fun, see if you can make your own grid control do just one of sorting, grouping, or filtering. If you want to get work done, the ASPxGridView from Devexpress is for you.
About the Author
Paul Kimmel is a freelance writer for Developer.com and CodeGuru.com. He is the founder of Software Conceptions, Inc, found in 1990. Paul Kimmel is architect for EDS, an HP Company. You may contact him about article questions at [email protected].
Check out Paul’s most recent books, LINQ Unleashed and Teach Yourself the ADO.NET Entity Framework in 24 Hours (coming Spring 2009).
Copyright © 2008 by Paul T. Kimmel. All Rights Reserved.