Gemli.Data: Pagination and Counts

by Jon Davis 5. October 2009 03:54

It occurred to me that I’m not blogging enough about how Gemli is shaping, other than mentioning the project on the whole in completely different blog posts.

In the dev branch I’ve dropped the abstract base DataModelQuery class (only DataModelQuery<TModel> remains) and replaced it with an IDataModelQuery interface. This was a long-needed change, as I was really only using it for its interfaces anyway. It was being used because I didn’t necessarily know the TModel type until runtime, namely in the DataProviderBase class which handles all the deep loads with client-side-joins-by-default behavior. But an interface will work for that just as well, and meanwhile the query chaining syntax behavior was totally inconsistent and in some cases useless.

Some recent changes that have been made in the dev branch include some more syntactical sugar, namely the support for loading and saving directly from a DataModel/DataModel<T> class or from a DataModelQuery<TModel> plus support for pagination and getting record counts.

Quick Loads

From DataModel:

List<MyPoco> collectionOfMyPoco = DataModel<MyPoco>.LoadAll().Unwrap<MyPoco>();

There are also Load() and LoadMany() on DataModel, but since they require a DataModelQuery object as a parameter, and DataModelQuery has new SelectFirst() and SelectMany() that do the same thing (forward the task on to the DataProvider), I’m not sure they’re needed and I might just delete them.

From DataModelQuery:

List<MyPoco> collectionOfMyPoco = DataModel<MyPoco>.NewQuery()
    .WhereProperty["Amount"].IsGreaterThan(5000m)
    .SelectMany().Unwrap<MyPoco>();

Pagination

Since Gemli is being built for web development, pagination support with pagination semantics (“page 3”, rather than “the rows between rows X and Y”) is a must. There are many things besides data grids that require pagination—actually, pretty much any list needs pagination if you intend not to show as many items on a page as there are in a database table.

Client-side pagination is implemented by default, but only if DB server-side pagination is not handled by the data provider. I still intend to add DB-side pagination for SQL Server.

// get rows 61-80, or page 4 @ 20 items per page, of my filtered query
var myListOfStuff = DataModel<Stuff>.NewQuery()
    .WhereProperty["IsActive"].IsEqualTo(true)
    .Page[4].OfItemsPerPage(20)
    .SelectMany().Unwrap<Stuff>();

To add server-side pagination support for a proprietary database, you can inherit DbDataProvider and override the two variations of CreateCommandBuilder() (one takes a DataModel for saving, the other takes a DataModelQuery for loading). The command builder object has a HandlePagination property that can be assigned a delegate. The delegate would then add or modify properties in the command builder that inject appropriate SQL text into the command.

Count

Support for a basic SELECT COUNT(*) FROM MyTable WHERE [..conditions..] is a pretty obvious part of any minimal database interaction library (O/RM or what have you). For this reason, GetCount<TModel>(DataModelQuery query) has been added to DataProviderBase, and in DbDataProvider it replaces the normal LoadModel command builder generated text with SELECT COUNT(*)…  The DataModelQuery<TModel> class also has a new SelectCount() method which forwards the invocation to the DataProvider.

long numberOfPeepsInMyNetwork = DataModel<Person>.NewQuery()
    .WhereProperty["Networkid"].IsEqualTo(myNetwork.ID)
    .SelectCount();

All of these changes are still pending another alpha release later. But if anyone wants to tinker it’s all in the dev branch in source code at http://gemli.codeplex.com/ .

LINQ May Be Coming

I still have a lot of gruntwork to do to get this next alpha built, particularly in adding a lot more tests. I still haven’t done enough testing of deep loads and deep saves and I’m not even confident that deep saves are working correctly at the basic level as I haven’t yet used them. Once all of that is out of my way, I might start looking at adding LINQ support. I’m not sure how awfully difficult LINQ support will prove to be yet, but I’m certain that it’s doable.

One of the biggest advantages of LINQ support is strongly typed member checking. For example, in Gemli, you currently have to declare your WHERE clauses with a string reference to the member name or the column name, whereas with LINQ the member name can be referenced as strongly-typed code. As far as I know, it does this by way of syntactic sugar that ultimately boils down to delegates that literally work with the object directly, depending on how the LINQ support was added.

Among the other advantages of adding LINQ support might be in adding support for anonymous types. For example, right now without LINQ you’re limited to DataModels and the class structures they represent. But LINQ lets you select specific members into your return object, for example, rather than the whole enchilada.

LINQ:

// only return ID and Name, not the whole Product
var mySelection = (from p in Product
                   select p.ID, p.Name).ToList();

It’s this underlying behavior that creates patterns for me to work with that I might drag into play the support for aggregate functions in SQL using terse Gemli+LINQ semantics. Right now, it’s literally impossible for Gemli to select the result of an aggregate function (other than Count(*)) unless that was wrapped in a stored procedure on the DB side and then wrapped in a DataModel on Gemli’s side. There’s also no GROUP BY support, etc. I like to believe that LINQ will help me consolidate the interface patterns I need to make all that work correctly. However, so far I’m still just one coder as no one has jumped on board with Gemli yet so we’ll see if LINQ support makes its way in at all.

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Tags: ,

Pet Projects | Software Development

Add comment


(Will show your Gravatar icon)  

  Country flag

biuquote
  • Comment
  • Preview
Loading




 

Powered by BlogEngine.NET 1.4.5.0
Theme by Mads Kristensen

About the author

Jon Davis (aka "stimpy77") has been a programmer, developer, and consultant for web and Windows software solutions professionally since 1997, with experience ranging from OS and hardware support to DHTML programming to IIS/ASP web apps to Java network programming to Visual Basic applications to C# desktop apps.
 
Software in all forms is also his sole hobby, whether playing PC games or tinkering with programming them. "I was playing Defender on the Commodore 64," he reminisces, "when I decided at the age of 12 or so that I want to be a computer programmer when I grow up."

Jon was previously employed as a senior .NET developer at a very well-known Internet services company whom you're more likely than not to have directly done business with. However, this blog and all of jondavis.net have no affiliation with, and are not representative of, his former employer in any way.

Contact Me 


Tag cloud

Calendar

<<  October 2020  >>
MoTuWeThFrSaSu
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678

View posts in large calendar