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";

        }
  1. 插入数据
    在这里插入图片描述
    后台代码
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 ? "插入成功" : "插入失败");

        }
  1. 根据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 ? "更新成功!" : "更新失败");

        }
  1. 删除用户
    在这里插入图片描述
    后台代码
      /// <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 ? "删除成功!" : "删除失败");
        }
  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值