Click here to Skip to main content
15,562,223 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

C#
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...
Posted
Updated 7-Dec-11 21:32pm
v2

Hi All,

I had already find a solution. See below code:

C#
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;    
        }
 
Share this answer
 
v3
for oracle you can give timeout in connection string.

C#
Data Source=myOracle;User Id=myUsername;Password=myPassword;Connection Timeout=60;
 
Share this answer
 
Comments
Al Moje 8-Dec-11 2:39am    
Hi,
Thanks for your answer but I am asking for a CommandTimeout not ConnectionTimeout like in SQL it works..

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900