On All Things Web

PetaPocoArchive

Feb 10
I posted a while back about a way to embed in metadata about an entity’s tables and columns into the entity itself. This is useful if you disable the auto select and auto named parameters features of PetaPoco. However, I realized how bad what I did actually was. The approach I took, to embed that information into the entity, violated the Singular Responsibility Principal (SRP), which states that each object should have a singular responsibility, and doing this gave it two responsibilities. So instead, I decided to use an alternative solution.

For my needs, a repository pattern was helpful to centralize the location of all my queries. However, I didn’t want to worry about a grandiose repository scheme; I really needed something very simple, as the project this is for is very small. So I chose to embed the repository into the PetaPoco.Generator.include T4 template.

Below is my added repository, which contains those useful methods. Again, you may not need this, but if you want to get the most speed out of PetaPoco, it’s something to consider:

public partial class Repository
{
protected string[] GetColumns()
{
return new string[]
{
<#
for(int i = 0, len = tbl.Columns.Count; i
“”

};
}

protected Sql GetBaseSelectSql()
{
return new Sql()
.Select(GetColumns())
.From(“”);
}

protected string GetTableName()
{
return “”;
}

public Get(int id)
{
var ctx = new ();
return ctx.FirstOrDefault<>(this.GetBaseSelectSql().Where(“=@0″, id));
}

public void Create( obj)
{
if (obj == null)
throw new ArgumentNullException(“obj”);

var ctx = new ();
ctx.Insert(“”, “”, obj);
}

public void Delete( obj)
{
if (obj == null)
throw new ArgumentNullException(“obj”);

var ctx = new ();
ctx.Delete(“”, “”, obj);
}

public void DeleteByKey(int id)
{
var ctx = new ();
ctx.Execute(“delete from where = @0″, id);
}

public void Update( obj)
{
if (obj == null)
throw new ArgumentNullException(“obj”);

var ctx = new ();
ctx.Update(“”, “”, obj);
}
}

RepoName is the name to the core PetaPoco repository for working with data objects. Notice how in the modification methods (create, update, delete), for performance improvements, it provides the table/PK name, which the T4 template will inject and code generate. Additionally, some queries are generated too (Get and DeleteByKey), which we could take to a further degree. And lastly, at the top, are our helpful methods for referring to fields.

I placed this at the top of the generate poco’s IF statement, but before the entity definition as in the following. At the time of writing, this is at line 134.


<#



//POCO definition
[TableName("")]


[PrimaryKey("")]

[PrimaryKey("", sequenceName="")]



[PrimaryKey("", autoIncrement=false)]

[ExplicitColumns]
public partial class : .Record<>
{
Dec 31
I’ve been experiencing with Micro ORM products on the market in the open source realm as of recent.  Two of the products a colleague of mine recommended were PetaPoco and Dapper.  In researching the two, I really liked some of the features of PetaPoco, and hence I decided to go with this product instead.  PetaPoco’s implementation is simple; download the package from GitHub or Nuget and install it on your machine.  PetaPoco comes in the form of 3 T4 templates (one master and 2 related templates) to generate the PetaPoco components (which you have full access to) and the data access components that map to your database.  Since these are T4 templates, you have control over the customization.

Because I wanted to optimize some of the speed performance improvements of PetaPoco, some of the convenience features are gone, such as automatically generating the select parameters, named parameters for queries and stored procedures, etc.  This means I’ll use the select option like the following:
new Sql() .Select(“*”) .From(“Users”) .Where(“IsActive = 1″)

However, instead of defining * as a convenience, I wanted to include all of the parameters names since * can perform slightly worse than named parameters; however, defining each column is harder to keep track of as table definitions change.  Therefore, the alternative I chose was to add some additional methods to the template, to handle this for me.

Before we get to that, let’s look at what PetaPoco requires.  In my project, there are three templates, the first is the master template named Database.tt.  It’s not critically important to understand what goes on in here except for the first section which has common settings:
// Settings ConnectionStringName = “DB”; Namespace = “My.DataAccess”; RepoName = “MyContext”; GenerateOperations = true; GeneratePocos = true; GenerateCommon = true; ClassPrefix = “”; ClassSuffix = “”; TrackModifiedColumns = true;

ConnectionStringName is important and must match a connection string defined within the app.config of the project.  Namespace determines the namespace o the components, and RepoName is the name of the custom database class generated.  PetaPoco uses an approach similar to LINQ to SQL or Entity Framework, where the core DataContext/ObjectContext classes are inherited from in the designer, creating a new, customized context class.

The additional options control whether to generate certain pieces of code (the poco component, common method operations, etc.), and how to modify the POCO’s being generated.  The shell of the custom database class appears below:
namespace My.DataAccess { public partial class MyContext : Database { public MyContext() : base(“DB”) { CommonConstruct(); } }

The next template we’ll look at is PetaPoco.Generator.include, which contains the definition of the POCO objects.  It’s this template that we can customize the process to add additional features, and make the process smooth.  At the top is the T4 template for generating the custom Database class as shown above.  Later on below is the template for each POCO, which looks like this:
<# if (GeneratePocos) { #> <# foreach(Table tbl in from t in tables where !t.Ignore select t) { #> [TableName("<#=tbl.Name#>")] <# if (tbl.PK!=null && tbl.PK.IsAutoIncrement) { #> <# if (tbl.SequenceName==null) { #> [PrimaryKey("<#=tbl.PK.Name#>")] <# } else { #> [PrimaryKey("<#=tbl.PK.Name#>", sequenceName="<#=tbl.SequenceName#>")] <# } #> <# } #> <# if (tbl.PK!=null && !tbl.PK.IsAutoIncrement) { #> [PrimaryKey("<#=tbl.PK.Name#>", autoIncrement=false)] <# } #> [ExplicitColumns] public partial class <#=tbl.ClassName#> <# if (GenerateOperations) { #>: <#=RepoName#>.Record<<#=tbl.ClassName#>> <# } #> {

PetaPoco can use attributes to identify the DB table name and primary key column name, if that option is enabled.   The partial class definition is where the meat of the generation options are.  This is where I added 3 additional items.
public static string GetPrimaryKeyName() { return “<#=tbl.PK.Name#>”; } public static string GetTableName() { return “<#= tbl.Name #>”; } public static string[] GetColumns() { return new string[] { <# for(int i = 0, len = tbl.Columns.Count; i < len; i++) { Column col = tbl.Columns[i]; if (!col.Ignore) { #> “<#= col.PropertyName #>”<#= (i != tbl.Columns.Count – 1 ? “,” : “”) #> <# } } #> }; }

The first method creates a static reference to the name of the primary key.  The second option creates a static reference to the name of the table, and the last option retrieves all of the names of the columns as an array.   The benefit to this option is it’s not reflective, it’s generated with the code gen and therefore compiled and not evaluated at runtime, making it still a pretty fast operation.  And I can update my SQL statement like the following:
new Sql() .Select(Users.GetColumnNames()) .From(Users.GetTableName()) .Where(“IsActive = 1″)

And, if I want to use the Save override that takes the table name and PK name, I can use this:
var db = new MyContext(); db. Save(users.GetTableName(), users.GetPrimaryKeyName(), poco);


To go even further, for selecting, we can generate the shell select statement as such:

public static Sql GetSelectSql() { return new Sql() .Select(GetColumns()) .From(GetTableName()); }


This makes it even easier to craft your select statements. You would have to use these options if you disabled certain features (like auto generation of select statements) of the database to improve performance.  I hope this helps illustrate how we can use code generation to help improve our applications.