学向勤中得,萤窗万卷书。这篇文章主要讲述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(); } } }
推荐阅读
- Could not find com.android.tools.build:gradle:2.2.3
- Android-SDK目录结构
- Spring閲岀殑applicationContext.getBeansWithAnnotation
- 使用CSS3的appearance属性改变元素的外观
- 记录Appium-desktop踩过的坑could not find devices
- Appium Desired Capabilities-Android Only
- 挖财记账理财下载|挖财记账理财安卓版下载
- Appimage版wine乱码解决
- 安卓-APP应用文件路径