using Dapper;

SqlConnection connection = Program.SqlConnection();

var d = connection.Query<Dog>("select * from dog where id = 1", null).Single<Dog>();

var dd = connection.Query<Dog>("select * from dog where id < 10", null).ToList<Dog>();

//动态参数

connection.Execute("INSERT INTO dog (Age,Name,Weight) VALUES (@age,@name,@Weight)",

new { @age = i,@name = Guid.NewGuid().ToString(), @Weight = i });   

//直接传入实体

connection.Execute("INSERT INTO dog (Age,Name,Weight) VALUES (@age,@name,@Weight)",model);

Execute a query and map the results to a strongly typed List

Note: all extension methods assume the connection is already open, they will fail if the connection is closed.

public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

Example usage:

publicclassDog

{

    publicint?Age{get;set;}

    publicGuidId{get;set;}

    publicstringName{get;set;}

    publicfloat?Weight{get;set;}

    publicintIgnoredProperty{get{return1;}}

}            

var guid =Guid.NewGuid();

var dog = connection.Query<Dog>("select Age = @Age, Id = @Id",new{Age=(int?)null,Id= guid });

dog.Count()

    .IsEqualTo(1);

dog.First().Age

    .IsNull();

dog.First().Id

    .IsEqualTo(guid);

Execute a query and map it to a list of dynamic objects

public static IEnumerable<dynamic> Query (this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

This method will execute SQL and return a dynamic list.

Example usage:

 var rows = connection.Query("select 1 A, 2 B union all select 3, 4");

((int)rows[0].A)

   .IsEqualTo(1);

((int)rows[0].B)

   .IsEqualTo(2);

((int)rows[1].A)

   .IsEqualTo(3);

((int)rows[1].B)

    .IsEqualTo(4);

Execute a Command that returns no results

public static int Execute(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null)

Example usage:

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);

Execute a Command multiple times

The same signature also allows you to conveniently and efficiently execute a command multiple times (for example to bulk-load data)

Example usage:

connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)",

    new[]{new{ a=1, b=1},new{ a=2, b=2},new{ a=3, b=3}}

  ).IsEqualTo(3);// 3 rows inserted: "1,1", "2,2" and "3,3"

This works for any parameter that implements IEnumerable<T> for some T.

var
dd = connection.Query<Dog>("select
* from dog where id < 10", null).ToList<Dog>();