Is there any way to get db-warnings from the database in C# OdbcConnection?

Question

I'm using the Odbc library with .NET Core 7 and, sometimes when gross errors occur in my sql commands ("like: select * from Non-Existent-object"), an Exception is thrown, then i can handle and identify the error more easily. Still, recently i detected that some other database warnings like data truncation and primary key violation does not throw anything and the code flows just after the task is done like nothing happened. Am i doing something wrong here? If not, how can i obtain these warnings to throw them

my odbc connection lifetime:

public class DBConnect : IDisposable
{
    private OdbcConnection _database;

    public async Task PlaceHolderOperation(string commandStr)
    {
        CheckConnection();
        OdbcCommand command = new(commandStr, _database);

        var perform = command.ExecuteNonQueryAsync();
        if (await Task.WhenAny(perform, Task.Delay(TimeSpan.FromMinutes(1.5))) != perform)
        {
            throw new TimeoutException($"Database didn't returned within expected limit time: 1.5 minutes");
        }
        // handle warnings here
    }
    protected void CheckConnection()
    {
        if (_database.State == ConnectionState.Closed)
        {
            var openning = _database.OpenAsync();
            if (Task.WhenAny(openning, Task.Delay(TimeSpan.FromMinutes(1.5))).Result != openning)
            {
                throw new TimeoutException($"Database didn't returned within expected limit time: 1.5 minutes");
            }
        }
    }
    public void Dispose() 
    {
        _database.Close();
    }

    // START_HERE   
    public static async Task Main()
    {
        using (var connection = new DBConnect { _database = new("{CONNECTION_STRING}") })
        {
            await connection.PlaceHolderOperation("placeholder_command");
        }
    }
}

using System.Data.Common;
using System.Data.Odbc;
using System.Data;

general purpose advices:

This is a dummy code, that exemplifies my connection cycle. the Disposable methodology checks if the connection is open before closing in the actual project. The other functions work just like in the complete code.

my idea is to get the database warnings somehow and throw it's messages as OdbcException objects as:

non-executable code

    throw new AggregateException( warnings.Select(war => new OdbcException(war.Message)).ToArray());

score 0 · Accepted Answer · answered May 22 '23 at 20:04

By the lack of response...

I finally got to a solution, using a try-catch block in the sql command returning error-messages when performing actions in the database, just like:

public struct DBErrorWarning 
{
    public int Line;
    public string Message;
    public override string ToString() => $"on line: {Line} fail: {Message}";
}

public class DatabaseWarnException : Exception
{
    public DBErrorWarning[] Errors;
    public DatabaseWarnException(IEnumerable<DBErrorWarning> errorSearch) :
        this(
            errors: errorSearch.ToArray()
        )
    { }
    public DatabaseWarnException(DBErrorWarning[] errors) : 
        base(
            message: string.Join("\nerror: ", errors)
        )
    {
        Errors = errors;
    }
}

public async Task DBPerformActionSAFE(string commandStr)
{
    string ERROR_CATCH_STATEMENT = $"""
        begin try
            {commandStr};
        end try
        begin catch
            SELECT
            ERROR_LINE()      AS ErrorLine,
            ERROR_MESSAGE()   AS ErrorMessage;
        end catch
        """;

    CheckConnection();
    OdbcCommand command = new(ERROR_CATCH_STATEMENT, Database);
    var perform = command.ExecuteReaderAsync();
    if (await Task.WhenAny(perform, Task.Delay(Timeout)) == perform)
    {
        var errors = ExtractReaderData(perform.Result).ToArray();

        if (errors.Length is not 0)
            throw new DatabaseWarnException(
                from warn in errors
                where warn.Length >= 2
                select new DBErrorWarning
                {
                    Line =    (int)    warn[0],
                    Message = (string) warn[1]
                }
            );
    }
    else
    {
        throw new TimeoutException($"Database didn't returned within expected limit time: {Timeout}");
    }
}

Is there any way to get db-warnings from the database in C# OdbcConnection?

1 Answers1