1/24/2007

ADO.NET ADO.NET TableAdapters and SQL Server Application Roles

I wonder how MS envisage to implement Application Roles in SQL Server and be able to use TableAdapters at the same time.

To make this work, you need to set the application role right after connecting to the database and unset it before closing the connection, otherwise connection pooling does not work.

I would have done this with the SQLClient.SQLConnection object if it had been extensible, unfortunately it is a sealed class (why?!?).

So now, instead of just using the table adapters as they are generated, I need to write a wrapper class for each table adapter, containing a wrapper method again for each data retrieval/update method, which will create a connection, open it, set the application role, then execute the original method, then unset the role and close the connection again.

This does not appear to me like a good design. Maybe you can think of a better way of achieving this, a more generic approach?

And finally, since Application Roles are a key concept of SQL Server, couldn't the connection object support it, or even better - why don't we just specify the application role in the connection string?

Any ideas are greatly appreciated!

No comments:

adaxas Web Directory