Using application roles with DataReader

I have an application that should use an application role from the database. I'm trying to make this work with queries that are actually run using Subsonic (2). To do this, I created my own DataProvider, which inherits from Subsonic's SqlDataProvider. It overrides the CreateConnection function, and calls sp_appsetrole to set the application role after the connection is created. This part works fine, and I'm able to get data using the application role.

The problem comes when I try to unset the application role. I couldn't find any place in the code where my provider is called after the query is done, so I tried to add my own, by changing SubSonic code. The problem is that Subsonic uses a data reader. It loads data from the data reader, and then closes it.

1. If I unset the application role before the data reader is closed, I get an error saying: There is already an open DataReader associated with this Command which must be closed first.
2. If I unset the application role after the data reader is closed, I get an error saying ExecuteNonQuery requires an open and available Connection. The connection's current state开发者_StackOverflow中文版 is closed.

I can't seem to find a way to close the data reader without closing the connection.

---

Do you have to use the role for every query?

If not you can use a SharedDbConnectionScope()

using(var scope = new SharedDbConnectionScope())
{

    // within this using block you have a single connection
    // that isn't closed until scope.Dispose() is called
    // (happens automatically while leaving this block)
    // and you have access to scope.CurrentConnection

    // Do your init stuff
    SetRole(scope.CurrentConnection);

    var product = new Product();
    product.Code = "12345";
    product.Save();

    // Revert to normal
    UnsetRole(scope.CurrentConnection);

}

---

The problem is that Subsonic executes its reader with CloseConnection. If I make it not close the connection I can unset the application role after the reader is closed.

---

May be you can subscribe to the event like this:

Connection.StateChange += new System.Data.StateChangeEventHandler(Connection_StateChange);

And then do some actions according to the new state of this connection:

if(e.CurrentState== System.Data.ConnectionState.Open)
dbworker.ExecuteCommand("EXEC sp_setapprole application, 'password'");