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 _r ...
Go to the complete details ...