Using application roles with DataReader
- by Shahar
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.
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.
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 is closed.
I can't seem to find a way to close the data reader without closing the connection.
