如何在c#中更新两个表

Question

我有两个表

1. tbl_employer (Emp_num、指定帧、Lname Phone_no)
2. tbl_system_users (Emp_num User_name,密码)

这两个表使用一个c#表单(employer.cs)填充。只有少数雇主有权限访问系统，称为系统用户。在employer.cs中有2个用户名和密码文本框。用户名和密码文本框不应该为空，以便将数据保存到tbl_system_users，如果两个文本框都为空，则意味着它们只是雇主而不是系统用户。

tbl_employer的Emp_num是一个自增字段和tbl_system_users的外键。如何更新雇主详细信息?

public void update_employers(DTOUsers Users)
{
SqlConnection con = new SqlConnection(ConnectionString);
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "UPDATE tbl_employer SET Designation = '" + Users.Designation 
+ "',,Employer_first_name = '" + Users.EmpFirstName + "',Employer_last_name = '"
+ Users.EmpLastName + "',, Phone_Num = '" + Users.PhoneNum + "' WHERE Emp_Num ='" + Users.EmpNum + "'";
con.Open();
cmd.ExecuteNonQuery();
if (Users.Password != "" && Users.UserName != "")
{
cmd.CommandText = "UPDATE tbl_system_users set User_name='" + Users.UserName + "',Password='" + Users.Password + "' where Emp_Num ='" + Users.EmpNum + "'";

cmd.ExecuteNonQuery();

}
con.Close();

在employer.cs

private void btn_update_Click(object sender, EventArgs e)
{
string emp_num = txt_emp_num.Text;
string designation = cmb_designation.Text;                   
string fname = txt_emp_fname.Text;
string lname = txt_emp_lname.Text;
string user_name = txt_user_name.Text;
string pw = txt_pw.Text;
string phno = txt_phn_num.Text;
DTOUsers emp = new DTOUsers();
emp.EmpNum = Convert.ToInt16(emp_num);
emp.Designation = designation;                   
emp.EmpFirstName = fname;
emp.EmpLastName = lname;                    
emp.UserName = user_name;
emp.Password = strh.Encrypt(pw);
emp.PhoneNum = phno;
dbh.update_employers(emp);
}

Answer 1

public void update_employers(DTOUsers Users)
{
bool firstStep = true;
SqlConnection con = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(con);

cmd.CommandText = "UPDATE tbl_employer SET Designation = @Designation,Employer_first_name = @EmpFirstName,Employer_last_name = @EmpLastName,Phone_Num = @PhoneNum WHERE Emp_Num = @PhoneNum";
//SqlDbType.VarChar Adjust according to the database values
cmd.Parameters.Add("@Designation", SqlDbType.VarChar, 30).Value = Users.Designation;
cmd.Parameters.Add("@EmpFirstName", SqlDbType.VarChar, 30).Value = Users.EmpFirstName;
cmd.Parameters.Add("@EmpLastName", SqlDbType.VarChar, 30).Value = Users.EmpLastName;
cmd.Parameters.Add("@PhoneNum", SqlDbType.VarChar, 30).Value = Users.PhoneNum;


try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message);
firstStep = false;
}
if (firstStep)
{
if (!string.IsNullOrEmpty(Users.Password) && !string.IsNullOrEmpty(Users.UserName))
{
cmd = new SqlCommand(con);
cmd.CommandText = "UPDATE tbl_system_users set User_name = @UserName,Password = @Password where Emp_Num = @PhoneNum";
cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 30).Value = Users.UserName;
cmd.Parameters.Add("@Password", SqlDbType.VarChar, 30).Value = Users.Password;
cmd.Parameters.Add("@PhoneNum", SqlDbType.VarChar, 30).Value = Users.PhoneNum;

try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message);
}
finally
{
con.Close();
}
}
}
}