c# ADO set context_info for SQL INSERT TRIGGER

Question

I have to use an existing INSERT trigger which uses CONTEXT_INFO...

How can I pass "context_info" to a c# SqlCommand so that this trigger keeps working correctly?

Usage in SQL-Trigger:

select @ctxt=context_info from master.dbo.sysprocesses where spid = @@spid
set @session=substring(@ctxt,1,4)
set @contextid=substring(@ctxt,5,4)
set @ntduser=substring(@ctxt,9,120)

Tried:

                            //string strContext = "declare @context_info varbinary(30) set @context_info = cast('abcdefghijklmnopqrstuv1234567890' as varbinary(30)) set context_info @context_info";
                            ///////////////

                            SqlCommandBuilder cb = new SqlCommandBuilder(da);
                            da.UpdateCommand = cb.GetUpdateCommand();
                            //da.UpdateCommand.CommandText= da.UpdateCommand.CommandText.Replace(" WHERE ", strContext+" WHERE ");
                            //da.UpdateCommand.CommandText = "SET CONTEXT_INFO  0x1 " + da.UpdateCommand.CommandText;
                            da.Update(dataTable);
                            da.Dispose();

see commented out code... In my SQL Trigger "context_info" was always empty

Already read that: https://social.msdn.microsoft.com/Forums/en-US/4a0ecb28-11cb-45ec-adbd-d72ac65b158a/how-to-pass-net-applications-parameter-to-a-trigger?forum=transactsql but also does not work.

Isn't there a sample out there where context_info is passed to SqlCommand or SqlConnection or SqlTransaction?

I'm not really following what you're trying to do here. Rather than just modifying and using the UpdateCommand, can you show us what it ends up being? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jun 7, 2018 at 7:51
See code sample from my trigger. How can I pass "context_info" ? — stefan
– stefan, Commented Jun 7, 2018 at 8:02
You are executing a query, not a trigger. If you have such complex logic (why?) write a stored procedure instead of trying to concatenate strings. What are you trying to do in the first place though? If you want to pass user and session information, do so explicitly. Don't try to depend on magic strings and global variables — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 7, 2018 at 8:16
Can't change the trigger since it is not mine. I have use the existing one and this requires context_info for login... — stefan
– stefan, Commented Jun 7, 2018 at 8:43

PrfctByDsgn · Accepted Answer · 2018-06-11 08:33:04Z

1

I used something like this (simplified) once to set some information (guid) so that it can be retrieved from inside a trigger ...

string sql = $"DECLARE @session uniqueidentifier; SET @session = '{guid}'; SET CONTEXT_INFO @session";
var cmd = new SqlCommand(sql, connection, transaction);
cmd.ExecuteNonQuery();

Be sure not to close the connection between setting the context_info and invokation of the trigger ...

answered Jun 11, 2018 at 8:33

PrfctByDsgn

1,0621 gold badge15 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

stefan Over a year ago

I send it before my INSERT command and it works. As described in the link I posted in my question. However, thanks for your help...

Collectives™ on Stack Overflow

c# ADO set context_info for SQL INSERT TRIGGER

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related