For faster application, you can try rewrite sql statement to Stored procedure.

 "Mithun Verma" <mithunv@MAQSoftware.com> =92=E5=AB=BC=83=E4=B6=81:#qJ8sj8ZDHA.2632@TK2MSFTNGP12.phx.gbl...
 Hello All,

 My windows application has a form that needs to display fresh data to the user at short intervals (say every 10 seconds). For this purpose, I have included a timer. On every timer tick, I perform the database related operations to display the result to the user.
 Note: Multiple instances of my application will be running at the same time and hence the timer running in each instance and initiation database operations.

 With this approach, I started receiving errors like: Connection timed out. The connection pool has reached its maximum size because every request to the database opened and closed a connection. I changed the approach to:
 Creating a single connection for the entire form so that all operations use the same open connection. The connection is closed when the form closes.
 Even then, I now get the following type of errors:

 a) 8/11/2003 3:21:40 PM    SqlUtility   Execute   Error: Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
 TimeTic Calling sp_ProductionTestBackgroundTask    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    at IMD.OM.Database.SqlUtility.Execute(SqlParameter& prmOutParam, SqlConnection cnnCommon) in d:\khyatis\work\projects\imd\imdom\misc\sqlutility.cs:line 595
 8/11/2003 3:21:40 PM    SC Status   Start NSC Monitor   Error:     at OM.Database.SqlUtility.Execute(SqlParameter& prmOutParam, SqlConnection cnnCommon) in d:\xyz\work\projects\\misc\sqlutility.cs:line 608
    at IMD.OM.SC.SCStatus.StartNSCMonitor(SqlConnection commonConnection) in d:\xyz\work\projects\scstatus.cs:line 564System.Data.SqlClient.SqlException: Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    at IMD.OM.Database.SqlUtility.Execute(SqlParameter& prmOutParam, SqlConnection cnnCommon) in d:\xyz\work\projects\\misc\sqlutility.cs:line 595

 I need to get rid of the above problem.

 Regards,
 Mithun