如何从代码中增加commandTimeout值 [英] how will increase commandTimeout value from code

问题描述

我的项目代码中有一个函数，例如：

I have one function in my project code like:

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;
       }

我收到错误消息，说Timeout已过期。在操作完成之前经过了超时时间或服务器没有响应。

就行了

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

object val = cmd.ExecuteScalar(); 

这个项目有一个大型数据库，我知道我无法处理我没有达到GUI的时间段。



另一个用于CommandTimeout属性的函数，例如

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

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);
               }
           }
       }

你能说出如何解决这个问题或我怎么做增加commandtimeout属性？

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

推荐答案

除非我遗漏了什么，只需设置SqlCommand.CommandTimeout [ ^ ]属性为您希望它等待的值之前它将报告超时

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与ConnectionTimeout不同。 ConnectionTimeout是在中止之前建立与数据源的连接时允许的时间量，而CommandTimeout是指针对数据执行特定命令



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

更正了您的代码：

Corrected your code:

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);
               }
           }
       }

您设置了与连接超时相同的值，即通常15秒，可能足以建立与数据库的连接，但不能用于更长的SQL语句调用。



希望这可以解决您的问题。我会通过应用程序设置来配置SqlCommand.CommandTimeout的值。



最好的问候，



-MRB

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

在下面尝试

Try below

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

ref SqlC命令cmd是关键。原始SqlCommand cmd将不对输入cmd执行任何操作，因为它只是本地副本。

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

这篇关于如何从代码中增加commandTimeout值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！