如何使用 C# 检查 SQL 查询是否成功
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14293179/
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
How to check whether a SQL query is successful with C#
提问by Aravind Bharathy
I am new to C# and SQL. Now from a form I access a function in a class.
我是 C# 和 SQL 的新手。现在我从一个表单访问一个类中的一个函数。
My code is
我的代码是
public void updateSupplierInformation(string id, string name, string balance, string place, string address, string phone, string bankname, string bankbranch, string accountno)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
SqlCommand NewCmd = conn.CreateCommand();
NewCmd.Connection = conn;
NewCmd.CommandType = CommandType.Text;
NewCmd.CommandText = " update supplier set " + " ID = " + "'" + id + "'" + " , NAME = " + "'" + name + "'" + " , BALANCE = " + "'" + balance + "'" + " , PLACE = " + "'" + place + "'" + " , LOCATION = " + "'" + address + "'" + ", PHONE = " + "'" + phone + "'" + " , BANK_NAME = " + "'" + bankname + "'" + " , BANK_BRANCH = " + "'" + bankbranch + "'" + ", ACCOUNT_NO = " + "'" + accountno + "'" + " where ID = " + "@id";
NewCmd.Parameters.AddWithValue("@id",id);
NewCmd.ExecuteNonQuery();
conn.Close();
}
Now if a record doesn't exist in the database with the given idthe application stops immediately. How can I handle this? I want to show a message that the data entered is wrong and ask the user to enter another data
现在，如果给定id的数据库中不存在记录，应用程序将立即停止。我该如何处理？我想显示输入数据错误的消息并要求用户输入另一个数据
采纳答案by sreejithsdev
ExecuteNonQuery() returns number of rows affected by an INSERT, UPDATE or DELETE statement.If you need to check sql exception you have to include a try catch statement in your function.
ExecuteNonQuery() 返回受 INSERT、UPDATE 或 DELETE 语句影响的行数。如果需要检查 sql 异常，则必须在函数中包含 try catch 语句。
public void updateSupplierInformation(string id, string name, string balance, string place, string address, string phone, string bankname, string bankbranch, string accountno)
{
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
SqlCommand NewCmd = conn.CreateCommand();
NewCmd.Connection = conn;
NewCmd.CommandType = CommandType.Text;
NewCmd.CommandText = " update supplier set " + " ID = " + "'" + id + "'" + " , NAME = " + "'" + name + "'" + " , BALANCE = " + "'" + balance + "'" + " , PLACE = " + "'" + place + "'" + " , LOCATION = " + "'" + address + "'" + ", PHONE = " + "'" + phone + "'" + " , BANK_NAME = " + "'" + bankname + "'" + " , BANK_BRANCH = " + "'" + bankbranch + "'" + ", ACCOUNT_NO = " + "'" + accountno + "'" + " where ID = " + "@id";
NewCmd.Parameters.AddWithValue("@id",id);
int a=NewCmd.ExecuteNonQuery();
conn.Close();
if(a==0)
//Not updated.
else
//Updated.
}
catch(Exception ex)
{
// Not updated
}
}
回答by Jon Skeet
ExecuteNonQueryreturns the number of rows affected - if it's 0, that means there were no matching rows to update. Of course, that's only if the update actually "works" in terms of not throwing an exception... whereas I suspect it's throwing an exception in your case, which probably isn'tto do with the row not existing in the database. (It's possible that there's some code you haven't shown which doesdepend on the row existing, mind you.)
ExecuteNonQuery返回受影响的行数 - 如果为 0，则表示没有要更新的匹配行。当然，这只有当实际的更新“工作”不抛出异常的方面......而我怀疑这是抛出一个异常，你的情况，这可能是不给行数据库中不存在的事情。（这可能是因为有您还没有表现出一些代码，不依赖于现有的行，请注意。）
Additionally:
此外：
- You should use parameterized SQL for allparameters rather than including the values directly in your SQL.
- You should use
usingstatements to dispose of resources reliably. - It looks like you're using a single connection - don't do that. Create (and dispose via
using) a new connection each time you want to perform a database operation, and let the connection pool handle the efficiency - Work out why the application is just stopping. An exception is almost certainly being thrown, and it's really important that when that happens, you get the details of the exception. You maywant to catch it and keep going (at some high level) depending on the exact context... but you should always at least end up logging it.
- 您应该对所有参数使用参数化 SQL，而不是直接在 SQL 中包含这些值。
- 您应该使用
using语句来可靠地处理资源。 - 看起来您正在使用单个连接 - 不要这样做。
using每次要执行数据库操作时创建（并通过 处置）一个新连接，并让连接池处理效率 - 找出应用程序停止的原因。几乎可以肯定会抛出异常，当发生这种情况时，获得异常的详细信息非常重要。您可能想要捕捉它并根据确切的上下文继续（在某个较高的级别）......但您应该始终至少最终记录它。
My guess(on a casual inspection) is that the problem is that your update statement tries to update the ID, which would presumably be read-only. But you'll find that out when you fix your exception handling.
我的猜测（在随意检查中）是问题在于您的更新语句试图更新 ID，该 ID 可能是只读的。但是当您修复异常处理时，您会发现这一点。
回答by John the horn
I know that there is already an answer posted but let`s try something else:
我知道已经发布了一个答案，但让我们尝试其他方法：
SqlConnection SQLConn = new SqlConnection("datahere");
SqlCommand SQLComm = new SqlCommand();
SQLcomm.Connection = SQLConn;
SQLConn.Open();
SQLComm.CommandText = "SQL statement goes here"
SqlDataReader dataReader = SQLComm.ExecuteReader();
dataReader.Read();
if(dataReader.HasRows == true){ //if it has rows do code
//do code
}
else{
// do other code
}
HasRows will return a bool value
HasRows 将返回一个布尔值
回答by Dilip Kumar Choudhary
Simply check the condition
只需检查条件
int a=NewCmd.ExecuteNonQuery();
if(a==0)
//Not updated.
else
//Updated.
ExecuteNonQuery() -> This function return integer value.
ExecuteNonQuery() -> 此函数返回整数值。
Thank you
谢谢
