我如何获得使用ExecuteNonQuery()时发生的错误消息?
本文关键字：错误 消息 何获得 ExecuteNonQuery | 更新日期: 2023-09-27 18:05:29
我是这样执行命令的:
var Command = new SqlCommand(cmdText, Connection, tr);
Command.ExecuteNonQuery();
命令中有一个错误，但是。net没有抛出任何错误消息。我如何知道命令没有正确执行，以及如何获得异常?
如果你的错误严重程度是16或以上，你只会在c#中得到一个异常。如果你使用PRINT，你不会在。net中得到异常。
如果你可以编辑raise错误代码，这将导致c#中的SqlException:
RAISERROR('Some error message', 16, 1)
只是一个旁注- SQL Server将继续运行命令之后的RAISERROR，如果你不直接RETURN之后。如果不返回，则会返回多个错误。
。. NET确实会引发错误消息…如果严重性是16或更高(因为它抛出一个异常)-消息将在异常.Message中。如果您使用的RAISERROR具有较低的严重性(或使用PRINT)，那么您将不得不订阅连接上的InfoMessage事件。
只有严重的错误才会在ExecuteNonQuery中被抛出。我用OdbcCommand.ExecuteNonQuery()方法观察到另一个场景。对于SqlCommand.ExecuteNonQuery()可能也是如此。如果CommandText属性中包含的SQL是单个语句(例如:INSERT INTO table (col1,col2) VALUES (2，'ABC');)如果在上述语句中存在外键冲突或主键冲突，ExecuteNonQuery将抛出异常。然而，如果你的CommandText是一个批处理，你有一个以上的SQL语句由分号分隔(如几个insert或UPDATES)，如果其中一个失败ExecuteNonQuery不会抛出异常。您需要显式地检查该方法返回的受影响记录的数量。简单地把代码放在try{}Catch{}中是没有用的。
受到M Hassan, Stefan Steiger和Mark Gravell在这篇文章中工作的启发，这里有一个最小的概念验证示例:
private static void DoSql()
{
// Errors of severity level of 10 or less
// will NOT bubble up to .Net as an Exception to be caught in the usual way
const string sql = @"RAISERROR('A test error message of low severity', 10, 1)";
using (SqlConnection conn = new SqlConnection(myConnString))
{
conn.Open();
// Hook up my listener to the connection message generator
conn.InfoMessage += new SqlInfoMessageEventHandler(MySqlMessageHandler);
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.ExecuteNonQuery();
// code happily carries on to this point
// despite the sql Level 10 error that happened above
}
}
}
private static void MySqlMessageHandler(object sender, SqlInfoMessageEventArgs e)
{
// This gets all the messages generated during the execution of the SQL,
// including low-severity error messages.
foreach (SqlError err in e.Errors)
{
// TODO: Something smarter than this for handling the messages
MessageBox.Show(err.Message);
}
}
我发现这在Oracle ODP的WCF服务中工作得很好。净——
try
{
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
}
catch (OracleException oex)
{
string errmsg = oex.Message;
Logger.Instance.WriteLog(@"Some error --> " + errmsg);
throw new Exception(errmsg);
}
catch (Exception ex)
{
throw ex;
}
finally
{
cleanup...
}
此代码在命令结束时，与try cath结合，返回错误:
SqlCommand.EndExecuteNonQuery(result)
这是我完整的类代码:
Imports System.Data.SqlClient
Imports System.DirectoryServices.ActiveDirectory
Class clsExecuteAsync
Public Event EnProceso(Identificador As Integer, Mensaje As String)
Public Event Finalizado(IDentificador As Integer, Mensaje As String)
Public Event CancelarProcesoEnEjecucion(Identificador As Integer, ByRef Cancel As Boolean)
Dim Cancelar As Boolean
Sub CancelarProceso()
Cancelar = True
End Sub
Function test() As Boolean
' This is a simple example that demonstrates the usage of the
' BeginExecuteNonQuery functionality.
' The WAITFOR statement simply adds enough time to prove the
' asynchronous nature of the command.
Dim commandText As String = "UPDATE Production.Product SET ReorderPoint = ReorderPoint + 1 " & "WHERE ReorderPoint Is Not Null;" & "WAITFOR DELAY '0:0:3';" & "UPDATE Production.Product SET ReorderPoint = ReorderPoint - 1 " & "WHERE ReorderPoint Is Not Null"
Return (RunCommandAsynchronously(0, commandText, GetConnectionString()))
Console.WriteLine("Press ENTER to continue.")
Console.ReadLine()
End Function
Function ExecuteAsync(Identificador As Integer, Sql As String, Optional CadenaConexion As String = "") As String
If CadenaConexion = "" Then
CadenaConexion = clsIni.LeeIni("Provider")
End If
Return RunCommandAsynchronously(Identificador, Sql, CadenaConexion)
End Function
Function RunCommandAsynchronously(Identificador As Integer, commandText As String, connectionString As String) As String
' Given command text and connection string, asynchronously execute
' the specified command against the connection. For this example,
' the code displays an indicator as it is working, verifying the
' asynchronous behavior.
Dim Resultado As String = ""
Try
Dim connection As SqlConnection
Dim SqlCommand As SqlCommand
connection = New SqlConnection(connectionString)
Dim count As Integer = 0
'testint to catch the error, but not run for me
AddHandler connection.InfoMessage, AddressOf ErrorEnConexion
SqlCommand = New SqlCommand(commandText, connection)
connection.Open()
Dim result As IAsyncResult = SqlCommand.BeginExecuteNonQuery()
While Not result.IsCompleted
Console.WriteLine("Waiting ({0})", count = count + 1)
' Wait for 1/10 second, so the counter
' does not consume all available resources
' on the main thread.
System.Threading.Thread.Sleep(100)
RaiseEvent EnProceso(Identificador, commandText)
Application.DoEvents()
If Cancelar Then
Cancelar = False
'cancelar
Dim Cancel As Boolean = False
RaiseEvent CancelarProcesoEnEjecucion(Identificador, Cancel)
If Cancel = False Then
Resultado = "Cancelado"
GoTo SALIR
End If
End If
End While
'Console.WriteLine("Command complete. Affected {0} rows.", Command.EndExecuteNonQuery(Result))
' MsgBox("El comando se ejecutó. " & SqlCommand.EndExecuteNonQuery(result), MsgBoxStyle.Information)
'detect error: this code lunch and error: Cath with try cacth code
SqlCommand.EndExecuteNonQuery(result)
RaiseEvent Finalizado(Identificador, SqlCommand.EndExecuteNonQuery(result))
Resultado = "OK"
Catch ex As SqlException
Console.WriteLine("Error ({0}): {1}", ex.Number, ex.Message)
Resultado = ex.Message
Catch ex As InvalidOperationException
Console.WriteLine("Error: {0}", ex.Message)
Resultado = ex.Message
Catch ex As Exception
' You might want to pass these errors
' back out to the caller.
Console.WriteLine("Error: {0}", ex.Message)
Resultado = ex.Message
End Try
SALIR:
Return Resultado
End Function
Private Sub ErrorEnConexion(sender As Object, e As SqlInfoMessageEventArgs)
MsgBox(e.Message)
End Sub
Private Function GetConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file.
' If you have not included "Asynchronous Processing=true" in the
' connection string, the command is not able
' to execute asynchronously.
Return "Data Source=(local);Integrated Security=SSPI;" & "Initial Catalog=AdventureWorks; Asynchronous Processing=true"
End Function
End Class
试试下面的
PS:仅仅因为你使用了事务，并不意味着你可以忽略处理异常和回滚。
public static void MessageEventHandler( object sender, SqlInfoMessageEventArgs e ) {
foreach( SqlError error in e.Errors ) {
Console.WriteLine("problem with sql: "+error);
throw new Exception("problem with sql: "+error);
}
}
public static int executeSQLUpdate(string database, string command) {
SqlConnection connection = null;
SqlCommand sqlcommand = null;
int rows = -1;
try {
connection = getConnection(database);
connection.InfoMessage += new SqlInfoMessageEventHandler( MessageEventHandler );
sqlcommand = connection.CreateCommand();
sqlcommand.CommandText = command;
connection.Open();
rows = sqlcommand.ExecuteNonQuery();
} catch(Exception e) {
Console.Write("executeSQLUpdate: problem with command:"+command+"e="+e);
Console.Out.Flush();
throw new Exception("executeSQLUpdate: problem with command:"+command,e);
} finally {
if(connection != null) { connection.Close(); }
}
return rows;
}
这是正确的事务处理:
//public static void ExecuteInTransaction(Subtext.Scripting.SqlScriptRunner srScriptRunner)
public override void ExecuteInTransaction(string strSQL)
{
System.Data.Odbc.OdbcTransaction trnTransaction = null;
try
{
System.Threading.Monitor.Enter(m_SqlConnection);
if (isDataBaseConnectionOpen() == false)
OpenSQLConnection();
trnTransaction = m_SqlConnection.BeginTransaction();
try
{
/*
foreach (Subtext.Scripting.Script scThisScript in srScriptRunner.ScriptCollection)
{
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(scThisScript.ScriptText, m_sqlConnection, trnTransaction);
cmd.ExecuteNonQuery();
}
*/
// pfff, mono C# compiler problem...
// System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(strSQL, m_SqlConnection, trnTransaction);
System.Data.Odbc.OdbcCommand cmd = this.m_SqlConnection.CreateCommand();
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
trnTransaction.Commit();
} // End Try
catch (System.Data.Odbc.OdbcException exSQLerror)
{
Log(strSQL);
Log(exSQLerror.Message);
Log(exSQLerror.StackTrace);
trnTransaction.Rollback();
} // End Catch
} // End Try
catch (Exception ex)
{
Log(strSQL);
Log(ex.Message);
Log(ex.StackTrace);
} // End Catch
finally
{
strSQL = null;
if(m_SqlConnection.State != System.Data.ConnectionState.Closed)
m_SqlConnection.Close();
System.Threading.Monitor.Exit(m_SqlConnection);
} // End Finally
} // End Sub ExecuteInTransaction
使用try/catch
捕获SqlException try
{
//.......
Command.ExecuteNonQuery();
}
catch (SqlException ex)
{
log (SqlExceptionMessage(ex).ToString());
}
下面的方法捕获SqlException的细节，可以记录或显示给用户
public StringBuilder SqlExceptionMessage(SqlException ex)
{
StringBuilder sqlErrorMessages = new StringBuilder("Sql Exception:'n");
foreach (SqlError error in ex.Errors)
{
sqlErrorMessages.AppendFormat("Mesage: {0}'n", error.Message)
.AppendFormat("Severity level: {0}'n", error.Class)
.AppendFormat("State: {0}'n", error.State)
.AppendFormat("Number: {0}'n", error.Number)
.AppendFormat("Procedure: {0}'n", error.Procedure)
.AppendFormat("Source: {0}'n", error.Source)
.AppendFormat("LineNumber: {0}'n", error.LineNumber)
.AppendFormat("Server: {0}'n", error.Server)
.AppendLine(new string('-',error.Message.Length+7));
}
return sqlErrorMessages;
}
生成的消息看起来像:
Sql Exception:
Mesage: Error converting data type nvarchar to datetime.
Severity level: 16
State: 5
Number: 8114
Procedure: Sales by Year
Source: .Net SqlClient Data Provider
LineNumber: 0
Server: myserver
-------------------------------------------------------