Arrival of David aka Micro Orm

If you have read my previous post, I have talked about how ORM can create mess with the application. And prior to that, I have written that it speeds up production like anything.

So, am I contradicting myself ???

No, I am just learning new things. I am using ORM, but now I am more careful while using it. That's it.

So, why Vietnam ???

My post is originally inspired by Ted Neward's blog about ORM. And the after-effect of that blog is in the blog of Jeff Atwood. These two blogs nailed the ORM like anything. So, now we have two things: ORM is good to increase productivity but bad when it comes to performance. And this gives birth to the Micro ORM.

As I remember, one of the first Micro ORMs is Dapper from the team of Stackoverflow. And in parallel, we have Massive from Rob Conery. Dapper came about because traditional ORM burned the server of Stackoverflow by continuously using 100% of CPU. This happens because of heavy access and complex queries generated by ORM. According to them, after that they rewrote the database access of the application, removed ORM, and used Dapper. And then they open-sourced it. And this is the only ORM which has better performance just after direct data access to the database. And Massive—it was just developed because Rob Conery is the kind of genius person who just likes to have one more open-source project under his belt.

There are a few others.

Obviously, Dapper is fastest according to the benchmarks available on their site. But I like Massive the most. And other than that, OrmLite is also quite good. I first like to talk about OrmLite and will talk about Massive in my next post.

Now, OrmLite is a wrapper around the System.Data namespace. And maybe this is the only ORM with table creation support. So, it does support code first development. It gives me the feeling of ORM without losing performance. So, now we dig into OrmLite.

If you have worked with classic data access, you must remember the data connection class and data command class. Then there is a string variable for the query, and the data command has that command text and command type. In that, we pass details as parameters to save ourselves from SQL injection. Good old days. I don't like this type of fuss that much, and every now and then I check over the connection. Writing queries in the database and then again in code.

This is the ORM which solves most of the problems. Here, I will explain how. I am using most of the examples from its site only. So, if you like to skip the topic here, you can, but I think if you like the above part, you will like the rest too. By the way, here is the link to OrmLite.

The first and best thing of OrmLite is that it creates tables. Though it provides only one-to-one mapping with the database, it still CAN create the TABLES. I am not a big fan of creating tables with queries. And after writing things on paper, you find very rare occasions where you need to write a query for creating a table. And also, I am more of a C# guy. Even when I started my journey with ORM with LINQ to SQL and Entity Framework, I went with reverse mapping. But after that, Entity Framework Code First came. That is awesome for creating tables for you. So, you can concentrate on the core logic. But again, it is Entity Framework, so!!!

Here is the sample of it. You can start with making POCO classes without worrying about your database.

public enum PhoneType {
  Home,
  Work,
  Mobile,
}
public enum AddressType {
  Home,
  Work,
  Other,
}
public class Address {
  public string Line1 { get; set; }
  public string Line2 { get; set; }
  public string ZipCode { get; set; }
  public string State { get; set; }
  public string City { get; set; }
  public string Country { get; set; }
}
public class Customer {
  public Customer() {
    this.PhoneNumbers = new Dictionary <PhoneType, string>();
    this.Addresses = new Dictionary <AddressType, Address>();
  }

  [AutoIncrement]
  public int Id { get; set; }

  public string FirstName { get; set; }
  public string LastName { get; set; }

  [Index(Unique = true)]
  public string Email { get; set; }

  public Dictionary <PhoneType, string> PhoneNumbers { get; set; }
  public Dictionary <AddressType, Address> Addresses { get; set; }
  public DateTime CreatedAt { get; set; }
}

Again, this supports all the data annotations from .NET 4.0. Here is an example of it.

public class Order {

  [AutoIncrement]
  public int Id { get; set; }

  [References(typeof(Customer))]
  public int CustomerId { get; set; }

  [References(typeof(Employee))]
  public int EmployeeId { get; set; }

  [StringLength(256)]
  public string ShippingAddress { get; set; }

  public DateTime? OrderDate { get; set; }
  public DateTime? RequiredDate { get; set; }
  public DateTime? ShippedDate { get; set; }
  public int? ShipVia { get; set; }
  public decimal Freight { get; set; }
  public decimal Total { get; set; }
}

So, it supports required, max length, and all others. I haven't checked all, though.

Now, the CRUD operations. It is easy as anything.

var dbFactory = new OrmLiteConnectionFactory(@"Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|App_DataDatabase1.mdf;Integrated Security=True;User Instance=True",SqlServerOrmLiteDialectProvider.Instance);

Here is the catch. I was unable to find a property to set the dialect provider after initiating the default blank constructor. But you can always go with this default example. Then, one just needs to remember those old days of connection creation and command creation and just fire up the command. But this is ORM. Things must be easy here. And yes, it is damn easy.

I have never seen this much classic use of extension methods before. OrmLite extends the functionality of the default class and makes our work easy as anything. Here is an example:

//Non-intrusive: All extension methods hang off System.Data.* interfaces
IDbConnection dbConn = dbFactory.OpenDbConnection()
IDbCommand dbCmd = dbConn.CreateCommand()

//Re-create all table schemas:
dbCmd.DropTable <OrderDetail>()
dbCmd.DropTable <Order>()
dbCmd.DropTable <Customer>()
dbCmd.DropTable <Product>()
dbCmd.DropTable <Employee>()

dbCmd.CreateTable <Employee>()
dbCmd.CreateTable <Product>()
dbCmd.CreateTable <Customer>()
dbCmd.CreateTable <Order>()
dbCmd.CreateTable <OrderDetail>()

dbCmd.Insert(new Employee { Id = 1, Name = "Employee 1" })
dbCmd.Insert(new Employee { Id = 2, Name = "Employee 2" })
var product1 = new Product { Id = 1, Name = "Product 1", UnitPrice = 10 }
var product2 = new Product { Id = 2, Name = "Product 2", UnitPrice = 20 }
dbCmd.Save(product1, product2)

var customer = new Customer
{
  FirstName = "Orm",
  LastName = "Lite",
  Email = "ormlite@servicestack.net",
  PhoneNumbers =
  {
    { PhoneType.Home, "555-1234" },
    { PhoneType.Work, "1-800-1234" },
    { PhoneType.Mobile, "818-123-4567" },
  },
  Addresses =
  {
    { AddressType.Work,
    new Address
      { Line1 = "1 Street", Country = "US", State = "NY", City = "New York", ZipCode = "10101" }
      },
    },
    CreatedAt = DateTime.UtcNow,
  }
}
dbCmd.Insert(customer)

var customerId = dbCmd.GetLastInsertId()
customer = dbCmd.QuerySingle <Customer>(new { customer.Email })
Assert.That(customer.Id, Is.EqualTo(customerId))

//Direct access to System.Data.Transactions:
using (var trans = dbCmd.BeginTransaction(IsolationLevel.ReadCommitted))
{
  var order = new Order
{
  CustomerId = customer.Id,
  EmployeeId = 1,
  OrderDate = DateTime.UtcNow,
  Freight = 10.50m,
  ShippingAddress = new Address { Line1 = "3 Street", Country = "US", State = "NY", City = "New York", ZipCode = "12121" },
}
dbCmd.Save(order)

order.Id = (int)dbCmd.GetLastInsertId()

var orderDetails = new[] {
  new OrderDetail
  {
    OrderId = order.Id,
    ProductId = product1.Id,
    Quantity = 2,
    UnitPrice = product1.UnitPrice,
  },
  new OrderDetail
  {
    OrderId = order.Id,
    ProductId = product2.Id,
    Quantity = 2,
    UnitPrice = product2.UnitPrice,
    Discount = .15m,
  }
}

dbCmd.Insert(orderDetails)

order.Total = orderDetails.Sum(x => x.UnitPrice * x.Quantity * x.Discount) + order.Freight

dbCmd.Save(order)

trans.Commit()
}

So, as you can see, it supports mighty POCOs and handles almost everything. If you have a question—can this be done by Entity Framework too? And even in a pretty nicer way? Then here is the answer: benchmark. Speed, performance, scalability—these are the things which make the difference.

Now, anyone can write code. I definitely don't mind that. But there are few who really write good code. And please, I am not saying about following standards of casing but about trimming things up, taking bold decisions, and following the rules of Martin Fowler.