insert/update/delete helpers

[GoogleCodeExporter]

there is a need for them

Serdar

Original issue reported on code.google.com by irmakser...@gmail.com on 13 Apr 2011 at 9:18

[GoogleCodeExporter]

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.  

Original comment by sam.saff...@gmail.com on 26 Apr 2011 at 4:09

[GoogleCodeExporter]

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

Original comment by irmakser...@gmail.com on 26 Apr 2011 at 6:50

[GoogleCodeExporter]

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. 

Original comment by irmakser...@gmail.com on 26 Apr 2011 at 9:48

[GoogleCodeExporter]

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 

Original comment by sam.saff...@gmail.com on 28 Apr 2011 at 5:59

[GoogleCodeExporter]

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.

Original comment by mattias%...@gtempaccount.com on 28 Apr 2011 at 11:27

[GoogleCodeExporter]

good idea :) thanks

Original comment by irmakser...@gmail.com on 28 Apr 2011 at 10:01

[GoogleCodeExporter]

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() ;)

Original comment by johan.da...@gmail.com on 10 May 2011 at 5:25

[GoogleCodeExporter]

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 :)

Original comment by johan.da...@gmail.com on 10 May 2011 at 10:49

[GoogleCodeExporter]

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<Post>(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>(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 

Original comment by sam.saff...@gmail.com on 11 May 2011 at 2:12

[GoogleCodeExporter]

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?

Original comment by johan.da...@gmail.com on 11 May 2011 at 8:15

[GoogleCodeExporter]

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

Original comment by sam.saff...@gmail.com on 11 May 2011 at 12:40

[GoogleCodeExporter]

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

Original comment by johan.da...@gmail.com on 11 May 2011 at 8:24

[GoogleCodeExporter]

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

cnn.Get<Post>(1);

or something like this:

cnn.Get<Post>(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 :)

Original comment by johan.da...@gmail.com on 14 May 2011 at 8:25

[GoogleCodeExporter]

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

Original comment by sam.saff...@gmail.com on 15 May 2011 at 11:48

[GoogleCodeExporter]

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

Original comment by johan.da...@gmail.com on 21 May 2011 at 9:47

[GoogleCodeExporter]

I merged this in, FastExpando is castable to IDictionary<string,object> 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 

Original comment by sam.saff...@gmail.com on 23 May 2011 at 2:55

[GoogleCodeExporter]

Original comment by sam.saff...@gmail.com on 23 May 2011 at 12:31

• Changed state: In-progress

[GoogleCodeExporter]

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

Original comment by johan.da...@gmail.com on 23 May 2011 at 10:02

[GoogleCodeExporter]

pulled that change in a while back 

Original comment by sam.saff...@gmail.com on 29 May 2011 at 11:59

[GoogleCodeExporter]

Original comment by sam.saff...@gmail.com on 1 Jun 2011 at 9:09

• Changed state: DapperContribOpen

[GoogleCodeExporter]

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<Type, EntityMap<object>> _maps;
public static EntityMap<T> GetEntityMap(this IDbConnection connection)
{
  var t = typeof(T);
  if(_maps.ContainsKey(t)) return _maps[t] as EntityMap<T>;

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

  return map;
}

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

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

  public EntityMap<T> Id(Expression<Func<T, object>> primaryKey)
  {
    Id = GetName(primaryKey);
    return this;
  }

  private string GetName(Expression<Func<T, object>> exp)
  {
    return (exp.Body as MemberExpression).Member.Name);
  }
}

Then you could do something like..

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

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

Cheers
Paul

Original comment by pasand...@gmail.com on 12 Jun 2011 at 6:52

[GoogleCodeExporter]

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

Original comment by trader...@gmail.com on 10 Sep 2011 at 4:01

[GoogleCodeExporter]

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.

Original comment by bdsut...@gmail.com on 17 Oct 2011 at 3:02

[GoogleCodeExporter]

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

Original comment by johan.da...@gmail.com on 18 Oct 2011 at 7:28

[GoogleCodeExporter]

DapperExtensions exists as it's own nuget package currently.

Install-Package DapperExtensions

Original comment by ch...@marisic.com on 1 Dec 2011 at 7:42

[GoogleCodeExporter]

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/SqlMappe
rExtensions.cs

Original comment by johan.da...@gmail.com on 2 Dec 2011 at 9:18

[GoogleCodeExporter]

Does DapperExtensions play well with Dapper.Rainbow?

Original comment by cecilphi...@gmail.com on 13 Jan 2012 at 1:17

[GoogleCodeExporter]

@cecil 

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

Original comment by sam.saff...@gmail.com on 22 Jan 2012 at 11:39

• Changed state: Done

[GoogleCodeExporter]

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

Original comment by sam.saff...@gmail.com on 22 Jan 2012 at 11:44