Massive Update Number 2

Having a lot of fun with this little tool - and more great comments are coming in. I've added some good stuff in the last few days - like Paging and streamed results.

Thanks To Jeroen Haegebaert

Jeroen and I had a great exchange on the first post about Massive where he asked if it was wise to pull in the results first (a bulk fetch), push them to a list, then send them out. It does take longer - but at the same time if you "stream" them, using yield/return, you're holding the connection open longer (possibly).

By yielding each element in a "stream" - it's possible to write some logic in your loop that affects the connection in the core routine. If you're aware of how yield/return works - well that's not all that big of a deal. But this can have nasty consequences if not used properly.

But as I always say - "educate, don't insulate" so I've plumbed in the streaming behavior by default, and added a "Fetch" method if you want to be explicit.

Inheritance

A lot of people didn't like the abstract base class approach - and I agree with that. You can now spin up a DynamicModel as needed:

var tbl = new DynamicModel("northwind", tableName:"Products", primaryKeyField:"ProductID");

Each of these arguments is optional. You can still use inheritance if you want, and if you don't supply a connectionStringName then Massive will just use the first one it finds (ConnectionStrings[0]). If you don't specify a PK - Massive will guess that you mean it to be "ID".

Paging

I've added in SQL-Server specific paging as well:

var tbl = new DynamicModel("northwind", tableName:"Orders", primaryKeyField:"OrderID");
var paged = tbl.Paged(where: "OrderID > 20", currentPage:2, pageSize:50);

This will return a single ExpandoObject with some properties set:

var totalRecords = paged.TotalRecords;
var totalPages = paged.TotalPages;
var itemList = paged.Items;

You can use Items to enumerate over. If you're using MySQL you'll want to change the SQL in this routine to use "LIMIT()" instead of the ridiculously verbose ROW_NUMBER() stuff.

Renames

In the last update I showed how to run a Transaction over several objects using the Transact() method:

var cats = new Categories();
var cat1 = new { CategoryName = "Piggy Bacon", Description = "Delete me" };
var cat2 = new { CategoryName = "Festivus Punch", Description = "Delete me" };
//add them both in the scope of a single connection/transaction
//this used to be called "Transact()"
cats.Save(cat1, cat2);

"Transact" is sorta wordy and a commenter suggested I just call it "Save()". So there you have it.

Scalars and Stored Procedures

I added a method to run a Scalar query (returns a single result), and in addition I tested this against a Stored Procedure and was quite stunned when it worked...

var orders = tbl.Query("CustOrdersOrders @0", "ALFKI");
foreach (var item in orders) {
    Console.WriteLine(item.OrderID);
}

All of these tweaks and changes, and I'm happy to say I lost another 20 lines!

• Tweet
•