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中添加连接字符串
<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
public static SqlConnection SqlConnection()
{
string sqlconnectionString = ConfigurationManager.ConnectionStrings["sqlconnectionString"].ToString();
var connection = new SqlConnection(sqlconnectionString);
connection.Open();
return connection;
}
获取MySql的连接数据库对象:MySqlConnection
public static MySqlConnection MySqlConnection()
{
string mysqlconnectionString = ConfigurationManager.ConnectionStrings["mysqlconnectionString"].ToString();
var connection = new MySqlConnection(mysqlconnectionString);
connection.Open();
return connection;
}
封装
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)实体类
public class Users
{
public int ID { get; set; }//自增主键
public string Name { get; set; }
public int Age { get; set; }
}
4)增删改查
//增
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注入
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 }
