Exemplo n.º 1
0
        public static DBResult DBSQL(string oracleDBConnection, string sql, bool needResult)
        {
            //Trace.TraceWrite("Execute script in DB: \n" + sql + "\n");

            DBResult DBResult = new DBResult();

            DBResult.result = String.Empty;
            DBResult.state  = Variables.SUCCESS;

            OracleConnection conn = new OracleConnection(oracleDBConnection);

            try
            {
                conn.Open();
                OracleCommand dbcmd = conn.CreateCommand();
                dbcmd.CommandText    = sql;
                dbcmd.CommandTimeout = 60;
                OracleDataReader reader = dbcmd.ExecuteReader();

                if (needResult)
                {
                    while (reader.Read())
                    {
                        DBResult.result = (string)reader["data"].ToString();
                    }
                }

                reader.Close();
                reader = null;

                //Trace.TraceWrite("Execution was OK\n\n");

                return(DBResult);
            }
            catch (OracleException ex)
            {
                //12519, 12520 - TNS refused connection
                if (ex.Number == 12519 || ex.Number == 12520)
                {
                    Thread.Sleep(1000);
                    DBResult = DBSQL(oracleDBConnection, sql, needResult);
                }
                else
                {
                    Trace.TraceWrite("Execution was FAIL: " + ex.ToString() + "\n\n");
                    DBResult.result = "Ошибка исполнения скрипта в БД\n";
                    DBResult.state  = Variables.ERROR;
                }

                return(DBResult);
            }
            catch (Exception e)
            {
                Trace.TraceWrite("Execution was FAIL: " + e.ToString() + "\n\n");
                DBResult.result = "Ошибка исполнения скрипта с БД\n";
                DBResult.state  = Variables.ERROR;
                return(DBResult);
            }
            finally
            {
                OracleConnection.ClearPool(conn);
                conn.Dispose();
                conn.Close();
                conn = null;
            }
        }