Dapper操作Sql Server和MySql数据库

学向勤中得，萤窗万卷书。这篇文章主要讲述Dapper操作Sql Server和MySql数据库相关的知识，希望能为你提供帮助。
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="Data Source=127.0.0.1; Database=hyd; 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; }

3. 实体类

public class Users { public int ID { get; set; }//自增主键 public string Name { get; set; } public intAge { 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 [email protected]"; 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 [email protected] WHERE [email protected]"; int result = conn.Execute(sqlCommandText, user); } //查 using (IDbConnection conn = DapperService.MySqlConnection()) { string sqlCommandText = @"SELECT * FROM USERS WHERE [email protected]"; 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 USERSLIMIT {0},{1} ", pageIndex * pageSize, pageSize); List< Users> user = conn.Query< Users> (sqlCommandText).ToList(); }

5. 防止Sql注入

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

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

public void TestDapperTransaction() { using (var conn = new MySql.Data.MySqlClient.MySqlConnection("server=localhost; Database=test; User Id=root; password=root")) { conn.Open(); //开户事务 IDbTransaction trans = conn.BeginTransaction(); try { int row = conn.Execute(@"update t set name=‘www.lanhusoft.com‘ where [email protected]", new { id = 3 }, trans); row += conn.Execute("delete from t where [email protected]", new { id = 5 }, trans); for (int i = 0; i < 100; i++) { conn.Execute(@"insert t(id, name) values (@id, @name)", new { id = i, name = "www.lanhusoft.com/" + i }); } trans.Commit(); //提交事务 conn.Close(); } catch (Exception) { trans.Rollback(); //回滚事务 conn.Close(); } } }