Dapper Extension Methods (via https://github.com/mgroves/)

namespace StatementProcessor.DataAccess.Reference
{
    using System.Collections.Generic;
    using System.Data.SQLite;
    using System.Linq;

    using Dapper;

    using Microsoft.VisualStudio.TestTools.UnitTesting;

    [TestClass]
    public class DapperReference
    {
        SQLiteConnection connection;

        [TestInitialize]
        public void TestSetup()
        {
            connection = new SQLiteConnection("DataSource=:memory:");
            connection.Open();
        }

        [TestCleanup]
        public void TestTeardown()
        {
            connection.Close();
        }

        private void DapperExtentions()
        {
            CommandDefinition definition = new CommandDefinition();
            connection.Execute(definition);
            connection.ExecuteScalar(definition);
            connection.ExecuteScalar<dynamic>(definition);
            connection.Query<dynamic>(definition);
            connection.QueryMultiple(definition);
            // along with Async versions
        }

        [TestMethod]
        public void ExecuteExample()
        {
            connection.Execute("CREATE TABLE TestTable_0 (Field1 INTEGER, Field2 INTEGER);");
            connection.Execute("INSERT INTO TestTable_0 (Field1, Field2) VALUES (42, 99);");

            int count = connection.ExecuteScalar<int>("SELECT COUNT(1) FROM TestTable_0;");

            Assert.AreEqual(1, count);

            connection.Execute("UPDATE TestTable_0 SET Field2 = 998");

            int field2 = connection.ExecuteScalar<int>("SELECT Field2 FROM TestTable_0;");
            Assert.AreEqual(998, field2);

            connection.Execute("DELETE FROM TestTable_0");
            count = connection.ExecuteScalar<int>("SELECT COUNT(1) FROM TestTable_0;");
            Assert.AreEqual(0, count);

            connection.Execute("DROP TABLE TestTable_0");
        }

        [TestMethod]
        public void ExecuteCanUseParameters()
        {
            connection.Execute("CREATE TABLE Person (Name TEXT, Age INTEGER);");
            connection.Execute("INSERT INTO Person (Name, Age) VALUES (@Name, @Age)",
                                new { Name = "Hawthorn", Age = 42 });

            int count = connection.ExecuteScalar<int>("SELECT COUNT(1) FROM Person");
            Assert.AreEqual(1, count);

            connection.Execute("DROP TABLE Person");
        }

        [TestMethod]
        public void ExecuteCanUseTransactions()
        {
            connection.Execute("CREATE TABLE Person (Name TEXT, Age INTEGER);");

            using (SQLiteTransaction transaction = connection.BeginTransaction())
            {
                connection.Execute("INSERT INTO Person (Name, Age) VALUES (@Name, @Age)",
                                new { Name = "Hawthorn", Age = 42 });
                transaction.Commit();
            }

            using (SQLiteTransaction transaction = connection.BeginTransaction())
            {
                connection.Execute("INSERT INTO Person (Name, Age) VALUES (@Name, @Age)",
                                new { Name = "Claymore", Age = 42 });
                transaction.Rollback();
            }

            int count = connection.ExecuteScalar<int>("SELECT COUNT(1) FROM Person");
            Assert.AreEqual(1, count);

            connection.Execute("DROP TABLE Person");
        }

        [TestMethod]
        public void UsingQueryDynamic()
        {
            connection.Execute("CREATE TABLE Person (Name TEXT, Age INTEGER);");
            connection.Execute("INSERT INTO Person (Name, Age) VALUES (@Name, @Age)",
                                new { Name = "Hawthorn", Age = 42 });

            IEnumerable<dynamic> result = connection.Query("SELECT Name, Age FROM Person");

            Assert.AreEqual(1, result.Count());
            Assert.AreEqual("Hawthorn", result.First().Name);
            Assert.AreEqual(42, result.First().Age);

            connection.Execute("DROP TABLE Person");
        }

        [TestMethod]
        public void UsingQueryT()
        {
            connection.Execute("CREATE TABLE Person (Name TEXT, Age INTEGER);");
            connection.Execute("INSERT INTO Person (Name, Age) VALUES (@Name, @Age)",
                                new { Name = "Hawthorn", Age = 42 });

            IEnumerable<Person> result = connection.Query<Person>("SELECT Name, Age FROM Person");

            Assert.AreEqual(1, result.Count());
            Assert.AreEqual("Hawthorn", result.First().Name);
            Assert.AreEqual(42, result.First().Age);

            connection.Execute("DROP TABLE Person");
        }

        [TestMethod]
        public void MismatchedColumnNames()
        {
            connection.Execute("CREATE TABLE Person (Name TEXT, Age INTEGER);");
            connection.Execute("INSERT INTO Person (Name, Age) VALUES (@Name, @Age)",
                                new { Name = "Hawthorn", Age = 42 });

            IEnumerable<Person> result =
                connection.Query<Person>("SELECT Name AS FirstName, Age FROM Person");

            Assert.AreEqual(1, result.Count());
            Assert.IsNull(result.First().Name);
            Assert.AreEqual(42, result.First().Age);

            connection.Execute("DROP TABLE Person");
        }

        [TestMethod]
        public void MultiInsert()
        {
            connection.Execute("CREATE TABLE Person (Name TEXT, Age INTEGER);");
            connection.Execute("INSERT INTO Person (Name, Age) VALUES (@Name, @Age)",
                                new[]
                                {
                                    new { Name = "Hawthorn", Age = 42 },
                                    new { Name = "Claymore", Age =  50 },
                                    new { Name = "Saul", Age =  37 },
                                    new { Name = "Livingstone", Age =  55 },
                                    new { Name = "Morton", Age = 35 },
                                    new { Name = "Stanley", Age = 48 },
                                });

            int count = connection.ExecuteScalar<int>("SELECT COUNT(1) FROM Person");

            Assert.AreEqual(6, count);

            connection.Execute("DROP TABLE Person");
        }

        [TestMethod]
        public void UsingInKeyword()
        {
            connection.Execute("CREATE TABLE Person (Name TEXT, Age INTEGER);");
            connection.Execute("INSERT INTO Person (Name, Age) VALUES (@Name, @Age)",
                                new[]
                                {
                                    new { Name = "Hawthorn", Age = 42 },
                                    new { Name = "Claymore", Age =  50 },
                                    new { Name = "Saul", Age =  37 },
                                    new { Name = "Livingstone", Age =  55 },
                                    new { Name = "Morton", Age = 35 },
                                    new { Name = "Stanley", Age = 48 },
                                });

            int count = connection.ExecuteScalar<int>(
                                    "SELECT COUNT(1) FROM Person WHERE Age IN @Ages",
                                    new { Ages = new[] {35, 55 }  });

            Assert.AreEqual(2, count);

            connection.Execute("DROP TABLE Person");
        }

        [TestMethod]
        public void MultiMapDefaultConvention()
        {
            connection.Execute(@"CREATE TABLE ComicBookCompanies (
                                    ComicBookCompanyID INTEGER PRIMARY KEY AUTOINCREMENT,
                                    CompanyName TEXT);");
            int dcID
                = connection.ExecuteScalar<int>(@"INSERT INTO ComicBookCompanies
                                                    ( CompanyName ) VALUES ( 'D.C.' );
                                                    SELECT last_insert_rowid();");

            connection.Execute(@"CREATE TABLE Heroes (
                                        HeroID INTEGER PRIMARY KEY AUTOINCREMENT,
                                        ComicBookCompanyID INTEGER,
                                        HeroName TEXT);");

            connection.Execute(@"INSERT INTO Heroes
                                    ( ComicBookCompanyID, HeroName )
                                    VALUES
                                    ( @ComicBookCompanyID, @HeroName );",
                                    new[]{  new { ComicBookCompanyID = dcID, HeroName = "Batman" },
                                            new { ComicBookCompanyID = dcID, HeroName = "Superman"} });

            // NOTE: Use of "AS Id" to allow Dapper to default to using Id
            //          to split the returned columns into objects
            string sql = @"SELECT h.HeroID AS Id, h.HeroName,
                                    c.ComicBookCompanyID AS Id, c.CompanyName
                                FROM Heroes h
                                    INNER JOIN ComicBookCompanies c
                                        ON c.ComicBookCompanyID = h.HeroID";

            // connection.Query<FirstObject, SecondObject, ReturnObject>
            Hero firstHero =
                    connection.Query<Hero, ComicBookCompany, Hero>(sql,
                            (hero, company) =>
                            {
                                hero.ComicBookCompany = company;
                                return hero;
                            }).First();

            Assert.AreEqual("Batman", firstHero.HeroName);
            // Assert.AreEqual(0, firstHero.HeroID);   // Don't like this
            Assert.AreEqual("D.C.", firstHero.ComicBookCompany.CompanyName);
            // Assert.AreEqual(0, firstHero.ComicBookCompany.CompanyID); // Or this

            connection.Execute("DROP TABLE ComicBookCompanies");
            connection.Execute("DROP TABLE Heroes");
        }

        [TestMethod]
        public void MultiMapCustomSplit_0()
        {
            connection.Execute(@"CREATE TABLE ComicBookCompanies (
                                    ComicBookCompanyID INTEGER PRIMARY KEY AUTOINCREMENT,
                                    CompanyName TEXT);");
            int dcID
                = connection.ExecuteScalar<int>(@"INSERT INTO ComicBookCompanies
                                                    ( CompanyName ) VALUES ( 'D.C.' );
                                                    SELECT last_insert_rowid();");

            connection.Execute(@"CREATE TABLE Heroes (
                                        HeroID INTEGER PRIMARY KEY AUTOINCREMENT,
                                        ComicBookCompanyID INTEGER,
                                        HeroName TEXT);");

            connection.Execute(@"INSERT INTO Heroes
                                    ( ComicBookCompanyID, HeroName )
                                    VALUES
                                    ( @ComicBookCompanyID, @HeroName );",
                                    new[]{  new { ComicBookCompanyID = dcID, HeroName = "Batman" },
                                            new { ComicBookCompanyID = dcID, HeroName = "Superman"} });

            string sql = @"SELECT h.HeroID, h.HeroName,
                                    c.ComicBookCompanyID, c.CompanyName
                                FROM Heroes h
                                    INNER JOIN ComicBookCompanies c
                                        ON c.ComicBookCompanyID = h.HeroID";

            // connection.Query<FirstObject, SecondObject, ReturnObject>
            // "splitOn" can be a list
            Hero firstHero =
                    connection.Query<Hero, ComicBookCompany, Hero>(sql,
                            (hero, company) =>
                            {
                                hero.ComicBookCompany = company;
                                return hero;
                            }, splitOn: "ComicBookCompanyID")
                            .First();

            Assert.AreEqual("Batman", firstHero.HeroName);
            Assert.AreEqual("D.C.", firstHero.ComicBookCompany.CompanyName);

            connection.Execute("DROP TABLE ComicBookCompanies");
            connection.Execute("DROP TABLE Heroes");
        }

        [TestMethod]
        public void MultiMapCustomSplit_1()
        {
            connection.Execute(@"CREATE TABLE ComicBookCompanies (
                                    ComicBookCompanyID INTEGER PRIMARY KEY AUTOINCREMENT,
                                    CompanyName TEXT);");
            int dcID
                = connection.ExecuteScalar<int>(@"INSERT INTO ComicBookCompanies
                                                    ( CompanyName ) VALUES ( 'D.C.' );
                                                    SELECT last_insert_rowid();");

            connection.Execute(@"CREATE TABLE Heroes (
                                        HeroID INTEGER PRIMARY KEY AUTOINCREMENT,
                                        ComicBookCompanyID INTEGER,
                                        HeroName TEXT);");

            long batmanID = connection.ExecuteScalar<long>(@"INSERT INTO Heroes
                                    ( ComicBookCompanyID, HeroName )
                                    VALUES
                                    ( @ComicBookCompanyID, @HeroName );
                                    SELECT last_insert_rowid();",
                                     new { ComicBookCompanyID = dcID, HeroName = "Batman" });

            connection.Execute(@"CREATE TABLE Sidekicks (
                                        SidekickID INTEGER PRIMARY KEY AUTOINCREMENT,
                                        HeroID INTEGER,
                                        SidekickName TEXT);");

            connection.Execute(@"INSERT INTO Sidekicks
                                    ( HeroID, SidekickName )
                                    VALUES
                                    ( @HeroID, @SidekickName );",
                                    new[] { new { HeroID = batmanID, SidekickName = "Robin" } });

            string sql = @"SELECT s.SidekickID, s.SidekickName,
                                    h.HeroID, h.HeroName,
                                    c.ComicBookCompanyID, c.CompanyName
                                FROM  Sidekicks s
                                    INNER JOIN Heroes h
                                        ON s.HeroID = h.HeroID
                                    INNER JOIN  ComicBookCompanies c
                                        ON c.ComicBookCompanyID = h.HeroID";

            Sidekick firstSidekick =
                    connection.Query<Sidekick, Hero, ComicBookCompany, Sidekick>(sql,
                            (sidekick, hero, company) =>
                            {
                                sidekick.Hero = hero;
                                hero.ComicBookCompany = company;
                                return sidekick;
                            }, splitOn: "HeroID,ComicBookCompanyID"
                            ).First();

            Assert.AreEqual("Robin", firstSidekick.SidekickName);
            Assert.AreEqual("Batman", firstSidekick.Hero.HeroName);
            Assert.AreEqual("D.C.", firstSidekick.Hero.ComicBookCompany.CompanyName);

            connection.Execute("DROP TABLE ComicBookCompanies");
            connection.Execute("DROP TABLE Heroes");
            connection.Execute("DROP TABLE Sidekicks");
        }

        [TestMethod]
        public void UseLookupForOneToMany()
        {
            connection.Execute(@"CREATE TABLE Starship (
                                    StarshipID INTEGER PRIMARY KEY AUTOINCREMENT,
                                    StarshipName TEXT)");

            int shipID = connection.ExecuteScalar<int>(
                                @"INSERT INTO Starship ( StarshipName )
                                    VALUES ( 'USS Enterprise NCC 1701' );
                                SELECT last_insert_rowid();");

            connection.Execute(@"CREATE TABLE CrewMember (
                                    CrewMemberID INTEGER PRIMARY KEY AUTOINCREMENT,
                                    StarshipID INTEGER,
                                    MemberName TEXT)");

            connection.Execute(@"INSERT INTO CrewMember
                                    ( StarshipID, MemberName )
                                    VALUES
                                    ( @StarshipID, @MemberName )",
                                    new[]
                                    {
                                        new { MemberName = "Luke Skywalker", StarshipID = shipID },
                                        new { MemberName = "Gandalf", StarshipID = shipID }
                                    });

            string sql = @"SELECT s.StarshipID, s.StarshipName,
                                c.CrewMemberID, c.StarshipID, c.MemberName
                                FROM Starship s
                                    INNER JOIN CrewMember c
                                        ON c.StarshipID = s.StarshipID";

            List<Starship> lookup = new List<Starship>();
            connection.Query<Starship, CrewMember, Starship>(sql,
                        (starship, crewmember) =>
                        {
                            Starship ship = lookup.FirstOrDefault(s => s.StarshipID == starship.StarshipID);

                            if (ship == null)
                            {
                                lookup.Add(starship);
                                ship = starship;
                            }

                            ship.Crew = ship.Crew ?? new List<CrewMember>();
                            ship.Crew.Add(crewmember);

                            return null;
                        }, splitOn: "CrewMemberID");

            Assert.AreEqual(1, lookup.Count);
            Assert.AreEqual(2, lookup[0].Crew.Count);
            Assert.AreEqual("Luke Skywalker", lookup[0].Crew[0].MemberName);
            Assert.AreEqual("Gandalf", lookup[0].Crew[1].MemberName);

            connection.Execute("DROP TABLE Starship");
            connection.Execute("DROP TABLE CrewMember");
        }

        [TestMethod]
        public void MultipleResultsSingleQuery()
        {
            connection.Execute("CREATE TABLE TestTable_0 (Field_0 INTEGER);");
            connection.Execute("CREATE TABLE TestTable_1 (Field_1 INTEGER);");
            connection.Execute("CREATE TABLE TestTable_2 (Field_2 INTEGER);");

            connection.Execute("INSERT INTO TestTable_0 (Field_0) VALUES (1)");
            connection.Execute("INSERT INTO TestTable_1 (Field_1) VALUES (2)");
            connection.Execute("INSERT INTO TestTable_2 (Field_2) VALUES (3)");

            string sql = @"SELECT Field_0 FROM TestTable_0;
                            SELECT Field_1 FROM TestTable_1;
                            SELECT Field_2 FROM TestTable_2;";

            using (SqlMapper.GridReader multiQuery = connection.QueryMultiple(sql))
            {
                // Check multiQuery.Read<T>()
                var result0 = multiQuery.Read().First();
                var result1 = multiQuery.Read().First();
                var result2 = multiQuery.Read().First();

                Assert.AreEqual(1, result0.Field_0);
                Assert.AreEqual(2, result1.Field_1);
                Assert.AreEqual(3, result2.Field_2);
            }

            connection.Execute("DROP TABLE TestTable_0");
            connection.Execute("DROP TABLE TestTable_1");
            connection.Execute("DROP TABLE TestTable_2");
        }

        [TestMethod]
        [ExpectedException(typeof(SQLiteException))]
        public void InvalidSQL()
        {
            connection.Execute("I am your mother's invalid SQL");
        }
    }

    public class ComicBookCompany
    {
        public int CompanyID { get; set; }
        public string CompanyName { get; set; }
    }

    public class Hero
    {
        public int HeroID { get; set; }
        public string HeroName { get; set; }
        public ComicBookCompany ComicBookCompany { get; set; }
    }

    public class Sidekick
    {
        public int SidekickID { get; set; }
        public string SidekickName { get; set; }
        public Hero Hero { get; set; }
    }

    public class Person
    {
        public string Name { get; set; }
        public int Age { get; set; }
    }

    public class Starship
    {
        public int StarshipID { get; set; }
        public string StarshipName { get; set; }
        public List<CrewMember> Crew { get; set; }
    }

    public class CrewMember
    {
        public int CrewMemberID { get; set; }
        public int StarshipID { get; set; }
        // Heheh ... "Member" name
        public string MemberName { get; set; }
    }
}

//Interesting idea for using splitOn
//Found at: http://stackoverflow.com/questions/7472088/correct-use-of-multimapping-in-dapper

//  public IEnumerable<TParent> GetParentChild<TParent, TChild>()
//  {
//      var sql = string.Format(@"select * from {0} p
//      inner join {1} c on p.{1}Id = c.Id",
//      typeof(TParent).Name, typeof(TChild).Name);

//      Debug.WriteLine(sql);

//      var data = _con.Query<TParent, TChild, TParent>(
//          sql,
//          (p, c) =>
//          {
//              p.GetType().GetProperty(typeof (TChild).Name).SetValue(p, c);
//              return p;
//          },
//          splitOn: typeof(TChild).Name + "Id");

//     return data;
// }

https://github.com/mgroves