Monthly Archives: May 2011
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.
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
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
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.
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.
As you can see the Glimpse team have made it super easy to create your own plugin. It literally takes minutes.
Enjoy
Adam
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
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
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.
- 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.
- 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.
- 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?
- 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.
- 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
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.
Increasing Event Binding Performance using Jquery Delegate
Today I was presented with a problem when binding click events to action icons. You know the scenario; you have a table with a list of data and one of the columns has icons/links like “edit”, “new”, “view”, “delete” etc. This is how I’m using the jQuery to bind the click event to the links like so.
$("a.action").click(function() { // perform tasks });
This worked great, however when the list grew to 50 or more items, IE (yes i know) started complaining that the script is running too long. Now at the moment I have 7 actions multiplied by 50 items in the list which means it was looping 350 times. It shouldn’t be that bad but before IE9 JS performance is woeful.
To fix this issue, I needed to reduce the amount of event bindings. This is where jQuery.delegate() comes in. The syntax is a little different but its pretty easy to understand.
$("#listtable").delegate("a.action", "click", function() { // perform tasks });
The selector in this case is the table where the links are contained within (children). The first parameter is the filter for the link that needs to be clicked. The second parameter is the event name. Custom events can be used here. And the third parameter is the callback function. So its pretty similar, however instead of creating 50 events, one for each row, I’m binding the event to the surrounding table element and then using the fact that the events bubble up through parent elements to catch the appropriate event.
IE happy, Adam happy!
Getting Back In Touch With SQL
I’ve always used native SQL for my applications. Approximately a year or so ago I finally decided to give NHibernate a look and see what all the hype was about. I gave it a test run on a new project as there is really nothing like real world requirements to show you what a tool is really made of.
It started off slowly, but was really enjoying the easy of which you could do inserts and updates. Even selects where pretty easy. At least at first.
As the system started to grow, the pain started. The queries were relatively straight forward, but the object graph needed to pull all the data together for a simple nested list was becoming convoluted to say the least. At once stage I think I spend almost a day trying to figure out a query, however at the time I was still learning so I knew there would be some pain but it was starting to become ridiculous.
Let me say though, I’m not entirely blaming NHibernate (or ORMS eg. EF) for the problems. The data interactions that I was trying to perform were pretty intense, with complex hierarchical relationships, but I knew there had to be a better way.
I started to switch most of the queries over to using session.CreateSqlQuery(sql) and using the AliasToBeanTransformer to project the query into a DTO that more closely represented how the data was to be displayed on the page.
It was at this time that CQRS was starting to gain some traction and it was all starting to make a lot of sense.
How you model the data is usually not how you need to display the data.
I couldn’t believe how simple everything was after separating the queries from the commands. It was definitely an “aha” moment!
I am currently not implementing CQRS in full (event sourcing, table per model, separate read store etc) but I think it is a great compromise.
I have started to put together a sample application of how I am doing Web Applications using CQRS-Lite and I will endeavour to blog about the details in the upcoming weeks. This will also include details on the new Micro-ORM PetaPoco that I have been helping Brad out on, and how much easier it has made things!
Adam