how will increase commandTimeout value from code

Question

1.30/5 (3 votes)

See more:

I have one function in my project code like:

C#

public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
       {

           SqlCommand cmd = new SqlCommand();

           PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
           object val = cmd.ExecuteScalar();
           cmd.Parameters.Clear();
           return val;
       }

I got an error saying Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
on the line

C#

object val = cmd.ExecuteScalar();

This project has a large database and I know that I can't handle time period i not reach GUI .

one another function use for CommandTimeout property like

C#

private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
       {

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

           cmd.Connection = conn;
           cmd.CommandText = cmdText;
           cmd.CommandTimeout = conn.ConnectionTimeout;

           if (trans != null)
               cmd.Transaction = trans;

           cmd.CommandType = cmdType;

           if (cmdParms != null)
           {
               foreach (SqlParameter parm in cmdParms)
               {
                   if(parm.SqlValue == null) {parm.SqlValue = DBNull.Value; }
                   cmd.Parameters.Add(parm);
               }
           }
       }

Can u tell how to solve this problem or how I could increase the commandtimeout property?

Posted 13-May-11 1:06am

rajjosh

Updated 13-May-11 1:10am

Nithin Sundar

v2

Add a Solution

Comments

Manfred Rudolf Bihy 13-May-11 7:32am

Why the repost? I already gave you the solution.

Manfred Rudolf Bihy 13-May-11 7:39am

Please explain what you mean by saying this: "This project has a large database and I know that I can't handle time period i not reach GUI"

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member Steven · Answer 1 · 2015-11-02T05:10:00

Solution 3

Try below

private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)

ref SqlC command cmd is the key. Original SqlCommand cmd will do nothing for input cmd since that is a local copy only.

Posted 2-Nov-15 5:10am

Member Steven

Updated 2-Nov-15 5:18am

v4

Comments

Richard Deeming 2-Nov-15 11:28am

This adds nothing to the other answers posted OVER FOUR YEARS AGO!

Manfred Rudolf Bihy · Answer 2 · 2011-05-13T01:38:00

Corrected your code:

C#

private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms, int commandTimeout)
       {

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

           cmd.Connection = conn;
           cmd.CommandText = cmdText;
           // Set the timeout for your SqlCommand which was given as a parameter to the procedure
           cmd.CommandTimeout = commandTimeout; // a value of 120 seconds should be good for starters

           if (trans != null)
               cmd.Transaction = trans;

           cmd.CommandType = cmdType;

           if (cmdParms != null)
           {
               foreach (SqlParameter parm in cmdParms)
               {
                   if(parm.SqlValue == null) {parm.SqlValue = DBNull.Value; }
                   cmd.Parameters.Add(parm);
               }
           }
       }

You had set the same value as the connection timeout which is usually 15 seconds and may be enough to make a connection to the database but not for a longer SQL statement call.

Hope this clears your issue. I would make the value for the SqlCommand.CommandTimeout configurable via applications settings.

Best Regards,

-MRB

Dylan Morley · Answer 3 · 2011-05-13T01:35:00

Unless I'm missing something, just set the SqlCommand.CommandTimeout[^] property to the value you want it to wait for before it will report Timeout

// Wait for 1 minute for the query to execute before timing out
cmd.CommandTimeout = 60;

// Wait for 2 minutes for the query to execute before timing out
cmd.CommandTimeout = 120;

// Wait indefinitely for the query to execute
cmd.CommandTimeout = 0;

You shouldn't be doing this...

cmd.CommandTimeout = conn.ConnectionTimeout;

CommandTimeout is different to ConnectionTimeout. ConnectionTimeout is the amount of time to allow when establishing a connection to your datasource before aborting, while CommandTimeout refers to the execution of a particular command against the datasouce

http://devpinoy.org/blogs/joeycalisay/archive/2006/08/15/CommandTimeout-vs-ConnectionTimeout.aspx[^]

how will increase commandTimeout value from code

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0