How to run an NHibernate SQL Delete with a final SELECT reporting completion?

Question

Description
For a project I'm working on, I'm creating a purge feature that deletes old data from this system's database.

I've found that, while my SQL works correctly when ran from SQL Studio, it does not work when ran from C# code. Based on some StackOverflow research, I believe it's due to my needing to call .ExecuteUpdate(), instead of .UniqueResult<PurgeResult>() to actually fire the SQL statement. However, my query is returning a PurgeResult based on if the DELETE statements throw any errors in SQL, so that developers can be informed if something goes wrong.

C# NHibernate Code:

public class PurgeResult {
    public int ResultCode { get; set; }
    public string Message { get; set; }

    public bool IsSuccessful => ResultCode == 1;
}

public static PurgeResult Purge(DateTime purgeTo)
{
    // Verified that my purge SQL runs in SQL studio.  It only fails
    // to delete anything when ran from my C# code.
    var sql = @"BEGIN TRY
                  TRANSACTION PurgeMySystem
                    DELETE FROM InsertTableHere
                      WHERE CreateDate <= :PurgeTo
                    -- Repeat deletes as necessary, specific delete statements may not be
                    -- relevant to this technical question.
                  COMMIT TRANSACTION PurgeMySystem

                  SELECT 1 AS ResultCode, 'Purge successful!' AS Message
                END TRY
                BEGIN CATCH
                  IF @@TRANCOUNT > 0
                  BEGIN
                    ROLLBACK
                  END

                  SELECT 0 AS ResultCode,
                         CONCAT('Message: ', ERROR_MESSAGE(), '\r\n\r\n', 'At line: ', ERROR_LINE()) AS Message
                END CATCH";

    using (var session = DatabaseService.CreateSession())
    {
        var query = session.CreateSQLQuery(sql)
            .SetParameter("PurgeTo", purgeTo)
            .SetResultTransformer(Transformers.AliasToBean(typeof(PurgeResult));

        // Problem line below!  I think for DELETE statements to work,
        // I need to run query.ExecuteUpdate(), but that only returns an
        // int representing row count affected by the statement.
        var result = query.UniqueResult<PurgeResult>();
        return result;
    }
}

Question:
Given a hybrid script above that both DELETEs records and returns a status of that effort, is there a way I can effectively run a script like this in NHibernate? If so, what's the correct way to do what I'm trying to do?

Alternatively: Is there a way to specify what would effectively be an out parameter to this query, so I could still .ExecuteUpdate(), and have my result too? In my normal programming, I try to avoid this, but a bool TryDoSomething(out SomeObject resultObject) pattern in C# isn't unknown, and this situation could well call for something like that setup.

score 0 · Answer 1 · answered Apr 28 '22 at 16:58

After some additional research, as well as conferring with colleagues, there's a simple solution to this problem: Tailor your queries to do a specific thing, and don't be afraid to let C# handle things like error handling/reporting, and transactional setup.

Put differently, this was a design problem. The source SQL was from a stored proc. While stored procs are useful in certain contexts, they have a problem: maintainability. Not only are they separate from your application's codebase (not necessarily a problem), they'll typically do a lot in one compact package. That's not very Single-Responsibility Principle-y at all.

NHibernate has an built-in bias towards keeping database actions focused. As a result, it doesn't support output parameters or returning a complex result from a set of update queries. The good news, though, is that some of the structures one may see in SQL lifted from a legacy proc - like, Try/Catch structures, creating, committing, and rolling back transactions, and reporting status based on if the Catch of a Try/Catch structure is hit - are all quite doable by modern programming languages.

So, do those things in modern programming languages. End result is the above code can be simplified somewhat...

public static PurgeResult Purge(DateTime purgeTo)
{
    PurgeResult result;

    var sql = @"DELETE FROM InsertTableHere
                    WHERE CreateDate <= :PurgeTo
                -- Repeat deletes as necessary, it's cool!";

    using (var session = DatabaseService.CreateSession())
    using (var transaction = session.BeginTransaction())
    {
        try {
            var query = session.CreateSQLQuery(sql)
                .SetParameter("PurgeTo", purgeTo);

            int purgeRowCount = query.ExecuteUpdate();
            transaction.Commit();

            result = PurgeResult.Success($"Purge complete.  Deleted {purgeRowCount} rows of data across various tables.");
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            result = PurgeResult.Exception(ex);
        }
    }

    return result;
}

How to run an NHibernate SQL Delete with a final SELECT reporting completion?

1 Answers1