Dapper.NET使用入门(三)【Helper测试】

没有一种不通过蔑视、忍受和奋斗就可以征服的命运。

Program.cs主程序

 class Program
        {
            static void Main()
            {
                SetupSqlServer();
                RunTestsSqlServer();
                Console.ReadKey();

            }

            private static void SetupSqlServer()
            {
                using (var connection = new SqlConnection(@"Data Source=.;Initial Catalog=Master;Persist Security Info=True;User ID=sa;Password=123456"))
                {
                    connection.Open();
                    try
                    {
                        connection.Execute(@" DROP DATABASE DapperSimpleCrudTestDb; ");
                    }
                    catch (Exception)
                    { }

                    connection.Execute(@" CREATE DATABASE DapperSimpleCrudTestDb; ");
                }

                using (var connection = new SqlConnection(@"Data Source=.;Initial Catalog=DapperSimpleCrudTestDb;Persist Security Info=True;User ID=sa;Password=123456"))
                {
                    connection.Open();
                    connection.Execute(@" create table Users (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, Age int not null, ScheduledDayOff int null, CreatedDate datetime DEFAULT(getdate())) ");
                    connection.Execute(@" create table Car (CarId int IDENTITY(1,1) not null, Id int null, Make nvarchar(100) not null, Model nvarchar(100) not null) ");
                    connection.Execute(@" create table BigCar (CarId bigint IDENTITY(2147483650,1) not null, Make nvarchar(100) not null, Model nvarchar(100) not null) ");
                    connection.Execute(@" create table City (Name nvarchar(100) not null, Population int not null) ");
                    connection.Execute(@" CREATE SCHEMA Log; ");
                    connection.Execute(@" create table Log.CarLog (Id int IDENTITY(1,1) not null, LogNotes nvarchar(100) NOT NULL) ");
                    connection.Execute(@" CREATE TABLE [dbo].[GUIDTest]([Id] [uniqueidentifier] NOT NULL,[name] [varchar](50) NOT NULL, CONSTRAINT [PK_GUIDTest] PRIMARY KEY CLUSTERED ([Id] ASC))");
                    connection.Execute(@" create table StrangeColumnNames (ItemId int IDENTITY(1,1) not null Primary Key, word nvarchar(100) not null, colstringstrangeword nvarchar(100) not null) ");
                    connection.Execute(@" create table UserWithoutAutoIdentity (Id int not null Primary Key, Name nvarchar(100) not null, Age int not null) ");
                    connection.Execute(@" create table IgnoreColumns (Id int IDENTITY(1,1) not null Primary Key, IgnoreInsert nvarchar(100) null, IgnoreUpdate nvarchar(100) null, IgnoreSelect nvarchar(100)  null, IgnoreAll nvarchar(100) null) ");
                }
                Console.WriteLine("创建数据库!");
            }

            private static void RunTestsSqlServer()
            {
                var stopwatch = Stopwatch.StartNew();
                var sqltester = new Tests(SimpleCRUD.Dialect.SQLServer);
                foreach (var method in typeof(Tests).GetMethods(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly))
                {
                    var testwatch = Stopwatch.StartNew();
                    Console.Write("运行 " + method.Name + " 在 sql server");
                    method.Invoke(sqltester, null);
                    testwatch.Stop();
                    Console.WriteLine(" - OK! {0}ms", testwatch.ElapsedMilliseconds);
                }
                stopwatch.Stop();

                // 输出结果
                Console.WriteLine("花费时间: {0}", stopwatch.Elapsed);

                using (var connection = new SqlConnection(@"Data Source=.;Initial Catalog=Master;Persist Security Info=True;User ID=sa;Password=123456"))
                {
                    connection.Open();
                    try
                    {
                        //删除任何剩余的连接，然后删除该数据库
                        connection.Execute(@" alter database DapperSimpleCrudTestDb set single_user with rollback immediate; DROP DATABASE DapperSimpleCrudTestDb; ");
                    }
                    catch (Exception)
                    { }
                }
                Console.Write("SQL Server的测试完成。");
                Console.ReadKey();
            }

        }

DTO类

 #region DTO类

    // [System.ComponentModel.DataAnnotations.Schema.Table("Users")]  或者 内置SimpleCRUD的属性
    [Table("Users")]
    public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        //我们修改，以便枚举被自动处理，我们也应该自动处理可空枚举
        public DayOfWeek? ScheduledDayOff { get; set; }

        [ReadOnly(true)]
        public DateTime CreatedDate { get; set; }
    }

    [Table("Users")]
    public class User1
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public int? ScheduledDayOff { get; set; }
    }

    public class Car
    {
        #region DatabaseFields

        //System.ComponentModel.DataAnnotations.Key
        [Key]
        public int CarId { get; set; }

        public int? Id { get; set; }
        public string Make { get; set; }
        public string Model { get; set; }

        #endregion

        #region RelatedTables

        public List<User> Users { get; set; }

        #endregion

        #region AdditionalFields

        [Editable(false)]
        public string MakeWithModel
        {
            get { return Make + " (" + Model + ")"; }
        }

        #endregion
    }

    public class BigCar
    {
        #region DatabaseFields

        //System.ComponentModel.DataAnnotations.Key
        [Key]
        public long CarId { get; set; }

        public string Make { get; set; }
        public string Model { get; set; }

        #endregion
    }

    [Table("CarLog", Schema = "Log")]
    public class CarLog
    {
        public int Id { get; set; }
        public string LogNotes { get; set; }
    }

    /// <summary>
    ///     这个类应该用于失败测试，因为没有指定架构和“CarLog”不在dbo下
    /// </summary>
    [Table("CarLog")]
    public class SchemalessCarLog
    {
        public int Id { get; set; }
        public string LogNotes { get; set; }
    }

    public class City
    {
        [Key]
        public string Name { get; set; }

        public int Population { get; set; }
    }

    public class GUIDTest
    {
        [Key]
        public Guid Id { get; set; }

        public string Name { get; set; }
    }

    public class StrangeColumnNames
    {
        [Key]
        [Column("ItemId")]
        public int Id { get; set; }

        public string Word { get; set; }

        [Column("colstringstrangeword")]
        public string StrangeWord { get; set; }

        [Editable(false)]
        public string ExtraProperty { get; set; }
    }

    public class IgnoreColumns
    {
        [Key]
        public int Id { get; set; }

        [IgnoreInsert]
        public string IgnoreInsert { get; set; }

        [IgnoreUpdate]
        public string IgnoreUpdate { get; set; }

        [IgnoreSelect]
        public string IgnoreSelect { get; set; }

        [IgnoreInsert]
        [IgnoreUpdate]
        [IgnoreSelect]
        public string IgnoreAll { get; set; }
    }

    public class UserWithoutAutoIdentity
    {
        [Key]
        [Required]
        public int Id { get; set; }

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

    #endregion

Tests类:

 public class Tests
    {
        private readonly SimpleCRUD.Dialect _dbtype;

        public Tests(SimpleCRUD.Dialect dbtype)
        {
            _dbtype = dbtype;
        }

        //获取打开连接
        private IDbConnection GetOpenConnection()
        {
            IDbConnection connection;
            if (_dbtype == SimpleCRUD.Dialect.PostgreSQL)
            {
                connection =
                    new NpgsqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
                        "localhost", "5432", "postgres", "postgrespass", "testdb"));
                SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);
            }
            else if (_dbtype == SimpleCRUD.Dialect.SQLite)
            {
                connection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
                SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLite);
            }
            else if (_dbtype == SimpleCRUD.Dialect.MySQL)
            {
                connection =
                    new MySqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
                        "localhost", "3306", "admin", "admin", "testdb"));
                SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
            }
            else
            {
                connection =
                    new SqlConnection(
                        @"Data Source=.;Initial Catalog=DapperSimpleCrudTestDb;Persist Security Info=True;User ID=sa;Password=123456;MultipleActiveResultSets=true;");
                SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLServer);
            }

            connection.Open();
            return connection;
        }


        //指定表名插入数据
        public void InsertWithSpecifiedTableName()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? id = connection.Insert(new User {Name = "User1", Age = 10});
                id.IsEqualTo(1);
                connection.Delete<User>(id);
            }
        }

        //使用BigInt主键插入数据
        public void InsertUsingBigIntPrimaryKey()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                var id = connection.Insert<long>(new BigCar {Make = "Big", Model = "Car"});
                id.IsEqualTo(2147483650);
                connection.Delete<BigCar>(id);
            }
        }

        //测试简单获取数据
        public void TestSimpleGet()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? id = connection.Insert(new User {Name = "UserTestSimpleGet", Age = 10});
                var user = connection.Get<User>(id);
                user.Name.IsEqualTo("UserTestSimpleGet");
                connection.Delete<User>(id);
            }
        }

        //测试按id删除
        public void TestDeleteById()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? id = connection.Insert(new User {Name = "UserTestDeleteById", Age = 10});
                connection.Delete<User>(id);
                connection.Get<User>(id).IsNull();
            }
        }

        //测试按对象删除
        public void TestDeleteByObject()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? id = connection.Insert(new User {Name = "TestDeleteByObject", Age = 10});
                var user = connection.Get<User>(id);
                connection.Delete(user);
                connection.Get<User>(id).IsNull();
            }
        }

        //测试简单获取列表
        public void TestSimpleGetList()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                connection.Insert(new User {Name = "TestSimpleGetList1", Age = 10});
                connection.Insert(new User {Name = "TestSimpleGetList2", Age = 10});
                IEnumerable<User> user = connection.GetList<User>(new {});
                user.Count().IsEqualTo(2);
                connection.Execute("Delete from Users");
            }
        }

        //测试筛选获取的列表
        public void TestFilteredGetList()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                connection.Insert(new User {Name = "TestFilteredGetList1", Age = 10});
                connection.Insert(new User {Name = "TestFilteredGetList2", Age = 10});
                connection.Insert(new User {Name = "TestFilteredGetList3", Age = 10});
                connection.Insert(new User {Name = "TestFilteredGetList4", Age = 11});

                IEnumerable<User> user = connection.GetList<User>(new {Age = 10});
                user.Count().IsEqualTo(3);
                connection.Execute("Delete from Users");
            }
        }

        //测试过滤，用SQL的GetList
        public void TestFilteredWithSQLGetList()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                connection.Insert(new User {Name = "TestFilteredWithSQLGetList1", Age = 10});
                connection.Insert(new User {Name = "TestFilteredWithSQLGetList2", Age = 10});
                connection.Insert(new User {Name = "TestFilteredWithSQLGetList3", Age = 10});
                connection.Insert(new User {Name = "TestFilteredWithSQLGetList4", Age = 11});

                IEnumerable<User> user =
                    connection.GetList<User>("where Name like 'TestFilteredWithSQLGetList%' and Age = 10");
                user.Count().IsEqualTo(3);
                connection.Execute("Delete from Users");
            }
        }

        //测试用null Where 获取列表
        public void TestGetListWithNullWhere()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                connection.Insert(new User {Name = "TestGetListWithNullWhere", Age = 10});
                IEnumerable<User> user = connection.GetList<User>(null);
                user.Count().IsEqualTo(1);
                connection.Execute("Delete from Users");
            }
        }

        //测试没有where条件获取列表
        public void TestGetListWithoutWhere()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                connection.Insert(new User {Name = "TestGetListWithoutWhere", Age = 10});
                IEnumerable<User> user = connection.GetList<User>();
                user.Count().IsEqualTo(1);
                connection.Execute("Delete from Users");
            }
        }

        //测试具有只读属性的获取
        public void TestGetWithReadonlyProperty()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? id = connection.Insert(new User {Name = "TestGetWithReadonlyProperty", Age = 10});
                var user = connection.Get<User>(id);
                user.CreatedDate.Year.IsEqualTo(DateTime.Now.Year);
                connection.Execute("Delete from Users");
            }
        }

        //测试只读属性的增加数据
        public void TestInsertWithReadonlyProperty()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? id =
                    connection.Insert(new User
                    {
                        Name = "TestInsertWithReadonlyProperty",
                        Age = 10,
                        CreatedDate = new DateTime(2001, 1, 1)
                    });
                var user = connection.Get<User>(id);
                //the date can't be 2001 - it should be the autogenerated date from the database
                user.CreatedDate.Year.IsEqualTo(DateTime.Now.Year);
                connection.Execute("Delete from Users");
            }
        }

        //测试只读属性的更新
        public void TestUpdateWithReadonlyProperty()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? id = connection.Insert(new User {Name = "TestUpdateWithReadonlyProperty", Age = 10});
                var user = connection.Get<User>(id);
                user.Age = 11;
                user.CreatedDate = new DateTime(2001, 1, 1);
                connection.Update(user);
                user = connection.Get<User>(id);
                //don't allow changing created date since it has a readonly attribute
                user.CreatedDate.Year.IsEqualTo(DateTime.Now.Year);
                connection.Execute("Delete from Users");
            }
        }

        //insert特定键的数据
        public void InsertWithSpecifiedKey()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? id = connection.Insert(new Car {Make = "Honda", Model = "Civic"});
                id.IsEqualTo(1);
            }
        }

        // insert额外的属性  应该跳过标记为只读的复杂类型和属性
        public void InsertWithExtraPropertiesShouldSkipNonSimpleTypesAndPropertiesMarkedEditableFalse()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? id =
                    connection.Insert(new Car
                    {
                        Make = "Honda",
                        Model = "Civic",
                        Users = new List<User> {new User {Age = 12, Name = "test"}}
                    });
                id.IsEqualTo(2);
            }
        }

        //测试更新
        public void TestUpdate()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                var newid = (int) connection.Insert(new Car {Make = "Honda", Model = "Civic"});
                var newitem = connection.Get<Car>(newid);
                newitem.Make = "Toyota";
                connection.Update(newitem);
                var updateditem = connection.Get<Car>(newid);
                updateditem.Make.IsEqualTo("Toyota");
                connection.Delete<Car>(newid);
            }
        }

        //根据有属性的对象测试删除
        public void TestDeleteByObjectWithAttributes()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? id = connection.Insert(new Car {Make = "Honda", Model = "Civic"});
                var car = connection.Get<Car>(id);
                connection.Delete(car);
                connection.Get<Car>(id).IsNull();
            }
        }

        //测试标记为可编辑的复杂类型 保存
        public void TestComplexTypesMarkedEditableAreSaved()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                var id = (int) connection.Insert(new User {Name = "User", Age = 11, ScheduledDayOff = DayOfWeek.Friday});
                var user1 = connection.Get<User>(id);
                user1.ScheduledDayOff.IsEqualTo(DayOfWeek.Friday);
                connection.Delete(user1);
            }
        }

        //测试可空简单类型的保存
        public void TestNullableSimpleTypesAreSaved()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                var id = (int) connection.Insert(new User1 {Name = "User", Age = 11, ScheduledDayOff = 2});
                var user1 = connection.Get<User1>(id);
                user1.ScheduledDayOff.IsEqualTo(2);
                connection.Delete(user1);
            }
        }

        //测试从不同的架构插入数据
        public void TestInsertIntoDifferentSchema()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? id = connection.Insert(new CarLog {LogNotes = "blah blah blah"});
                id.IsEqualTo(1);
                connection.Delete<CarLog>(id);
            }
        }

        //测试从不同的架构 获取数据
        public void TestGetFromDifferentSchema()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? id = connection.Insert(new CarLog {LogNotes = "TestGetFromDifferentSchema"});
                var carlog = connection.Get<CarLog>(id);
                carlog.LogNotes.IsEqualTo("TestGetFromDifferentSchema");
                connection.Delete<CarLog>(id);
            }
        }

        //测试 试图从表在架构没有数据对象声明 获取表数据  应该失败
        public void TestTryingToGetFromTableInSchemaWithoutDataAnnotationShouldFail()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                try
                {
                    connection.Get<SchemalessCarLog>(1);
                }
                catch (Exception)
                {
                    return;
                }

                throw new ApplicationException("Expected exception");
            }
        }

        //测试用不是INT的主键 Get From Table
        public void TestGetFromTableWithNonIntPrimaryKey()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                //note - there's not support for inserts without a non-int id, so drop down to a normal execute
                connection.Execute("INSERT INTO CITY (NAME, POPULATION) VALUES ('Morgantown', 31000)");
                var city = connection.Get<City>("Morgantown");
                city.Population.IsEqualTo(31000);
            }
        }

        //测试用不是INT的主键 DELETE FROM表
        public void TestDeleteFromTableWithNonIntPrimaryKey()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                //note - there's not support for inserts without a non-int id, so drop down to a normal execute
                connection.Execute("INSERT INTO CITY (NAME, POPULATION) VALUES ('Fairmont', 18737)");
                connection.Delete<City>("Fairmont").IsEqualTo(1);
            }
        }

        //测试可空枚举插入
        public void TestNullableEnumInsert()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                connection.Insert(new User {Name = "Enum-y", Age = 10, ScheduledDayOff = DayOfWeek.Thursday});
                User user = connection.GetList<User>(new {Name = "Enum-y"}).FirstOrDefault() ?? new User();
                user.ScheduledDayOff.IsEqualTo(DayOfWeek.Thursday);
                connection.Delete<User>(user.Id);
            }
        }

        //更改方言 
        public void ChangeDialect()
        {
            SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLServer);
            SimpleCRUD.GetDialect().IsEqualTo(SimpleCRUD.Dialect.SQLServer.ToString());
            SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);
            SimpleCRUD.GetDialect().IsEqualTo(SimpleCRUD.Dialect.PostgreSQL.ToString());
        }


        //GUID作为主键测试
        //插入到表 
        public void InsertIntoTableWithUnspecifiedGuidKey()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                var id = connection.Insert<Guid>(new GUIDTest {Name = "GuidUser"});
                id.GetType().Name.IsEqualTo("Guid");
                var record = connection.Get<GUIDTest>(id);
                record.Name.IsEqualTo("GuidUser");
                connection.Delete<GUIDTest>(id);
            }
        }

        //根据GUID增加记录到表
        public void InsertIntoTableWithGuidKey()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                var guid = new Guid("1a6fb33d-7141-47a0-b9fa-86a1a1945da9");
                var id = connection.Insert<Guid>(new GUIDTest {Name = "InsertIntoTableWithGuidKey", Id = guid});
                id.IsEqualTo(guid);
                connection.Delete<GUIDTest>(id);
            }
        }

        //根据GUID项来获取记录
        public void GetRecordWithGuidKey()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                var guid = new Guid("2a6fb33d-7141-47a0-b9fa-86a1a1945da9");
                connection.Insert<Guid>(new GUIDTest {Name = "GetRecordWithGuidKey", Id = guid});
                Guid id = connection.GetList<GUIDTest>().First().Id;
                var record = connection.Get<GUIDTest>(id);
                record.Name.IsEqualTo("GetRecordWithGuidKey");
                connection.Delete<GUIDTest>(id);
            }
        }

        //根据GUID来删除记录
        public void DeleteRecordWithGuidKey()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                var guid = new Guid("3a6fb33d-7141-47a0-b9fa-86a1a1945da9");
                connection.Insert<Guid>(new GUIDTest {Name = "DeleteRecordWithGuidKey", Id = guid});
                Guid id = connection.GetList<GUIDTest>().First().Id;
                connection.Delete<GUIDTest>(id);
                connection.Get<GUIDTest>(id).IsNull();
            }
        }

        //测试异步多数据插入
        public void TestMultiInsertASync()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                connection.InsertAsync(new User {Name = "TestMultiInsertASync1", Age = 10});
                connection.InsertAsync(new User {Name = "TestMultiInsertASync2", Age = 10});
                connection.InsertAsync(new User {Name = "TestMultiInsertASync3", Age = 10});
                connection.InsertAsync(new User {Name = "TestMultiInsertASync4", Age = 11});
                Thread.Sleep(300);
                //tiny wait to let the inserts happen
                IEnumerable<User> list = connection.GetList<User>(new {Age = 10});
                list.Count().IsEqualTo(3);
                connection.Execute("Delete from Users");
            }
        }

        //GUID的异步多数据插入
        public void MultiInsertWithGuidAsync()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                connection.InsertAsync<Guid>(new GUIDTest {Name = "MultiInsertWithGuidAsync"});
                connection.InsertAsync<Guid>(new GUIDTest {Name = "MultiInsertWithGuidAsync"});
                connection.InsertAsync<Guid>(new GUIDTest {Name = "MultiInsertWithGuidAsync"});
                connection.InsertAsync<Guid>(new GUIDTest {Name = "MultiInsertWithGuidAsync"});
                //tiny wait to let the inserts happen
                Thread.Sleep(300);
                IEnumerable<GUIDTest> list = connection.GetList<GUIDTest>(new {Name = "MultiInsertWithGuidAsync"});
                list.Count().IsEqualTo(4);
                connection.Execute("Delete from GUIDTest");
            }
        }

        //测试简单异步获取
        public void TestSimpleGetAsync()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? id = connection.Insert(new User {Name = "TestSimpleGetAsync", Age = 10});
                Task<User> user = connection.GetAsync<User>(id);
                user.Result.Name.IsEqualTo("TestSimpleGetAsync");
                connection.Delete<User>(id);
            }
        }

        //测试按id异步删除
        public void TestDeleteByIdAsync()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? id = connection.Insert(new User {Name = "UserAsyncDelete", Age = 10});
                connection.DeleteAsync<User>(id);
                //tiny wait to let the delete happen
                Thread.Sleep(300);
                connection.Get<User>(id).IsNull();
            }
        }

        //测试通过对象异步删除
        public void TestDeleteByObjectAsync()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? id = connection.Insert(new User {Name = "TestDeleteByObjectAsync", Age = 10});
                var user = connection.Get<User>(id);
                connection.DeleteAsync(user);
                connection.Get<User>(id).IsNull();
                connection.Delete<User>(id);
            }
        }

        //测试简单的异步获取列表
        public void TestSimpleGetListAsync()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                connection.Insert(new User {Name = "TestSimpleGetListAsync1", Age = 10});
                connection.Insert(new User {Name = "TestSimpleGetListAsync2", Age = 10});
                Task<IEnumerable<User>> user = connection.GetListAsync<User>(new {});
                user.Result.Count().IsEqualTo(2);
                connection.Execute("Delete from Users");
            }
        }

        //测试筛选异步获取的列表
        public void TestFilteredGetListAsync()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                connection.Insert(new User {Name = "TestFilteredGetListAsync1", Age = 10});
                connection.Insert(new User {Name = "TestFilteredGetListAsync2", Age = 10});
                connection.Insert(new User {Name = "TestFilteredGetListAsync3", Age = 10});
                connection.Insert(new User {Name = "TestFilteredGetListAsync4", Age = 11});

                Task<IEnumerable<User>> user = connection.GetListAsync<User>(new {Age = 10});
                user.Result.Count().IsEqualTo(3);
                connection.Execute("Delete from Users");
            }
        }

        //column attribute tests
        //指定列名插入数据
        public void InsertWithSpecifiedColumnName()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? itemId =
                    connection.Insert(new StrangeColumnNames
                    {
                        Word = "InsertWithSpecifiedColumnName",
                        StrangeWord = "Strange 1"
                    });
                itemId.IsEqualTo(1);
                connection.Delete<StrangeColumnNames>(itemId);
            }
        }

        //测试删除指定的列名的对象
        public void TestDeleteByObjectWithSpecifiedColumnName()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? itemId =
                    connection.Insert(new StrangeColumnNames
                    {
                        Word = "TestDeleteByObjectWithSpecifiedColumnName",
                        StrangeWord = "Strange 1"
                    });
                var strange = connection.Get<StrangeColumnNames>(itemId);
                connection.Delete(strange);
                connection.Get<StrangeColumnNames>(itemId).IsNull();
            }
        }

        //测试简单获取指定的列名的List
        public void TestSimpleGetListWithSpecifiedColumnName()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? id1 =
                    connection.Insert(new StrangeColumnNames
                    {
                        Word = "TestSimpleGetListWithSpecifiedColumnName1",
                        StrangeWord = "Strange 2",
                    });
                int? id2 =
                    connection.Insert(new StrangeColumnNames
                    {
                        Word = "TestSimpleGetListWithSpecifiedColumnName2",
                        StrangeWord = "Strange 3",
                    });
                IEnumerable<StrangeColumnNames> strange = connection.GetList<StrangeColumnNames>(new {});
                strange.First().StrangeWord.IsEqualTo("Strange 2");
                strange.Count().IsEqualTo(2);
                connection.Delete<StrangeColumnNames>(id1);
                connection.Delete<StrangeColumnNames>(id2);
            }
        }

        //测试更新指定列名的对象
        public void TestUpdateWithSpecifiedColumnName()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                var newid =
                    (int)
                        connection.Insert(new StrangeColumnNames {Word = "Word Insert", StrangeWord = "Strange Insert"});
                var newitem = connection.Get<StrangeColumnNames>(newid);
                newitem.Word = "Word Update";
                connection.Update(newitem);
                var updateditem = connection.Get<StrangeColumnNames>(newid);
                updateditem.Word.IsEqualTo("Word Update");
                connection.Delete<StrangeColumnNames>(newid);
            }
        }

        //测试获取过滤的指定的列名的List
        public void TestFilteredGetListWithSpecifiedColumnName()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                connection.Insert(new StrangeColumnNames {Word = "Word 5", StrangeWord = "Strange 1",});
                connection.Insert(new StrangeColumnNames {Word = "Word 6", StrangeWord = "Strange 2",});
                connection.Insert(new StrangeColumnNames {Word = "Word 7", StrangeWord = "Strange 2",});
                connection.Insert(new StrangeColumnNames {Word = "Word 8", StrangeWord = "Strange 2",});

                IEnumerable<StrangeColumnNames> strange =
                    connection.GetList<StrangeColumnNames>(new {StrangeWord = "Strange 2"});
                strange.Count().IsEqualTo(3);
                connection.Execute("Delete from StrangeColumnNames");
            }
        }

        //测试获取分页列表
        public void TestGetListPaged()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int x = 0;
                do
                {
                    connection.Insert(new User
                    {
                        Name = "Person " + x,
                        Age = x,
                        CreatedDate = DateTime.Now,
                        ScheduledDayOff = DayOfWeek.Thursday
                    });
                    x++;
                } while (x < 30);

                IEnumerable<User> resultlist = connection.GetListPaged<User>(2, 10, null, null);
                resultlist.Count().IsEqualTo(10);
                resultlist.Skip(4).First().Name.IsEqualTo("Person 14");
                connection.Execute("Delete from Users");
            }
        }

        //测试获取特定的主键的分页列表
        public void TestGetListPagedWithSpecifiedPrimaryKey()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int x = 0;
                do
                {
                    connection.Insert(new StrangeColumnNames {Word = "Word " + x, StrangeWord = "Strange " + x});
                    x++;
                } while (x < 30);

                IEnumerable<StrangeColumnNames> resultlist = connection.GetListPaged<StrangeColumnNames>(2, 10, null,
                    null);
                resultlist.Count().IsEqualTo(10);
                resultlist.Skip(4).First().Word.IsEqualTo("Word 14");
                connection.Execute("Delete from StrangeColumnNames");
            }
        }

        //测试获取Where子句的分页列表
        public void TestGetListPagedWithWhereClause()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int x = 0;
                do
                {
                    connection.Insert(new User
                    {
                        Name = "Person " + x,
                        Age = x,
                        CreatedDate = DateTime.Now,
                        ScheduledDayOff = DayOfWeek.Thursday
                    });
                    x++;
                } while (x < 30);

                IEnumerable<User> resultlist1 = connection.GetListPaged<User>(1, 3, "Where Name LIKE 'Person 2%'",
                    "age desc");
                resultlist1.Count().IsEqualTo(3);

                IEnumerable<User> resultlist = connection.GetListPaged<User>(2, 3, "Where Name LIKE 'Person 2%'",
                    "age desc");
                resultlist.Count().IsEqualTo(3);
                resultlist.Skip(1).First().Name.IsEqualTo("Person 25");

                connection.Execute("Delete from Users");
            }
        }

        //测试删除 Where子句的分页列表
        public void TestDeleteListWithWhereClause()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int x = 0;
                do
                {
                    connection.Insert(new User
                    {
                        Name = "Person " + x,
                        Age = x,
                        CreatedDate = DateTime.Now,
                        ScheduledDayOff = DayOfWeek.Thursday
                    });
                    x++;
                } while (x < 30);

                connection.DeleteList<User>("Where age > 9");
                IEnumerable<User> resultlist = connection.GetList<User>();
                resultlist.Count().IsEqualTo(10);
                connection.Execute("Delete from Users");
            }
        }

        //测试删除Where对象的列表
        public void TestDeleteListWithWhereObject()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int x = 0;
                do
                {
                    connection.Insert(new User
                    {
                        Name = "Person " + x,
                        Age = x,
                        CreatedDate = DateTime.Now,
                        ScheduledDayOff = DayOfWeek.Thursday
                    });
                    x++;
                } while (x < 10);

                connection.DeleteList<User>(new {age = 9});
                IEnumerable<User> resultlist = connection.GetList<User>();
                resultlist.Count().IsEqualTo(9);
                connection.Execute("Delete from Users");
            }
        }

        //测试总记录
        public void TestRecordCount()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int x = 0;
                do
                {
                    connection.Insert(new User
                    {
                        Name = "Person " + x,
                        Age = x,
                        CreatedDate = DateTime.Now,
                        ScheduledDayOff = DayOfWeek.Thursday
                    });
                    x++;
                } while (x < 30);

                IEnumerable<User> resultlist = connection.GetList<User>();
                resultlist.Count().IsEqualTo(30);
                connection.RecordCount<User>().IsEqualTo(30);

                connection.RecordCount<User>("where age = 10 or age = 11").IsEqualTo(2);


                connection.Execute("Delete from Users");
            }
        }

        //特定的主键INSERT数据
        public void InsertWithSpecifiedPrimaryKey()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? id = connection.Insert(new UserWithoutAutoIdentity {Id = 999, Name = "User999", Age = 10});
                id.IsEqualTo(999);
                var user = connection.Get<UserWithoutAutoIdentity>(999);
                user.Name.IsEqualTo("User999");
                connection.Execute("Delete from UserWithoutAutoIdentity");
            }
        }

        //特定的主键  异步INSERT数据
        public void InsertWithSpecifiedPrimaryKeyAsync()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                Task<int?> id =
                    connection.InsertAsync(new UserWithoutAutoIdentity {Id = 999, Name = "User999Async", Age = 10});
                id.Result.IsEqualTo(999);
                Task<UserWithoutAutoIdentity> user = connection.GetAsync<UserWithoutAutoIdentity>(999);
                user.Result.Name.IsEqualTo("User999Async");
                connection.Execute("Delete from UserWithoutAutoIdentity");
            }
        }

        //测试获取WHERE可空的列表
        public void TestGetListNullableWhere()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                connection.Insert(new User
                {
                    Name = "TestGetListWithoutWhere",
                    Age = 10,
                    ScheduledDayOff = DayOfWeek.Friday
                });
                connection.Insert(new User {Name = "TestGetListWithoutWhere", Age = 10});

                //用NULL属性测试
                IEnumerable<User> list = connection.GetList<User>(new {ScheduledDayOff = (DayOfWeek?) null});
                list.Count().IsEqualTo(1);


                //用db.null值测试
                list = connection.GetList<User>(new {ScheduledDayOff = DBNull.Value});
                list.Count().IsEqualTo(1);

                connection.Execute("Delete from Users");
            }
        }

        //忽略属性
        public void IgnoreProperties()
        {
            using (IDbConnection connection = GetOpenConnection())
            {
                int? itemId =
                    connection.Insert(new IgnoreColumns
                    {
                        IgnoreInsert = "OriginalInsert",
                        IgnoreUpdate = "OriginalUpdate",
                        IgnoreSelect = "OriginalSelect",
                        IgnoreAll = "OriginalAll"
                    });
                var item = connection.Get<IgnoreColumns>(itemId);
                //验证插入列被忽略
                item.IgnoreInsert.IsNull();

                //验证选定值没有选择
                item.IgnoreSelect.IsNull();


                IgnoreColumns fromDapper =
                    connection.Query<IgnoreColumns>("Select * from IgnoreColumns where Id = @Id", new {id = itemId})
                        .First();
                fromDapper.IgnoreSelect.IsEqualTo("OriginalSelect");

                //改变值 更新
                item.IgnoreUpdate = "ChangedUpdate";
                connection.Update(item);

                //验证更新没有带来影响
                item = connection.Get<IgnoreColumns>(itemId);
                item.IgnoreUpdate.IsEqualTo("OriginalUpdate");

                IgnoreColumns allColumnDapper =
                    connection.Query<IgnoreColumns>("Select IgnoreAll from IgnoreColumns where Id = @Id",
                        new {id = itemId}).First();
                allColumnDapper.IgnoreAll.IsNull();

                connection.Delete<IgnoreColumns>(itemId);
            }
        }
    }

结果如图:

---