PetaPoco – One To Many and Many To One

PetaPoco is a great way to map results from a database. Usually these are flat objects, however sometimes its useful to map many-to-one/one-to-many relationship directly into a viewmodel or complex object.

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

Advertisements
Posted in .NET | Tagged , , , | 7 Comments

PetaPoco.Glimpse – NuGet Package

A couple of weeks ago I showed how we can use the awesome Glimpse project with PetaPoco to show all the SQL’s processed in the current request. The problem was there was a few things that needed to make it into both PetaPoco and Glimpse to support this. The PetaPoco changes were in place as of v3.0.2 (thanks Brad) and a bug fix I submitted to the Glimpse team was just merged into (thanks Nik and Anthony) v0.82.

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.

PetaPoco.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.

PetaPoco.Glimpse1

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

Posted in .NET | Tagged , , , | 5 Comments

Fluent PetaPoco – External Mappings

Update 20/Nov/2011: This has been now added to my master branch and is available here for download as of 4.0.3.5. https://github.com/schotime/PetaPoco/downloads

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

Posted in .NET | Tagged , , | 8 Comments

Creating Your Own Glimpse Plugin

Last weekend I created a Glimpse plugin for PetaPoco. It was surprisingly easy. In this post I’ll show you how to create your own.

Please note as Glimpse is still in Beta, this example may change. but I will endeavour to update the post when and if the interface changes. This plugin is based on Glimpse 0.81.

Here is the complete code for our first plugin.

    [GlimpsePlugin(ShouldSetupInInit=true)]
    public class MyFirstGlimpsePlugin : IGlimpsePlugin
    {
        private int MaxValue;

        public object GetData(HttpApplication application)
        {
            // Return the data you want to display on your tab
            var data = new List<object[]> {new[]{ "Column1", "Column2", "Column3" }};

            for (int i = 0; i < MaxValue; i++)
            {
                // Usually get your data from application.Context.Items
                data.Add(new object[] {"Data " + i, 10*i, DateTime.Now.AddHours(i)});
            }

            return data;
        }

        public void SetupInit(HttpApplication application)
        {
            // Perform an initialisation that needs to be done.
            // Run once at startup
            MaxValue = 5;
        }

        public string Name
        {
            get { return "MyFirstPlugin"; }
        }
    }

There are three parts.

1. The Name get accessor: This is the name of the tab.

2. SetupInit method: This method will run once when the Plugin is loaded. Perform any initialisation code here.

3. GetData method: This is where the action happens. All data returned from here will be serialized into JSON and sent to the client to be rendered by the Glimpse client side framework. How it is exactly rendered depends on whether you return arrays or objects or arrays of objects etc. Initial details for this can be found on the Protocol page. Usually you would put the data you need to display in the HttpContext.Items dictionary in part of your application. You can then pull the data out using the application variable provided to you in the GetData method.

For example

-> data goes in:  HttpContext.Current.Items[“test”] = “Some data”;

-> data comes out:  var testdata = application.Context.Items[“test”]

The following is rendered by Glimpse.

Glimpse-firstPlugin

As you will see, it doesn’t matter what data you put in what column as Glimpse will do its best to render the data in the most approriate way. If I change the GetData method to be:

        public object GetData(HttpApplication application)
        {
            // Return the data you want to display on your tab
            var data = new List<object[]> {new[]{ "Column1", "Column2", "Column3" }};

            for (int i = 0; i < MaxValue; i++)
            {
                // Usually get your data from application.Context.Items
                data.Add(new object[] {"Data " + i, 10*i, DateTime.Now.AddHours(i)});
            }

            var nestedData = new Dictionary<string, object>
                                 {
                                     {"NestKey1", "NestedValue"},
                                     {"NestKey2", 2 },
                                     {"NestKey3", DateTime.Now }
                                 };

            data.Add(new object[] { "Data Nested", 60, nestedData });

            return data;
        }

the following is rendered by Glimpse.

Glimpse-firstPlugin2

As you can see the Glimpse team have made it super easy to create your own plugin. It literally takes minutes.

Enjoy

Adam

Posted in .NET | Tagged , , | 5 Comments

A Glimpse into PetaPoco

Last week I blogged about upcoming performance statistics that enable you to see all the Sql’s that were executed by PetaPoco in the current request.

It was also just over a week ago that I learned about Glimpse from this Mix video by Scott Hanselman. And wow what an impact. Anthony van der Hoorn (@anthony_vdh) and Nik Molnar (@nikmd23) had created a wonderful diagnostics system for Asp.net MVC. Firebug for the server if you will.

I then wondered if I could somehow plug PetaPoco diagnostics into the display for Glimpse and it was unbelievably easy. Implementing a single interface was all it took. Less than 50 lines of code in total to produce the following results. I will show you how to create your own tab in a future post. Update: Creating Your Own Glimpse Plugin

petapoco_glimpse2

This gives a pretty clear display of exactly what queries were performed during the current request.

Another powerful feature of Glimpse is that it saves the last n (default 5) requests so it is indeed possible to get access to the Sql’s performed when posting data back to the server.

It really is a credit to the guys for a wonderful project which still hasn’t reached a V1.

My aim is to have both the integration to Glimpse and html source statistics available within the week but it all depends on whether the integration points make it into the main branch before hand.

Update (01/06/2011): This is now available as a package from NuGet called PetaPoco.Glimpse.

Until then, check out Glimpse (on NuGet as well) and support it by providing valuable feedback to the team.

Adam

Posted in .NET | Tagged , , , , | 1 Comment

PetaPoco – Why I’m Using A Micro-ORM

As I mentioned a few posts ago, in the last couple of years I moved away from writing native SQL to a full blown ORM and back again. Well not all the way back. Here is where PetaPoco comes in.

I had been using my own SQL to Object mapper for a while before I stumbled over PetaPoco, and it had very similar ideas to mine. It has gone one step further though, with nice support for paging, inserts, updates, deletes and multiple database support.

Since forking the repository at Github, I have implemented and suggested many changes that have made it back (thanks to Brad) into the main repository. Some of these include support for Oracle (including sequences for PK), DB field to Object field name conventions (and the ability to customize these per project).

So why have I come the full circle on this.

  1. Well firstly, SQL is a great query language for RDMS databases. It is very very powerful and I don’t think that it is all that difficult to learn.
  2. Just give me my data. I just want to create a sql query and map it to my object so I can use it. I don’t want to have explicit mapping files/code.
  3. The pain is just not worth it IMO. Why should I have to spend excess time trying to figure out if this query is going to produce a select n+1 or that I have to traverse 4 deep to get a piece of data?
  4. One file. Its not 1 DLL or 10 DLLs. Its one cs file that you just drop in to your project. It doesn’t get much simpler than that.
  5. Performance. I just don’t have to worry that I’m losing any performance between the database and my object. If there are performance issues, its either the query or the business logic.

And those are just some of the reasons.

To see how to use the basic features of PetaPoco, please visit the PetaPoco home page.

At the moment my fork contains a few extra features that are not currently in Brad’s repository. These include multiple primary key support for legacy database, concurrency using a version column, an OnCommandExecuted extension point and a IDatabase interface. I also have the PetaPoco.cs file set to “No Dynamic” at the moment as I’m using it with .NET 3.5. To enable the dynamic object mapping support, comment out the follow line (currently line 11).

#define PETAPOCO_NO_DYNAMIC

In future blog posts I intend to go through a few examples of how I have using it and how the extension points enable you to do some pretty interesting things.

Now I know this won’t be for everyone but unless forced, I won’t be going back to a full blown ORM.

Adam

Posted in .NET | Tagged , , | 8 Comments

PetaPoco Performance Statistics

The other day Sam from stackoverflow blogged about a slow page caused by a combination of problems resulting in them using Dapper to map native SQL to the objects resulting in a nice performance improvement.

I liked how he made the queries executed for the current request display in html comments in the html source, so I thought I would see if I could implement the same thing with PetaPoco. I intend to go through how I implemented the following in future posts (after I go through some basics), but for the moment I’ll give you a quick screen grab of what it looks like. Thanks goes to Sam for providing the inspiration.

petapoco_db_stats

Posted in .NET | Tagged , , | 1 Comment