ORM之Dapper操作Sql Server和MySql数据库-白红宇

ORM之Dapper操作Sql Server和MySql数据库

阅读量：4971 次

发布时间：2019-06-12

本文共 5559 字，大约阅读时间需要 18 分钟。

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中添加连接字符串

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
     
       list = new List
      
       ();                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
       
        (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
        
          user = conn.Query
         
          (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
     
      (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  }

转载于:https://www.cnblogs.com/cnki/p/5723426.html

你可能感兴趣的文章