The problem with this is that it heavily relies on StructureMap (FWIW I love SM, but its overkill here), and doesn’t quite fit into the way ASP.net MVC does things. It also requires a lot of setup. I found this out almost 2 years ago now, and so SchoStack.Web was born. Over the coming posts I will go through what’s included in the library, but here is how you quickly get up and running with the default conventions (these represent the defaults that render lists, dropdowns, checkbox’s which are all ASP.net MVC compliant).

1. Create new mvc project.
2. install-package schostack.web
3. Replace System.Web.Html namespaces in Views/web.config with the following
   <add namespace="SchoStack.Web.Html" />
   <add namespace="SchoStack.Web.Html.Form" />
4. In Application_Start add:
   HtmlConventionFactory.Add(new DefaultHtmlConventions());
   HtmlConventionFactory.Add(new DataAnnotationHtmlConventions());
   HtmlConventionFactory.Add(new DataAnnotationValidationHtmlConventions());
5. Thats it.

The following are the three main helpers used to generate all HTML based on your conventions.

@Html.Input(x=>x.Name)
@Html.Display(x=>x.Name)
@Html.Label(x=>x.Name)

All the usual MVC helpers are available as well like ValidationSummary and ValidationMessage.

I’ll try to cover the following things in upcoming posts, but if you have any questions or issues you can ask/raise them on github.

1. Creating your own conventions
2. Closer look at out of the box conventions
3. Validation (Server and Client validation using either FluentValidation and Data Annotations driven off the input model, not the viewmodel)
4. Foreach Loop Helpers
5. Url Generation and Routing

Adam

Most of the documentation has been published to the github wiki, however if you find something that is missing please let me know and I will endeavour to fix that.

There should be very limited breaking changes in 2.0, but if you have always just passed the connection string to the Database object then nothing will probably change. You can also specify the database type now, by passing ‘DatabaseType.SqlServer2012’ for example into the Database constructor. This will change the way the paging works in this case.

One of the latest features that has made it into 2.0 is the ability to use LINQ to specify simple where clauses, ordering and paging when fetching a single object. For example:

Database.FetchBy<User>(sql => sql.Where(x => x.Name == "Bob")
                                 .OrderBy(x => x.UserId)
                                 .Limit(10, 10))

There is also a FetchWhere<> which only takes a where expression.

Thanks again to all the contributors and if you have any issues be sure to raise them on github.

Adam

Install-Package Npgsql

Once this has been installed, we need to make a change to the web.config.

  <system.data>
    <DbProviderFactories>
      <add name="Npgsql Data Provider" 
           invariant="Npgsql" 
           description="Data Provider for PostgreSQL" 
           type="Npgsql.NpgsqlFactory, Npgsql" />
    </DbProviderFactories>
  </system.data>

Now we are setup to create our connection string. Here is a sample one.

<add name="Conn" 
    connectionString="Server=localhost;Database=db;User Id=user1;
                      Password=user1;Use Extended Types=true" 
    providerName="Npgsql"/>

The important part here is that you use the “User Extended Types=true”. This will return “TimeStampTz” types using Npgsql’s own type “NpgsqlTimeStampTZ” instead of a plain DateTime. Note: Npgsql does not automatically convert “timestamptz” types to DateTimeOffset, but using NPoco’s built in Mapper we can do this ourselves.

if (DestType == typeof(DateTimeOffset) || DestType == typeof(DateTimeOffset?)
    || DestType == typeof(DateTime) || DestType == typeof(DateTime?))
{
    return x =>
    {
        if (x is NpgsqlTimeStampTZ)
        {
            if (DestType == typeof(DateTime))
                return (DateTime)((NpgsqlTimeStampTZ)x);
            if (DestType == typeof(DateTime?))
                return (DateTime?)((NpgsqlTimeStampTZ)x);
            if (DestType == typeof(DateTimeOffset))
                return (DateTimeOffset)((NpgsqlTimeStampTZ)x);
            if (DestType == typeof(DateTimeOffset?))
                return (DateTimeOffset?)((NpgsqlTimeStampTZ)x);
        }
        if (x is NpgsqlTimeStamp)
        {
            if (DestType == typeof(DateTime))
                return (DateTime)((NpgsqlTimeStamp)x);
            if (DestType == typeof(DateTime?))
                return (DateTime?)((NpgsqlTimeStamp)x);
        }
        if (x is NpgsqlDate)
        {
            if (DestType == typeof(DateTime))
                return (DateTime)((NpgsqlDate)x);
            if (DestType == typeof(DateTime?))
                return (DateTime?)((NpgsqlDate)x);
        }

        return x;
    };
}

Here we check if the Destination Type is “DateTime” or “DateTimeOffset” and convert the Npgsql type into the CLR type. To wire this Mapper up we do the following:

var db = new Database("Conn") { Mapper = new Mapper() };

Usually I will create a static method that will create my Database so that I don’t have to write this every time. eg.

public class DbHelper
{
    public static IDatabase GetDb()
    {
        return new Database("Conn") { Mapper = new Mapper() };
    }
}

Now I can call DbHelper.GetDb() everywhere I need my database and the mapper will be plugged in appropriately.

As you can see, it is very easy to get up and running with NPoco and PostgreSQL. Please leave a comment if you have any questions.

Adam

MiniProfiler is an awesome project by Sam Saffron that can easily profile your site and SQL queries. It is very easy to install and there is heaps of documentation at its home page.

To install MiniProfiler type Install-Package MiniProfiler in the Package Manager Console.

So how do you wire it up to NPoco? That’s also very easy.

public class TheDatabase : Database
{
    public TheDatabase(string connectionStringName) 
        : base(connectionStringName) {}

    public override IDbConnection OnConnectionOpened(IDbConnection conn)
    {
        return new ProfiledDbConnection((DbConnection) conn, MiniProfiler.Current);
    }
}

Create a class that inherits from Database and override the OnConnectionOpened returning a new ProfiledDbConnection. Now instantiate your new class instead of Database when running queries.

As long as you have the @MiniProfiler.RenderIncludes() in your layout and MiniProfiler is started each request as per the installation instructions then your queries should be displayed in the top corner.

Let me know how you get on, and thank Sam for his awesome project. 
Adam

Currently I’m working on V2 which is available now on NuGet if you use the –Pre console switch or by selecting “Include Prerelease” in Visual Studio. This does not include the T4 templates. I do take pull requests if you like this feature of PetaPoco and want it in NPoco.

There is also a fair bit of documentation about NPoco located at the Wiki. It includes basic documentation of how to use the library as well as the features only in NPoco. Some of these include.

1. Query onto an existing object
2. One-to-Many query helpers
3. Mapping to Nested Objects query helpers
4. Dictionary<string, object> and object[] queries for dynamic results
5. Change tracking for updates
6. Composite Primary Key support
7. Queries that returns Multiple Result Sets
8. Fluent Mappings including Conventional based mappings
9. Version column support
10. IDatabase interface
11. Sql Templating

I’m still finalising all the documentation for these features, so if documentation doesn’t exist for one of the let me know and I will prioritise that feature.

The code is also available at Github, so go have a look, give it a try, send me a pull request and let me know how you get on.

Adam

** I did try to get my changes for PetaPoco merged back in to the main project, however I feel this will be better for my changes going forward.

Here is the simplest example.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
    });
}

This just tells the FluentMapping to scan the assembly with the type MvcApplication in it and any class it finds, define a mapping so that the object will can be persisted to PetaPoco. Now by itself this is pretty pointless because by default PetaPoco already does this for you on the fly. It will use the same defaults PetaPoco currently invokes which is

1. TableName = Type name
2. PrimaryKey = “ID”
3. PrimaryKeyAutoIncrement = on

You would also not want add every type in your project either as most of them will not be used for database.

Ok, so we don’t want all the types, but we want to filter them by their namespace. All my DB models are in Models/Db, which has the namespace MvcApplication.Models.Db so we will use this as the filter.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
        x.IncludeTypes(type => type.Namespace == "MvcApplication.Models.Db");
    });
}

The IncludeTypes method takes in a lambda expression with the Type as the argument and returns a bool. This method can also be used to exclude types.

So with all our DB models now being mapped using defaults, lets change them. Say that all our tables have the prefix “t_” on them because our DB admin likes hungarian notation. He also wants the primary key to auto increment (identity column) and the column name to end with “Id”. Ok mister Db man….you’re the boss.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
        x.IncludeTypes(type => type.Namespace == "MvcApplication.Models.Db");
        x.TablesNamed(type => "t_" + type);
        x.PrimaryKeysNamed(type => type + "Id");
        x.PrimaryKeysAutoIncremented(type => true);
    });
}

Too easy.

Next we can configure how columns are mapped. By default the column name will map to the property name, but it doesn’t have to. Our Db man says all columns should have the “d_” prefix if the column is a DateTime property, and the rest should remain the same as the property name.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
        x.IncludeTypes(type => type.Namespace == "MvcApplication.Models.Db");
        x.TablesNamed(type => "t_" + type);
        x.PrimaryKeysNamed(type => type + "Id");
        x.PrimaryKeysAutoIncremented(type => true);
        x.Columns.Named(prop =>
            prop.PropertyType == typeof(DateTime) ? "d_" + prop.Name : prop.Name);
    });
}

I think you get the idea. You can also Ignore properties, set a Version columns and set a Result column.

There are also two extension methods that set some smart defaults. These are:

1. Tables are the pluralized version of the Type name
2. PrimaryKeys are the Type name concatenated with “Id”
3. All complex properties (classes) are ignored

These can be set simply like this.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
        x.IncludeTypes(type => type.Namespace == "MvcApplication.Models.Db");
        x.WithSmartConventions();
    });
}

You can create your own extension methods as well. They are extremely easy. Check out the code for the WithSmartConventions to see how easy it is.

There is one last thing. Conventions are great, but if you can’t override them then they’re pretty useless, because there is always an exception to the rule. Mr Db man comes to me and says that there is one table which is used by 5 systems and does not play by our conventions we have setup. The Type name is Abc, but the table name needs to be “Abcies” not “Abcs”, and the primary key is “MyAbcId”. He also doesn’t want to map the Name column.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
        x.IncludeTypes(type => type.Namespace == "MvcApplication.Models.Db");
        x.TablesNamed(type => "t_" + type);
        x.PrimaryKeysNamed(type => type + "Id");
        x.PrimaryKeysAutoIncremented(type => true);
        x.Columns.Named(prop =>
            prop.PropertyType == typeof(DateTime) ? "d_" + prop.Name : prop.Name);

        x.OverrideMappingsWith(new MappingOverrides());
    });
}

public class MappingOverrides : Mappings
{
    public MappingOverrides()
    {
        For<Abc>().TableName("Abcies")
                    .PrimaryKey("MyAbcId")
                    .Columns(x => x.Column(y=>y.Name).Ignore());
    }
}

Any mappings defined in our mapping overrides will be overlayed on top of our conventions. We covered 99% of our mappings with these conventions but still needed to manually configure one table.

I hope you can see how powerful this can be, especially when you’re got more than 100 tables.

This is currently only available on my branch, which can be found https://github.com/schotime/PetaPoco and downloaded Schotime-PetaPoco-4.0.3.11.zip.

Let me know how you get on or if you have any issues.

Adam

Given these two classes with corresponding tables:

    public class Table1

    {

        public string Name { get; set; }

    }

    public class Table2

    {

        public string Col1 { get; set; }

        public int Col2 { get; set; }

    }

Both results can be retrieved in one call to the database like so:

    var result = db.FetchMultiple<Table1, Table2>("select name from table1;" +

                                                  "select col1,col2 from table2");

    var table1list = result.Item1;  //List<Table1>

    var table2list = result.Item2;  //List<Table2>

where result here is a Tuple<List<Table1>, List<Table2>>.

Here the semi-colon represents the separation of the two queries. This is the MS SQL server syntax so please check your database specific syntax to use multiple result sets.

If you don’t like returning a Tuple from this query you can provide your own callback to the FetchMultiple method. The callback for the above query would be of type. Func<List<Table1>, List<Table2>, ReturnClass>

The feature is currently only available in my master branch where you can find the download package here https://github.com/schotime/PetaPoco/downloads or directly https://github.com/downloads/schotime/PetaPoco/Schotime-PetaPoco-4.0.3.5.zip.

Hope you find it useful.

This feature has been possible in PetaPoco since version 4 as shown in the blog post by Brad, (http://www.toptensoftware.com/Articles/115/PetaPoco-Mapping-One-to-Many-and-Many-to-One-Relationships) however, custom mapping needed to be created each type. Therefore I decided to have a crack at a more generic way of mapping these.

You can grab it now and give it a try. Its available on NuGet under PetaPoco.RelationExtensions.

The extensions add two new methods to the Database class. They come with a variety of overloads for a multitude of generic arguments.

1. FetchOneToMany<>
2. FetchManyToOne<>

This is how it works:

var results1 = db.FetchOneToMany<BudgetPeriod, Expense>(x => x.BudgetPeriodId,
        "select b.*, e.* from budgetperiods b " +
        "   inner join expenses e on b.budgetperiodid = e.budgetperiodid");

var results2 = db.FetchManyToOne<Expense, BudgetPeriod, BudgetPeriod>(x => x.ExpenseId,
        "select e.*, b.*, b2.* from budgetperiods b " +
        "   inner join expenses e on b.budgetperiodid = e.budgetperiodid " +
        "   inner join budgetperiods b2 on e.budgetperiodid = b2.budgetperiodid ");

results1 will return a List<BudgetPeriod>

results2 will return a List<Expense>

There are some key things to remember when using these.

1. It is critical that the columns that you want to map into the class are in the same order you specify the generic arguments as seen in the results2 example above. eg. Expense, BP, BP –> e.*, b.*, b2.*
2. The first parameter is a lamda which should refer to the primary key of the first T argument.

Currently you can only have 1 one-to-many relation mapped and up to 3 many-to-one relations mapped in one go.

Enjoy

Adam

Therefore I give you PetaPoco.Glimpse. A simple NuGet package that brings it all together. Firstly create a new Asp.Net MVC 3 Project. Right click on References and select “Add Library Package Reference”. The following dialog will appear. Search for petapoco in the top right corner. It will look similar to below. Select PetaPoco.Glimpse and select Install. This will install PetaPoco.Core as well as Glimpse.

Once it downloads the package and installs it you are almost ready to go. Here is the simplest way to get under way, however usually you would create your own DB class which inherits DatabaseWithProfiling.

// "Peta" is the name of my connection string
var db = new DatabaseWithProfiling("Peta");
var data = db.Fetch<dynamic>("select id, name from users where id = @0", 1);

Placed the above code into the HomeController and run the application. Note: You will also have to turn Glimpse On by heading to /Glimpse/Config. Once done, head back to the default url and you see an Eye icon in the bottom right corner. Clicking this will result it the following output.

As you can see, the PetaPoco tab shows you all the details about the SQL’s that were run in the order they were run.

There are a few caveats on the SQL’s showing up.

1. Debug must be on.

2. You can force the logging by setting –> db.ForceLogging = true;

Please let me know if you have any queries and enjoy.

Adam

The other day I set out to build another way to configure the mappings for PetaPoco. Here is how you currently configure a mapping using attributes.

    [TableName("AttribPocos")]
    [PrimaryKey("Id")]
    public class attribpoco
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Result { get; set; }

        [Ignore]
        public int IgnoreColumn { get; set; }
    }

This is pretty easy and requires only a couple of attributes. After you do a few of these, sometimes I wish I could set all the mappings in one place, so here is the same mappings but with my new configuration. Method 1.

    public class MyMappings : PetaPocoMappings
    {
        public MyMappings()
        {
            For<attribpoco>()
                .TableName("AttribPocos")
                .PrimaryKey(x => x.Id)
                .Columns(x =>
                             {
                                 x.Ignore(y => y.IgnoreColumn);
                             });
        }
    }

So you can define multiple mappings here with new For<> statements. If you want to be more explicit and have one mapping class per mapping you can do the following. Method 2.

    public class AttribPocoMap : PetaPocoMap<attribpoco>
    {
        public AttribPocoMap()
        {
            TableName("AttribPocos");
            PrimaryKey(x => x.Id);
            Columns(x =>
                        {
                            x.Ignore(y => y.IgnoreColumn);
                        });
        }
    }

Both ways of mapping have the same API, so they can be interchanged, however when hooking the mappings into PetaPoco, things differ a little.

You need to configure the mappings just once in your application so a good place to do that in a web application is the Application_Start() method in the global.asax.cs. Here is how you do this for the first method of mapping configuration.

PetaPoco.FluentMappings.Configuration.Configure(new MyMappings());

For method 2 you have to be a little more explicit at the moment. For each mapping you need to include it as a parameter to Configure.

PetaPoco.FluentMappings.Configuration.Configure(new AttribPocoMap(),…);

Also, just because you have used the fluent mappings for one class, you can use the attribute mapping style at any time as it will automatically default back to the attribute mappings if you don’t have a fluent mapping configured. You also don’t have to define all the columns. By default all columns are mapped unless you are using explicit mappings or you have used the Ignore or Result column like above.

Currently all features supported by attributes in my branch are supported in the fluent mappings. eg. Sequences, Explicit Columns, Composite Keys, Versioning, Ignore Columns and Result Columns.

If you would like to give it a try you can download the my PetaPoco branch here:

https://github.com/schotime/PetaPoco/downloads

Let me know what you think.

Adam