Dapper使用方法

这里记下Dapper容易忘的使用方法：

返回的数据可以有相同名称的列，会分配到不同的类上，默认使用Id这个列作为分割列

connection.Open();  //手动打开的话会保持长连接，否则每次查询之后会关闭连接

构造函数的参数类型必须严格与数据库字段类型相同

/// <summary>
/// 也可以直接用数组
/// </summary>
public void TestListOfAnsiStrings()
{
    var results = connection.Query<string>("select * from (select 'a' str union select 'b' union select 'c') X where str in @strings",
        new { strings = new[] { new DbString { IsAnsi = true, Value = "a" }, new DbString { IsAnsi = true, Value = "b" } } }).ToList();

    results[0].IsEqualTo("a");
    results[1].IsEqualTo("b");
}

 映射类型支持枚举及可为空枚举，但是枚举必须继承short,int,long等其中一种类型，并且与数据库字段类型必须相同，需要注意的是mysql默认数字为long类型

/// <summary>
/// 可以执行多条语句
/// </summary>
public void TestExecuteCommand()
{
   connection.Execute(@"
          set nocount on 
          create table #t(i int) 
          set nocount off 
          insert #t 
          select @a a union all select @b 
          set nocount on 
          drop table #t", new { a = 1, b = 2 }).IsEqualTo(2);
}

        /// <summary>
        /// 执行语句，返回输出参数值
        /// </summary>
        public void TestExecuteCommandWithHybridParameters()
        {
            var p = new DynamicParameters(new { a = 1, b = 2 });
            p.Add("c", dbType: DbType.Int32, direction: ParameterDirection.Output);
            connection.Execute(@"set @c = @a + @b", p);
            p.Get<int>("@c").IsEqualTo(3);
        }

        /// <summary>
        /// 好方法，返回无实体类数据，使用动态类型
        /// </summary>
        public void TestExpandWithNullableFields()
        {
            var row = connection.Query("select null A, 2 B").Single();
            
            ((int?)row.A)
                .IsNull();

            ((int?)row.B)
                .IsEqualTo(2);
        }

 

/// <summary>
/// 批量插入的简洁写法，Execute返回成功执行的数量，报错则跳出
/// </summary>
public void TestExecuteMultipleCommand()
{
    connection.Execute("create table #t(i int)");
    try
    {
        int tally = connection.Execute(@"insert #t (i) values(@a)", new[] { new { a = 1 }, new { a = 2 }, new { a = 3 }, new { a = 4 } });
        int sum = connection.Query<int>("select sum(i) from #t").First();
        tally.IsEqualTo(4);
        sum.IsEqualTo(10);
    }
    finally
    {
        connection.Execute("drop table #t");
    }
}

class TestObj
{
    public int _internal;
    internal int Internal { set { _internal = value; } }

    public int _priv;
    private int Priv { set { _priv = value; } }

    private int PrivGet { get { return _priv; } }
}

/// <summary>
/// ？？？？私有字段也能映射
/// </summary>
public void TestSetPrivate()
{
    connection.Query<TestObj>("select 10 as [Priv]").First()._priv.IsEqualTo(10);
}

        /// <summary>
        /// 好方法，返回无实体类数据，使用动态类型
        /// </summary>
        public void TestExpandWithNullableFields()
        {
            var row = connection.Query("select null A, 2 B").Single();
            
            ((int?)row.A)
                .IsNull();

            ((int?)row.B)
                .IsEqualTo(2);
        }

/// <summary>
/// datareader方式的查询，不释放连接，如果有新的连接会报错
/// </summary>
public void TestEnumeration()
{
    var en = connection.Query<int>("select 1 as one union all select 2 as one", buffered: false);
    var i = en.GetEnumerator();
    i.MoveNext();

    bool gotException = false;
    try
    {
        var x = connection.Query<int>("select 1 as one", buffered: false).First();
    }
    catch (Exception)
    {
        gotException = true;
    }

    while (i.MoveNext())
    { }

    // should not exception, since enumertated
    en = connection.Query<int>("select 1 as one", buffered: false);

    gotException.IsTrue();
}

/// <summary>
/// QueryMultiple可以返回多结果集
/// </summary>
public void TestMultiMapGridReader()
{
    var createSql = @"
        create table #Users (Id int, Name varchar(20))
        create table #Posts (Id int, OwnerId int, Content varchar(20))

        insert #Users values(99, 'Sam')
        insert #Users values(2, 'I am')

        insert #Posts values(1, 99, 'Sams Post1')
        insert #Posts values(2, 99, 'Sams Post2')
        insert #Posts values(3, null, 'no ones post')
";
    connection.Execute(createSql);

    var sql =@"select p.*, u.Id, u.Name + '0' Name from #Posts p 
            left join #Users u on u.Id = p.OwnerId 
            Order by p.Id

            select p.*, u.Id, u.Name + '1' Name from #Posts p 
            left join #Users u on u.Id = p.OwnerId 
            Order by p.Id
            ";

    var grid = connection.QueryMultiple(sql);

    for (int i = 0; i < 2; i++)
    {
        var data = grid.Read<Post, User, Post>((post, user) => { post.Owner = user; return post; }).ToList();
        var p = data.First();

        p.Content.IsEqualTo("Sams Post1");
        p.Id.IsEqualTo(1);
        p.Owner.Name.IsEqualTo("Sam" + i);
        p.Owner.Id.IsEqualTo(99);

        data[2].Owner.IsNull();
    }

    connection.Execute("drop table #Users drop table #Posts");

}

/// <summary>
/// buffered是指将数据都读取出来，释放datareader，否则是不能再次打开datareader
/// </summary>
public void TestQueryMultipleNonBufferedIncorrectOrder()
{
    using (var grid = connection.QueryMultiple("select 1; select 2; select @x; select 4", new { x = 3 }))
    {
        var a = grid.Read<int>(false);
        try
        {
            var b = grid.Read<int>(false);
            throw new InvalidOperationException(); // should have thrown
        }
        catch (InvalidOperationException)
        {
            // that's expected
        }

    }
}
public void TestQueryMultipleNonBufferedCcorrectOrder()
{
    using (var grid = connection.QueryMultiple("select 1; select 2; select @x; select 4", new { x = 3 }))
    {
        var a = grid.Read<int>(false).Single(); //Single将会释放datareader，只能运行一次
        var b = grid.Read<int>(false).Single();
        var c = grid.Read<int>(false).Single();
        var d = grid.Read<int>(false).Single();

        a.Equals(1);
        b.Equals(2);
        c.Equals(3);
        d.Equals(4);
    }
}

splitOn用于分割多个实体类的字段

Dapper支持返回dynamic类型

/// <summary>
/// 支持将字符串转为枚举
/// </summary>
public void TestEnumStrings()
{
    connection.Query<TestEnumClassNoNull>("select 'BLA' as [EnumEnum]").First().EnumEnum.IsEqualTo(TestEnum.Bla);
    connection.Query<TestEnumClassNoNull>("select 'bla' as [EnumEnum]").First().EnumEnum.IsEqualTo(TestEnum.Bla);

    connection.Query<TestEnumClass>("select 'BLA' as [EnumEnum]").First().EnumEnum.IsEqualTo(TestEnum.Bla);
    connection.Query<TestEnumClass>("select 'bla' as [EnumEnum]").First().EnumEnum.IsEqualTo(TestEnum.Bla);
}

/// <summary>
/// ExpandoObject有什么用
/// </summary>
public void TestSupportForExpandoObjectParameters()
{
    dynamic p = new ExpandoObject();
    p.name = "bob";
    object parameters = p;
    string result = connection.Query<string>("select @name", parameters).First();
    result.IsEqualTo("bob");
}

/// <summary>
/// 执行存储过程
/// </summary>
public void TestProcSupport()
{
    var p = new DynamicParameters();
    p.Add("a", 11);
    p.Add("b", dbType: DbType.Int32, direction: ParameterDirection.Output);
    p.Add("c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

    connection.Execute(@"create proc #TestProc 
                        @a int,
                        @b int output
                        as 
                        begin
                        set @b = 999
                        select 1111
                        return @a
                        end");
    connection.Query<int>("#TestProc", p, commandType: CommandType.StoredProcedure).First().IsEqualTo(1111);

    p.Get<int>("c").IsEqualTo(11);
    p.Get<int>("b").IsEqualTo(999);

}

/// <summary>
/// DbString用于代替数据库字符串类型
/// </summary>
public void TestDbString()
{
    var obj = connection.Query("select datalength(@a) as a, datalength(@b) as b, datalength(@c) as c, datalength(@d) as d, datalength(@e) as e, datalength(@f) as f",
        new
        {
            a = new DbString { Value = "abcde", IsFixedLength = true, Length = 10, IsAnsi = true },
            b = new DbString { Value = "abcde", IsFixedLength = true, Length = 10, IsAnsi = false },
            c = new DbString { Value = "abcde", IsFixedLength = false, Length = 10, IsAnsi = true },
            d = new DbString { Value = "abcde", IsFixedLength = false, Length = 10, IsAnsi = false },
            e = new DbString { Value = "abcde", IsAnsi = true },
            f = new DbString { Value = "abcde", IsAnsi = false },
        }).First();
    ((int)obj.a).IsEqualTo(10);
    ((int)obj.b).IsEqualTo(20);
    ((int)obj.c).IsEqualTo(5);
    ((int)obj.d).IsEqualTo(10);
    ((int)obj.e).IsEqualTo(5);
    ((int)obj.f).IsEqualTo(10);
}

/// <summary>
/// Query返回的结果是动态的
/// </summary>
public void TestFastExpandoSupportsIDictionary()
{
    var row = connection.Query("select 1 A, 'two' B").First() as IDictionary<string, object>;
    row["A"].IsEqualTo(1);
    row["B"].IsEqualTo("two");
}

 

class Parent
{
    public int Id { get; set; }
    public readonly List<Child> Children = new List<Child>();
}
class Child
{
    public int Id { get; set; }
}
/// <summary>
/// 可以在map的方法内对数据进行处理
/// </summary>
public void ParentChildIdentityAssociations()
{
    var lookup = new Dictionary<int, Parent>();
    var parents = connection.Query<Parent, Child, Parent>(@"select 1 as [Id], 1 as [Id] union all select 1,2 union all select 2,3 union all select 1,4 union all select 3,5",
        (parent, child) =>
        {
            Parent found;
            if (!lookup.TryGetValue(parent.Id, out found))
            {
                lookup.Add(parent.Id, found = parent);
            }
            found.Children.Add(child);
            return found;
        }).Distinct().ToDictionary(p => p.Id);
    parents.Count().IsEqualTo(3);
    parents[1].Children.Select(c => c.Id).SequenceEqual(new[] { 1, 2, 4 }).IsTrue();
    parents[2].Children.Select(c => c.Id).SequenceEqual(new[] { 3 }).IsTrue();
    parents[3].Children.Select(c => c.Id).SequenceEqual(new[] { 5 }).IsTrue();
}

SqlMapper.IDynamicParameters是参数的基类，可通过重写来封装自己的DB参数

/// <summary>
/// DynamicParameters的使用
/// </summary>
public void TestAppendingAnonClasses()
{
    DynamicParameters p = new DynamicParameters();
    p.AddDynamicParams(new { A = 1, B = 2 });
    p.AddDynamicParams(new { C = 3, D = 4 });

    var result = connection.Query("select @A a,@B b,@C c,@D d", p).Single();

    ((int)result.a).IsEqualTo(1);
    ((int)result.b).IsEqualTo(2);
    ((int)result.c).IsEqualTo(3);
    ((int)result.d).IsEqualTo(4);
}

public void TestAppendingADictionary()
{
    var dictionary = new Dictionary<string, object>();
    dictionary.Add("A", 1);
    dictionary.Add("B", "two");

    DynamicParameters p = new DynamicParameters();
    p.AddDynamicParams(dictionary);

    var result = connection.Query("select @A a, @B b", p).Single();

    ((int)result.a).IsEqualTo(1);
    ((string)result.b).IsEqualTo("two");
}

/// <summary>
/// 使用事务
/// </summary>
public void TestTransactionCommit()
{
    try
    {
        connection.Execute("create table #TransactionTest ([ID] int, [Value] varchar(32));");

        using (var transaction = connection.BeginTransaction())
        {
            connection.Execute("insert into #TransactionTest ([ID], [Value]) values (1, 'ABC');", transaction: transaction);

            transaction.Commit();
        }

        connection.Query<int>("select count(*) from #TransactionTest;").Single().IsEqualTo(1);
    }
    finally
    {
        connection.Execute("drop table #TransactionTest;");
    }
}

/// <summary>
/// 使用事务方法二
/// </summary>
public void TestCommandWithInheritedTransaction()
{
    connection.Execute("create table #TransactionTest ([ID] int, [Value] varchar(32));");

    try
    {
        using (var transaction = connection.BeginTransaction())
        {
            var transactedConnection = new TransactedConnection(connection, transaction);

            transactedConnection.Execute("insert into #TransactionTest ([ID], [Value]) values (1, 'ABC');");

            transaction.Rollback();
        }

        connection.Query<int>("select count(*) from #TransactionTest;").Single().IsEqualTo(0);
    }
    finally
    {
        connection.Execute("drop table #TransactionTest;");
    }
}

/// <summary>
/// 自定义映射方法
/// </summary>
public void TestCustomTypeMap()
{
    // default mapping
    var item = connection.Query<TypeWithMapping>("Select 'AVal' as A, 'BVal' as B").Single();
    item.A.IsEqualTo("AVal");
    item.B.IsEqualTo("BVal");

    // custom mapping
    var map = new CustomPropertyTypeMap(typeof(TypeWithMapping),
        (type, columnName) => type.GetProperties().Where(prop => prop.GetCustomAttributes(false).OfType<DescriptionAttribute>().Any(attr => attr.Description == columnName)).FirstOrDefault());
    SqlMapper.SetTypeMap(typeof(TypeWithMapping), map);

    item = connection.Query<TypeWithMapping>("Select 'AVal' as A, 'BVal' as B").Single();
    item.A.IsEqualTo("BVal");
    item.B.IsEqualTo("AVal");

    // reset to default
    SqlMapper.SetTypeMap(typeof(TypeWithMapping), null);
    item = connection.Query<TypeWithMapping>("Select 'AVal' as A, 'BVal' as B").Single();
    item.A.IsEqualTo("AVal");
    item.B.IsEqualTo("BVal");
}

public class TypeWithMapping
{
    [Description("B")]
    public string A { get; set; }

    [Description("A")]
    public string B { get; set; }
}

/// <summary>
/// 动态查询结果可以直接转化为IDictionary
/// </summary>
public void TestDynamicMutation()
{
    var obj = connection.Query("select 1 as [a], 2 as [b], 3 as [c]").Single();
    ((int)obj.a).IsEqualTo(1);
    IDictionary<string, object> dict = obj;
    Assert.Equals(3, dict.Count);
    Assert.IsTrue(dict.Remove("a"));
    Assert.IsFalse(dict.Remove("d"));
    Assert.Equals(2, dict.Count);
    dict.Add("d", 4);
    Assert.Equals(3, dict.Count);
    Assert.Equals("b,c,d", string.Join(",", dict.Keys.OrderBy(x => x)));
    Assert.Equals("2,3,4", string.Join(",", dict.OrderBy(x => x.Key).Select(x => x.Value)));

    Assert.Equals(2, (int)obj.b);
    Assert.Equals(3, (int)obj.c);
    Assert.Equals(4, (int)obj.d);
    try
    {
        ((int)obj.a).IsEqualTo(1);
        throw new InvalidOperationException("should have thrown");
    }
    catch (RuntimeBinderException)
    {
        // pass
    }
}

public void TestIssue131()
{
    var results = connection.Query<dynamic, int, dynamic>(
        "SELECT 1 Id, 'Mr' Title, 'John' Surname, 4 AddressCount",
        (person, addressCount) =>
        {
            return person;
        },
        splitOn: "AddressCount"
    ).FirstOrDefault();

    var asDict = (IDictionary<string, object>)results;

    asDict.ContainsKey("Id").IsEqualTo(true);
    asDict.ContainsKey("Title").IsEqualTo(true);
    asDict.ContainsKey("Surname").IsEqualTo(true);
    asDict.ContainsKey("AddressCount").IsEqualTo(false);
}

/// <summary>
/// 强制指定映射范围
/// </summary>
public void TestSplitWithMissingMembers()
{
    var result = connection.Query<Topic, Profile, Topic>(
    @"select 123 as ID, 'abc' as Title,
                cast('2013-1-1' as datetime) as CreateDate,
                'ghi' as Name, 'def' as Phone",
    (T, P) => { T.Author = P; return T; },
    null, null, true, "ID,Name").Single();

    result.ID.Equals(123);
    result.Title.Equals("abc");
    result.CreateDate.Equals(new DateTime(2013, 2, 1));
    result.Name.IsNull();
    result.Content.IsNull();

    result.Author.Phone.Equals("def");
    result.Author.Name.Equals("ghi");
    result.Author.ID.Equals(0);
    result.Author.Address.IsNull();
}
public class Profile
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string Phone { get; set; }
    public string Address { get; set; }
    //public ExtraInfo Extra { get; set; }
}

public class Topic
{
    public int ID { get; set; }
    public string Title { get; set; }
    public DateTime CreateDate { get; set; }
    public string Content { get; set; }
    public int UID { get; set; }
    public int TestColum { get; set; }
    public string Name { get; set; }
    public Profile Author { get; set; }
    //public Attachment Attach { get; set; }
}

class param {
    public int Age { get; set; }
    public string MName { get; set; } 
}
/// <summary>
/// 支持使用实体传递参数，实体不需与sql变量一一对应
/// </summary>
public void TestBindObjectParameters()
{
    var car = new param()
    {
        Age = 11,
        MName = "fff",
    };
    var p = new DynamicParameters(car);
    var val = connection.Query("select ?Age A ", p);
}