PetaPoco – Old Documentation

I have used PetaPoco extensively on a number of past projects, especially those incorporating Umbraco. I  had to revisit one of these solutions recently and because with age comes forgetfulness, needed to revisit the documentation.  It no longer exists.

Originally the brainchild of Brad Robinson it has now been taken over by Collaborating Platypus.  However the documentation is minimal and although a link is supplied to the original documentation that now appears to have been removed from the Topten Software site.

Luckily there is a wonderful thing called the Way Back Machine  which holds an archive of past sites and I was able to access the original documentation here.   Using this and associated blog pages I  put together the following document for my own reference but thought it may be useful to others too.  I have only included the stuff that I use and find useful, the major omission being T4 templates but that can be found via the above links.

If I am in some way breaching copyright or if putting this up here is seen as bad etiquette then I will take this post down.

Running Queries

First define your POCOs:

// Represents a record in the "articles" table

public class article

{

    public long article_id { get; set; }

    public string title { get; set; }

    public DateTime date_created { get; set; }

    public bool draft { get; set; }

    public string content { get; set; }

}

Next, create a PetaPoco.Database and run the query:

// Create a PetaPoco database object

var db=new PetaPoco.Database("connectionStringName");


// Show all articles    

foreach (var a in db.Query<article>("SELECT * FROM articles"))

{

    Console.WriteLine("{0} - {1}", a.article_id, a.title);

}

To query a scalar:

long count=db.ExecuteScalar<long>("SELECT Count(*) FROM articles");

Or, to get a single record:

var a = db.SingleOrDefault<article>("SELECT * FROM articles WHERE article_id=@0", 123));

Paged Fetches

PetaPoco can automatically perform paged requests.

var result=db.Page<article>(1, 20, // <-- page number and items per page

        "SELECT * FROM articles WHERE category=@0 ORDER BY date_posted DESC", "coolstuff");

In return you’ll get a PagedFetch object:

public class Page<T> where T:new()

{

    public long CurrentPage { get; set; }

    public long ItemsPerPage { get; set; }

    public long TotalPages { get; set; }

    public long TotalItems { get; set; }

    public List<T> Items { get; set; }

}

Behind the scenes, PetaPoco does the following:

  1. Synthesizes and executes a query to retrieve the total number of matching records
  2. Modifies your original query to request just a subset of the entire record set

You now have everything to display a page of data and a pager control all wrapped up in one handy little object!

Query vs Fetch

The Database class has two methods for retrieving records Query and Fetch. These are pretty much identical except Fetch returns a List<> of POCO’s whereas Query uses yield return to iterate over the results without loading the whole set into memory.

Non-query Commands

To execute non-query commands, use the Execute method

db.Execute("DELETE FROM articles WHERE draft<>0");

Inserts, Updates and Deletes

PetaPoco has helpers for insert, update and delete operations.

To insert a record, you need to specify the table and its primary key:

// Create the article

var a=new article();

a.title="My new article";

a.content="PetaPoco was here";

a.date_created=DateTime.UtcNow;



// Insert it

db.Insert("articles", "article_id", a);



// by now a.article_id will have the id of the new article

Updates are similar:

// Get a record

var a = db.SingleOrDefault<article>("SELECT * FROM articles WHERE article_id=@0", 123);

// Change it

a.content="PetaPoco was here again";

// Save it

db.Update("articles", "article_id", a);

Or you can pass an anonymous type to update a subset of fields. In this case only the article’s title field will be updated.

db.Update("articles", "article_id", new { title="New title" }, 123);

To delete:

// Delete an article extracting the primary key from a record

db.Delete("articles", "article_id", a);

// Or if you already have the ID elsewhere

db.Delete("articles", "article_id", null, 123);

Decorating Your POCOs

In the above examples, it’s a pain to have to specify the table name and primary key all over the place, so you can attach this info to your POCO:

// Represents a record in the "articles" table

[PetaPoco.TableName("articles")]
[PetaPoco.PrimaryKey("article_id")]
public class article
{
    public long article_id { get; set; }
    public string title { get; set; }
    public DateTime date_created { get; set; }
    public bool draft { get; set; }
    public string content { get; set; }

}

Now inserts, updates and deletes get simplified to this:

// Insert a record

var a=new article();
a.title="My new article";
a.content="PetaPoco was here";
a.date_created=DateTime.UtcNow;
db.Insert(a);

// Update it

a.content="Blah blah";
db.Update(a);

// Delete it

db.Delete(a);

There are also other overloads for Update and Delete:

// Delete an article

db.Delete<article>("WHERE article_id=@0", 123);

// Update an article

db.Update<article>("SET title=@0 WHERE article_id=@1", "New Title", 123);

You can also tell it to ignore certain fields:

public class article
{

    [PetaPoco.Ignore]
    public long SomeCalculatedFieldPerhaps
    { 
        get; set; 
    }

}

Or, perhaps you’d like to be a little more explicit. Rather than automatically mapping all columns you can use the ExplicitColumns attribute on the class and the Column to indicate just those columns that should be mapped.

// Represents a record in the "articles" table

[PetaPoco.TableName("articles")]
[PetaPoco.PrimaryKey("article_id")]
[PetaPoco.ExplicitColumns]
public class article
{

    [PetaPoco.Column] public long article_id { get; set; }

    [PetaPoco.Column] public string title { get; set; }

    [PetaPoco.Column] public DateTime date_created { get; set; }

    [PetaPoco.Column] public bool draft { get; set; }

    [PetaPoco.Column] public string content { get; set; }

}

This works great with partial classes. Put all your table binding stuff in one .cs file and calculated and other useful properties can be added in a separate file with out thinking about the data layer).

You can also change the column name to which a property is mapped by specifying it as a argument to the [Column] attribute. eg:

[PetaPoco.Column("article_id")] long id { get; set; }

Result Columns

Sometimes it’s handy to run queries that return not only columns in a POCO’s table, but also calculated or joined columns. We need to be able to populate these columns with the results of a query, but ignore them in Update and Insert operations.

For this there is the [ResultColumn] attribute.

Suppose you have a categories table and you want to able to retrieve the number of articles in each category.

[TableName("categories")]
[PrimaryKey("category_id")]
[ExplicitColumns]
public class category
{
    [Column] public long category_id { get; set; }
    [Column] public string name { get; set; }
    [ResultColumn] public long article_count { get; set; }
}

You can still perform updates and inserts as before (and the article_count property will be ignored).

var c =  db.SingleOrDefault<category>("WHERE name=@0", "somecat");
c.name="newname";
db.Save(c);

But you can also use it to capture the results of a join:

var sql = new PetaPoco.Sql()
    .Append("SELECT categories.*, COUNT(article_id) as article_count")
    .Append("FROM categories")
    .Append("JOIN article_categories ON article_categories.category_id = categories.category_id")
    .Append("GROUP BY article_categories.category_id")
    .Append("ORDER BY categories.name");

foreach (var c in db.Fetch<category>(sql))
{
    Console.WriteLine("{0}\t{1}\t{2}", c.category_id, c.article_count, c.name);
}

Note, that to populate a [ResultColumn] you must explicitly reference it in your select clause. PetaPoco won’t include these columns when it automatically generates the column list for a select statement (as in the SingleOrDefault method call in the above example).

dynamic Support

You can disable it if you’re running older versions of .NET.

To turn off support for dynamic:

  • Bring up the Project Properties for the project that you added PetaPoco.cs to.
  • Switch to the Build tab
  • In the field Conditional compilation symbols, add PETAPOCO_NO_DYNAMIC

To do a “dynamic” query just use the existing query methods but pass dynamic as the generic parameter. The returned objects will have a property for each column returned by the query:

foreach (var a in db.Fetch<dynamic>("SELECT * FROM articles"))
{
    Console.WriteLine("{0} - {1}", a.article_id, a.title);
}

Note there’s no support for automatic SELECT clauses – since PetaPoco doesn’t know the table name.

You can also do updates, inserts and deletes but you need to specify the table name and primary key of the table to update:

// Create a new record
dynamic a = new ExpandoObject();
a.title = "My New Article";

// Insert it
db.Insert("articles", "article_id", a);

// New record ID returned with a new property matching the primary key name
Console.WriteLine("New record @0 inserted", a.article_id")

Here’s an update:

// Update
var a = db.Single("SELECT * FROM articles WHERE article_id=@0", id);
a.title="New Title";
db.Update("articles", "article_id", a);

Delete()Save() and IsNew() all work similarly.

Automatic Select clauses

When using PetaPoco, most queries start with “SELECT * FROM table”. Given that we can now grab the table name from the POCO object using the TableName attribute, there’s no reason we can’t automatically generate this part of the select statement.

If you run a query that doesn’t start with “SELECT”, PetaPoco will automatically put it in. So this:

// Get a record

var a=db.SingleOrDefault<article>("SELECT * FROM articles WHERE article_id=@0", 123);

can be shortened to this:

// Get a record

var a=db.SingleOrDefault<article>("WHERE article_id=@0", 123);

PetaPoco doesn’t actually generate “SELECT *”… rather it picks the column names of the POCO and just queries for those columns.

It will also handle this:

var a = db.SingleOrDefault<article>("FROM whatever WHERE id=@0", 123);

IsNew and Save Methods

Sometimes you have a POCO and you want to know if it’s already in the database. Since we have the primary key all we need to do is check if that property has been set to something other than the default value.

So to test if a record is new:

// Is this a new record 

if (db.IsNew(a))

{

    // Yes it is...

}

And related, there’s a Save method that will work out whether to Insert or Update

// Save a new or existing record

db.Save(a);

Transactions

Transactions are pretty simple:

using (var scope=db.Transaction)
{

    // Do transacted updates here

    // Commit
    scope.Complete();

}

Transactions can be nested, so you can call out to other methods with their own nested transaction scopes and the whole lot will be wrapped up in a single transaction. So long as all nested transcaction scopes are completed the entire root level transaction is committed, otherwise everything is rolled back.

Note: for transactions to work, all operations need to use the same instance of the PetaPoco database object.

PetaPoco’s SQL Builder

The point of this is to make formatting the SQL strings easy and to use proper parameter replacements to protect from SQL injection. This is not an attempt to ensure the SQL is syntactically correct, nor is it trying to hold anyone’s hand with intellisense.

Here’s its most basic form:

var id=123;

var a=db.Query<article>(PetaPoco.Sql.Builder

    .Append("SELECT * FROM articles")

    .Append("WHERE article_id=@0", id)

)

Big deal right? Well what’s cool about this is that the parameter indicies are specific to each .Append call:

var id=123;

var a=db.Query<article>(PetaPoco.Sql.Builder

    .Append("SELECT * FROM articles")

    .Append("WHERE article_id=@0", id)

    .Append("AND date_created<@0", DateTime.UtcNow)

)

You can also conditionally build SQL.

var id=123;

var sql=PetaPoco.Sql.Builder

    .Append("SELECT * FROM articles")

    .Append("WHERE article_id=@0", id);



if (start_date.HasValue)

    sql.Append("AND date_created>=@0", start_date.Value);



if (end_date.HasValue)

    sql.Append("AND date_created<=@0", end_date.Value);



var a=db.Query<article>(sql)

Note that each append call uses parameter @0? PetaPoco builds the full list of arguments and updates the parameter indices internally for you.

You can also use named parameters and it will look for an appropriately named property on any of the passed arguments

sql.Append("AND date_created>=@start AND date_created<=@end", 

                new 
                { 
                    start=DateTime.UtcNow.AddDays(-2), 
                    end=DateTime.UtcNow 
                }

            );

With both numbered and named parameters, if any of the parameters can’t be resolved an exception is thrown.

There are also methods for building common SQL stuff:

var sql=PetaPoco.Sql.Builder()

            .Select("*")

            .From("articles")

            .Where("date_created < @0", DateTime.UtcNow)

            .OrderBy("date_created DESC");

Smart Consecutive Clause Handling

Sometimes when building up SQL statements you need to add multiple optional WHERE clauses. PetaPoco’s  consecutive clause handling automatically joins these correctly.

Imagine you’re trying to query a database based on a set of optional conditions, say a starting date and and ending date:

List<article> GetArticles(DateTime? start, DateTime? end)
{
    var sql=new Sql();

    if (start.HasValue)
        sql.Append("WHERE start_date>=@0", start.Value);

    if (end.HasValue)
    {
        if (start.HasValue)
            sql.Append("AND end_date<=@0", end.value);
        else
            sql.Append("WHERE end_data<@0", end.Value);
    }

    return article.Fetch(sql);
}

Working out whether the second condition is a WHERE or an AND clause makes things tedious. Well PetaPoco can now automatically detect consecutive WHERE clauses and automatically turns subsequent ones into AND clauses.

List<article> GetArticles(DateTime? start, DateTime? end)
{
    var sql=new Sql();

    if (start.HasValue)
        sql.Append("WHERE start_date>=@0", start.Value);

    if (end.HasValue)
        sql.Append("WHERE end_data<@0", end.Value);

    return article.Fetch(sql);
}

There’s a few caveats, but as long as you’re aware of them it’s easy to work with.

  1. The WHERE clause must be the first part of an Sql fragment, so this wont work:
    sql.Append("WHERE condition1 WHERE condition2");
    

    but this will

    sql.Append("WHERE condition1").Append("WHERE condition2");
    
  2. The Sql fragments must be adjacent, so this won’t work:
    sql.Append("WHERE condition1").Append("OR condition2").Append("WHERE condition3");
    
  3. You might need to parenthesize individual conditions to get correct operator precedence.
    sql.Append("WHERE x");
    sql.Append("WHERE y OR Z");
    

    should be written:

    sql.Append("WHERE x");
    sql.Append("WHERE (y OR z)");
    

This functionality also works for ORDER BY clauses:

var sql=new Sql();
sql.Append("ORDER BY date");
sql.Append("ORDER BY name");

will yield:

ORDER BY date, name

SQL Command Tracking

Sometime it’s useful to be able to see what SQL was just executed. PetaPoco exposes these three properties:

  • LastSQL – pretty obvious
  • LastArgs – an object[] array of all arguments passed
  • LastCommand – a string that shows the SQL and the arguments

Watching the LastCommand property in the debugger makes it easy to see what just happened!

OnException Handler Routine

PetaPoco wraps all SQL command invocations in try/catch statements. Any exceptions are passed to the virtual OnException method. By logging these exceptions (or setting a breakpoint on this method) you can easily track where an when there are problems with your SQL.

Google Chromecast update fail and other issues

Fix – Update stuck or hanging? Can’t connect? Read this troubleshooting guide.

We have used Chromecasts since the first ‘model’ came out, and generally they have run without an issue.  Our first Chromecast appeared to have died, it would no longer connect,  so we bought a new Chromecast 2 and spent an evening trying to set it up.  Everything appeared to be working OK until it tried to fetch an update, at which point it just stayed at 0% (zero percent).  Here’s what we tried and how we eventually fixed it:

  • First we tried switching it off and waiting 30 seconds or so before switching back on.  This has worked for some but not for us, it just went straight to the update screen and stayed at 0%.
  • Next we tried resetting it to factory settings, another online suggestion, by holding the reset button for 25 seconds or so until it rebooted.  We went through the setup procedure again, it tried to fetch an update annnnnnnd stuck once more at 0%.
  • We recently upgraded our broadband and BT had kindly sent us a new Home Hub.  Now I don’t know if other routers have this functionality but when you log on for the first time with a new device you are redirected to a set up page where you can set parental controls etc.  Possibly the Chromecast was being treated as a new device when it tried to get an update, being redirected to this page would certainly confuse it.  To switch off this feature do the following:
    • In your browser go to http://bthomehub.home/
    • Select ‘Advanced Settings’
    • On the next screen enter your username and password and on the following screen select ‘continue to advanced settings’.
    • Select ‘Home Network’ and then ‘Smart Setup’
    • Change ‘Enable Smart Setup’ to ‘No’ and click the apply button.
    • Reset the Chromecast to factory settings once more and try again!
  • Now this didn’t work for us initially but we did discover that our old Chromecast 1 was now able to connect and function correctly once more and I think this was part of the solution to getting the Chromecast 2 up and running also.
  • The final key to the puzzle for us was to ……..

    Change to another HDMI port, I don’t know why that worked but suddenly the update was fetched and we’ve been streaming without an issue ever since.

Unable to force Windows 10 Upgrade?

How to solve the issues of wuauclt.exe/updatenow appearing to do nothing

I have been trying to force Windows 7 to upgrade to Windows 10 using the the following steps:

  •  Delete all files in C:\Windows\SoftwareDistribution\Download.
  • Run the command prompt as administrator. (Right click the Command Prompt icon and select ‘Run as Administrator’)
  • Type in ‘wuauclt.exe/updatenow’ without the quotes.
  • Hit Enter

Nothing happened.  I was puzzled as I had already run these steps successfully on a PC running Windows 8.1 and it worked flawlessly.  No matter what I did or how up to date my updates were wuauclt didn’t seem to do anything.  However after some hunting around I found the solution:

Open the registry editor (regedit.exe) and locate the registry key: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\OSUpgrade]

It should exist, but if not, create it.

Create a new DWORD (32-bit) Value with Name = “AllowOSUpgrade” (without the quotes), and set the Value = 0x00000001 – you actually just need to enter 00000001, 0x is added by the editor.

Now, run through the steps above once more and Windows 10 should begin to download. I hope that saves you from the frustration that I suffered.  Full details on how to force Windows 10 upgrade and this solution can be found here.

SOLD – VW T25 High Top Campervan

1981 2ltr Petrol Air cooled with Drive Away Awning – MOT September 2015

VW with Awning

Features include:

  • Drive away awning.
  • Captains chair (passenger seat).
  • 2 ring gas cooker and grill.
  • 12 volt/gas fridge. Runs off battery when travelling, gas when parked up.
  • Sink with electric pump.
  • Large on board water tank (housed under rear seats)
  • Rock ‘n roll bed.
  • Additional double bed in high top.
  • Leisure battery – Requires replacing.
  • CD player/radio with 4 speakers (2 front, 2 rear) and line in.
  • Over cab storage.
  • Hook up lead with 4 bank adapter.
  • New curtains.
  • Taxed

Recently MOT’d has had a replacement rear swinging arm fitted.  The body work is sound and runs well.  Took us to Glastonbury and on for a tour of Devon this year with numerous weekend jaunts into Wales.  Not in the first flush of youth but ready to go.  Cosmetically leaves you plenty of scope to make your own mark.

Select thumbnail below for a whole gallery of images!

 

WordPress, Jetpack, How-To show a gallery in Carousel by single link or thumbnail

How to launch a gallery in the Jetpack Carousel from a single thumbnail or link without rendering all the gallery as thumbnails in a WordPress Post

I quite like the Jetpack Carousel but unfortunately to have it display a gallery you have to render the entire gallery as thumbnails. What I want to do is launch the carousel from a single thumbnail or link. This gives the advantage of being able to display a couple of choice photos in a post and allowing the reader to view the full set if adequately interested.

So where to start? Well the one minor inconvenience is that you need to be working in Text rather than Visual mode in the WordPress post editor. Then select the Add Media button to create your gallery.
caro1

Then from the Insert Media screen choose Create Gallery.
Insert Media

I’m just going to choose a few random pictures that I have previously uploaded for this example.

Create Gallery

Adding captions and titles will make the carousel display more interesting and informative. Once you are happy with the gallery, insert it and in text mode in the editor it will look something like this:

Gallery code

and in your post preview it will display thus:

which actually doesn’t look too bad but when you have a hundred holiday shots or thirty/forty illustrations as in this post. Then it starts to get a bit messy. What I want to do is just display the thumbnail of the two horses and use that as the link for the entire gallery.

The first step is to preview your post so far, then click on the image that you want to use to launch the carousel. Note, the image you choose to launch the carousel will also be the first image that displays in the carousel.

Carousel

Then copy the address of the page from the top of the browser which should look something like this:
http://redeyedmonster.co.uk/?p=1669&preview=true#jp-carousel-1997
If you don’t mind publishing your post at this point and then selecting an image from the carousel you will find that the link is a lot neater:
http://redeyedmonster.co.uk/#jp-carousel-1997
That is the link you are going to use to launch the carousel. If you just want a text link then you could do something like this in the text editor:
<a href="http://redeyedmonster.co.uk/#jp-carousel-1997">Look at my photos!</a>

Which will render in your post thus:
Look at my photos!

To use one of the gallery thumbnails as an image for the link, select the add media button and select the image you want to use from the media library. It will already be there as part of your gallery.

Insertthumbnail

Select alignment and the image size you require e.g. thumbnail and then in the ‘link to’ section select Custom URL and paste the link to your carousel in the box below.

And that’s all you need to do:

Horses at the hilltop pool

Ta Da! “But wait a minute”, you say, “I still have all these gallery thumbnails clogging up my post!” Now here is the easy part, all you need to do is wrap the gallery code in a div with it’s display properties set to none:

<div style="display: none;">
[gallery ids="2003,2002,1998,1997,1991"]
</div>

That’s it. I hope you’ve found this helpful.