PetaPoco - Version 2.1.0

This post is about PetaPoco - "a tiny ORMish thing for your POCOs". Read more on the PetaPoco Project Page.

Support for Dynamics

After gathering feedback on whether support for dynamic should be included in PetaPoco I've decided to put it in, but with the ability to 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

For more information on using PetaPoco's dynamic support see this previous blog post. The short version is that you can now do this:

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

Columns with Spaces (and other non-identifier characters)

Previous versions of PetaPoco assumed that any column name coming out of your database was a valid C# property name. This of course failed with columns containing spaces, but has now been rectified in two ways:

1. PetaPoco correctly escapes all column names in its SQL with the appropriate delimiter for the database in question (eg: [column], `column` or "column")

2. The T4 templates now cleans column names to make them C# compatible and uses the Column attribute to set the DB name of the column. eg:

   [Column("col with spaces")] string col_with_spaces { get; set; }
   

One caveat with all this is if you're using dynamic's with incompatible column names. PetaPoco does not attempt to correct them in this case and you'll need to either modify your SQL to return a valid column name:

var a=db.SingleOrDefault<dynamic>(
        "SELECT id, [col with spaces] as col_with_spaces FROM whatever WHERE id=@0", 23); 
Console.WriteLine(a.col_with_spaces);

Or, cast the returned Expandos to a dictionary:

var a=db.SingleOrDefault<dynamic>(
        "SELECT id, [col with spaces] FROM whatever WHERE id=@0", 23); 
Console.WriteLine((a as IDictionary<string, object>)["col with spaces"]);

If you're wondering why PetaPoco doesn't just clean the column names for dynamics, it's because there's no way to map back the other way if you're trying to update using the same expando instance.

Ansi String Support

DBA guru Rob Sullivan yesterday pointed out that SQL Server has pretty severe performance overhead if you try to query an index with varchar column using a unicode string parameter. To fix this the parameter needs to be bound as DbType.AnsiString. To facilitate this you can now wrap such string parameters in a new AnsiString class:

var a = db.SingleOrDefault<article>("WHERE title=@0", new PetaPoco.AnsiString("blah"));

Thanks Rob.

PetaPoco.Core Nuget Package

It seems there are two camps of PetaPoco users - those who like their POCOs written by the T4 templates and those that like to hand code their own. For the first group the existing Nuget package includes the T4 templates. For the latter groups there's a new package "PetaPoco.Core" that includes just the C# file and nothing else.

• PetaPoco on Nuget

Exists(PrimaryKey) and Delete(PrimaryKey)

These two were in a few commits ago, but I neglected to mention them. You can now check for the existence of a record by primary key:

if (db.Exists<article>(23)) ...

and delete by primary key:

db.Delete<article>(23);

Semantic Versioning

PetaPoco now conforms to Semantic Versioning. Until now PetaPoco's version numbers have been haphazard to say the least. From now on sanity will prevail...

Available now

All this is available now in NuGet and GitHub now.