如何在Oracle中设置CommandTimeout. [英] How set CommandTimeout in Oracle.

问题描述

是否有人知道如何在Oracle中设置CommandTimeout，或者可能有其他替代方法/建议在.NET FRAMEWORK 3.5或更高版本中增加Oracle中的CommandTimeout.

我尝试过的示例:

Hi,

Is there any one knows how to set a CommandTimeout in Oracle, or may have other alternatives/sugestion to increase CommandTimeout in Oracle in .NET FRAMEWORK 3.5 or higher.

as I tried Example:

try
{
    // For SQL ---
    cmd.CommandTimeout = 900;  // For SQL
    sConn.Open();
    trx = sConn.BeginTransaction();
    cmd.Transaction = trx;
    // End SQL ---

    // For Oracle --    
    ocmd.CommandTimeout =  900;  // For Oracle -- Exception goes here...
    oConn.Open();
    otrx = oConn.BeginTransaction();
    ocmd.Transaction = otrx;
    // End Oracle --
}
catch (Exception)
{
   return "Connection to database failed.";
}

在此先感谢...

Thanks in advance...

推荐答案

大家好，

我已经找到了解决方案.参见以下代码:

Hi All,

I had already find a solution. See below code:

public string UpdateUploadedXls()
        {
            OleDbConnection oConn;
            SqlConnection sConn;
            SqlTransaction trx;
           
            //Increase the ConnectionTimeout and the CommandTimeout
            string strConn1 = ConfigurationManager.ConnectionStrings["SqlConnection"].ConnectionString.ToString();
            if (!strConn1.ToUpper().Contains("TIMEOUT"))
            {
                strConn1 = strConn1 + ";Connection Timeout=900; pooling='true';Max Pool Size=900";
            }
            string strConn2 = ConfigurationManager.ConnectionStrings["ORAConnection"].ConnectionString.ToString();
            if (!strConn2.ToUpper().Contains("TIMEOUT"))
            {
                strConn2 = strConn2 + ";Connection Timeout=900;pooling='true';Max Pool Size=900";
            }
            try
            {
                sConn = new SqlConnection(strConn1);
                sConn.Open();
                trx = sConn.BeginTransaction();
                
                oConn = new OleDbConnection(strConn2);
                oConn.Open();
                otrx = oConn.BeginTransaction();
            }
            catch (Exception)
            {
                return "Connection to database failed.";
            }
            try
            {
                #region 'For SQL'
                using (cmd = new SqlCommand())
                {
                    cmd.CommandTimeout = 900;  
                    cmd.Connection = sConn;
                    cmd.Transaction = trx;
                    StringBuilder sb1 = new StringBuilder();
                    // Some code goes here...
                    cmd.CommandText = sb1.ToString();
                    cmd.ExecuteNonQuery();
                }
                #endregion
                #region 'For Oracle'
                using (ocmd = new OleDbCommand())
                {
                    ocmd.CommandTimeout = 900;  
                    ocmd.Connection = oConn;
                    ocmd.Transaction = otrx;
                    StringBuilder sb2 = new StringBuilder();
                    // Some code goes here...
                    ocmd.CommandText = sb2.ToString();
                    ocmd.ExecuteNonQuery();
                }
                #endregion
                trx.Commit();
                otrx.Commit();  
            }
            catch (Exception)
            {
                trx.Rollback();
                otrx.Rollback(); 
                return "Uploading process aborted, Transaction were rolled back";
            }
            finally
            {
                cmd.Dispose();
                ocmd.Dispose();
                sConn.Close();
                oConn.Close();
                otrx.Dispose();
                trx.Dispose();
            }
            return string.Empty;    
        }

对于oracle，您可以在连接字符串中指定超时.

for oracle you can give timeout in connection string.

Data Source=myOracle;User Id=myUsername;Password=myPassword;Connection Timeout=60;

这篇关于如何在Oracle中设置CommandTimeout.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！