DapperTests.cs

using System;
using System.Data;
using Dapper;
using Dapper.Contrib.Extensions;
using NUnit.Framework;
using ServiceStack.Text;

//using Dommel;

namespace DataAccess
{
    [TestFixture]
    public class DapperTests
    {
        private IDbConnection Db { get; set; }

        [TestFixtureSetUp]
        public void TestFixtureSetup()
        {
            // TODO: Find out why table names are pluralized and how to stop it!
            Db = new Database().Db;
        }

        [Test]
        public void DbGet()
        {
            var album = Db.Get<Album>(1);
            Console.WriteLine(album.Dump());
        }

        [Test]
        public void DbQuery()
        {
            var album = Db.Query<Album>("select * from albums");
            Console.WriteLine(album.Dump());
        }

        [Test]
        public void ArtistQuery()
        {
            var artists = Db.Query<Artist>("select * from artist");
            Console.WriteLine(artists.Dump());
        }

        [Test]
        public void DbSqlBuilder()
        {
            var title = "A%";
            var skip = 10;
            var take = 10;


            var builder = new SqlBuilder();
            // /**select**/  -- has to be low case
            var query = builder.AddTemplate(@"
                select ar.Name, replace(replace(Title, '[', '('), ']', ')') as Title
                , count(ar.ArtistId) as NumRecs from Albums al 
                join Artist ar on ar.ArtistId = al.ArtistId
                /**where**/
                group by ar.Name,al.ArtistId, Title
                --having count(ar.ArtistId) > 1 
                order by al.ArtistId              
                offset @skip rows fetch next @take rows only                
            ");           
            builder.Where("Title like @title");
            //sql.Where("Id > 0");
            builder.OrWhere("Id < 100000");            
            builder.AddParameters(new {title = title, skip= skip, take= take});
            Console.WriteLine(query.RawSql.Dump());

            var albums = Db.Query(query.RawSql, query.Parameters);
           
            Console.WriteLine(albums.Dump());
        }
    }
}