How can I set Context_info every time a connection is created. Also, anyone happen to know how this may affect connection pooling, info is sparse about that.

I'm sorry, but don't you also agree that you give very little to no information ? - which DB type? - llblgen pro version - .net version - what's Context_Info and why do you want to set it? (I've never seen it)

Otis wrote:

I'm sorry, but don't you also agree that you give very little to no information ? - which DB type? - llblgen pro version - .net version - what's Context_Info and why do you want to set it? (I've never seen it)

Indeed. And I should have said info about this on the net is sparse regarding context_info and connection pooling.

SQL 2005 llbl 2 & .net 2

Context_info is a connection param that supplies 128 bytes to any connection transaction. I want to set it with a user id value which will be picked up by table triggers.

Still, I have no clue what you want , sorry. I can't find info about context_info in connectionstring, SqlConnection, SqlTransaction etc. What I do find is a SqlConnectionInfo class but that's in the microsoft.sqlserver.management.common namespace.

Otis wrote:

Still, I have no clue what you want , sorry. I can't find info about context_info in connectionstring, SqlConnection, SqlTransaction etc. What I do find is a SqlConnectionInfo class but that's in the microsoft.sqlserver.management.common namespace.

Nod. Here is the page: http://msdn2.microsoft.com/en-us/library/ms180125.aspx

Example:

SET  CONTEXT_INFO  0x1256698456 
GO 
SELECT  CONTEXT_INFO() 
GO

So, essentially, I could restate the question as: How can I add a transact SQL statement to an Entity.Save(), SelfServicing v2? The Context_Info value will then be picked up by triggers. Very cool I think.

Edit: I have an idea how to do this, but need feedback. I was thinking that I could create a sproc which accepts the value I want to set, the sproc does the SET CONTEXT_INFO. I override OnSave and start a transaction, I call the sproc, then I override OnSaveComplete and commit the transaction. Then the context info could be picked up anywhere in that transaction after the sproc was called. Does this sound feasible?

Edit: Here is more info: From: ms-help /MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/571320f5-7228-4b0e-9d01-ab732d2d1eab.htm

Edit: removed copied text

And another page: From: ms-help: //MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a0b7b9f3-dbda-4350-a274-bd9ecd5c0a74.htm

Edit: removed copied text

You dont need to do all that hassle. Take a look at the Transaction class, which is generated. That class is always used when creating a transaction. In the constructor it creates a connection. So if you alter that template to emit some additional code which then, when it creates the connection and physical transaction, sends the contextinfo statement, it should always work when a transaction is created (directly by you in code or indirectly by llblgen pro when you save entities recursively)

Otis wrote:

You dont need to do all that hassle. Take a look at the Transaction class, which is generated. That class is always used when creating a transaction. In the constructor it creates a connection. So if you alter that template to emit some additional code which then, when it creates the connection and physical transaction, sends the contextinfo statement, it should always work when a transaction is created (directly by you in code or indirectly by llblgen pro when you save entities recursively)

Mmmm I see. Might need some more hand holding:

1) I still need to override OnSave and OnSaveCompleted to create & commit the transaction right?

2) As for modifying Transaction.cs - Which method should I put the code for "SET CONTEXT_INFO <value>" - I am assuming CreatePhysicalTransaction .

I will start implementing this now and just see what happens, LOL.

Edit: This is what I have, <deleted>:

protected override System.Data.IDbTransaction CreatePhysicalTransaction()
        {
            IDbTransaction Trans = DbUtils.CreateTransaction(base.ConnectionToUse, base.TransactionIsolationLevel, this.Name);

            System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand();
            Cmd.Connection = (System.Data.SqlClient.SqlConnection)base.ConnectionToUse;
            Cmd.CommandText = "SET CONTEXT_INFO " + gContextInfo;
            Cmd.Transaction = (System.Data.SqlClient.SqlTransaction)Trans;
            Cmd.ExecuteNonQuery();

            return Trans;
        }

private Transaction Trans;
        protected override void OnSave()
        {
            Trans = new Transaction(IsolationLevel.Unspecified, "Trans");
            Trans.Add(this);
        }

        protected override void OnSaveComplete()
        {
            Trans.Commit();
            base.OnSaveComplete();
        }

Edit: It does WORK! It works perfect!!! YOU ROCK DUDE!