August 2, 2005 at 4:56 am
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
August 2, 2005 at 5:19 am
It's a little bit difficult to understand all that happens since some code is missing, but it seems to me you are creating an instance of the class in the static method (the trigger) and working with an instance variable (the connection) on that instance. Try changing the code to this:
public partial class Triggers
{
[Microsoft.SqlServer.Server.SqlTrigger(Name="ClrTrigger", Target="Triplets",Event="FOR INSERT")]
public static void CLRTrigger()
{
using(SqlConnection con = new SqlConnection("context connection = true");
{
using(SqlCommand cmd = con.CreateCommand())
{
string cmdText = "select dirname from Docs where id=45";
cmd.CommandText = cmdText;
using(SqlDataReader sdr = cmd.ExecuteReader())
{
// Do stuff with reader
}
}
}
}
}
This is written by hand (don't have anything to test with) so it may not be 100% correct, and it may not do exactly what you want, but I think it should set you off in the right direction.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply