none
Reuse context connection while DataReader still open

    Question

  • I try to create trigger using .net clr to replace insert procedure (INSTEAD OF), but I've problem on re-use SqlConnection while my DataReader is open. Here my trigger code:

    	[Microsoft.SqlServer.Server.SqlTrigger (Name="InsteadOf_Insert_TestCLR", Target="TestCLR", Event="INSTEAD OF INSERT")]
    	public static void TestCLR()
    	{
    		using (SqlConnection _connection1 = new SqlConnection("context connection=true"))
    		{
    			_connection1.Open();
    			SqlDataReader _reader1 = (new SqlCommand("select * from INSERTED", _connection1)).ExecuteReader();
    			while (_reader1.Read())
    			{
    				SqlCommand _command = new SqlCommand("select * from TestCLRHash", _connection1);
    				DataTable _table1 = new DataTable();
    				(new SqlDataAdapter(_command)).Fill(_table1);
    				
    				// Check hash value on _reader1 hash with TestCLRHash.
    				// If exists on TestCLRHash table return TestCLRHashId value to be used as _reader1 record Id
    			}
    		}
    	}
    And this is the query the I use to test:
    declare @temp table(FirstName varchar(50), LastName varchar(50))
    insert into @temp values ('John', 'Doe')
    insert into @temp values ('Sarah', 'Doe')
    insert into @temp values ('Mike', 'Doe')
    
    insert into TestCLR
    select * from @temp
    From query above I need the SqlDataReader to read INSERTED values so I can check each inserted record if it exists or not on TestCLRHash table. And if it exists then I need to use the TestCLRHashId as my new record identifier. The problem is I can't load the TestCLRHash table since the _reader1 is still open. I can't use DataTable as solution to put the INSERTED record and then foreach thru it since the inserted values can be thousands so the memory can be full.

    Wednesday, April 18, 2012 1:15 AM

All replies

  • Hello,

    You can't reuse a connect as long as a data reader is open (you already got this as an error message) and you also can't open a second "context connection", that's a further limitation for CLR.

    At all, this simple due is much better done in T-SQL then as a CLR.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Wednesday, April 18, 2012 4:30 AM
  • But there will be no issue if I create new SqlConnection but the connection string not context connection, example Datasource=.\SQLEXPRESS;Database=testdb;....
    Wednesday, April 18, 2012 12:07 PM
  • Yes, that will work as long as all properties like database name stays the same as in the connection string.

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Thursday, April 19, 2012 6:36 AM
  • Well the solution with create new connection it won't work to me either, it still slow since I must iterate thru all records. So I'm back to drawing board to check if there CLR method that enable me to analyze/calculate single row as stored function parameter. So I can do this on my sql:

    insert into @tempTable
    select *, Func1(<data row here>) as Func1Result
    from SourceTable

    so then I can execute

    insert into TestClr
    select * from @tempTable where Func1Result = 0 -- 0 or something

    Wednesday, April 25, 2012 9:04 AM
  • hi please go through the below link.

    http://boardreader.com/thread/SoapHttpClientProtocol_reuse_connection_ryt4__325cfb3b-65bb-43e9-8202-7fc4702cfd53.html

    Friday, May 04, 2012 12:16 PM
  • ayutechi, there is really no relation between the question and your suggested link.

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Friday, May 04, 2012 3:22 PM