SQL Database If-Else statement

Question

private void btnChange_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update Customer set MembershipPoint='" + textMembershipPoint.Text + "' where NameCustomer='" + textNameCustomer.Text + "'";
cmd.ExecuteNonQuery();
if (cmd.ExecuteScalar() != null)
{
textMembershipPoint.Text = Convert.ToString(cmd.ExecuteScalar());
}
else if (cmd.ExecuteScalar() != )
{
MessageBox.Show("Invalid Name of Customer.");
}
else if (cmd.ExecuteScalar() != )
{
MessageBox.Show("Invalid Membership Point. Only Number Allowed.");
}
else
{
MessageBox.Show("Membership Point is changed.");
}
con.Close();
display_data();
}

我有一个名为Customer的数据库表，其中列ID_Customer,NameCustomer和MembershipPoint。

当客户输入的名称不在Customer表中时，输出将显示"无效的客户名称"。

如果客户输入无效的MembershipPoint，输出将显示&;invalid MembershipPoint。只允许数量。";

如果其他都是好的，那么输出将显示&;Membership Point is changed.&;

有谁能告诉我，为了实现这一点，我需要为if else语句做些什么?

Answer 1

首先，你必须学习使用参数化查询为了避免头号漏洞——SQL注入!-ALWAYS-无例外。

其次，现在，你正在多次执行你的UPDATE语句，这是非常糟糕的....只需执行一次，记录结果，然后根据结果进行推理——不要多次执行SQL命令。

第三:它被普遍接受最佳实践在需要的时候创建SqlConnection和SqlCommand——不要在代码之外的某个地方打开连接，让它长时间挂起——在需要的时候在这里创建它(完成后释放它)。

那么试试这样写:

private void btnChange_Click(object sender, EventArgs e)
{
// check if the membership points text is a valid INT or not
int membershipPoints = 0;

if (!int.TryParse(textMembershipPoint.Text, out membershipPoints))
{
MessageBox.Show("Invalid Membership Point. Only Number Allowed.");
return;
}
// use a properly parametrized query
string updateQuery = "UPDATE dbo.Customer SET MembershipPoint = @Points WHERE NameCustomer = @CustomerName;";

// put your SqlConnection and SqlCommand into a proper "using" block
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand (updateQuery, con))
{
// define the parameters and set their values
cmd.Parameters.Add("@Points", SqlDbType.Int).Value = membershipPoints;
cmd.Parameters.Add("@CustomerName", SqlDbType.VarChar, 100).Value = textNameCustomer.Text;

// open connection, execute UPDATE, record number of rows updated, close connection
con.Open();
int rowsUpdated = cmd.ExecuteNonQuery();
con.Close();

// now reason just on the result
if (rowsUpdated > 0)
{
// some rows were updated --> success
MessageBox.Show("Success - rows updated");
}
else
{
// no rows were updated --> 
MessageBox.Show("No rows updated - most likely invalid customer name");
}
}

display_data();
}