Problem in Context connection of CLR Trigger

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
vinoth@.gsdindia.comHi
Support for SQL Server 2005:
http://communities.microsoft.com/ne...p=sqlserver2005
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"None" wrote:

> 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
> vinoth@.gsdindia.com
>sql