title: Dapper micro ORM, database agnostic and MySql Guid type
body: <p>I am experimenting <a href="http://code.google.com/p/dapper-dot-net/" rel="nofollow">Dapper</a> on a pet project. I am using <a href="http://www.sqlite.org/" rel="nofollow">SQLite</a> to run all the tests and MySql for "production". However I am not sure how to best use Dapper to handle database agnostic situation.</p>
  
  <p>The particular problem I am having is with MySql which <strong>doesn't support Guid type</strong> for primary key therefore I am using <strong>varchar(40)</strong> as the type (SQLite supports unique identifier which is a guid). So here comes the problem if I have a generic repository as below I would get into trouble when trying to select from MySql database. Because the type of Id property is Guid and Dapper will throw "<strong>Error parsing column 10</strong>" because varchar type doesn't match guid type.</p>
  
  <p>If I change the Id property from guid to int then the raw sql inside the GetById would be even trickier which I am not sure how to even write. It will be something like, 1. start transaction, 2. insert, 3. select last inserted id and return it. So am I going to go with if database type is mysql, then use <a href="http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id" rel="nofollow">last_insert_id</a>, or if it's sqlite then use <a href="http://www.sqlite.org/c3ref/last_insert_rowid.html" rel="nofollow">last_insert_rowid</a>? Because the raw sql syntax would be quite different from database to database...</p>
  
  <pre><code>public IEnumerable&lt;T&gt; GetById(Guid id) //convention: Id is always of type Guid.
  {
    return UnitOfWork.DbConnection.Query&lt;T&gt;(
        string.Format(
          "select * from {0} where Id = @Id", typeof (T).Name), new {Id = id});
  }
  </code></pre>
  
  <p>Other examples would be to limit number of rows returned (especially for paging) and so on. So how am I going to write database agnostic raw sql queries with dapper? And maybe in my situation Dapper isn't suitable? Perhaps I should use the same old NHibernate here. Any suggestions? Am I doing it wrong? Thanks!</p>