我想了3种方法来创建一个命令并执行它。
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)
这样做的正确方法是什么？由于某种原因，第三个看起来不对，但我不知道为什么。
首先，您应该使用参数来避免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对象，并且在需要的时候打开所有的东西，在不再需要的时候关闭所有的东西。
