I'm attempting to extend the DataAccessAdapter class so that the first operation on every newly opened connection is a call to sp_setapprole, and the last operation on every connection before it is closed is a call to sp_unsetapprole.

The virtual methods exposed by the DataAccessAdapterBase appear to do exactly what i need OpenConnection/CloseConnection without digging into the source code. And upon further inspection I find the only place there is a call to _activeConnection.Open is in the OpenConnection method (Great). But the problem is that the DataAccessAdapterBase source code doesn't consistently use the CloseConnection method every time it wants to close _activeConnection e.g. CloseConnection, Dispose, Commit, Rollback

I want to alter the llbgen source as a last resort, so I want to make sure that its necessary before going ahead. Also I'd like to get some opinions on the least intrusive way to wire up the DataAccessAdapterBase source to achieve the desired result. I've thought that maybe I'd create an abstract method OnAfterConnectionOpen/OnBeforeConnectionClose, or maybe the following code:

public static class DbConnectionExtensions
    {
        /// <summary>
        /// Opens the connection and immediatley sets the application role
        /// </summary>
        /// <param name="conn"></param>
        /// <returns>The cookie returned by the call to set the application role</returns>
        public static byte[] OpenWithApplicationRole(this DbConnection conn)
        {
            conn.Open();

            if (conn.State == ConnectionState.Open && conn is SqlConnection)
            {
                string roleName = ConfigurationManager.AppSettings["ApplicationRoleName"];
                string password = ConfigurationManager.AppSettings["ApplicationRolePassword"];

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn as SqlConnection;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_setapprole";
                cmd.Parameters.Add(new SqlParameter("@rolename", roleName));
                cmd.Parameters.Add(new SqlParameter("@password", password));
                cmd.Parameters.Add(new SqlParameter("@fCreateCookie", true));

                SqlParameter cookieParam = new SqlParameter("@cookie", SqlDbType.VarBinary, 8000);
                cookieParam.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(cookieParam);

                try
                {
                    cmd.ExecuteNonQuery();
                    return cookieParam.Value as byte[];
                }
                finally
                {
                    cmd.Dispose();
                }
            }

            return null;
        }

        /// <summary>
        /// Unsets the application role before closing the connection
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="cookie">The cookie returned by the call to set the application role</param>
        public static void CloseWithApplicationRole(this DbConnection conn, byte[] cookie)
        {
            if (cookie != null)
            {
                if (conn.State == ConnectionState.Open && conn is SqlConnection)
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn as SqlConnection;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_unsetapprole";
                    cmd.Parameters.Add(new SqlParameter("@cookie", cookie));

                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        cmd.Dispose();
                    }
                }
            }

            conn.Close();
        }
    }

Then replace all the

 _activeConnection.Open

with

_activeConnection.OpenWithApplicationRole

and the same with all the closing statements

Has anyone built and redeployed the ormsupportclasses under .Net 3.5? Can you think of a less intrusive way to accomplish my goal? Can someone with a deeper knowledge of the DataAccessAdapter spot a problem with this solution?

Thanks in advance for any assistance anyone can provide.

LLBLGEN 2.5 Adapter Templates (obviously) SQL Server 2005 .NET 3.5

The thing with connection close is indeed that it's not always using CloseConnection(). There are 3 other places besides in CloseConnection() where this is present. However, all those locations are in special areas: Commit, Rollback and Dispose so they use the direct call.

Calling CloseConnection() from some of these locations causes a loop, as CloseConnection() first rollbacks a transaction in progress, however Rollback does that as well

So you should do this: - derive a class from DataAccessAdapter - override OpenConnection. Add your code there to start the role=using code. First call the base method - override OnAfterTransactionCommit and OnAfterTransactionRollback and simply call CloseConnection in there, and then set KeepConnectionOpen to false. - Override CloseConnection and do your thing there before calling the base method. - Override dispose: close connection first, then call base method.

Now the important thing: you should use the dataaccessadapter with KeepConnectionOpen = true. This will assure you that Commit and Rollback won't close the connection, so you can do so in the OnAfter... methods.

This should get you started without altering the base' code at all

Thanks for the quick response. I used those nifty extension methods when I had to crack the Enterprise Library source for the same issue.

I'm assuming that DataAccessAdapter only has one open connection at a time. This appears to be the case after looking at the source, but if you could confirm that I'd appreciate it.

Here's the code for everyone's future reference.

public class CoreDataAccessAdapter : DataAccessAdapter
    {
        private byte[] m_CookieByteArray;

        public CoreDataAccessAdapter()
            : base(true)
        { }

        public override void OpenConnection()
        {
            base.OpenConnection();

            SqlConnection _SqlConnection = this.GetActiveConnection() as SqlConnection;

            if (_SqlConnection != null
                &&_SqlConnection.State == ConnectionState.Open
                && m_CookieByteArray == null)
            {
                string _RoleName = System.Configuration.ConfigurationManager.AppSettings["ApplicationRoleName"];
                string _Password = System.Configuration.ConfigurationManager.AppSettings["ApplicationRolePassword"];

                SqlCommand _SqlCommand = new SqlCommand();
                _SqlCommand.Connection = _SqlConnection;
                _SqlCommand.CommandType = CommandType.StoredProcedure;
                _SqlCommand.CommandText = "sp_setapprole";
                _SqlCommand.Parameters.Add(new SqlParameter("@rolename", _RoleName));
                _SqlCommand.Parameters.Add(new SqlParameter("@password", _Password));
                _SqlCommand.Parameters.Add(new SqlParameter("@fCreateCookie", true));

                SqlParameter _CookieSqlParameter = new SqlParameter("@cookie", SqlDbType.VarBinary, 8000);
                _CookieSqlParameter.Direction = ParameterDirection.Output;
                _SqlCommand.Parameters.Add(_CookieSqlParameter);

                try
                {
                    _SqlCommand.ExecuteNonQuery();
                    m_CookieByteArray = _CookieSqlParameter.Value as byte[];
                }
                finally
                {
                    _SqlCommand.Dispose();
                }
            }
        }

        protected override void OnAfterTransactionCommit()
        {
            CloseConnection();
            KeepConnectionOpen = false;
        }

        protected override void OnAfterTransactionRollback()
        {
            CloseConnection();
            KeepConnectionOpen = false;
        }

        public override void CloseConnection()
        {
            SqlConnection _SqlConnection = this.GetActiveConnection() as SqlConnection;

            if (_SqlConnection != null 
                && _SqlConnection.State == ConnectionState.Open
                && m_CookieByteArray != null)
            {
                SqlCommand _SqlCommand = new SqlCommand();
                _SqlCommand.Connection = _SqlConnection;
                _SqlCommand.CommandType = CommandType.StoredProcedure;
                _SqlCommand.CommandText = "sp_unsetapprole";
                _SqlCommand.Parameters.Add(new SqlParameter("@cookie", m_CookieByteArray));

                try
                {
                    _SqlCommand.ExecuteNonQuery();
                }
                finally
                {
                    _SqlCommand.Dispose();
                    m_CookieByteArray = null;
                }
            }

            base.CloseConnection();
        }

        protected override void Dispose(bool isDisposing)
        {
            CloseConnection();
            base.Dispose(isDisposing);
        }

    }

becker-samm wrote:

Thanks for the quick response. I used those nifty extension methods when I had to crack the Enterprise Library source for the same issue.

I'm assuming that DataAccessAdapter only has one open connection at a time. This appears to be the case after looking at the source, but if you could confirm that I'd appreciate it.

Definitely just 1 open connection at a time Also just 1 transaction at a time

Thanks for sharing the code!

LLBLGen version: 5.1.1

The use of application role works for the entity fetching with the above mentioned changes. It seems not to work if a Retrival procedure is called, should I do some more changes? Error is no permissions to execute, but the application role has execute rights for this procedure!

                    using (CoreDataAccessAdapter adapter = CoreDataAccessAdapter.CreateInstance())
                    {
                        if (!mCMPinsDict.ContainsKey(Convert.ToInt32(cable.CM1ID)))
                        {
                            mCMPinsDict[Convert.ToInt64(cable.CM1ID)] = RetrievalProcedures.AllControlModulePins(Convert.ToInt64(cable.CM1ID), adapter);
                            mDataTbl.Merge(mCMPinsDict[Convert.ToInt64(cable.CM1ID)]);
                        }
                        if (!mCMPinsDict.ContainsKey(Convert.ToInt32(cable.CM2ID)))
                        {
                            mCMPinsDict[Convert.ToInt64(cable.CM2ID)] = RetrievalProcedures.AllControlModulePins(Convert.ToInt64(cable.CM2ID), adapter);
                            mDataTbl.Merge(mCMPinsDict[Convert.ToInt64(cable.CM2ID)]);
                        }
                    }

It seems not to work if a Retrival procedure is called, should I do some more changes? Error is no permissions to execute, but the application role has execute rights for this procedure!

Do you mean, your code with invoked and the AppRole was set, but still the SP call failed? Could you please post the exact exception text and stack trace?

Walaa wrote:

Do you mean, your code with invoked and the AppRole was set, but still the SP call failed? Could you please post the exact exception text and stack trace?

What I mean is that the public override void OpenConnection() is not called in case of retrival procedure call! So the approle is not set at all!

The call ends up in DbDataAdapter.Fill which opens the connection and close it implicitly. We'll check if we can do that explicitly, and get back to you .

the OpenConnection is bypassed by the usage of DbDataAdapter.Fill. We'll look into how to make sure the connection is opened beforehand.

Change your code into:

using (CoreDataAccessAdapter adapter = CoreDataAccessAdapter.CreateInstance())
{
    try
    {
        adapter.OpenConnection();
        if (!mCMPinsDict.ContainsKey(Convert.ToInt32(cable.CM1ID)))
        {
            mCMPinsDict[Convert.ToInt64(cable.CM1ID)] = RetrievalProcedures.AllControlModulePins(Convert.ToInt64(cable.CM1ID), adapter);
            mDataTbl.Merge(mCMPinsDict[Convert.ToInt64(cable.CM1ID)]);
        }
        if (!mCMPinsDict.ContainsKey(Convert.ToInt32(cable.CM2ID)))
        {
            mCMPinsDict[Convert.ToInt64(cable.CM2ID)] = RetrievalProcedures.AllControlModulePins(Convert.ToInt64(cable.CM2ID), adapter);
            mDataTbl.Merge(mCMPinsDict[Convert.ToInt64(cable.CM2ID)]);
        }
    }
    finally
    {
        adapter.CloseConnection();
    }
}

this way you have the open connection (and therefore the roll applied) and it will re-use that connection when fetching the data.

We'll change the runtime in the next version so it'll open the connection if it's not already open explicitly so you dont' have to do that anymore.

Thanks for the workaround