SqlDataAdapter object CommandTimeOut does not work in C#

Question

I am using a SqlDataAdapter to pull a result from a stored procedure that takes up to 5 minutes to execute and return the result.

I am using a

da.SelectCommand.CommandTimeout = 1800;

setting, but the timeout does not work. The code is not honoring the timeout in real. It fails earlier.

Any idea how to fix this timeout?

This is my code:

var cpdbconn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString);   

using (SqlCommand cmd = new SqlCommand()) 
{
    cmd.Connection = cpdbconnection; 
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = readStoredProcedureName;

    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
    {
        try
        {
            da.SelectCommand.CommandTimeout = 1800;
            da.Fill(dt);

            // Check datatable is null or not
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dataRow in dt.Rows)
                {
                    lstring.Add(Convert.ToString(dataRow["ServerName"]));
                }
            }

            // Add "','" in each row to convert the result to support nested query format
            InnerQryResultStr = string.Join("','", lstring.ToArray());

            if (multinestedQry != null)
            {
                combinedQry = qryName;
                qryName = multinestedQry + "('" + InnerQryResultStr + "')";
            }
            else
            {
                qryName = qryName + "('" + InnerQryResultStr + "')";
            }
        }
        catch (SqlException e)
        {
            Logger.Log(LOGTYPE.Error, String.Format("Inserting Data Failed for server {0} with Exception {1}", "DiscreteServerData", e.Message));

            if(e.Number == -2)
            {
                Logger.Log(LOGTYPE.Error, String.Format("TimeOut occurred while executing SQL query / stored procedure ", "DiscreteServerData", e.Message));
            }

            strMsg = e.Message.ToString();
            file.WriteLine(strMsg.ToString());
        }
    }
}

"The stored procedure takes up to 5 minutes to execute the result." What are you doing with your poor DB that it takes 5 minutes to execute? Normal response times are measured in milliseconds. A minor redesign to avoid those 5 minutes might be in order. — Christopher
– Christopher, Commented Nov 7, 2019 at 19:42
There is also a bunch of timeouts. CommandTimeout, Connection Timeout, Timeouts of the server on the other end, Slow Lorris detection, etc. — Christopher
– Christopher, Commented Nov 7, 2019 at 19:44
The StoredProdcuede produce the millions of record. so the execution time for SP is expected — Umapathy
– Umapathy, Commented Nov 7, 2019 at 19:50

demp · Accepted Answer · 2019-11-07 23:13:58Z

1

did you try to set the timeout on your SqlCommand? cmd.CommandTimeout = 300;

answered Nov 7, 2019 at 23:13

demp

12.8k2 gold badges25 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Umapathy Over a year ago

Adding the timeout in the connection string and the above line helped me to fix the issue

Christopher · Accepted Answer · 2019-11-07 19:59:19Z

With Database Operations, there is a whole bunch of timeouts to consider:

The Command has a timeout.

The connection has a timeout.

Every layer of the Networking part has a timeout.

The server on the other end has a timeout.

The server on the other end might have slow loris protection.

The locks and transaction might time out (because at some point, somebody else might want to work with that table too).

According to your comments, your stored procedure returns "millions of records". Wich is propably where the issue lies. That is not a useable query. That is so much data it exceeds being a mere Network or DB Problem - and possibly becomes a memory problem on the client.

It is a common mistake to retreive too much, even if I can not remember anything quite on this scale (the bigest was in the 100k's). There is no way for a user to process this kind of information, so there has to be more filtering, pagination and the like. Never do those steps in the Client. At best you transfer useless amounts of data over the network. At worst you run into timeouts and concurrency issues. Always do as much filtering, pagination, etc in the query.

You want to retreive as little data as possible overall. Bulk operations like merging, backup, etc. should generally be done in the DBMS. If you move it to the client, you only add another layer of failure and two networking ways for the data.

For a better answer, I will need more precise information of the problem.

Collectives™ on Stack Overflow

SqlDataAdapter object CommandTimeOut does not work in C#

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related