C# 通过ExecuteNonQuery检查数据库中是否存在表中的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17903092/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Check if record in a table exist in a database through ExecuteNonQuery
提问by Albert A-w
in my program i need to check if a record in the database already exists in the table using the ifstatement.
using c# i am trying to do this through an sql connection.
as i supposed that the ExecuteNonQuery();command returns an integer value, if my supposing is true, i want to know what value is true to know that a certain record exists in the table or not. here's a sample of my code:
在我的程序中，我需要使用该if语句检查数据库中的记录是否已存在于表中。使用 c# 我试图通过 sql 连接来做到这一点。因为我认为该ExecuteNonQuery();命令返回一个整数值，如果我的假设为真，我想知道什么值为真以知道表中是否存在某个记录。这是我的代码示例：
using (SqlConnection sqlConnection = dbUtil.GetSqlConnection(dbUtil.GetConnectionStringByName("NonConnectionString")))
{
using (SqlCommand sqlCommand = new SqlCommand("SELECT * from users where user_name like 'Adam' AND password like '123456'", sqlConnection))
{
sqlresult = sqlCommand.ExecuteNonQuery();
}
}
considering sqlresult has been initialized previously in the main as int sqlresult;so i would like to know, that if this user 'Adam' exists in the database or not. and if he exists, then i want to proceed with an 'if' statement saying for example:
考虑到 sqlresult 之前已经在 main 中初始化，int sqlresult;所以我想知道，数据库中是否存在这个用户“Adam”。如果他存在，那么我想继续使用“if”语句，例如：
if(sqlresult == 0)
{
MessageBox.Show("Adam exists!");
}
so i just don't know the integer that it should return, and i am either not sure that this is the proper way to do it so.
所以我只是不知道它应该返回的整数，而且我不确定这是这样做的正确方法。
thank you.
谢谢你。
采纳答案by Tim Schmelter
If you want to check if the user exists, you have to change your sql and use COUNTor EXISTS:
如果要检查用户是否存在，则必须更改 sql 并使用COUNTor EXISTS：
So instead of
所以代替
SELECT * from users where user_name like 'Adam' AND password like '123456'
this
这个
SELECT COUNT(*) from users where user_name like 'Adam' AND password like '123456'
Now you can use ExecuteScalarto retrieve the count of users with this username and password:
现在您可以使用ExecuteScalar此用户名和密码来检索用户数：
int userCount = (int) sqlCommand.ExecuteScalar();
if(userCount > 0)
// user exists ....
Note that you should use sql-parameters to prevent sql-injection:
请注意，您应该使用 sql-parameters 来防止sql-injection：
using (SqlCommand sqlCommand = new SqlCommand("SELECT COUNT(*) from users where user_name like @username AND password like @password", sqlConnection))
{
sqlConnection.Open();
sqlCommand.Parameters.AddWithValue("@username", userName);
sqlCommand.Parameters.AddWithValue("@password", passWord);
int userCount = (int) sqlCommand.ExecuteScalar();
...
}
回答by Claudio Redi
You should be using ExecuteScalarfor cheking if the record exists. ExecuteNonQueryruns a transact-SQL statement against the connection and returns the number of rows affected for an UPDATE, INSERT, or DELETE. It doesn't apply for SELECT statements
ExecuteScalar如果记录存在，您应该用于检查。ExecuteNonQuery对连接运行 transact-SQL 语句，并返回受 UPDATE、INSERT 或 DELETE 影响的行数。它不适用于 SELECT 语句
回答by Avitus
You should do a count(1)on the table instead of a select *and then executescalarto get that integer value.
您应该count(1)在表上执行 a而不是 aselect *然后executescalar获得该整数值。
Using your existing code I would change it to be:
使用您现有的代码，我会将其更改为：
using (SqlConnection sqlConnection = dbUtil.GetSqlConnection(dbUtil.GetConnectionStringByName("NonConnectionString")))
{
using (SqlCommand sqlCommand = new SqlCommand("SELECT count(1) from users where user_name = 'Adam' AND password = '123456'", sqlConnection))
{
sqlresult = sqlCommand.ExecuteNonQuery();
}
}
Please note that I have used equals values instead of like values.
请注意，我使用的是 equals 值而不是 like 值。
Also if I were do to this I would change your inline sql to use a stored procedure.
此外，如果我这样做，我会更改您的内联 sql 以使用存储过程。
回答by Alireza Kousha
I would use Select Top 1 Id rather than the count(*) because it can be much faster
我会使用 Select Top 1 Id 而不是 count(*) 因为它可以更快
回答by Lucas
If someday you want to use EF just do:
如果有一天您想使用 EF，请执行以下操作：
private MyDb db = new MyDb();
public bool UserExists(string userName, string password){
return db.Users.Any(x => x.user_name.Equals(userName, StringComparison.InvariantCultureIgnoreCase)
&& x.password.Equals(password, StringComparison.InvariantCultureIgnoreCase));
}
Or do a generic method, so you can handle multiple entites:
或者做一个通用的方法，这样你就可以处理多个实体：
public bool EntityExists<T>(Expression<Func<T, bool>> predicate) where T : class, new()
{
return db.Set<T>().Any(predicate);
}
Usage:
用法：
EntityExists<Users>(x => x.user_name.Equals(userName, StringComparison.InvariantCultureIgnoreCase)
&& x.password.Equals(password, StringComparison.InvariantCultureIgnoreCase));
