ORM 框架 Dapper的CRUD案例

ORM 框架 Dapper的CRUD

首先创建一个项目（我这里创建的是winform），添加引用

创建实体类，连接数据库的DBhelper以及实体类的service
目录结构：

Person.cs

 public class Person
    {
        public int id { get; set; }
        public string firstName { get; set; }
        public string lastName { get; set; }
        public string email { get; set; }
       // public DateTime CreateOn { get; set; }

    }

DBhelper

public class DBHelper
    {
        public static string ConnStrings
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
            }
        }
    }

App.config中的连接字符串

 <connectionStrings>
    <add name="connString" connectionString="server=.;database=stuDB;uid=sa;pwd=123456"/>
  </connectionStrings>

PersonService

public class PersonService
    {
        //根据用户姓氏 查询用户的集合
        public List<Person> FindListByLastName(string lastname)
        {
            //查询
            using (IDbConnection db = new SqlConnection(DBHelper.ConnStrings))
            {
                string sql = $"select * from Person where lastName = @lastName";
                IEnumerable<Person> lst = db.Query<Person>(sql, new { lastname = lastname });
                return lst.ToList();
            }
        }

        //根据id查询用户
        public Person FindByPersonId(int perID)
        {
            using (IDbConnection db = new SqlConnection(DBHelper.ConnStrings))
            {
                string sql = "select * from Person where id=@id";
                IEnumerable<Person> list = db.Query<Person>(sql,new { id=perID});
                return list.FirstOrDefault(); //返回序列中的第一个元素，如果没有则返回默认值
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="person"></param>
        /// <returns></returns>
        public bool InsertData(Person person)
        {
            using(IDbConnection db = new SqlConnection(DBHelper.ConnStrings))
            {
                //准备插入语句
                string sql = "insert into Person(firstName,lastName,email) values (@firstName,@lastName,@email)";
                int res = db.Execute(sql, person);
                return res > 0;

            }
        }


        /// <summary>
        /// 根据id更新数据
        /// </summary>
        /// <param name="person"></param>
        /// <returns></returns>
        public bool UpdateData(Person person)
        {
            using (IDbConnection db = new SqlConnection(DBHelper.ConnStrings))
            {
                //准备更新语句
                string sql = "update Person set firstName=@firstName,lastName=@lastName,email=@email where id=@id";
                //执行更新语句
                int res = db.Execute(sql, person);
                return res > 0;
            }
        }


        public bool DeleteData(Person person)
        {
            using (IDbConnection db = new SqlConnection(DBHelper.ConnStrings))
            {
                string sql = "delete from Person where id = @id";
                int res = db.Execute(sql, person);
                return res > 0;
            }
        }
    }

1. 根据用户姓氏 查询用户的集合
   Form1
   
   后台代码

     /// <summary>
        /// 搜索按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSearch_Click(object sender, EventArgs e)
        {
            //实例化业务逻辑类
            PersonService ps = new PersonService();
            List<Person> list = ps.FindListByLastName(this.txtName.Text.Trim());
            //绑定数据源
            listContent.DataSource = list;
            //显示成员
            listContent.DisplayMember = "email";

        }

2. 插入数据
   
   后台代码

private void btnAdd_Click(object sender, EventArgs e)
        {
            PersonService personService = new PersonService();
            Person person = new Person();
            person.firstName = txtfName.Text.Trim();
            person.lastName = txtlName.Text.Trim();
            person.email = txtEmail.Text.Trim();
            var success =  personService.InsertData(person);
            MessageBox.Show(success ? "插入成功" : "插入失败");

        }

3. 根据id，修改用户
   
   后台代码

 /// <summary>
        /// 搜索按钮的事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSearch_Click(object sender, EventArgs e)
        {
            PersonService ps = new PersonService();
            int perID = int.Parse(this.txtid.Text.Trim());
            //根据id查找用户对象的方法，并返回person类型的对象
            Person person = ps.FindByPersonId(perID);

            //绑定数据
            this.txtfName.Text = person.firstName;
            this.txtlName.Text = person.lastName;
            this.txtEmail.Text = person.email;
        }

        /// <summary>
        /// 更新按钮的事件处理
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            PersonService ps = new PersonService();
            //对象初始化器
            Person person = new Person()
            {
                id = Convert.ToInt32(this.txtid.Text.Trim()),
                firstName = this.txtfName.Text.Trim(),
                lastName = this.txtlName.Text.Trim(),
                email = this.txtEmail.Text.Trim()
               
            };
            var success = ps.UpdateData(person);
            MessageBox.Show(success ? "更新成功！" : "更新失败");

        }

4. 删除用户
   
   后台代码

      /// <summary>
        /// 删除按钮的事件处理
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnDelete_Click(object sender, EventArgs e)
        {
            //获取要删除的id
            int personId = Convert.ToInt32(this.txtDelId.Text);
            Person person = new Person
            {
                id = personId
            };
            PersonService personService = new PersonService();
            var success = personService.DeleteData(person);
            MessageBox.Show(success ? "删除成功！" : "删除失败");
        }