ORM之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中上加连接字符串

  <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();
            }

立马要是用到了DaMySQLpper的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  }

 

 

 

网站地图xml地图