This post is the is part of series of posts covering the various microORMs in the .NET world. I intend to look at how each microORM meets certain data access and manipulation needs. The current series includes,
Moving swiftly on, if all our solutions only used different way to select data then how would we ever get data into our solution? So lets look at how these microORMs handle Inserts, Updates and Deletes1. These examples will follow on from previous ones so I am only going publish the key lines. If you want the complete code you can grab them from the GitHub project.
Dapper
Insert
Nothing unexpected here. More SQL. Sam Saffron previously pointed me in the direction of Dapper.Contrib that has extra extension methods for doing things like INSERT, UPDATE and DELETE but at the time of trying I couldn't get it to work and have an open issue on GitHub. If I get an update on this I'll post the slightly cleaner syntax.
conn.Open();
int count = conn.Execute(
"INSERT Authors(Username, FullName, CreatedDate) " +
"VALUES (@Username, @FullName, GETDATE())",
new { Username = "[email protected]", FullName = "James Hughes" });
Update
Update is very similar to insert. The nice thing about this approach vs. using POCOs is that you only need to send the delta of the object up where as with the other approaches you may have to fetch the object before updating.
int count = conn.Execute(
"UPDATE Authors SET FullName = @FullName WHERE Id = @Id",
new { FullName = "James Hughes", Id = 1 });
Delete
Delete is pretty much what you'd expect. I'll update these examples when I get my problems with Dapper.Contrib sorted but when you are so close to the SQL metal there isn't a massive amount to discuss.
int count = conn.Execute(
"DELETE FROM Authors WHERE Id = @Id",
new { Id = 1 });
Massive
Insert
Massive keeps things simple as usual. Insert can take POCOs as well as dynamic object (and a number of other types) and it uses the info found in the Authors DynamicModel class to generate the SQL for you.
var tbl = new Authors();
var x = tbl.Insert(new
{
Username = "[email protected]",
FullName = "James Hughes",
CreatedDate = DateTime.Now
});
As a bonus feature Massive can wrap multiple inserts in a single transaction and perform multiple inserts at once.
var tbl = new Authors();
var x = tbl.InsertMany( new [] {
{ Username = "[email protected]", FullName = "James Hughes", CreatedDate = DateTime.Now },
{ Username = "[email protected]", FullName = "James Hughes", CreatedDate = DateTime.Now },
{ Username = "[email protected]", FullName = "James Hughes", CreatedDate = DateTime.Now }
});
Update
Like Dapper, Massive allows us to send only a delta between the old and new object.
var tbl = new Authors();
var x = tbl.Update(new
{
FullName = "James Hughes"
}, 1);
Massive also gets extra points for the UpdateMany that, not suprisingly, behaves like the InsertMany method mentioned above (except for updates of course).
Delete
Ultra simple approach to Deleting specific objects just pass in an id and you're done,
var tbl = new Authors();
var x = tbl.Delete(2);
PetaPoco
Insert
PetaPoco offers 2 main ways to insert your data2. You can pass in a plain POCO (or in our case on that uses attributes to normalise the difference between the schema and the C# class),
PetaPoco.Database db = new PetaPoco.Database("DefaultConnectionString");
var x = db.Insert(new Author
{
Username = "[email protected]",
FullName = "James Hughes",
CreatedDate = DateTime.Now
});
Or you can pass in an anonymous object specifying the table and primary key field,
var x = db.Insert("Authors", "Id", new
{
Username = "[email protected]",
FullName = "James Hughes",
CreatedDate = DateTime.Now
});
Update
There are plenty of ways to update data using PetaPoco. The first one is to pass in your model object and the row will get updated. I am creating a new object in this example but you could just fetch it from the database as you'd expect,
PetaPoco.Database db = new PetaPoco.Database("DefaultConnectionString");
var x = db.Update(new Author
{
Id = 9,
Username = "[email protected]",
FullName = "James Hughes",
CreatedDate = DateTime.Now
});
Another approach is to just pass up a delta of the changes,
var x = db.Update("Authors", "Id", new
{
Id = 10,
FullName = "James Hughes"
});
Alternatively you can simply pass up the delta and specify the primary key value externally,
db.Update("Authors", "Id", new { FullName = "James Hughes" }, 12);
Variety is the spice of life in PetaPoco!
Delete
Thanks to the custom attribute on our Author class deleting a record is a matter of passing the Id to the Delete method,
db.Delete<Author>(8);
ServiceStack ORMLite
Insert
ORMLite is pretty much inline with the other microORMs a simple Insert method that accepts a POCO or an anonymous object and generates SQL based on the info given. This can be done in one of two ways,
using (IDbConnection db = Program.ConnectionString.OpenDbConnection())
using (IDbCommand cmd = db.CreateCommand())
{
cmd.Insert<Author>(new Author
{
Username = "[email protected]",
FullName = "James Hughes",
CreatedDate = DateTime.Now
});
}
Or the factory approach discussed in the first article,
IDbConnectionFactory dbFactory = new OrmLiteConnectionFactory(
Program.ConnectionString,
SqlServerOrmLiteDialectProvider.Instance);
dbFactory.Exec(dbCmd => dbCmd.Insert<Author>(new Author
{
Username = "[email protected]",
FullName = "James Hughes",
CreatedDate = DateTime.Now
}));
Update
ORMLite sticks with one approach for updating a row - passing in a POCO that it can map to a table/row,
cmd.Update(new Author
{
Id = 7,
Username = "[email protected]",
FullName = "James Hughes",
CreatedDate = DateTime.Now
});
If you really don't want to fetch your row again and only want to send up a delta you'll have to roll your own approach (simple enough though).
Delete
ORMLite supports a number of neat ways to delete rows,
DeleteById(id)Delete(where_clause, id)Delete(object)
cmd.DeleteById<Author>(5);
cmd.Delete<Author>("Id = @0", 6);
cmd.Delete(new Author { Id = 7 });
Simple.Data
Insert
Guess what Simple.Data's approach is once again very fluent, clean and easy to understand. OK I don't get Intellisense as the methods are dynamic but I didn't need it anyway.
Author x = Simple.Data.Database.Open().Author.Insert(new Author
{
Username = "[email protected]",
FullName = "James Hughes",
CreatedDate = DateTime.Now
});
Update
Simple.Data has 2 ways of updating. Update takes a POCO and maps the id to the specific row,
Simple.Data.Database.Open().Authors.Update(new Author
{
Id = 5,
Username = "[email protected]",
FullName = "James Hughes",
CreatedDate = DateTime.Now
});
Alternatively Simple.Data supports the delta approach too using UpdateById
Simple.Data.Database.Open().Authors.UpdateById(
Id: 6,
FullName: "James Hughes");
Delete
Finally Simple.Data has 2 ways to delete a record,
DeleteById(id)Delete(named_parameters)
Simple.Data.Database.Open().Authors.DeleteById(3);
Simple.Data.Database.Open().Authors.Delete(Id: 4);
There We Have It
OK OK I guess this post feels a bit rushed. I started out throwing lots of details into selects and stuff but this one just isn't up to par. I slowly discovered that I'd have to spend almost all my spare time covering all the aspects I wanted. So I cut it back to a kind of "Starter for 10" approach. Yeah thats right I'm leaving stuff up to you to ask and research :-P. Anyway in these basic examples no one ORM really stands out too much. Simple.Data is, as always, lovely and clean and PetaPoco offers a very flexible experience. I like the ability to send deltas instead of full objects back (in case you hadn't guessed) and I had a few annoyances around updates using Massive and Dapper but I think it was my own stupidity (I'll update when I investigate). If I had to choose between them I am still tending towards Simple.Data and PetaPoco (probably PetaPoco at this stage) but I do find all of them a much more pleasant experience vs. MyIbatis or NHibernate.
That should be enough to start anyone off on the right foot.
1 Hehehehe IUDs
2 You can pass a SQL string or a SQL builder as well but it's fairly straightforward so I'll not cover it here. See the original SELECT post for a SQL and SQL Builder example.