Problem in Context connection of CLR Trigger

[None]

Hi,

I'm using June CTP Visual Studio 2005.

I have Created CLR Object. In my CLR Method i have opened a
connection like below

public partial class Triggers
{
//In my CLR Method i have Opened the connection like below

[Microsoft.SqlServer.Server.SqlTrigger(Name="ClrTrigger",
Target="Triplets",Event="FOR INSERT")]
public static void CLRTrigger()
{
Triggers objTriggers = new Triggers();
objTriggers.Connection(true);
...
.....
Test();
// Here i'm calling one more method Test();

....
....
objTriggers.Connection(false);
}

In this test method i'm Executing the command.
Public void Test()
{
....
SqlCommand cmd = new SqlCommand();
string cmdText = "select dirname from Docs where id=45";
cmd.CommandText = cmdText;
cmd.Connection = con;
try
{
sdr = cmd.ExecuteReader();
}
catch (Exception exp)
{
WriteLog("R E ::: "+exp.Message);
}
.......
}
//Connection open and Close method
public void Connection(bool status)
{
if (status)
{
con = new SqlConnection("context connection = true");
con.Open();
}
else
{
con.Close();
}
}
}

I'm getting the error of Connection already in use. I have properly
opened the connection in begin of

method and Closed in end of method. Inside the execution of method if i
called some method means then it

can also use the same connection. Then Why its giving this error.
Please anybody knows solution to my

problem let me know to solve it.

Thanks,
Vinoth

(e-mail address removed)

 

[Sahil Malik [MVP]]

Vinoth,

Here is a good explanation of how many context connections can you have open
at a given time in CLR in SQL Server -

http://codebetter.com/blogs/sahil.malik/archive/2005/07/26/129824.aspx

Please let me know if your question is still unanswered and I'll try and
answer.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx