入门C#之数据库增删改查II

四.数据库的修改数据

1.添加控件，所对应的属性为

2.给DataGridView控件添加RowEnter事件代码，作用是，让表格中鼠标选中的行，其数据对应显示到对话框中

private void dtgCustomers_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            int rowselected=e.RowIndex;//current row
            txtId.Text = dtgCustomers.Rows[rowselected].Cells[0].Value.ToString();
            txtCustomerName.Text=dtgCustomers.Rows[rowselected].Cells[1].Value.ToString();
            txtPhoneNumber.Text = dtgCustomers.Rows[rowselected].Cells[2].Value.ToString();
            txtBillAmount.Text = dtgCustomers.Rows[rowselected].Cells[3].Value.ToString();
            txtProduct.Text = dtgCustomers.Rows[rowselected].Cells[4].Value.ToString();
        }

3.在Dal.cs文件里添加update函数

public bool Update(DALCustomer obj)
        {

            //open connection
            try
            {
                string connstr = @"Data Source=ACER-PC\SQLEXPRESS;Initial Catalog=CustomerDB;Integrated Security=True";
                SqlConnection conn = new SqlConnection(connstr);
                conn.Open();

                //sql--> command
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = "update tblCustomer " +
                    "set CustomerName= '" + obj.CustomerName + "'"+
                    " ,PhoneNumber='" + obj.PhoneNumber +
                    "' ,ProductName='" + obj.ProductName +
                    "' ,BillAmount=" + obj.BillAmount +
                    " where customerId=" + obj.CustomerId;
                command.ExecuteNonQuery();

                //close connection
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

4.给CustomerService.cs里添加update函数

public void Update(BLCustomer customer)
        {
            new CustomerDal().Update(new DALCustomer()
        {
                CustomerId  = customer.CustomerId,
                PhoneNumber = customer.PhoneNumber,
                CustomerName = customer.CustomerName,
                ProductName = customer.ProductName,
                BillAmount = customer.BillAmount
            });
        }

以及相应的Entity Framework的update函数

public void EF_Update(BLCustomer customer)
        {
            using (var context = new DbCustomer())
            {
                var findTheOne = context.tblCustomers.FirstOrDefault(x => x.CustomerId == customer.CustomerId);
                if (findTheOne == null) { throw new DataException(); }
                findTheOne.CustomerName = customer.CustomerName;

                context.SaveChanges();
            }
        }

5.给UI的update按钮添加click事件，附带一个ClearUI的函数

其中是调用了Dal的Update函数

private void btnUpdate_Click(object sender, EventArgs e)
        {
            //loading the values from the UI
            BLCustomer updatedCustomer = new BLCustomer();
            updatedCustomer.CustomerName = txtCustomerName.Text;
            updatedCustomer.PhoneNumber = txtPhoneNumber.Text;
            updatedCustomer.ProductName = txtProduct.Text;
            updatedCustomer.BillAmount = Convert.ToDecimal(txtBillAmount.Text);
            updatedCustomer.CustomerId = int.Parse(txtId.Text);

            var service = new CustomerService();
            service.Update(updatedCustomer);

            dtgCustomers.DataSource = service.EF_All();

            ClearUI();
        }

private void ClearUI()
        {
            txtCustomerName.Text = "";
            txtPhoneNumber.Text = "";
            txtProduct.Text = "";
            txtBillAmount.Text = "";
            
        }








五.数据库的删除数据
1.创建UI中delete按钮的click事件代码
其中是调用了Dal的Delete函数
//注释里是调用了Entity Framework的EF_Delete函数

private void btnDelete_Click(object sender, EventArgs e)
        {
            BLCustomer delCustomer = new BLCustomer();
            delCustomer.CustomerId = Convert.ToInt16(txtId.Text);

            var service = new CustomerService();

            service.Delete(delCustomer.CustomerId);

            //service.EF_Delete(delCustomer);

            dtgCustomers.DataSource = service.EF_All();
        }




2.在Dal.cs中添加delete函数

public bool Delete(int CustomerId)
        {
            // Step 1 :- open connection
            try
            {
                string connstr = @"Data Source=ACER-PC\SQLEXPRESS;Initial Catalog=CustomerDB;Integrated Security=True";
                SqlConnection conn = new SqlConnection(connstr);
                conn.Open();

                // Step 2 :- SQL --> Command
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = "delete from tblCustomer" +
                                      " where customerId=" + CustomerId;
                command.ExecuteNonQuery();
                // Close connection
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

3.在CustomerService.cs中添加Delete函数以及EF_delete函数


public void Delete(int customerId)
        {
            new CustomerDal().Delete(customerId);
        }


public void EF_Delete(BLCustomer customer)
        {
            using (var context = new DbCustomer())
            {
                var delCustomer = context.tblCustomers.FirstOrDefault(x => x.CustomerId == customer.CustomerId);
                if (delCustomer == null) { throw new DataException(); }
                context.tblCustomers.Remove(delCustomer);

                context.SaveChanges();

            }
        }







六.图片展示代码结构
1.CustomerUI中的代码






2.BusinessLogicCustomer中BLCustomer.cs中的代码






3.BusinessLogicCustomer中CustomerService中的代码






4.Dal中Dal.cs的代码