检查表中的记录通过的ExecuteNonQuery一个数据库中存在 [英] Check if record in a table exist in a database through ExecuteNonQuery

问题描述

在我的节目，我需要检查，如果在数据库中的记录已经在表中使用如果语句存在。
使用C＃我试图通过SQL连接来做到这一点。
，因为我认为，该的ExecuteNonQuery（）; 命令返回一个整数值，如果我的假设是真的，我想知道什么样的价值是真实的知道，某些记录存在于表与否。这里是我的代码示例：

in my program i need to check if a record in the database already exists in the table using the if statement. 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:

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

考虑的SQLResult先前已在主要的初始化 INT的SQLResult;
，所以我想知道，如果这个用户'亚当'在数据库中存在与否。如果他存在，那么我想用如果声明说，例如进行：

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:

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.

感谢您。

推荐答案

如果您要检查该用户是否存在，你必须改变你的SQL，并使用 COUNT 或 EXISTS ：

If you want to check if the user exists, you have to change your sql and use COUNT or EXISTS:

因此，而不是

SELECT * from users where user_name like 'Adam' AND password like '123456'

这

SELECT COUNT(*) from users where user_name like 'Adam' AND password like '123456'

现在你可以使用的ExecuteScalar 来检索与此用户名和密码的用户的计数：

Now you can use ExecuteScalar to retrieve the count of users with this username and password:

int userCount = (int) sqlCommand.ExecuteScalar();
if(userCount > 0)
    // user exists ....

请注意，您应该使用SQL参数，以防止 SQL注入：

Note that you should use sql-parameters to prevent 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();
    ...
}

这篇关于检查表中的记录通过的ExecuteNonQuery一个数据库中存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！