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="ClrTri gger",
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

vi****@gsdindia.com

[Nicholas Paldino [.NET/C# MVP]]

None,

I wouldn't handle the connection opening and closing like this. Rather,
what I would do is have the Connection method return the opened connection,
and then you can use that in a using statement. I get the feeling there is
more to this than you are showing, that maybe an exception is being thrown
and a connection is being left out there dangling.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"None" <vi********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

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="ClrTri gger",
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

vi****@gsdindia.com