Dapper操作Sql Server和MySql数据库

1.为什么选择Dapper

1)轻量。

2)速度快。Dapper的速度接近与IDataReader，取列表的数据超过了DataTable。

3)支持多种数据库。Dapper可以在所有Ado.net Providers下工作，包括sqlite, sqlce, firebird, oracle, MySQL, PostgreSQL and SQL Server

4)可以映射一对一，一对多，多对多等多种关系。

5)性能高。通过Emit反射IDataReader的序列队列，来快速的得到和产生对象，性能不错。

6)支持FrameWork2.0，3.0，3.5，4.0，4.5

7)Dapper语法十分简单。并且无须迁就数据库的设计

2.使用教程

1)在配置文件web.config中添加连接字符串

1 2 3 4

<connectionStrings>    <add name= "sqlconnectionString"  connectionString= "server=127.0.0.1;database=MyDataBase;User=sa;password=123456;Connect Timeout=1000000" />    <add name= "mysqlconnectionString"  connectionString= "Database=hyd;Data Source=127.0.0.1;User Id=root;Password=root;CharSet=utf8;port=3306" /> </connectionStrings>

2)获取连接数据库对象

  获取Sql Server的连接数据库对象:SqlConnection  

1 2 3 4 5 6 7

public  static  SqlConnection SqlConnection()          {              string  sqlconnectionString = ConfigurationManager.ConnectionStrings[ "sqlconnectionString" ].ToString();              var  connection =  new  SqlConnection(sqlconnectionString);              connection.Open();              return  connection;          }

　获取MySql的连接数据库对象:MySqlConnection  

1 2 3 4 5 6 7

public  static  MySqlConnection MySqlConnection()     {         string  mysqlconnectionString = ConfigurationManager.ConnectionStrings[ "mysqlconnectionString" ].ToString();         var  connection =  new  MySqlConnection(mysqlconnectionString);         connection.Open();         return  connection;     }

封装

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

public  class  DapperService      {          public  static  SqlConnection SqlConnection()          {              string  sqlconnectionString = ConfigurationManager.ConnectionStrings[ "sqlconnectionString" ].ToString();              var  connection =  new  SqlConnection(sqlconnectionString);              connection.Open();              return  connection;          }          public  static  MySqlConnection MySqlConnection()          {              string  mysqlconnectionString = ConfigurationManager.ConnectionStrings[ "mysqlconnectionString" ].ToString();              var  connection =  new  MySqlConnection(mysqlconnectionString);              connection.Open();              return  connection;          }      }

3)实体类

1 2 3 4 5 6

public  class  Users   {       public  int  ID {  get ;  set ; } //自增主键       public  string  Name {  get ;  set ; }       public  int   Age {  get ;  set ; }   }

4)增删改查

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55

//增      using  (IDbConnection conn = DapperService.MySqlConnection())      {          Users user =  new  Users();          user.Name =  "CNKI" ;          user.Age = 38;          string  sqlCommandText =  @"INSERT INTO USERS(Name,Age)VALUES(@Name,@Age)" ;          int  result = conn.Execute(sqlCommandText, user);      }    //批量增      using  (IDbConnection conn = DapperService.MySqlConnection())      {          List<Users> list =  new  List<Users>();          for  ( int  i = 0; i < 5; i++)          {              Users user =  new  Users();              user.Name =  "CNKI" ;              user.Age = 38;              list.Add(user);          }          string  sqlCommandText =  @"INSERT INTO USERS(Name,Age)VALUES(@Name,@Age)" ;          int  result = conn.Execute(sqlCommandText, list);      }      //删      using  (IDbConnection conn = DapperService.MySqlConnection())      {          Users user =  new  Users();          user.ID = 1;          string  sqlCommandText =  @"DELETE FROM USERS WHERE ID=@ID" ;          int  result = conn.Execute(sqlCommandText, user);      }      //改      using  (IDbConnection conn = DapperService.MySqlConnection())      {          Users user =  new  Users();          user.ID = 2;          user.Name =  "CNKI" ;          user.Age = 18;          string  sqlCommandText =  @"UPDATE USERS SET Age=@Age WHERE ID=@ID" ;          int  result = conn.Execute(sqlCommandText, user);      }      //查      using  (IDbConnection conn = DapperService.MySqlConnection())      {          string  sqlCommandText =  @"SELECT * FROM USERS WHERE ID=@ID" ;          Users user = conn.Query<Users>(sqlCommandText,  new   { ID=2 }).FirstOrDefault();      }     //分页      using  (IDbConnection conn = DapperService.MySqlConnection())      {          int  pageIndex = 0;          int  pageSize = 2;          string  sqlCommandText =  string .Format( @"SELECT * FROM USERS  LIMIT {0},{1} " , pageIndex * pageSize, pageSize);          List<Users> user = conn.Query<Users>(sqlCommandText).ToList();      }

3.防止Sql注入

1 2 3 4 5 6 7

using  (IDbConnection conn = DapperService.MySqlConnection())   {       string  sqlCommandText =  @"SELECT * FROM USER WHERE ID=@ID" ;       var  p =  new  DynamicParameters();       p.Add( "@ID" , 1);       User user2 = conn.Query<User>(sqlCommandText,p).FirstOrDefault();   }

这要用到了Dapper的DynamicParameters动态参数集合类，从上面可以看到可以能过Add方法加入参数。最后通过conn.Query<MSys_Admin>(sqlText, p)执行sql,，返回结果。因为用的是命令参数的形式，让sql注入无机可乘，所以这种方案是安全的。

4.操作事物

 1 [TestMethod]
 2  public void TestDapperTransaction()
 3  {
 4      using (var conn = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;User Id=root;password=root;Database=test"))
 5      {
 6          conn.Open();
 7          IDbTransaction trans = conn.BeginTransaction();
 8          int row = conn.Execute(@"update t set name='www.lanhusoft.com' where id=@id", new { id = 3 }, trans);
 9          row += conn.Execute("delete from t where id=@id", new { id = 5 }, trans);
10          for (int i = 0; i < 100; i++)
11          {
12              conn.Execute(@"insert t(id, name) values (@id, @name)", new { id = i, name = "www.lanhusoft.com/" + i });
13          }
14          trans.Commit();
15          conn.Close();
16      }
17  }
参见:
http://www.cnblogs.com/cnki/p/5723426.html