Correct way to create SQL commands in C# [closed]

I have thought of 3 ways to create a command and execute it.

  1. Every command have a different method.

     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;
                 }
             }
         }
     }
    
  2. Have 3 methods: scalar, nonquery and query. And each method receives as a parameter the command:

     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);
             }
         }
     }
    

Or 3) like the second but the method receives as parameter but, split into

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

What is the correct way to do this?

For some reason the third look wrong but I don’t know why.

Answer

First of all you should use parameters to avoid SQL Injection. i can recommend you the following pattern to execute Sql Queries:

var username = "testUser";
//Passwords that you don't need to know anymore should always be hashed with a salt-key before storing them in a Database this is just an example for better readability 
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("@username", SqlDbType.Varchar, 50).Value = userName;
    cmd.Parameters.Add("@password", SqlDbType.Varchar, 50).Value = password;

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

It’s easy to read, you can reuse the command object and everything is opened when needed and closed when not needed anymore.