vb.net SqlDataReader.ExecuteReader 也不例外，而 SQL Server 返回转换错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19634521/
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
No exception with SqlDataReader.ExecuteReader while SQL Server returns converstion error
提问by datadev
When I execute the following statement in SQL Server Management Studio
当我在 SQL Server Management Studio 中执行以下语句时
exec sp_executesql N'select 1 where 1 = @p1',N'@p1 nvarchar(3)',@p1=N'a'
exec sp_executesql N'select 1 where 1 = @p1',N'@p1 nvarchar(3)',@p1=N'a'
go
走
I get the below error
我收到以下错误
Msg 245, Level 16, State 1, Line 1
消息 245，级别 16，状态 1，第 1 行
Conversion failed when converting the nvarchar value 'a' to data type int.
将 nvarchar 值“a”转换为数据类型 int 时转换失败。
But when I use ExecuteReader, I don't get any exception
但是当我使用 ExecuteReader 时，我没有得到任何异常
Why?
为什么？
How can I get and handle this error as exception in app
如何在应用程序中作为异常获取和处理此错误
Imports System.Data.SqlClient
导入 System.Data.SqlClient
Module Module1
模块模块1
Sub Main()
Dim TestSqlConnection As SqlConnection = Nothing
Dim TestSqlCommand As SqlCommand = Nothing
Dim TestReader As SqlDataReader = Nothing
Dim TestGetSchemaTable As DataTable = Nothing
TestSqlConnection = New SqlConnection
TestSqlConnection.ConnectionString = "Data Source=(local);Database=master;Integrated Security=true"
TestSqlConnection.Open()
TestSqlCommand = New SqlCommand()
TestSqlCommand.Connection = TestSqlConnection
TestSqlCommand.CommandType = CommandType.Text
TestSqlCommand.CommandText = "select 1 where 1 = @p1"
Dim TestSqlParameter As SqlParameter = New SqlParameter
TestSqlParameter.ParameterName = "@p1"
TestSqlParameter.SqlDbType = SqlDbType.NVarChar
TestSqlParameter.Size = 3
TestSqlParameter.Direction = ParameterDirection.Input
TestSqlParameter.Value = "a"
TestSqlCommand.Parameters.Add(TestSqlParameter)
Try
TestReader = TestSqlCommand.ExecuteReader()
Catch ex As Exception
Console.WriteLine("Exception")
Finally
Console.WriteLine("Finally")
End Try
End Sub
End Module
终端模块
采纳答案by paparazzo
For me this fixes the SSMS
对我来说，这修复了 SSMS
exec sp_executesql N'select 1 where N''a'' = @p1',N'@p1 nvarchar(3)',@p1=N'a'
exec sp_executesql N'select 1 where N''1'' = @p1',N'@p1 nvarchar(3)',@p1=N'a'
I totally disagree with Rene Reader.
On my system it executes on ExecuteReader.
ExecuteReader would be a real bad name if it did not actually execute.
Did you consider that it is not catching and exception because it is not throwing an exception.
I know you see an error in SQL Profiler but if I introduce a syntax error it is caught.
我完全不同意 Rene Reader。
在我的系统上，它在 ExecuteReader 上执行。
如果 ExecuteReader 没有实际执行，它会是一个真正的坏名字。
您是否认为它没有捕获和异常，因为它没有抛出异常。
我知道您在 SQL Profiler 中看到了一个错误，但是如果我引入了一个语法错误，它就会被捕获。
This is C# but is is not throwing an exception for me.
And if I change it to:
"select 1 where N'a' = @p1";
Then it returns a row.
这是 C#，但不会为我抛出异常。
如果我将其更改为：
“选择 1，其中 N'a' = @p1”；
然后它返回一行。
If I introduce a syntax error:
"select 1 whereX 1 = @p1";
Then it does throw an exception and it throws it on the ExecuteReader line.
如果我引入一个语法错误：
“select 1 whereX 1 = @p1”;
然后它确实抛出一个异常并将其抛出到 ExecuteReader 行上。
If you want 1 to be a literal you should use:
"select 1 where '1' = @p1";
如果您希望 1 成为文字，您应该使用：
“select 1 where '1' = @p1”;
SQLcmd.CommandType = CommandType.Text;
SQLcmd.CommandText = "select 1 where N'1' = @p1";
SqlParameter TestSqlParameter = new SqlParameter();
TestSqlParameter.ParameterName = "@p1";
TestSqlParameter.SqlDbType = SqlDbType.NChar;
TestSqlParameter.Value = "a";
SQLcmd.Parameters.Add(TestSqlParameter);
try
{
rdr = SQLcmd.ExecuteReader();
Int32 waste;
if (rdr.HasRows)
{
while (rdr.Read())
{
waste = rdr.GetInt32(0);
}
}
}
catch (Exception Ex)
{
Debug.WriteLine(Ex.Message);
}
回答by pingoo
ExecuteReader doesn't actually perform the query. The first call to .Read()will throw the error.
ExecuteReader 实际上并不执行查询。第一次调用.Read()将抛出错误。
If you want to only catch the SqlExceptionyou can do the following:
如果您只想捕获 ，SqlException您可以执行以下操作：
Try
TestReader = TestSqlCommand.ExecuteReader()
TestReader.Read()
Catch ex As SqlException
Console.WriteLine("SQL error.")
Catch ex As Exception
Console.WriteLine("Exception")
Finally
Console.WriteLine("Finally")
End Try
回答by mkaj
I solved this problem if the sql string passed used EXEC sp_executesql by ensuring I read every result and result set... like so:
如果传递的 sql 字符串使用 EXEC sp_executesql 通过确保我读取每个结果和结果集，我解决了这个问题......像这样：
....
using (var conn = new SqlConnection(_connectionString))
{
try
{
conn.Open();
using (var cmd = new SqlCommand(sql, conn))
{
cmd.CommandTimeout = 0;
using (var rdr = cmd.ExecuteReader())
{
// TODO: Do stuff with the rdr here.
FlushReader(rdr);
}
}
}
catch (Exception ex)
{
_logger.Fatal("Exception: {0}\r\nSQL:\r\n{1}", ex.Message, sql);
throw;
}
finally
{
conn.Close();
}
}
...
/// <summary>
/// Continue trying to read in case the server threw an exception.
/// </summary>
private static void FlushReader(IDataReader rdr)
{
while (rdr.Read())
{
}
while (rdr.NextResult())
{
while (rdr.Read())
{
}
}
}
Without calling the FlushReader method the application continues without throwing an exception of any kind. Even if I use a SQL THROW statement. Running the same sql in SQL management studio will show the error.
在不调用 FlushReader 方法的情况下，应用程序将继续运行而不会引发任何类型的异常。即使我使用 SQL THROW 语句。在 SQL management studio 中运行相同的 sql 将显示错误。
Hope this helps.
希望这可以帮助。
