Dapper使用方法 - 漂流的鱼 - 博客园
多写原创的文章
posts - 10, comments - 76, trackbacks - 0, articles - 0
这里记下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
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)");
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);
connection.Execute("drop table #t");
class TestObj
public int _
internal int Internal { set { _internal = } }
public int _
private int Priv { set { _priv = } }
private int PrivGet { get { return _ } }
/// &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;
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 = return }).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);
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 =
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
@b int output
set @b = 999
select 1111
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",
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) =&
if (!lookup.TryGetValue(parent.Id, out found))
lookup.Add(parent.Id, found = parent);
found.Children.Add(child);
}).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()
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);
connection.Query&int&("select count(*) from #TransactionT").Single().IsEqualTo(1);
connection.Execute("drop table #TransactionT");
/// &summary&
/// 使用事务方法二
/// &/summary&
public void TestCommandWithInheritedTransaction()
connection.Execute("create table #TransactionTest ([ID] int, [Value] varchar(32));");
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 #TransactionT").Single().IsEqualTo(0);
connection.Execute("drop table #TransactionT");
/// &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 =
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);
((int)obj.a).IsEqualTo(1);
throw new InvalidOperationException("should have thrown");
catch (RuntimeBinderException)
public void TestIssue131()
var results = connection.Query&dynamic, int, dynamic&(
"SELECT 1 Id, 'Mr' Title, 'John' Surname, 4 AddressCount",
(person, addressCount) =&
splitOn: "AddressCount"
).FirstOrDefault();
var asDict = (IDictionary&string, object&)
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('' 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 { }
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 { }
class param {
public int Age { get; set; }
public string MName { get; set; }
/// &summary&
/// 支持使用实体传递参数，实体不需与sql变量一一对应
/// &/summary&
public void TestBindObjectParameters()
var car = new param()
MName = "fff",
var p = new DynamicParameters(car);
var val = connection.Query("select ?Age A ", p);在中我介绍了使用dapper进行CURD基本操作，但在示例代码中参数虽然也是通过@开头，但其实不是真正意义的参数化查询，而是拼接sql，这种方式不利于防止sql注入，所以在Dappe中可以使用DynamicParameters动态参数集合添加参数，从而实现dapper下的参数化查询；
using (var connection = new MySqlConnection(connstr))
//声明动态参数
DynamicParameters Parameters = new DynamicParameters();
//开始事务
IDbTransaction transaction = connection.BeginTransaction();
int cnt = <span style="color: #;
string sQuery = "Delete FROM user " + "WHERE User_Id=@Id";
//添加参数
Parameters.Add("Id", id);
connection.Execute(sQuery, Parameters, transaction, null, null);
//提交事务
return true;
catch (Exception ex)
//出现异常，事务Rollback
transaction.Rollback();
return false;
throw new Exception(ex.Message);
阅读(...) 评论()2016年1月 总版技术专家分月排行榜第二2015年11月 总版技术专家分月排行榜第二2015年10月 总版技术专家分月排行榜第二
优秀小版主
2016年1月 总版技术专家分月排行榜第二2015年11月 总版技术专家分月排行榜第二2015年10月 总版技术专家分月排行榜第二
优秀小版主
2016年1月 总版技术专家分月排行榜第二2015年11月 总版技术专家分月排行榜第二2015年10月 总版技术专家分月排行榜第二
优秀小版主
匿名用户不能发表回复！|
每天回帖即可获得10分可用分！小技巧：
你还可以输入10000个字符
(Ctrl+Enter)
请遵守CSDN，不得违反国家法律法规。
转载文章请注明出自“CSDN（www.csdn.net）”。如是商业用途请联系原作者。查看: 1313|回复: 1
怎么用参数化查询
编程互助，代码之美！红色代码诚邀您加入互助大家庭：）
才可以下载或查看，没有帐号？
Dapper怎么用参数化查询啊
比如 conn.Query&Keyword&(&select * from keywords where word=?word&, new {&&}).Count() != 0;&&怎么给?word 赋值？ 我用的是mysql
new {word=1}这样就行了
积分兑换实物排行
Powered by}