Entlib 3.1 set sp_approle within transactionscope

Topics: Data Access Application Block
May 12, 2010 at 11:45 AM


How can I set the sp_setapprole when the transactionscope is started from the BL.

For example.

                using (TransactionScope ts = new TransactionScope())
                    db.ExecuteNonQuery(CommandType.Text, "INSERT INTO dummyTable (id, message) VALUES (123,'dit is a test test')");

I made a special provider, when there is a transactionscope their will be a new connection generated and the approle will be set.

                DbCommand dBCommand = (sender as DbConnection).CreateCommand();

                dBCommand.CommandType = CommandType.Text;
                dBCommand.CommandText = string.Format("sp_setapprole '{0}', '{1}'", roleName, rolePassword);

But when I set the approle I get the message "approle cannot be set within an transaction". How am I able to set the approle?



May 13, 2010 at 1:43 AM

sp_setapprole cannot be executed within a user-defined transaction.  So you made the special provider so that you can provide a connection that is different from the connection used in the transaction to the DbCommand for the sp_setapprole, is this right?  I'm not sure about this but I think in the code you posted, it seems like you create a new command but used the same connection

(sender as DbConnection).CreateCommand();


Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.

Dec 26, 2014 at 3:47 AM
I have same problem. Please explain what you did to resolve this issue.
  1. As for I know if you call sp_setapprole on connection object. you should call the other command on the same connection object. scope for set_approle is same connection.