SQL Server C# SqlCommand ExecuteReader not returning rows from CATCH

nqwrtyyt 于 2023-03-17 发布在 C#

关注(0)|答案(1)|浏览(144)

I am trying to get the error code and message from an INSERT query when it conflicts with a PRIMARY or FOREIGN key constraint, but when the statement in the CATCH clause is executed, SqlDataReader doesn't pick up the returned row.

Here is how I create the SqlCommand:

connection.Open();
string query =
    @"BEGIN TRY
        INSERT INTO Table1 (primkey, forkey) OUTPUT -1 AS err, NULL as msg VALUES (@primkey, @forkey)
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS err, ERROR_MESSAGE() as msg
    END CATCH;";
SqlCommand command = new(query, connection);
command.Parameters.Add("@primkey", SqlDbType.VarChar);
command.Parameters.Add("@forkey", SqlDbType.Int);

And here I am executing the query in a loop to insert multiple rows:

command.Parameters[0].Value = primKey;
command.Parameters[1].Value = forKey;
SqlDataReader reader = command.ExecuteReader();
reader.Read(); //The query should always return a row OR throw an exception, so no need to check
switch (getInt32(reader, "err") ) //System.InvalidOperationException: 'Invalid attempt to read when no data is present.'
{
    case -1: //NO ERROR
    case 2627: //PK CONSTRAINT ERROR, IGNORE
        break;
    case 547: //FK CONSTRAINT ERROR - forKey NOT FOUND
        throw new DatabaseExceptions.RecordNotFoundException(this, forKey);
    default:
        throw new DatabaseExceptions.DBException(this, getString(reader, "msg") );
}
reader.Close();

When no error occurs, the OUTPUT clause works and a row is picked up by the reader, but if an error occurs and is caught, the result of the SELECT statement in the CATCH clause is not picked up... Why? This query works as expected in SQL Server Management Studio.

I am using SQL Server 2022 and .NET 5

sql-server

来源：https://stackoverflow.com/questions/75736156/c-sharp-sqlcommand-executereader-not-returning-rows-from-catch

1条答案

按热度按时间

pu82cl6c1#

You have a number of issues here:

You are catching and swallowing the exception, and returning an error row to the client, instead let the error feed through
You are not actually checking if there is a row anyway. If you were, you could have checked the result of reader.Read() .
This new way, you don't need a reader at all. Just execute the command and catch any exception.
You need to dispose the objects with using

using var connection = new SqlConnection(YourConnString);
connection.Open();
const string query = @"
INSERT INTO Table1 (primkey, forkey)
VALUES (@primkey, @forkey);
";
using SqlCommand command = new(query, connection);
command.Parameters.Add("@primkey", SqlDbType.VarChar, 250);
command.Parameters.Add("@forkey", SqlDbType.Int);

foreach (var obj in someList)
{
    command.Parameters["@primkey"].Value = obj.primKey;
    command.Parameters["@forkey"].Value = obj.forKey;
    try
    {
        command.ExecuteNonQuery();
    }
    catch (SqlException ex) when (ex.Number == 2627)
    { //PK CONSTRAINT ERROR, IGNORE
    }
    catch (SqlException ex) when (ex.Number == 547)
    { //FK CONSTRAINT ERROR - forKey NOT FOUND
        throw new DatabaseExceptions.RecordNotFoundException(obj, forKey);
    }
}

赞(0）回复(0）举报 2023-03-17

我来回答

SQL Server C# SqlCommand ExecuteReader not returning rows from CATCH

1条答案

相关问题

热门标签

最新问答