C#

C#三层架构DataGridview的增删查改

来自 |已发表

版权声明：转载原创文章请以超链接形式请注明原文章出处，尊重作者，尊重原创！


恰饭广告




实现效果：（图片较大，加载较慢）

实现效果

项目的数据模块图：

项目结构图

数据库表：（设置标识符）

数据库表内容

BLL层： 

using Dgv.Model;
using Dgv.DAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Dgv.BLL
{
    public class UserInfoBLL
    {
        UserInfoDAL userDal = new UserInfoDAL();
        #region 加载所有数据
        public object LoadGetAllData()
        {
            object all = userDal.GetAllData();
            return all;
        }
        #endregion
        #region 插入数据
        public void UserInsert(UserInfo user)
        {
            userDal.UserInsert(user);
        }
        #endregion
        #region 更改数据
        public void UserUpdate(UserInfo user)
        {
            userDal.UserUpdate(user);
        }
        #endregion
        #region 删除数据
        public void UserDelete(int id)
        {
            userDal.DeleteById(id);
        }
        #endregion
        #region id查询
        public object LoadGetDataById(int id)
        {
            object _id = userDal.GetDataById(id);
            return _id;
        }
        #endregion
        #region 性别查询
        public object LoadGetDataBySex(string sex)
        {
            object _sex = userDal.GetDataBySex(sex);
            return _sex;
        }
        #endregion
        #region 年龄查询
        public object LoadGetDataByAge(int age)
        {
            object _age = userDal.GetDataByAge(age);
            return _age;
        }
        #endregion
        #region 部门查询
        public object LoadGetDataByDepartment(string department)
        {
            object _dep = userDal.GetDataByDepartment(department);
            return _dep;
        }
        #endregion
    }
}

DAL层：

using Dgv.DBHelper;
using Dgv.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Dgv.DAL
{
    public class UserInfoDAL
    {
        SQLHelper sqlhelp = new SQLHelper();
        private UserInfo ToUserInfo(DataRow row)
        {
            UserInfo user = new UserInfo();
            user.Id = (int)row["Id"];
            user.Name = (string)row["Name"];
            user.Sex = (string)row["Sex"];
            user.Age = (int)row["Age"];
            user.Department = (string)row["Department"];
            user.BirthDay = (DateTime?)(row["BirthDay"]);
            user.HireDate = (DateTime?)(row["HireDate"]);
            user.TelNum = (string)row["TelNum"];
            user.Address = (string)row["Address"];
            return user;
        }
        #region 加载所有信息
        public UserInfo[] GetAllData()
        {
            DataTable table = sqlhelp.ExecuteDataTable("select * from T_UserInfo");
            UserInfo[] user= new UserInfo[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                user[i] = ToUserInfo(row);
            }
            return user;
        }
        #endregion
        #region 添加用户信息
        public void UserInsert(UserInfo user)
        {
            sqlhelp.ExecuteNonQuery(@"INSERT INTO [T_UserInfo]
                       ([Name],[Sex],[Age],[Department]
                       ,[BirthDay],[HireDate],[Address],[TelNum])
                        VALUES
                       (@Name,@Sex,@Age,@Department,@BirthDay,@HireDate,@Address,@TelNum)",
                new SqlParameter("@Name", user.Name),
                new SqlParameter("@Sex", user.Sex),
                new SqlParameter("@Age", user.Age),
                new SqlParameter("@Department", user.Department),
                new SqlParameter("@BirthDay", user.BirthDay),
                new SqlParameter("@HireDate", user.HireDate),
                new SqlParameter("@Address", user.Address),
                new SqlParameter("@TelNum", user.TelNum));
        }
        #endregion
        #region 修改用户信息
        public void UserUpdate(UserInfo user)
        {
            sqlhelp.ExecuteNonQuery(@"UPDATE [T_UserInfo]
                        SET [Name]=@Name
                           ,[Sex]=@Sex
                           ,[Age]=@Age
                           ,[Department]=@Department
                           ,[BirthDay] = @BirthDay
                           ,[HireDate]=@HireDate
                           ,[Address] = @Address
                           ,[TelNum] = @TelNum
                            WHERE Id=@Id",
                new SqlParameter("@Name", user.Name),
                new SqlParameter("@Sex", user.Sex),
                new SqlParameter("@Age", user.Age),
                new SqlParameter("@Department", user.Department),
                new SqlParameter("@BirthDay", user.BirthDay),
                new SqlParameter("@HireDate", user.HireDate),
                new SqlParameter("@Address", user.Address),
                new SqlParameter("@TelNum", user.TelNum),
                new SqlParameter("@Id", user.Id));
        }
        #endregion
        #region 删除用户信息
        public void DeleteById(int id)
        {
            sqlhelp.ExecuteNonQuery("delete from T_UserInfo where Id=@Id",
                new SqlParameter("@Id", id));
        }
        #endregion
        #region id查找
        public UserInfo[] GetDataById(int id)
        {
            DataTable dt = sqlhelp.ExecuteDataTable("select * from T_UserInfo where Id=@Id",
                new SqlParameter("@Id", id));
            UserInfo[] user = new UserInfo[dt.Rows.Count];
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else if (dt.Rows.Count > 1)
            {
                throw new Exception("严重错误，查出多条数据！");
            }
            else
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow row = dt.Rows[i];
                    user[i] = ToUserInfo(row);
                }
            }
            return user;
        }
        #endregion
        #region 性别查找
        public UserInfo[] GetDataBySex(string Sex)
        {
            DataTable dt = sqlhelp.ExecuteDataTable("select * from T_UserInfo where Sex=@Sex",
                new SqlParameter("@Sex", Sex));
            UserInfo[] user = new UserInfo[dt.Rows.Count];
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow row = dt.Rows[i];
                    user[i] = ToUserInfo(row);
                }
            }
            return user;
        }
        #endregion
        #region 年龄查找
        public UserInfo[] GetDataByAge(int Age)
        {
            DataTable dt = sqlhelp.ExecuteDataTable("select * from T_UserInfo where Age=@Age",
                new SqlParameter("@Age", Age));
            UserInfo[] user = new UserInfo[dt.Rows.Count];
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow row = dt.Rows[i];
                    user[i] = ToUserInfo(row);
                }
            }
            return user;
        }
        #endregion
        #region 部门查找
        public UserInfo[] GetDataByDepartment(string Department)
        {
            DataTable dt = sqlhelp.ExecuteDataTable("select * from T_UserInfo where Department LIKE @Department",
                new SqlParameter("@Department", "%"+Department+"%"));
            UserInfo[] user = new UserInfo[dt.Rows.Count];
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow row = dt.Rows[i];
                    user[i] = ToUserInfo(row);
                }
            }
            return user;
        }
        #endregion
    }
}

Model层（Entity类）：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Dgv.Model
{
    public class UserInfo
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Sex { get; set; }
        public int Age { get; set; }
        public string Department { get; set; }
        public DateTime? BirthDay { get; set; }
        public DateTime? HireDate { get; set; }
        public string TelNum { get; set; }
        public string Address { get; set; }
    }
}

UI层：（EditDgv.cs）

using System;
using Dgv.Model;
using Dgv.BLL;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Dgv
{
    public partial class EditDgv : Form
    {
        public EditDgv()
        {
            InitializeComponent();
        }
        UserInfoBLL userBll = new UserInfoBLL();
        public bool Insert { get; set; }
        private DataGridViewRow _CurrentRow;
        public DataGridViewRow CurrentRow
        {
            get { return _CurrentRow; }
            set { _CurrentRow = value; }
        }
        #region 保存数据
        private void btnSave_Click(object sender, EventArgs e)
        {
            UserInfo user = new UserInfo();
            user.Name = textName.Text;
            user.Sex = radioButton1.Checked ? "男" : "女";
            user.Age = Convert.ToInt32(textAge.Text);
            user.Department = cobDepart.Text;
            user.BirthDay = Convert.ToDateTime(dateBirth.Text);
            user.HireDate = Convert.ToDateTime(dateHire.Text);
            user.Address = textAddress.Text;
            user.TelNum = textTel.Text;
            if (Insert)    //如果添加数据走里面这一段代码
            {
                userBll.UserInsert(user);
                MessageBox.Show("添加成功!");
            }
            else           //否则,修改走里面这一段代码
            {
                user.Id = Convert.ToInt32(_CurrentRow.Cells["Id"].Value.ToString());
                userBll.UserUpdate(user);
                MessageBox.Show("修改成功!");
            }
            this.Close();
        }
        #endregion
        #region 窗体运行加载数据
        private void EditDgv_Load(object sender, EventArgs e)
        {
            cobDepart.SelectedIndex = 0;
            if (Insert)  //增加
            {
                return;
            }
            else     //更改
            {
                textName.Text = _CurrentRow.Cells["Name"].Value.ToString();
                string s = _CurrentRow.Cells["Sex"].Value.ToString();
                if (s == "男")
                {
                    radioButton1.Checked = true;
                }
                else
                {
                    radioButton2.Checked = true;
                }
                textAge.Text = _CurrentRow.Cells["Age"].Value.ToString();
                cobDepart.Text = _CurrentRow.Cells["Department"].Value.ToString();
                dateBirth.Text = _CurrentRow.Cells["Birthday"].Value.ToString();
                dateHire.Text = _CurrentRow.Cells["HireDate"].Value.ToString();
                textAddress.Text = _CurrentRow.Cells["Address"].Value.ToString();
                textTel.Text = _CurrentRow.Cells["TelNum"].Value.ToString();
            }
        }
        #endregion
    }
}

UI层：（MainDgv.cs）

using System;
using Dgv.BLL;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Dgv
{
    public partial class MainDgv : Form
    {
        public MainDgv()
        {
            InitializeComponent();
        }
        UserInfoBLL userBll = new UserInfoBLL();
        #region 加载所有数据方法
        public void LoadAllData()
        {
            dataGridView1.DataSource = userBll.LoadGetAllData();
        }
        #endregion
        #region 加载id数据
        public void LoadDataById(int id)
        {
            dataGridView1.DataSource = userBll.LoadGetDataById(id);
        }
        #endregion
        #region 加载性别数据
        public void LoadDataBySex(string sex)
        {
            dataGridView1.DataSource = userBll.LoadGetDataBySex(sex);
        }
        #endregion
        #region 加载年龄数据
        public void LoadDataByAge(int Age)
        {
            dataGridView1.DataSource = userBll.LoadGetDataByAge(Age);
        }
        #endregion
        #region 加载部门数据
        public void LoadDataByDepartment(string department)
        {
            dataGridView1.DataSource = userBll.LoadGetDataByDepartment(department);
        }
        #endregion
        #region 运行程序加载数据
        private void MainDgv_Load(object sender, EventArgs e)
        {
            LoadAllData();
            cobQuery.SelectedIndex = 0;
            string[] htext = { "编号", "姓名", "性别", "年龄", "部门", "出生日期", "入职时间", "联系号码", "通讯地址" };
            for (int i = 0; i < htext.Length; i++)
            {
                dataGridView1.Columns[i].HeaderText = htext[i];
            }
        }
        #endregion
        #region 添加数据
        private void btnAdd_Click(object sender, EventArgs e)
        {
            EditDgv editDgv = new EditDgv();
            editDgv.Insert = true;
            editDgv.ShowDialog();
            LoadAllData();
        }
        #endregion
        #region 删除数据
        private void btnDelete_Click(object sender, EventArgs e)
        {
            DataGridViewRow _Row = dataGridView1.CurrentRow;
            DataGridViewSelectedRowCollection selectedrows = dataGridView1.SelectedRows;
            if (_Row == null)
            {
                MessageBox.Show("没有数据行,操作无效!");
                return;
            }
            List<long> list = new List<long>();
            foreach (DataGridViewRow singleRow in selectedrows)
            {
                list.Add(Convert.ToInt32(singleRow.Cells["Id"].Value.ToString()));
            }
            if (MessageBox.Show("确定删除此行数据?", "删除信息", MessageBoxButtons.OKCancel) == DialogResult.OK)
            {
                foreach (int s in list)
                {
                    userBll.UserDelete(s);
                }
                MessageBox.Show("删除数据成功!");
                LoadAllData();
            }
        }
        #endregion
        #region 修改数据
        private void btnEdit_Click(object sender, EventArgs e)
        {
            DataGridViewRow _Row = dataGridView1.CurrentRow;
            if (_Row == null)
            {
                MessageBox.Show("没有数据行,当前操作无效!");
                return;
            }
            EditDgv editDgv = new EditDgv();
            editDgv.CurrentRow = _Row;
            editDgv.Insert = false;
            editDgv.ShowDialog();
            LoadAllData();
        }
        #endregion
        #region 查询数据
        private void btnQuery_Click(object sender, EventArgs e)
        {
            string Input = textQuery.Text;
            string SelectedText = cobQuery.Text;
            if (SelectedText == "")
            {
                MessageBox.Show("请选择查询条件!");
                return;
            }
            if (Input == "")
            {
                if (SelectedText == "全部")
                {
                    return;
                }
                else
                {
                    MessageBox.Show("请输入查询条件!");
                    return;
                }
            }
            switch (SelectedText)
            {
                case "全部":
                    LoadAllData();
                    break;
                case "编号":
                    LoadDataById(Convert.ToInt32(Input));
                    break;
                case "性别":
                    LoadDataBySex(Input);
                    break;
                case "年龄":
                    LoadDataByAge(Convert.ToInt32(Input));
                    break;
                case "部门":
                    LoadDataByDepartment(Input);
                    break;
                default:
                    break;
            }
        }
        #endregion
        #region comboBox更改属性事件
        private void cobQuery_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cobQuery.Text == "全部")
            {
                textQuery.Text = "全部";
                textQuery.Enabled = false;
            }
            else
            {
                textQuery.Text = "";
                textQuery.Enabled = true;
            }
        }
        #endregion
    }
}

还有数据库访问类（SQLHelper.cs）代码太多就不贴出来了

GitHub源码下载：https://github.com/kiritobin/DgvDataThreeFram

原文链接：https://www.idaobin.com/archives/1100.html

让我恰个饭吧.ヘ(￣ω￣ヘ)

支付宝 ——————- 微信
图片加载中图片加载中



恰饭广告


广告

你可能也喜欢

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注

70 − 66 =