run multiple stored procedure with single transaction

[araxis]

[mooshpot]

Hi araxis

I hope that you're well.

The best way would be to create a wrapper stored procedure that called the others and then call wrapper from code.

Cheers Moosh

[SamFessler]

Hi araxis

I hope that you're well.

The best way would be to create a wrapper stored procedure that called the others and then call wrapper from code.

Cheers Moosh

Would it be possible to pass a transaction to the dbContext through LoadStoredProc or an extra optional method? I am running into scenarios where we need to do bulk inserts to many tables and the only other option would be passing JSON to a stored procedure wrapper. It would be nice to use the existing transactions supported by EFCore- https://docs.microsoft.com/en-us/ef/core/saving/transactions

[snickler]

@SamFessler - I'm not 100% sure, but it could be something worth looking into. If you want to take a stab at the integration, I will totally welcome it.

[SamFessler]

While it is posible to pass a transaction to snickler, I've found it much easier to just wrap the procedure calls in a TransactionScope, and set "manageConnection:false" on all procedures executed other than the last, which will close the connection. Note: EFCore .SaveChanges() closes any transaction and must be called in the same scope, after any procedures.

Issue could be closed.

Example:

using (TransactionScope trans = new())
{
    await _darwinEHRContext.LoadStoredProc("[Schema].[USP_DoUpdate1]")
                           .WithSqlParam("@Param1", variableExample)
                           .ExecuteStoredProcAsync((handler) =>
                           {
                               restult1= (handler.ReadToList<ModelDto1>() as List<ModelDto1>);
                           }, manageConnection: false);

    await _darwinEHRContext.LoadStoredProc("[Schema].[USP_DoUpdate2]")
                           .WithSqlParam("@Param1", exampleParam2)
                           .ExecuteStoredProcAsync((handler) =>
                           {
                               result2 = (handler.ReadToList<ModelDto2>() as List<ModelDto2>);
                           });
}