Dapper连接MySql数据库

首先通过NuGet在项目中分别安装Dapper和MySql.Data的引用

Web.config

<connectionStrings>
    <add name="MySqlConnectionString" connectionString="Database=mysql_test;Data Source=localhost;User Id=root;Password=123456;CharSet=utf8;port=3306" />
  </connectionStrings>

 

public class DapperService
{
        public static MySqlConnection MySqlConnection()
        {
            string mysqlConnectionString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ToString();
            var connection = new MySqlConnection(mysqlConnectionString);
            connection.Open();
            return connection;
        }
}

MySql数据库

User实体

public class User
{
        public int ID { get; set; }

        public string Name { get; set; }

        public bool Sex { get; set; }

        public decimal Age { get; set; }

        public string Tel { get; set; }

        public DateTime? ModifyTime { get; set; }
}

Select查询

using (IDbConnection conn = DapperService.MySqlConnection())
{
        string sqlCommandStr = @"SELECT * FROM USER";
        List<User> userList = conn.Query<User>(sqlCommandStr).ToList();
        return View(userList);
}

Insert插入

public void InsertInfo(User user)
{
     string insertSqlStr = @"INSERT INTO  user(Name,Sex,Age,Tel)VALUES(@Name,@Sex,@Age,@Tel)";

     using (IDbConnection conn = DapperService.MySqlConnection())
     {
　　　　　　// conn.Execute(insertSqlStr, new { Name="ssss",Sex=true,Age="22",Tel="2222"});　//这种是手动赋值
          conn.Execute(insertSqlStr, user);　//这种是按实体插入
     }
}