Extended datasource APIs with the ASPxGridView

Julian,

I'm using ASPXGridView in a project and let me say that I've "wrestled"  with this issue a lot.

I believe that the problem stems from the way we look at the grid. If you consider the ASPXGridView primarly as a tool for reporting scenarios your concerns about groups, summaries and filters "might" be valid but in many cases I use the grid to display records and then "act" on them (CRUD and other "operational" use cases). In those scenarios I don't need, nor want, to use groups and filters but only the ability to sort and page.

So from my point of view ASPXGridView needs a "ligthweight" "modus operandi". This is my primary concern about this, otherwise great, component.

I hope to have been able to explain my points.

Sincerly,

Massimo

I agree with Massimo. As a developer, I would rather always start with a basic 'lightweight' grid (menu, treelist, navbar or whatever) and gradually enable features as I need them. I find it frustrating to have to disable multiple features in order to end up with a basic grid. It makes more sense to me to have to enable (or opt in to) the more advanced features in order to use them, rather than turn them off because they are not needed. Think Windows Vista security - completely tied down by default (in theory anyway), and it's up to the administrator to grant access to resources as required.

That goes for grid databinding too - I need paging and sorting far more frequently than I need filtering, summaries, and grouping in particular. If I enable an advanced feature, and I need to do a bit more coding to get it to work then so be it, but please don't ask me to add extra markup or code to make the grid less complicated!

Give me a plain cheese and tomato pizza - if I want extra toppings I'm quite happy to take the time to add them.

Regardless, the ASPxGridView is an outstanding grid control and I wouldn't swap it for any other.

Many thanks,

Ben S

"Of vital importance with this paging is that it still works well when the user is grouping records. By that I mean that if the user groups on City, say, and the grid displays a page of cities, all collapsed, then the paging subsystem only returns those city records, and not all the detail records making up the groups. Expand a city, and the paging subsystem returns the records needed to expand that group and fill the grid."

I think one major reason it doesn't work properly is that there are different ways to look at how paging and grouping should work together: is paging applied BEFORE the grouping or AFTER the grouping? If paging is applied BEFORE the grouping, the grid groups the data that's visible in the grid and nothing further. If the grouping is applied AFTER grouping, the grouping has to be part of the query. There's no other way around this.

The thing is that if you have 100K rows or perhaps a couple of million rows, and you want to manipulate a couple of rows, using a screen which has your grid on it, paging is VITAL. No paging mechanism means NO performance at all: you definitely don't want to pull 1million rows into the grid. (we have customers who work with databases which get 10million new rows a DAY in some tables, you can't possibly ask these people to put paging on a lower priority than grouping).

So back to grouping and the query at hand. When paging is necessary (read: required, otherwise nothing works, see my example above) and grouping is something which should be done, because the user dragged 'city' to the grouping bar, it's essential that the grouping is part of the query, as the PAGING is applied AT the query. Only in that situation it's possible to obtain a page from the resultset from the db server.

But... grouping how the grids want to do this (in hierarchical sets) CANT be part of the query. The point is that grouping as it is used in a grid, is a hierarchical projection, like Linq grouping is done: for every key (the grouped element, e.g. city) a list of elements is kept. However, SQL produces a flat resultset.

I'm not sure if XPO's linq provider can do this, but if you do:

var q = from c in ctx.Customers

          group c by c.Country into g

          select g;

you get all customers grouped by country. However this is a hierarchical fetch: first the keys (country, using a groupby) then the customers, and they're merged together by the engine (at least, that's the most efficient way). There's NO WAY you can get this in 1 query, unless you make assumptions how a group by is executed on the server.

Now, for your grid, how to solve this? First it has to be made clear what the page numbers mean. If I have 100,000 customers in a table and I group them on City, and there are 1000 different cities,all with on average 100 customers in them, and I show 10 rows per page, is the paging per set of 10 cities? Or is a group header also a row, or is the # of customers on the page shown  always 10 and it could be that there are 3 group headers, or probably 1 or 0 ?

Personally I find that a question which is already impossible to answer in 1 way. You always will run into people who will want to page over the KEYS, not over the ROWS in the keys (for example because they're expanded on demand).

There's a shortcut solution though. :) Sorting. It requires that you define paging with grouping as: if the page size is 10, there are always 10 customers in the grid, regardless of the # of groups they're in. This works, because the groupings in a grid are simple: a set of fields, no expressions.

Say you have these 100,000 customers. You want to group them on Country and City, and sort them on company name. All you now have to do is sort on: Country, City, CompanyName, and then simply PAGE over the set of 100,000 customers.

If you now get page 123, your grid will simply check which country and city are in the page shown and will add the grouping bars. As the data is in the right order, this always works.

-------------

About the LinqDataSource: it's for Linq to Sql only. So despinte effort put into supporting that: it won't bring you support for other datasourcecontrols out there, which ARE supported by all other grids out there.

-------------

About Linq being the solution here: Linq is for fetching only. That's great for readonly data (but then I don't need your grid's features), but there's a missing part of the story when the data has to be updated and saved. How are you planning to do that? The problem isn't new: all datasourcecontrols have already solved this. However, the ASP.NET architecture requires that the data used in the grid (via the datasourcecontrol) is INSIDE the datasourcecontrol as that datasourcecontrol receives the ExecuteUpdate etc. calls.

-------------

About summaries/total values: In my forum post about this same topic, I suggested how to do this: raise an event. The main problem here is: the data set is very large, e.g. more than a couple of hundred rows. As soon as that's known, you can toss out any client-side code you want to add, because it won't work: you can't pull all data to the client to calculate some value, with 100K rows, that's simply unacceptable. (I hope that the reason why that's unacceptable is clear). Summary and total values are scalars: calculated from the total set of data. Typically these can be perfectly calculated in a routine in the BL for example. Also: some architectures REQUIRE that these kind of values are calculated by code which is already there, and not some grid. How is this going to work with the grid? Is it possible to hook a routine to the grid for total/summary calculation? If so, you've solved your problem: IF paging THEN manual summary/total calculations. Of course, if the datasourcecontrol implements an interface you provide, you can use that.

-------------

What I find a bit sad in this whole discussion (which is dragging on for a while now), is that paging is apparently something that is an option. No, Julian, paging is something that is ESSENTIAL for some applications to be able to even work because of the vast amount of data they have to work with. And if something is essential, you can't compromise to ignore it: you HAVE TO deal with it.

I truly hope DevExpress solves this properly. If not, well, it's obvious we can't continue advising to pick DevExpress' grid controls for large applications, as essential parts of the application requirements can't be met.

"If the grouping is applied AFTER grouping,"

... should have been

if the paging is applied AFTER grouping...

Personally, I am in the boat that Summaries are hardly ever used. Summaries typically are used for reporting mechanisms and is that not what the XtraReports Suite is all about?

It seems that a grid is more about CRUD and the main focus should be that, and perhaps in order to get the Summaries, etc. make the developer do more to get that, not make the developer do more to get the more mainstay every day behavior.

As Frans said, I have some tables with several million rows, and often time I might display a grid, expect to get back the first page, then do some filtering, find the row or rows I need to deal with, edit, update and be done with it. Bring back a few millions rows is nuts when using a standard datasource control. Summaries are not necessary for that, grouping is used in a limited fashion.

I think the feature set of the ASPxGridView is great, just may need to be tweaked to design it to be used in the everyday behavior (CRUD, Paging, etc) by default, and some extra code to add the bells and whistles. By no means am I saying remove any features, just find a way to make it useable on large datasets in a more open way. XPO and Linq are not the ORMs out there.

Not to mention, the current LinqServerModeDataSource being read only means that even with the Linq implementation, the developer is forced to write even more code handling the RowInserting, RowUpdating and RowDeleteing events simply to make CRUD operations work. If a developer has to do that, doesn't the defeat the purpose of a DataSource Control that makes the "look mom, no hands" no coding approach non-existent?

As stated above, I hope this issue is dealt with properly. If you do take the time to work through this issue, then you can truly boast you have a performant grid with large datasets. While today, that may be true for read only operations, and I rarely ever use a grid in read only mode. I use reports for that.

I too was seriously disappointed with the design decision that was made in the grid. I purchase controls to take the hassle out of developing applications.

The grid, for me, is one of the most powerful tools to manage CRUD operations in an application. When it comes to displaying information ABOUT the data that is not present in the grid, I ALWAYS (well almost) prefer to put that in a report. The grid is a tool to facilitate data entry, reports are a tool to facilitate data understanding.

It is unfortunate that DevExpress does not see that since, hands down, you guys have the BEST, as far as I'm concerned, tools for creating reports. Why duplicate effort and put reporting features in an editing control?

If someone is truly interested in having summaries, etc. let them handle it the way they want through some sort of event or helper method.

As a side note, I recently switched from Telerik because of your tremendous ability to respond to customer needs and feedback. I hope I am not disappointed in this regard.

Do you want to make the min requirements for the component .NET 3.5?  I'm using .NET 3.5 and will use this and later, but I don't know that all of your ASPxGridView users are or will go to .NET 3.5.  So is LINQ going to tie your hands?

"Of course we will support "standard" data source paging (which is supported only by ObjectDataSource and LinqDataSource)."

No, it's supported by _ALL_ datasource controls, including ours, as it is part of the parameters passed to ExecuteSelect. However your grid doesn't pass these parameters, in ANY way, hence _NO_ paging.

And when will this paging work again? Do you have an estimate when this will be fixed?

"In addition to this of course, we already have built-in support for full "server side" data browsing - you and your customers have almost no limits - you can do everything with data without writing a single line of code. You can group, sort and filter. Print or export. You can do all of these things and the best part is that it all works fast...With almost any size of data...Under serious load."

If you want to sell me your marketing stuff, save yourself the trouble. If I have to group on a set of 100K rows, and there's no paging passed, nowhere will you get any performance whatsoever, as there's no paging parameter passed to the datasourcecontrol.

If you're now claiming these parameters ARE passed, show me.

"It works for our XPO objects and also it works really well for SQL Server databases (using our LinqServerModeDataSource). And almost any datasource provider can support these features - easily- by implementing our interface or via LINQ support."

I'm sorry, but this is your solution to the real problem? That I have to implement your proprietary interface on my runtime library, and therefore have to ship a devexpress dll just because your grid sucks in the paging department?

What if I want to pull some entities with a filter, and some related entities from the db from a set of 10 million rows into the grid, edit them and persist them (which should call ExecuteUpdate/Insert() on the datasourcecontrol) ? Can your linq code do that? No, because it has no clue what to do with updates. That's the reason why there are datasourcecontrols.

Julian, you are saying that the ASPxGridView does pass paging information to the datasource control as part of ExecuteSelect in a way that other third party datasource controls can be used with the grid to return just the page of information requested?  Or something else?

>>What we are saying is that just because Frans thinks its right >>does not mean it is so.

Ray,

I'm sorry if I wasn't clear in my question or my intention. I have no idea who is right and who is wrong or even if there is a right and a wrong.

I asked my question to try to see if I was misunderstanding Julian's response or Fran's statement.  To me, his response implied that the "standard" protocol is being followed by the grid, but Fran's is saying it isn't.

My current understanding (right or wrong) is that it doesn't (follow the protocol) but the DevX position is that, that protocol isn't needed, (at least not if you use XPO).  

I'm not clear, yet, on the non-xpo solutions for providing data to the grid.

Julian,

Could you please point to the actual sample application (ASPxGridView with XPO) that you build in your tutorial.

Thanks!

-Eugene