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 still being developed:

Official PetaPoco wiki

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 i.e. the PetaPoco.Core, the major omission being T4 templates but that can be found via the above links.

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));

Using WHERE <Field> IN (Select <field> FROM <table>)

When using a WHERE IN clause with an array or list do not use the standard @<index> i.e. @0, @1 etc. instead use the following syntax:


 string[] cats = new string[]{"cat1","cat2"};
 return _database.Fetch(@"
            select *
            from articles                   
            where category IN (@cats)",
            new { cats });

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.

Edit Any Table v2.1.2

A WordPress plugin/Dashboard Widget that allows you to connect to any database and edit the contents.

This plugin is a dashboard widget to allow you to connect to any MySQL database of your choice (as long as your hosting allows) and search, edit, add and delete records in an easy to use interface. Ideal if you have built a site referencing another database. You can allow other editors/administrators of the site to alter, update, delete, add and correct entries without having to give them the full capabilities of administration tools such as phpMyAdmin.

If you find this plug-in useful then please rate it, thanks.

Installation

To install, download the .zip file from the link below, extract the contents and place the edit-any-table folder in your plugin directory (typically \\your-site\wp-content\plugins\)

Setup

Once installed go to the Dashboard->Plugins page of your WordPress site and activate the plugin:

Edit Any Table activation and settings linkNow go to the Dashboard->Settings->Edit Any Table page and enter the required details:

Edit Any Table Settings

First you’ll need to enter the host (often localhost), the name of the database you wish to connect to and a valid user name and password. Under Admin Settings you can choose to allow Administrators only to have access to the widget or Editors as well. Note: If neither of these boxes are ticked the widget will not display in your WordPress Dashboard. You can choose what to allow the Editor to do i.e. Add, Edit and/or Delete.  Leaving these boxes unchecked will give the Editor view only access.   Display Settings allows you to select the maximum number of columns to display for returned searches. Edit Any Table displays best in a single column dashboard configuration and I find five columns is a comfortable fit but adjust to suit. Here you can also select to apply a friendly name for your connected database, this can help to assure editors (who don’t have access to the settings) they are working on the correct records. Finally, if you don’t want Edit Any Table to display as a widget then you can choose to have it rendered in its own separate admin page. Tables, if you have entered your database credentials correctly and saved them, you will get a list of all the tables.  Select which ones you would like to be accessed via the admin screen.

You will also notice that at the top of the settings page is an option to switch debug mode on or off. Switching it on will display all the SQL sent to your database and may help identify and issues or strange results you may be getting.

Using Edit Any Table

Once configured return to the dashboard and you should see the following widget:

Edit Any Table WidgetNow choose a table from the drop down list and select Open Note: If there is nothing in the list then you have either not entered the correct details in your settings or there are no tables in your database.

Here you can either search or add a new record with the values you have entered. Entering no search criteria returns all the records from the table. To clear any values hit the Reset button. If you enter values that are not of the correct type or in the wrong format when adding a new record these values will be substituted for the database defaults.  Wild cards do not work in searches but by selecting the ‘Fuzzy’ check box Edit Any Table will look for the phrase or part word you entered within any string field i.e.  entering ‘Liz’ in a first name field could return Elizabeth, Liz or Lizzy. Please be aware that fuzzy searches can take a lot longer when searching large datasets. Any time you want to return to this screen just select the Open button again.Search

These are the search results for a search against the AString field using the phrase hello:

Edit Any Table search resultsNow you can edit the record contents or delete a record. To return to the search/add record screen just select Open again. That’s it, enjoy.

Known Issues / Features

  • There is no bulk update or delete
  • After saving you are not returned to the previously displayed records
  • Cannot search blobs
  • Using reserved words as column names can cause issues

Please Note

To use this plugin you need a basic knowledge of database design and setup. Modifying records can have unpredictable results so if you are at all unsure I would suggest creating a test table first before using in anger.

DOWNLOAD LATEST VERSION OF PLUGIN

If you have a support question or have discovered a bug in Edit-Any-Table then please post here

Restoring WordPress

Moving across from legacy SupaNames account to 123reg and issues with permalinks

Supanames was taken over by 123-reg a few years ago.  This wasn’t something that affected me initially and I continued to use my Supanames account as always. However when recently trying to install the latest version of WordPress I found that the version of MySQL was not compatible with the latest upgrade.  I contacted 123-reg who informed me that there was no plan to upgrade old Supanames hosting and so with great reluctance I began to make preparations to move over to the more up to date 123-reg hosting.


The first thing to do was to backup the database.  This is remarkably simple to do using the Supanames MyPanel interface.

  1. Go to MyPanel and then in the Left-hand menu choose MySQL DATABASESMySQL Backup Submenu.
  2. Click on the link on the next page in MyPanel to Backup your Database.

 

If successful, you will receive a message confirming the backup has been completed. The backup is zipped and stored on the root directory of your web space. Having done this I then downloaded my entire site via FTP (I use Filezilla) including the zipped database backup.

I am fortunate that I don’t have hundreds of email boxes consider, all mine are hooked into gmail accounts and I have records of the alternate addresses. So I contacted the account holders to inform them that their mail would be down for maintenance.  Once this was done I gave 123-reg the go-ahead to close down my old Supanames hosting and fire up the new one.  This was ready to go in a couple of hours and the first thing I did was re-establish the mail boxes and send instructions to the users on how to reconnect.

Next I FTP’d the entire site back up to the root directory so that it had exactly the same structure as it did before.

The final step is to restore the database and modify the configuration so pages and database can talk to one another.  Restoring a database with 123-reg is not quite as simple as Supanames but it is no great technical feat either.  First you need to create a database. In your 123-reg control panel go to Web Hosting -> Manage

Manage Web Hosting

Then select Manage Database -> Add database

Add Database

Take a note of the username, password and server, you will need these for the config file. Return to the Add database screen and log on to your new database. Select the Import tab:

Import SQL

Select the previously downloaded backup via Choose File and then hit the Go button (you should be able to leave all other settings at default).  Hey presto your database is restored. Now for the configuration.

wp-config.phpYou need to edit your wp-config.php file typically found in the root directory of your web space.  Enter the database name, user and password against DB_NAME, DB_USER, DB_PASSWORD respectively (you can get the database name from the Add Database screen). On the old Supanames hosting the host name (DB_HOST) was set to ‘localhost’ this now needs to be set to the server name.  Once done save wp-config.php back to your web space.

After doing this I was pleased to see my front page (this blogg) functioning correctly, however when I selected any of the links I was given a 404 Not Found error.  A quick Google and my suspicions were confirmed, it was something to do with the permalink structure I was using. I found a number of solutions and didn’t like any of them much so before trying any of them I went into my WordPress  Admin and selected Settings -> Permalinks.  I did not change any of the settings, merely selected the Save Changes button.  This completely fixed my problem, so I urge you to try this before one of the more complex and possibly dangerous solutions on the net.