Dapper基本用法--MySql

NuGet:

• Dapper 2.0.35
• MySql.Data
• System.Data

实体（Entity）

   public class student
    {
        public int Id { get; set; }
        public string RealName { get; set; }
        public DateTime EnrollmenDate { get; set; }
    }

配置文件数据库连接

  <connectionStrings>
    <add name="mysqlConn" connectionString="server=127.0.0.1;database=数据库;User ID=root;password=密码"/>
  </connectionStrings>

MySql连接字符串

private static string connStr = string.Empty;
        public MySqlOperation()
        {
            connStr = System.Configuration.ConfigurationManager.ConnectionStrings["mysqlConn"].ConnectionString;
        }

查询语句

        /// <summary>
        /// 查询所有
        /// </summary>
        /// <returns>lists</returns>
        public List<student> Query()
        {
            if (string.IsNullOrEmpty(connStr))
            {
                return new List<student>(); ;
            }
            try
            {
                using (IDbConnection db = new MySqlConnection(connStr))
                {
                    db.Open();//打开数据库
                    string mysqlStr = "select * from  student";
                    List<student> lists = db.Query<student>(mysqlStr).ToList();
                    db.Close();//关闭数据库
                    return lists;
                }
            }
            catch (Exception)
            {
                return new List<student>();
            }
        }
        /// <summary>
        /// 根据Id查询
        /// </summary>
        /// <param name="id"></param>
        /// <returns>lists</returns>
        public List<student> Query(string id)
        {
            if (string.IsNullOrEmpty(connStr))
            {
                return new List<student>();
            }
            try
            {
                using (IDbConnection db = new MySqlConnection(connStr))
                {
                    db.Open();//打开数据库
                    string mysqlStr = "select * from  student where Id=@id";
                    List<student> lists = db.Query<student>(mysqlStr, new { Id = id }).ToList();
                    db.Close();//关闭数据库
                    return lists;
                }
            }
            catch (Exception)
            {
                return new List<student>();
            }
        }

插入语句

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="student">student</param>
        /// <returns>-1:连接字符串为空，-2：新增失败</returns>
        public int Insert(student student)
        {
            if (string.IsNullOrEmpty(connStr))
            {
                return -1;
            }
            try
            {
                using (IDbConnection db = new MySqlConnection(connStr))
                {
                    db.Open();//打开数据库
                    string mysqlStr = "insert into student(Id,RealName,EnrollmenDate) values(@Id,@RealName,@EnrollmenDate)";
                    int list = db.Execute(mysqlStr, student);
                    db.Close();//关闭数据库
                    return list;
                }
            }
            catch (Exception)
            {
                return -2;
            }
        }

删除数据

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delect(string id)
        {
            if (string.IsNullOrEmpty(connStr))
            {
                return -1;
            }
            try
            {
                using (IDbConnection db = new MySqlConnection(connStr))
                {
                    db.Open();//打开数据库
                    string mysqlStr = "delete from student where Id=@id";
                    int list = db.Execute(mysqlStr, new { Id = id });
                    db.Close();//关闭数据库
                    return list;
                }
            }
            catch (Exception)
            {
                return -2;
            }
        }

更新数据

        /// <summary>
        /// 更新信息
        /// </summary>
        /// <param name="student"></param>
        /// <returns>-1:连接字符串为空，-2：更新失败</returns>
        public int Update(student student)
        {
            if (string.IsNullOrEmpty(connStr))
            {
                return -1;
            }
            try
            {
                using (IDbConnection db = new MySqlConnection(connStr))
                {
                    db.Open();//打开数据库
                    string mysqlStr = "update student set RealName=@RealName where Id=@Id";
                    int list = db.Execute(mysqlStr, student);
                    db.Close();//关闭数据库
                    return list;
                }
            }
            catch (Exception)
            {
                return -2;
            }
        }