How to organize a polling thread

[Alexander Dronov]

Hello guys.

I have a polling thread in my application. It calls GetMyStructs method with interval 1,5 seconds.

// ...
SqlConnection _sqlConnection = new SqlConnection() ;
_sqlConnection. ConnectionStrin g = string.Format(" Server = {0}; User Id = {1}; pwd = {2}; Database = {3}; Pooling = false; MultipleActiveR esultSets = true", Instance, Login, Password, Database); // OK

_activateAppRol e = new SqlCommand("EXE C sp_setapprole application, '12345'", _sqlConnection) ; // OK
// ...

public MyStruct[] GetMyStructs()
{
List<MyStruct> structs = new List<MyStruct>( );

try
{
_sqlConnection. Open();
_activateAppRol e.ExecuteNonQue ry();

SqlCommand cmd = new SqlCommand("Get MyStructs", _sqlConnection) ;
cmd.CommandType = CommandType.Sto redProcedure;

SqlDataReader rdr = cmd.ExecuteRead er();

while (rdr.Read())
{
MyStruct struct = new MyStruct();

struct.Id = Convert.ToInt32 (rdr["ID"]);

// ...

structs.Add(str uct);
}
}
catch (Exception exc)
{
// occasionally, I get an error described below here 'impersonate security context' ....

throw;
}
finally
{
try
{
_sqlConnection. Close();
}
catch
{
}
}

return structs.ToArray ();
}


Occasionally (only occasionally), I get an error:

"Impersonat e Session Security Context" cannot be called in this batch because a simultaneous batch has called it.


I know that interval 1,5 seconds is too small but my manager insists on that interval. I understand that error occurs because of previous request is not completed yet (Am I right ?).

I'm not very familiar with ms sql server or ado .net internals, but how can I manage this situation ? What should I do in order to fix my problem ?


PS: i'm not a native english speaker, so forgive me my possible grammar errors.

[ck9663]

It looks like you're using a single connection profile to your sql-server to execute your sp. Since your interval of 1.5 is too small, the first connection is still active when your second tries.

Can you try using a different connection type? This, however, would depend on your front-end, which I assume a C-based (C#, Java, etc) tool...

Good Luck!!!

~~ CK