Problem while updating DB in mulithreaded windows service

[EDN Admin]

Hi,
Im developing windows service that listens TIBCO.EMS queue which provides xml messages. Each message is parsed and inserted into the table.

In OnStart() method app is creating DB connection and connecting to the queue:
<pre class="prettyprint protected override void OnStart(string[] args)
{
DBConnectionRead = GetDBConnection();
DBConnectionRead.Open();

connectQueue(getAppString("TIBCOServer"),
getAppString("TIBCOUser"), getAppString("TIBCOPassword"), getAppString("TIBCOReadQueue"));
}[/code]
<pre class="prettyprint private void connectQueue(String server, String uid, String pid, String queuename)
{
try {
ConnectionFactory factory = new TIBCO.EMS.ConnectionFactory(server);

// create the connection
connection = factory.CreateConnection(uid, pid);

// create the session
session = connection.CreateSession(false, Session.AUTO_ACKNOWLEDGE);

// set the exception listener
connection.ExceptionListener = this;

// create the destination
destination = session.CreateQueue(queuename);

//Console.WriteLine("Subscribing to destination: " + queuename);

// create the consumer
msgConsumer = session.CreateConsumer(destination);

// set the message listener
msgConsumer.MessageListener = this;

// start the connection
connection.Start();

// Note: when message callback is used, the session
// creates the dispatcher thread which is not a daemon
// thread by default. Thus we can quit this method however
// the application will keep running. It is possible to
// specify that all session dispatchers are daemon threads.
}
catch (Exception e)
{
EventLog.WriteEntry("Exception: " + e.Message);
EventLog.WriteEntry(e.StackTrace);
}
}[/code]
When new xml message is sent to the queue following method is invoked:
<pre class="prettyprint public void OnMessage(Message msg)
{
try
{
TIBCO.EMS.TextMessage txtmsg = (TIBCO.EMS.TextMessage)msg;
TIBCO.EMS.Queue queue = (TIBCO.EMS.Queue)txtmsg.Destination;

if (queue.QueueName.Equals("Test_QUEUE"))
{
ParseXmlRead(txtmsg.Text);
}
}
catch (Exception e)
{
EventLog.WriteEntry("Unexpected exception message callback!");
EventLog.WriteEntry(e.Message);
}
}[/code]
After parsing xml method InsertIntoRead() is called:
<pre class="prettyprint public void InsertIntoRead(String SqlInsert, int DocId, Hashtable hsTable)
{

if (DBConnectionRead.State != ConnectionState.Open)
DBConnectionRead.Open();

// Start a local transaction
SqlTransaction myTrans = DBConnectionRead.BeginTransaction();

SqlCommand Command = new SqlCommand();
Command.Connection = DBConnectionRead;
Command.Transaction = myTrans;
try
{
Command.CommandText = "SELECT DocId FROM Reads WHERE DocId=@DocID";
Command.Parameters.AddWithValue("@DocID", DocId); //checking if Document with DocId already exists
if (Command.ExecuteScalar() == null)
{
Command.CommandText = SqlInsert;
Command.Parameters.RemoveAt("@DocID");
foreach (DictionaryEntry entry in hsTable)
Command.Parameters.AddWithValue(Convert.ToString(entry.Key), entry.Value);

if (Command.ExecuteNonQuery() > 0)
myTrans.Commit();
else
myTrans.Rollback();
}
else
{

Command.CommandText = "DELETE FROM Reads WHERE DocId=@DocID";
if (Command.ExecuteNonQuery() > 0)
{
Command.CommandText = SqlInsert;
Command.Parameters.RemoveAt("@DocID");
foreach (DictionaryEntry entry in hsTable)
Command.Parameters.AddWithValue(Convert.ToString(entry.Key), entry.Value);

if (Command.ExecuteNonQuery() > 0)
myTrans.Commit();
else
myTrans.Rollback();
}
else
{
myTrans.Rollback();
}
}
}
catch (Exception e)
{
myTrans.Rollback();
ventLog.WriteEntry(e.Message);
}
} [/code]
Unfortunately in the above method I get following error messages:
"SqlConnection does not support parallel transactions"
I noticed that it happens after inserting about 20 records.
<br/>

In console type application code works correctly. I dont get any errors.
<br/>

Does anyone know how I can construct my code to avoid such errors?<br/>

<br/>

Thanks,<br/>

Luk<br/>

<br/>


View the full article