Blog Home  Home Feed your aggregator (RSS 2.0)  
ObjectDataSource in Depth (Part 2) - Manuel Abadia's ASP.NET stuff
 
# Friday, 17 February 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)
Wednesday, 29 March 2006 15:19:35 (Romance Daylight Time, UTC+02:00)
Here a new Paging function for the new SQL server 2005 or Express to use for maximum results!


create PROCEDURE [dbo].[GetPagedLists]
@Category NVarChar(25),
@StartRowIndex INT,
@NumRows INT
AS

BEGIN

WITH ListEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY ListID ASC)AS Row, ListID, [Name], Category FROM Lists where Category=@Category)

SELECT ListID, [Name], Category
FROM ListEntries
WHERE Row between @StartRowIndex and @StartRowIndex+@NumRows

END
Thursday, 17 August 2006 23:45:36 (Romance Daylight Time, UTC+02:00)
I have written few articles on the problems I have encountered when working with ObjectDataSource.
You can find them on my blog:
http://vaultofthoughts.net/ProblemsWithObjectDataSource.aspx
I have also created a working solution which you can also find on my blog along with some other control:
http://vaultofthoughts.net/ASPNETControlsPack.aspx
Wednesday, 06 September 2006 20:43:50 (Romance Daylight Time, UTC+02:00)
Hi,

Your articles helped me a lot in understanding the ODS. I like the way you wrote these articles. However, I've got a question about the ObjectDataSource and I hope you, or one of the readers here, can answer this question (although I know this is not a newsgroup).

Our BLL (business-Logic-Layer) doesn't fit on the ODS. You described a way to put objects in a cache, this works fine for retreiving the data but we want to go further. We need the ODS to fill the property's from an existing instance after using the update button. We use ODS on top of a GridView, after pressing the 'Update' button on the GridView, ODS creates a new instance of a specified ClassType and set all the properties to the changed values. I want ODS to use an exisiting instance from the cache and start filling the properties of this instance with the new values. The reason that we want to use these 'expensive' objects is that there are lot's of business rules that has te be validated in the 'update' method. These rules require child-objects and those are allready fetched from the database and we don't want any extra roundtrips to the DB.

Do you have any idear on how to give an existing instance to the ODS in stead of letting the ODS create a brand new one after pressing update?

Kind regards,

J. v/d Hoeven (Netherlands)

Van den Hoeven
Wednesday, 06 September 2006 21:30:21 (Romance Daylight Time, UTC+02:00)
J. v/d Hoeven,

I'm glad you liked my article.

About your question, unfortunately the ODS does not provide any way to do that. I created a replacement for the ODS with more features that does have this functionality. Check this online demo:

http://www.manuelabadia.com/livedemo/extendedobjectdatasource/DataObjectTypeControl.aspx

I think it does what you are looking for.

Regards,
Manuel Abadia
Wednesday, 18 March 2009 10:43:19 (Romance Standard Time, UTC+01:00)
This series of articles are just best amongst all...............................................cheers
Joydeep Sen
All comments require the approval of the site owner before being displayed.
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

[Captcha]Enter the code shown (prevents robots):

Live Comment Preview
Copyright © 2018 Manuel Abadia. All rights reserved.
DasBlog 'Portal' theme by Johnny Hughes.