how can i set the context_info sql statement from

问题:

I need to set the Context_info from my winforms application so that i can notify the database not to run a trigger if my application is saving a record vs a legacy app which needs to run the trigger. Everything i have read says it needs to be set using a data context.

In my application i am using an entity manager. How can i set the data context using an entity manager instead of a datacontext. I just want the trigger to know that it is my app running and saving the data on which the trigger is set

i want to do like the follow. "set context_info '0x1234'

at the start of the trigger i check to see if the context_info is set and dont run the trigger. The legacy does not set the context_info.

回答1:

We needed to do this same thing for our app. As Kim mentioned, there is a lot of information in the Dev Force forums. You can find a full explanation of what we did in this forum post but I'll reproduce the important parts here for reference...

We had a similar requirement in our application. In our case, we needed to call a custom stored procedure every time a DB connection was opened - the procedure would 'mark' the connection with what user was currently active because we have lots of triggers that need to know the current user (we use context_info to accomplish this 'marking').

We were able to handle this with the help of the EF Provider Wrapper Toolkit (also seems to be on Nuget now). That basically lets you inject your own logic into various ADO.NET objects - so at the very lowest level of database access. We then made our own custom DbConnection class that DevForce/EntityFramework end up using. It was actually pretty easy and has given us a lot of nice 'hooks' into the lowest level of database access that has come in handy a lot.

Here is some sample code for our custom DbConnection class that shows the kinds of things you can accomplish:

/// <summary>
/// Custom implementation of a wrapper to <see cref="DbConnection"/>.
/// Allows custom behavior at the connection level.
/// </summary>
internal class CustomDbConnection : DbConnectionWrapper
{
    /// <summary>
    /// Opens a database connection with the settings specified by 
    /// the <see cref="P:System.Data.Common.DbConnection.ConnectionString"/>.
    /// </summary>
    public override void Open()
    {
        base.Open();

        //After the connection has been opened, do our logic to prep the connection
        SetContextInfo();

        //...and we do some other stuff not relevant to this discussion
    }

    /// <summary>
    /// Closes the connection to the database. This is the preferred method of closing any open connection.
    /// </summary>
    /// <exception cref="T:System.Data.Common.DbException">
    /// The connection-level error that occurred while opening the connection.
    /// </exception>
    public override void Close()
    {
        //Before closing, we do some cleanup with the connection to make sure we leave it clean
        //   for the next person that might get it....

        base.Close();
    }

    /// <summary>
    /// Attempts to set context_info to the current connection if the user is 
    /// logged in to our application.
    /// </summary>
    private void SetContextInfo()
    {
        //See if a user is logged in
        var user = Thread.CurrentPrincipal as OurCustomUserType;

        //If not, we don't need to do anything - this is probably a very early call in the application
        if (user == null)
            return;

        //Create the ADO.NET command that will call our stored procedure
        var cmd = CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "p_prepare_connection_for_use";

        //Set the parameters based on the currently logged in user
        cmd.CreateParameter("as_session_id", user.SessionID, null, DbType.Guid);
        cmd.CreateParameter("ai_user_sid", user.UserID, null, DbType.Int32);

        //Run the SP
        cmd.ExecuteNonQuery();
    }

In EF6 and beyond, there might be a cleaner way to intercept database calls....but this approach has been working great for years.

回答2:

Although the IdeaBlade forums have been closed to new activity, they are still searchable and do often contain helpful answers and information regarding DevForce issues. In this case, if you search for context_info there you will find some useful threads. One in particular shows how to use either the EF Provider Wrapper Toolkit or EF 6 DbCommandInterceptor to work with the context_info. These do not require a DbContext.