there is a need for them

Serdar

The way I see this it belongs in a different layer. Dapper is responsible for data mapping not SQL statement generation.

I am interested on another layer on top of dapper that does expression -> SQL generation and provides helper for SQL generation, this is a separate file / project.

I mean only crud helpers to simplfy crud operations like select helpers, no need a sql generation system.

maybe simple helpers to partly abstract low level ado.net executescalar and executenonquery commands and releated classes. If I use dapper dot net in a project, select operations will look sweet, but crud operations will seem like a page from history in the same project.

How about if we start a dapper.contrib project with helper methods like this, then we ship that for people who want this kind of stuff

dapper.contrib sounds like a good idea. If you make SqlMapper partial you can have an dapper.contrib project that just links in the core SqlMapper (perhaps a nuget "source" reference) and then have different partial classes which users can choose to include for edge cases or features just not "baked" yet. Would also make it easy to merge in new features to the core/official distribution. And all in dapper.contrib should be considered subject to change / use at own risk.

good idea :) thanks

I created a very simple Insert extension which suits my needs. See code at http://bit.ly/m0p70p if you're interested. Dapper itself does all the heavy lifting, I'm just reflecting over an object and building sql, sending it over to Execute() ;)

Based on some feedback I got on my blog, I refactored a bit and added crude Update() and Delete() extensions as well. See http://weblogs.asp.net/jdanforth/archive/2011/05/11/refactored-dapper-extensions.aspx and please comment or better yet, fork and refactor :)

Johan ... I like the direction but the API needs some work, it is fairly risky as it is not using params, this will cause a leak.

I need to do a dapper refactor to allow for another way to pass in params

Here are my ideas on an API:

var post = cnn.Get(id); // will get a post object, I think that it should be proxied using linfu like tricks, so we track which columns change for Update

newPost.Id = cnn.Insert(newPost); // at the end of the process, probably update newPost.Id as

bool success = cnn.Update(post); // will update all fields on a post using Id as key - returns true if rowcount is 1 false if 0 - if the object is proxies will only update changed fields

bool success = cnn.Update(post.Id, new {post.Title}); // alternative update syntax : TODO, decide if we want batch update support.

bool success = cnn.Delete(post) // will delete a post with the post Id

Convention 1. table name is inferred from the class with a trivial pluralizer (Post -> Posts) ... allow override with an attribute decoration 2. primary key column is assumed to be Id. ... allow override with an attribute decoration, or some sort of global setting

Sam, I see what you mean and I agree. Re table name overrides and id's, the existing Table- and Key-attributes in System.Components.DataAnnotations should work?

So you will try to remove the sql string in-parameter completely to reduce leak-risks?

@Johan ... see my test re: DynamicParameters ... the api I designed above should be fairly easy to implement now

@Sam cheers, will do as soon as I get an hour off.

@Sam, I started looking at a simple solution for Get(id), but should it support a single id only :

cnn.Get(1);

or something like this:

cnn.Get(new {Id = 1, Type = 2});

The linfu style proxying sounds cool, but I've never built something like that before so I'm not sure I could pull it off. would love to try though :)

@Johan ... I would only worry about non-composite primary keys for now ... its by far the common case.

I just pushed a spike of get, insert, update and delete, pretty much according to your ideas of an API, to my clone at http://code.google.com/r/johandanforth-dapper-dot-net-contrib/

The get-method is generating a very simple intercepting proxy from an interface, not a concrete class. It's intercepting set properties and does a simple "isdirty-tracking" which the update-method is looking at before trying to update. Will try to do a virtual property override proxy from concrete class in the next days if you like.

Would like to discuss your thoughts in the insert-method as it's running inside a transaction now and returning identity via @@identity (as identity_scope is not working with sqlce). I don't know of a better way of returning the identity of inserted records, but would like to get feedback.

Tried to do a simple type/property cache as well, for better performance, but I'm sure it can be done better :)

Had to make FastExpando public to get the values via property name inside the Get-method.

Cheers, Johan

I merged this in, FastExpando is castable to IDictionary so that sorts that one out.

I slightly improved caching for Get and removed the class proxy for now, it makes sense to have one if properties are all virtual on the underlying class.

We need to think about the update api for non-tracked objects and somehow allow the delete api to take in an Id only.

Perf is really good for Get which is a win

Thanks heaps ... ping me when you want me to pull in changes, be sure to pull the latest version of dapper prior to doing a pull request

I just pushed support for table-attribute on POCO classes to my clone. Should work with ANY class-attribute named "TableAttribute" that has a "Name" property for it's value (I supplied one in contrib, and also tested with the Table-attribute in EntityFramework). Cleaned up ProxyGenerator for readability.

The name-getting for a table is also being cached for perf.

Will try to look at the class proxy later this week :)

Cheers, Johan

pulled that change in a while back

Hey guys, really good stuff you're doing.

Just my 2c if you don't mind... Would be good if there was a way to map primary key/foreign keys and other useful conversion explicitly, thus avoiding polluting POCO objects with attributes.

If you've got this covered without attributes, then please ignore me. But, if not...

My immediate thought (based on Fluent NHibernate) would be something like this...

private static Dictionary> _maps; public static EntityMap GetEntityMap(this IDbConnection connection) { var t = typeof(T); if(_maps.ContainsKey(t)) return _maps[t] as EntityMap;

var map = new EntityMap(); map.Type = t; _maps.Add(t, map);

return map; }

public class EntityMap { public Type Type {get; private set;} public string Id {get;set;} public string TableName {get;set;}

public EntityMap Name(string name) { TableName = GetName(name); return this; }

public EntityMap Id(Expression> primaryKey) { Id = GetName(primaryKey); return this; }

private string GetName(Expression> exp) { return (exp.Body as MemberExpression).Member.Name); } }

Then you could do something like..

conn.GetEntityMap().Name("tblOrders").Id(o => o.OrderId);

Like I said, just my 2c, and I obviously bow to your performance tuning abilities. :)

Cheers Paul

It would be useful to add an InsertMultiple extension, as currently bulk loading is inefficient with large datasets (will execute many single INSERT statements instead of only one with many values).

I saw that the Dapper.Contrib/SqlMapperExtensions made it into the dapper project, but they aren't making it into nuget. Are the extensions going to be part of the Dapper nuget or is it going to be added as Dapper.Contrib to nuget? Either way, I think it would be a valuable contribution.

Sam will have to answer that in detail, but my guess is he thinks it is not stable or field-tested enough yet for nuget :)

/Johan

DapperExtensions exists as it's own nuget package currently.

Install-Package DapperExtensions

That DapperExtensions in nuget may work well, but as far as I can see, it is not the same code as, and not based on, the SqlMapperExtensions.cs in this project.

See https://github.com/SamSaffron/dapper-dot-net/blob/master/Dapper.Contrib/SqlMapperExtensions.cs

Does DapperExtensions play well with Dapper.Rainbow?

@cecil

I have no idea how they would play together, Dapper Extensions is not mine. I would use one or the other.

also see: http://samsaffron.com/archive/2012/01/16/that-annoying-insert-problem-getting-data-into-the-db-using-dapper