SqlCommand timeout in C# MMO application

Question

I have a C# project that is working with TCP socket in an asynchronous way.

Every request comes from client and ask question from SQL Server stored procedure, opens and closes a SQL connection after ending of question.

I've used this code:

using (var con = new SqlConnection(setting.ConnectionString))
{
    try
    {
        //some codes (edited)
        SqlCommand command = new SqlCommand(con);
        command.CommandText = "procedurename1";
        command.CommandType = CommandType.StoredProcedure; 
        command.Parameters.Add(new SqlParameter("@name", sb.ToString()));
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        try
                {
                    adapter.Fill(dataSet);
                }
                catch (Exception ex)
                {
                    con.Close();
                    con.Dispose();
                    throw ex;
                }
                finally {
                    con.Close();
                    con.Dispose();
                }
    }
    catch(Exception ex)
    {}
    finally
    {
        con.close();
        con.dispose();
    }
}

I've used

netstat -a -n | find /c "1433"

to count SQL connections open and close.

Problem is SQL connections count increases and it rarely decreases and count down.

Main problem, is when my program works under lots of requests about 30 minutes, I get

SqlCommand timeout error (default 30 seconds passed)

and after restarting my C# program, the SqlCommand timeout will be gone.

Is this a problem of my program or SQL Server side?

Remember it always calls a stored procedure in SQL Server, not executing query directly.

main method:

public void main()
{
Task.Factory.StartNew(() =>
                    {                        
                        allDone.Reset();
                        mySocket.AcceptAsync(e);
                        allDone.WaitOne();                       
                    });
}

public void e_Completed(object sender, SocketAsyncEventArgs e)
        {
            var socket = (Socket)sender;
            ThreadPool.QueueUserWorkItem(HandleTcpRequest, e.AcceptSocket);
            e.AcceptSocket = null;
            socket.AcceptAsync(e);
        }

public void HandleTcpRequest(object state)
{
    //do some code and connection to SQL server
    DLL.Request httprequest = new DLL.Request(dataSet.Tables[0], fileDt);
    DLL.IHttpContext _context = new DLL.HttpContext(httprequest);
    _context.GetResults();
}

Answer 1

I'm going to take a long-shot based on the way the limited Sql-related code we can see is written since we can't see "//some codes".

I'm going to guess that some of the disposable things like SqlCommand, DataReader, SqlDataAdapter, TransactionScope, etc are not in 'using' blocks, so are holding resources open on the database.

It may also be worth raising the possibility that this kind of problem could be in the code shown in the question or any other program accessing that database, including your own applications and SSMS (e.g. if a developer has an uncommitted transaction running in a window).

P.S. I would suggest deleting everything in the using block except the "//some codes" part.

UPDATE after more code was added

Here is your code after correction. this will ensure that the resources are disposed, which will prevent the leaking resources that are probably causing your problem.

    using (var con = new SqlConnection(setting.ConnectionString))
    {
        //some codes (edited)
        using (SqlCommand command = new SqlCommand(con))
        {
            command.CommandText = "procedurename1";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@name", sb.ToString()));
            using (var adapter = new SqlDataAdapter(command))
            {
                adapter.Fill(dataSet);
            }
        }
    }

P.S. don't ever write "throw ex;" from inside a catch ever again. It causes the stack trace to be lost - just use "throw;".

Answer 2

Main problem, is when my program works under lots of requests about 30 minutes,

To isolate the root problem of the time-out, I suggest testing the sql query of the stored procedure independent of TCP socket calls for 30 minutes and log the time-out exception details for inspection

Run the following query within 30 minutes to simulate your working environment:

     public void RunQuery()
     {
        using (var con = new SqlConnection(setting.ConnectionString))
        {
            try
            {
                //some codes
            }
            catch(SqlException ex)
            {
            //test for timeout
                if (ex.Number == -2) {
                Console.WriteLine ("Timeout occurred");
                // log ex details for more inspection
                }
            }

        }

    }

Read How to handle the CommandTimeout properly?

As you use async calls, I suggest you to try to use Asynchronous Database Calls With Task-based Asynchronous Programming Model (TAP)