Example #1
0
        public static Exception ThrowOracleUserException(OracleException ex, string commandText)
        {
            if (ex.Number == ORACLE_WRONG_PARAMETERS)
            {
                if (m_CachedParameters.ContainsKey(commandText))
                {
                    m_CachedParameters.Remove(commandText);
                }
            }

            if (ex.Number == ORACLE_USER_HANDLED_EXCEPTION_CODE)
            {
                var match = Regex.Match(ex.Message, "<ERROR ID=([0-9]+)>([^<]*)</ERROR>");
                if (match.Success)
                {
                    var errCode    = int.Parse(match.Groups[1].Value);
                    var errMessage = match.Groups[2].Value;

                    if (!string.IsNullOrEmpty(errMessage))
                    {
                        return(ErrorUtils.CreateErrorWithSubMessage(errCode, errMessage));
                    }
                    return(ErrorUtils.CreateError(errCode));
                }
            }
            return(ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_EXECUTE_COMMAND_FAIL, ex.Message));
        }
Example #2
0
        public static DataContainer FillDataTable(string connectionString, string commandText, params ParamInfo[] values)
        {
            using (var conn = new OracleConnection(connectionString))
            {
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                    throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_OPEN_CONNECTION_FAIL, ex.Message);
                }

                try
                {
                    DataTable dt;
                    using (var comm = new OracleCommand(commandText, conn))
                    {
                        var adap = new OracleDataAdapter(comm);
                        var ds   = new DataSet();
                        comm.CommandType = CommandType.StoredProcedure;
                        AssignParameters(comm, values);

                        adap.Fill(ds);

                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];
                        }
                        else
                        {
                            dt = null;
                        }
                    }
                    return(new DataContainer {
                        DataTable = dt
                    });
                }
                catch (OracleException ex)
                {
                    throw ThrowOracleUserException(ex, commandText);
                }
                catch (FaultException)
                {
                    throw;
                }
                catch (Exception ex)
                {
                    throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_EXECUTE_COMMAND_FAIL, ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
Example #3
0
        public static void ExecuteSql2Table(string connectionString, string commandText, out DataTable resultTable, params object[] values)
        {
            using (var conn = new OracleConnection(connectionString))
            {
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                    throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_OPEN_CONNECTION_FAIL, ex.Message);
                }

                try
                {
                    using (var comm = new OracleCommand(commandText, conn))
                    {
                        var adap = new OracleDataAdapter(comm);
                        var ds   = new DataSet();
                        comm.CommandType = CommandType.Text;
                        AssignParameters(comm, values);

                        adap.Fill(ds);
                        if (ds.Tables.Count > 0)
                        {
                            resultTable = ds.Tables[0];
                        }
                        else
                        {
                            resultTable = null;
                        }
                    }
                }
                catch (OracleException ex)
                {
                    throw ThrowOracleUserException(ex, commandText);
                }
                catch (FaultException)
                {
                    throw;
                }
                catch (Exception ex)
                {
                    throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_EXECUTE_COMMAND_FAIL, ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
Example #4
0
        public static List <T> ExecuteCommandText <T>(string connectionString, string commandText, params object[] values)
            where T : class, new()
        {
            using (var conn = new OracleConnection(connectionString))
            {
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                    throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_OPEN_CONNECTION_FAIL, ex.Message);
                }

                using (var comm = new OracleCommand(commandText, conn))
                {
                    try
                    {
                        comm.BindByName  = true;
                        comm.CommandType = CommandType.Text;
                        for (int i = 0; i < values.Length; i++)
                        {
                            comm.Parameters.Add(":" + i, values[i]);
                        }

                        using (var dr = comm.ExecuteReader())
                        {
                            return(dr.ToList <T>());
                        }
                    }
                    catch (OracleException ex)
                    {
                        throw ThrowOracleUserException(ex, commandText);
                    }
                    catch (FaultException)
                    {
                        throw;
                    }
                    catch (Exception ex)
                    {
                        throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_EXECUTE_COMMAND_FAIL, ex.Message);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
Example #5
0
        public static OracleDataReader[] ExecuteReader(string connectionString, string commandText, params object[] values)
        {
            using (var conn = new OracleConnection(connectionString))
            {
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                    throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_OPEN_CONNECTION_FAIL, ex.Message);
                }

                try
                {
                    var comm = new OracleCommand(commandText, conn)
                    {
                        CommandType = CommandType.StoredProcedure
                    };
                    AssignParameters(comm, values);

                    comm.ExecuteNonQuery();

                    var readers = (from OracleParameter param in comm.Parameters
                                   where param.OracleDbType == OracleDbType.RefCursor
                                   select
                                       (param.Value as OracleDataReader) ?? ((OracleRefCursor)param.Value).GetDataReader()).ToArray();

                    return(readers);
                }
                catch (OracleException ex)
                {
                    throw ThrowOracleUserException(ex, commandText);
                }
                catch (FaultException)
                {
                    throw;
                }
                catch (Exception ex)
                {
                    throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_EXECUTE_COMMAND_FAIL, ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
Example #6
0
        public static List <T> ExecuteStoreProcedure <T>(string connectionString, string commandText, params object[] values)
            where T : class, new()
        {
            using (var conn = new OracleConnection(connectionString))
            {
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                    throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_OPEN_CONNECTION_FAIL, ex.Message);
                }

                using (var comm = new OracleCommand(commandText, conn))
                {
                    try
                    {
                        comm.CommandType = CommandType.StoredProcedure;
                        AssignParameters(comm, values);

                        using (var dr = comm.ExecuteReader())
                        {
                            return(dr.ToList <T>());
                        }
                    }
                    catch (OracleException ex)
                    {
                        throw ThrowOracleUserException(ex, commandText);
                    }
                    catch (FaultException)
                    {
                        throw;
                    }
                    catch (Exception ex)
                    {
                        throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_EXECUTE_COMMAND_FAIL, ex.Message);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
Example #7
0
        public static void ExecuteCommandText(string connectionString, string commandText, params object[] values)
        {
            using (var conn = new OracleConnection(connectionString))
            {
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                    throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_OPEN_CONNECTION_FAIL, ex.Message);
                }

                using (var comm = new OracleCommand(commandText, conn))
                {
                    try
                    {
                        comm.CommandType = CommandType.Text;
                        for (int i = 0; i < values.Length; i++)
                        {
                            comm.Parameters.Add(":" + i, values[i]);
                        }

                        comm.ExecuteNonQuery();
                    }
                    catch (OracleException ex)
                    {
                        throw ThrowOracleUserException(ex, commandText);
                    }
                    catch (FaultException)
                    {
                        throw;
                    }
                    catch (Exception ex)
                    {
                        throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_EXECUTE_COMMAND_FAIL, ex.Message);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
Example #8
0
        public static void FillDataSet(string connectionString, string commandText, out DataSet ds, params object[] values)
        {
            using (var conn = new OracleConnection(connectionString))
            {
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                    throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_OPEN_CONNECTION_FAIL, ex.Message);
                }

                try
                {
                    using (var comm = new OracleCommand(commandText, conn))
                    {
                        comm.CommandType = CommandType.StoredProcedure;
                        AssignParameters(comm, values);
                        ds = new DataSet();
                        var adap = new OracleDataAdapter(comm);
                        adap.Fill(ds);
                    }
                }
                catch (OracleException ex)
                {
                    throw ThrowOracleUserException(ex, commandText);
                }
                catch (FaultException)
                {
                    throw;
                }
                catch (Exception ex)
                {
                    throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_EXECUTE_COMMAND_FAIL, ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
Example #9
0
        public static void ExecuteStoreProcedure(string connectionString, string commandText, params object[] values)
        {
            using (var conn = new OracleConnection(connectionString))
            {
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                    throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_OPEN_CONNECTION_FAIL, ex.Message);
                }

                try
                {
                    var comm = new OracleCommand(commandText, conn)
                    {
                        CommandType = CommandType.StoredProcedure
                    };
                    AssignParameters(comm, values);

                    comm.ExecuteNonQuery();
                }
                catch (OracleException ex)
                {
                    throw ThrowOracleUserException(ex, commandText);
                }
                catch (FaultException)
                {
                    throw;
                }
                catch (Exception ex)
                {
                    throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_EXECUTE_COMMAND_FAIL, ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
Example #10
0
        public static void DiscoveryParameters(OracleCommand comm)
        {
            try
            {
                // discovery parameter
                var cachedKey = comm.CommandText;
                if (m_CachedParameters.ContainsKey(cachedKey))
                {
                    var source = m_CachedParameters[cachedKey];
                    foreach (var param in source)
                    {
                        comm.Parameters.Add((OracleParameter)param.Clone());
                    }
                }
                else
                {
                    lock (m_CachedParameters)
                    {
                        comm.CommandText += "--" + (new Random().Next());
                        OracleCommandBuilder.DeriveParameters(comm);
                        comm.CommandText = cachedKey;

                        var source = new OracleParameter[comm.Parameters.Count];

                        for (var i = 0; i < comm.Parameters.Count; i++)
                        {
                            source[i] = (OracleParameter)comm.Parameters[i].Clone();
                        }

                        m_CachedParameters[cachedKey] = source;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_DISCOVERY_PARAMS_FAIL, ex.Message);
            }
        }
Example #11
0
        public static List <T> ExecuteStoreProcedureGeneric <T>(string connectionString, string commandText, params object[] values)
        {
            using (var conn = new OracleConnection(connectionString))
            {
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                    throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_OPEN_CONNECTION_FAIL, ex.Message);
                }

                try
                {
                    var comm = new OracleCommand(commandText, conn)
                    {
                        CommandType = CommandType.StoredProcedure
                    };
                    AssignParameters(comm, values);

                    using (var dr = comm.ExecuteReader())
                    {
                        var list = new List <T>();

                        if (comm.Parameters.Contains("RETURN_VALUE"))
                        {
                            var value     = comm.Parameters["RETURN_VALUE"].Value;
                            var valueType = value.GetType();

                            if (valueType == typeof(OracleDecimal))
                            {
                                value = ((OracleDecimal)value).Value;
                            }
                            else if (valueType == typeof(OracleDate))
                            {
                                value = ((OracleDecimal)value).Value;
                            }
                            else if (valueType == typeof(OracleString))
                            {
                                value = ((OracleString)value).Value;
                            }

                            list.Add((T)Convert.ChangeType(value, typeof(T)));
                            return(list);
                        }

                        while (dr.Read())
                        {
                            list.Add((T)Convert.ChangeType(dr.GetValue(0), typeof(T)));
                        }
                        return(list);
                    }
                }
                catch (OracleException ex)
                {
                    throw ThrowOracleUserException(ex, commandText);
                }
                catch (FaultException)
                {
                    throw;
                }
                catch (Exception ex)
                {
                    throw ErrorUtils.CreateErrorWithSubMessage(ERR_SQL.ERR_SQL_EXECUTE_COMMAND_FAIL, ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }