I have implemented a new class called ApplicationRoleConnectionProvider in
VB.NET so that I can make use of MS SQL Server application roles, and I
substituted it in for the DriverConnectionProvider in the nhibernate
configuration. The implementation of the GetConnection and CloseConnection
methods are as follows:
Public Class ApplicationRoleConnectionProvider
Inherits DriverConnectionProvider
Public Overrides Function GetConnection() As IDbConnection
Try
Dim connection As IDbConnection = MyBase.GetConnection()
Dim cookie As Byte()
Using cmd As SqlCommand = connection.CreateCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "sp_setapprole"
cmd.Parameters.Add(New SqlParameter("@rolename",
"rolename"))
cmd.Parameters.Add(New SqlParameter("@password",
"rolepassword"))
cmd.Parameters.Add(New SqlParameter("@fCreateCookie",
True))
cmd.Parameters.Add(New SqlParameter() With
{.ParameterName = "@cookie",
.DbType =
DbType.Binary,
.Direction
= ParameterDirection.Output,
.Size =
8000})
cmd.ExecuteNonQuery()
Dim outVal As SqlParameter = cmd.Parameters("@cookie")
' This returns a byte array value for the cookie
generated
cookie = outVal.Value
' Return connection
Return New ApplicationRoleConnectionWrapper(connection,
cookie)
End Using
Catch
Throw
End Try
End Function
Public Overrides Sub CloseConnection(ByVal conn As IDbConnection)
Dim myConn As ApplicationRoleConnectionWrapper = conn
If myConn IsNot Nothing Then
Using cmd As IDbCommand = myConn.CreateCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "sp_unsetapprole"
cmd.Parameters.Add(New SqlParameter("@cookie",
myConn.Cookie))
cmd.ExecuteNonQuery()
End Using
End If
MyBase.CloseConnection(conn)
End Sub
End Class
When I attempt to use a session to query the database, an exception is
thrown indicating that *ApplicationRoleConnectionWrapper cannot be cast to
System.Data.Common.DBConnection*. ApplicationRoleConnectionWrapper
implements IDBConnection and passes all method calls onto the enclosed
connection object passed to it in the constructor. It also maintains the
cookie value returned from SQL Server for later use when the connection is
closed. If I modify ApplicationRoleConnectionWrapper to descend from
DBConnection and pass a SqlServerConnection object to its constructor, the
exception raised when querying is *ApplicationRoleConnectionWrapper cannot
be cast to System.Data.Common.SqlConnection.* I cannot create a connection
wrapper that inherits from SqlConnection as the class is sealed and cannot
be used as a base class for a new class.
Can anyone see what I've done wrong in this code? Has anyone successfully
written a custom DriverConnectionProvider class for using MS SQL Server
application roles? NHibernate version is 3.3.1.4000.
Thanks for any help.
Rich
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/nhusers/-/RJ9s52XSJQYJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
