Custom Repo Disable/Set Transaction scope? #3633

bbakermmc created 6 years ago

I have a custom repo which calls a stored proc and I get this error if I don't set the controller to [UnitOfWork(isTransactional: false)] I would much rather in the repo set the transaction to the same as the UoW or disable it in the repo/app service and not in the controller/anywhere I might call the method.

ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

public void ImportExternalAction(int contactId, string packageCode, string activityCode, string emailAddress,
        int sourceId, string user)
    {
        var cmd = Context.Database.GetDbConnection().CreateCommand();

        cmd.CommandText = "EXTERNAL_API@ImportExternalAction";
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add(new SqlParameter("i_ContactId", contactId));
        cmd.Parameters.Add(new SqlParameter("i_PackageCode", packageCode));
        cmd.Parameters.Add(new SqlParameter("i_EmailAddress", emailAddress));
        cmd.Parameters.Add(new SqlParameter("i_ActivityCode", activityCode));
        cmd.Parameters.Add(new SqlParameter("i_SourceId", sourceId));
        cmd.Parameters.Add(new SqlParameter("i_LoginName", user));

        var isOpen = cmd.Connection.State == ConnectionState.Open;
        if (!isOpen)
        {
            cmd.Connection.Open();
        }

        cmd.ExecuteNonQuery();

        if (isOpen)
        {
            cmd.Connection.Close();
        }

    }

3 Answer(s)

yekalkan created 6 years ago

Hello,

You have to get the transaction manually. That should fix your problem:

private readonly IActiveTransactionProvider _transactionProvider;

       public YourRepository(. . . . .  IActiveTransactionProvider transactionProvider)
            : base(dbContextProvider)
        {
           .
           .
            _transactionProvider = transactionProvider;
        }
        .
        .
       cmd.CommandText = "EXTERNAL_API@ImportExternalAction";
       cmd.CommandType = CommandType.StoredProcedure;


           cmd.Transaction = (DbTransaction)_transactionProvider.GetActiveTransaction(new ActiveTransactionProviderArgs
            {
                {"ContextType", typeof(YourAppDbContext) },
                {"MultiTenancySide", MultiTenancySide }
            });


        cmd.Parameters.Add(new SqlParameter("i_ContactId", contactId));
        cmd.Parameters.Add(new SqlParameter("i_PackageCode", packageCode));
        .
        .

0

bbakermmc created 6 years ago

Thanks, that was it.
0

alper created 6 years ago
Support Team

Thanks for your feedback ;)

Have an answer to this question? Log in and write your answer.