创建SqL命令的正确方法C#

Question

我想了3种方法来创建一个命令并执行它。

1. 每个命令都有不同方法。IE：

public bool UserisExist(string username, string password)
        {
            using (SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\...\Database.mdf;Integrated Security=True"))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "select Count(*)from UsersTable where Name='" + username + "' AND Password ='" + password + "'";
                    cmd.Connection = connection;

                    connection.Open();
                    int x = Convert.ToInt32(cmd.ExecuteScalar());
                    connection.Close();

                    UserManager.ClientID = GetClientID(username);
                    return (x > 0);
                }
            }

        }


        
        public int GetClientID(string username)
        {
            using (SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\...\Database.mdf;Integrated Security=True"))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        cmd.CommandText = "Select UserID from UserInfoTable Where UserName ='" + username + "'";
                        cmd.Connection = connection;
                        connection.Open();
                        rdr = cmd.ExecuteReader();
                        if (rdr.HasRows)
                        {
                            rdr.Read(); // read first row
                            var userId = rdr.GetInt32(0);
                            return userId;
                        }
                        else
                            return -1;
                    }
                    catch
                    {
                        return -1;
                    }
                }
            }


        }

 public bool ExecuteScalarScalar(string command)
        {
            using (SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\...\ Security=True"))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = command;
                    cmd.Connection = connection;
                    connection.Open();
                    int x = Convert.ToInt32(cmd.ExecuteScalar());
                    return (x > 0);
                }
            }
        }

或者3）像第二个but一样，方法作为参数but接收，拆分为

(string tableName,string conditionColumn,string conditionValue,string columnToGet)

这样做的正确方法是什么？由于某种原因，第三个看起来不对，但我不知道为什么。

Answer 1

首先，您应该使用参数来避免SQL注入。我可以推荐您使用以下模式来执行Sql查询：

var username = "testUser";
var password = "1234";

using (var con = new SqlConnection("ConString"))
{
  con.Open();
  using (var cmd = con.CreateCommand())
  {
    cmd.CommandText = "select Count(*)from UsersTable where Name = @username AND Password = @password";
    cmd.Parameters.Add(new SqlParameter("@username", username));
    cmd.Parameters.Add(new SqlParameter("@password", password));

    int count = (int)cmd.ExecuteScalar();
  }
}

它易于阅读，您可以重用command对象，并且在需要的时候打开所有的东西，在不再需要的时候关闭所有的东西。