Blog Home  Home Feed your aggregator (RSS 2.0)  
ObjectDataSource in Depth (Part 2) - Manuel Abadia's ASP.NET stuff
 
# Friday, February 17, 2006

I’m going to add paging support to the example I was using for the ObjectDataSource.

First we have to enable it in the GridView setting the AllowPaging property to "true". Also, set the PageSize to something low and insert more rows to the Products table.

Now we have to set the EnablePaging property in the ObjectDataSource to let it know that we want to support paging. If we have enabled paging, our select method will need to handle two additional parameters. Their names are configurable using the StartRowIndexParameterName and MaximumRowsParameterName properties. If we don’t set those properties the default values are used: startRowIndex and maximumRow. For the example I used startIndex and maxRows respectively.

Now we have to modify the select method to implement a paging mechanism. When the GridView needs data, it will call the ObjectDataSourceView’s select method with the index for the initial item it wants and the maximum number of items it wants (the initial values will depend on what you set on GridView’s PageIndex and PageSize properties).

There is a last thing we need to do to support paging properly. A pager needs to know how many pages it has to show. In order to calculate that, it needs to know the total number of entries. There’s a property called SelectCountMethod where we specify the name of a method that returns the total number of registers. I’ve seen a lot of people complaining about having another method to return that value instead of doing it in the SelectMethod because they have to do the same query twice. I don’t know why they choose to make two distinct methods but fortunately we can avoid executing the same query two times.

If you remember, in my previous post I told you that if the method to call was an instance method, ObjectDataSource creates an instance of our data mapper class, then calls the method and then disposes the instance. There’s an exception to this. If we have enabled paging and our SelectMethod and SelectCountMethod are instance methods, the same instance is used when calling both methods, so we can save the total number of registers in a field in the SelectMethod and then we can return it in the SelectCountMethod.

Before going to code, I’m going to explain the method I have used for paging in this example. It’s not the best one but works well for not very big tables and it’s easy to read. What I do is create a temporary table where I’ll assign a correlative number (starting at 0) to all my primary keys in the Products table. Then I’ll select only the rows I need from the temporary table joining it with the Products table to return all the information needed for the products I’m interested in, and also the total number of rows of the temporary table.

If you want to learn more about different paging methods and some performance information check out this cool article:

http://codeproject.com/aspnet/paginglarge.asp

The select methods look like this:

public List<Product> LoadAll(int startIndex, int maxRows)

{

    List<Product> products = new List<Product>();

 

    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

    string commandText = @"

-- create a temp table for paging

CREATE TABLE #PageIndexForTable

(

    IndexId int IDENTITY (0, 1) NOT NULL,

    id int   

)

 

-- insert into our temp table

INSERT INTO #PageIndexForTable

(

    [id]   

)

SELECT

    [id]   

FROM Products;

SET @totalRecords = @@ROWCOUNT

 

-- get the requested registers

SELECT

    src.[id],

    src.[name],

    src.[description],

    src.[price]   

FROM Products src, #PageIndexForTable p

WHERE 

    src.[id] = p.[id] AND

    p.IndexId >= @StartIndex AND p.IndexId < (@startIndex + @maxRows)";

 

    SqlCommand command = new SqlCommand(commandText, conn);

    command.Parameters.Add(new SqlParameter("@startIndex", startIndex));

    command.Parameters.Add(new SqlParameter("@maxRows", maxRows));

    command.Parameters.Add(new SqlParameter("@totalRecords", SqlDbType.Int));

    command.Parameters["@totalRecords"].Direction = ParameterDirection.Output;

 

    conn.Open();

    SqlDataReader dr = command.ExecuteReader();

    while (dr.Read()) {

        Product prod = new Product();

 

        prod.Id = (int)dr["id"];

        prod.Name = (string)dr["name"];

        prod.Description = (string)dr["description"];

        prod.Price = (decimal)dr["price"];

 

        products.Add(prod);

    }

 

    dr.Close();

    conn.Close();

 

    _count = (int)command.Parameters["@totalRecords"].Value;

 

    return products;

}

 

public int CountAll()

{

    return _count;

}

 

To add sorting the only thing we have to do is to configure the GridView’s AllowSorting property to true and set the ObjectDataSource’s SortParameterName to sortedBy. Now the select method needs another parameter called sortedBy that will contain the sorting order. In the source code you can see the source code for the LoadAll method with sorting.

If our select method returns a DataView, DataTable or DataSet and we want to use the sorting capabilities of the DataView, we don’t have to specify the SortParameterName because the SortExpression passed to the Select method in the DataSourceSelectArguments is used to set the sort property on the DataView that is finally used. If we set the SortParameterName in this case, the sorting will not work properly (the control will set the SortExpression to the empty string) because it thinks that we're doing our own sorting.

If our select method returns a DataTable or DataSet, we can also enable filtering. If our select method returns a DataView filtering doesn’t work (probably a bug). To use filtering we have to set ObjectDataSource’s FilterExpression property to a string with placeholders for parameters (for example Name LIKE {0}). Each placeholder in the FilterExpression will be substituted for the corresponding filter parameter from the FilterParameters collection ({0} for FilterParameters[0], {1} for FilterParameters[1], etc).

Now that parameters have appeared, let’s talk a little bit about them. For each CRUD method you have an associated parameter collection. Insert, Update and Delete methods are passed one or more dictionaries with key/value pairs. Each CRUD method uses the associated parameter collection and the associated dictionaries to obtain a final dictionary of key/value pairs that will correspond to the parameters and values passed to its associated method (the parameters are case insensitive). This merging strategy is specific for each method and will be covered later. When two dictionaries are merged, one acts as the source and other as the destination. The source entries will be copied to the destination, but if there is a parameter in the associated ParameterCollection for the operation we’re performing with the same name as the current entry in the source dictionary, the value copied to the destination is the result of evaluating the current value of that parameter. I hope that makes sense to you!

As the merging strategies differ if optimistic concurrency is enabled, I’ll not explain the different merging strategies in this post (but I’ll do it in the next one about ObjectDataSource).

The last thing you should know about ObjectDataSource’s parameters is that SelectParameters and FilterParameters are persisted in the View State and re-evaluated on the LoadComplete stage. Any change on them at any time will trigger a DataSourceViewChanged event since the control captures the ParametersChanged events for both collections.

Now I’m going to talk about the events. The ObjectDataSource control has events for data mapper’s object creation and disposal and for CRUD events.

The creation and disposal events were introduced in Part 1 so the only thing I’ll tell you about them is that if our data mapper object implements IDisposable, after the ObjectDisposing event, the Dispose method will be called (if we don’t set the argument’s Cancel property to true).

Each CRUD method has two related events, one that fires before the call to the CRUD operation and the other after the CRUD method has been called. All events firing before the operation have an argument where you can set the Cancel property to true in order to stop processing. The Select operation is a special case as it can fire up to 8 events (3 of them are creation/disposal events). First the Selecting event is fired, and if the user doesn’t cancel the select operation, then the control calls our SelectMethod and fires the Selected event. After that, if Paging is enabled and a SelectCountMethod is defined, the controls fires the Selecting event again, then calls our SelectCountMethod and then fires the Selected event (if we don’t set Cancel to true in the Selecting event). If we have filtering enabled, the Filtering event will be fired before the filtering takes place.
If we’re handling the Selecting or Selected events we can use the ExecutingSelectCount property from the ObjectDataSourceSelectingEventArgs class to know when we’re being called.

For each “before” event there’s a property called InputParameters that contains a dictionary with the parameters that will be passed to the CRUD method. We can change any value there. However, we can add or remove items only if we're passing simple type parameters to our data mapper.

In the “after” event, we can get the return value for the called method using the ReturnValue and there’s also a property called OutputParameters that is a dictionary containing all the parameters from our data mappers method that were marked as out or ref.

In the example project I’ve captured most of the control’s events in order to show the data passed to the handler.

As you can see, the ObjectDataSource is a little beast. The last part in this ObjectDataSource tutorial will be available next week and I’ll discuss optimistic concurrency, parameter merging and Attributes for the ObjectDataSource.

Update:
You can access to the other parts here:

ObjectDataSource in Depth (Part 1)
ObjectDataSource in Depth (Part 3)
ObjectDataSource Limitations, Problems and Possible Solutions

Example source code: ObjectDataSourcePart2.zip (170,73 KB)
Copyright © 2014 Manuel Abadia. All rights reserved.
DasBlog 'Portal' theme by Johnny Hughes.