c# - System.Data.dll 中发生类型为 'System.Data.SqlClient.SqlException' 的异常

Question

当我执行下面的代码时，会出现此错误消息:

"An exception of type 'System.Data.SqlClient.SqlException' occurred inSystem.Data.dll but was not handled in user code

Additional information: Incorrect syntax near '='. "

这是代码:

string position;

SqlConnection con = new SqlConnection("server=free-pc\\FATMAH; Integrated Security=True; database=Workflow; ");
con.Open();
SqlCommand cmd = new SqlCommand("SELECT EmpName FROM Employee WHERE EmpID=" + id.Text, con);

SqlDataReader Read = cmd.ExecuteReader();

if (Read.Read()==true)
{
    position = Read[0].ToString();
    Response.Write("User Registration successful");
}
else
{
    Console.WriteLine("No Employee found.");
}

Read.Close(); 

是什么原因造成的，我该如何解决？

Answer 1

您的代码存在一些问题。首先，我建议使用参数化查询，这样你就可以避免 SQL 注入(inject)攻击，而且参数类型也会被框架发现:

var cmd = new SqlCommand("SELECT EmpName FROM Employee WHERE EmpID = @id", con);
cmd.Parameters.AddWithValue("@id", id.Text);

其次，由于您只对查询返回的一个值感兴趣，因此最好使用 ExecuteScalar :

var name = cmd.ExecuteScalar();

if (name != null)
{
   position = name.ToString();
   Response.Write("User Registration successful");
}
else
{
    Console.WriteLine("No Employee found.");
}

最后一件事是将 SqlConnection 和 SqlCommand 包装到 using 中，这样它们使用的任何资源都将被释放:

string position;

using (SqlConnection con = new SqlConnection("server=free-pc\\FATMAH; Integrated Security=True; database=Workflow; "))
{
  con.Open();

  using (var cmd = new SqlCommand("SELECT EmpName FROM Employee WHERE EmpID = @id", con))
  {
    cmd.Parameters.AddWithValue("@id", id.Text);
  
    var name = cmd.ExecuteScalar();
  
    if (name != null)
    {
       position = name.ToString();
       Response.Write("User Registration successful");
    }
    else
    {
        Console.WriteLine("No Employee found.");
    }
  }
}