示例#1
0
        /// <summary>
        /// call StoredProcedure 但是參數可有output( not RefCursor )
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="hasReturnParameter"></param>
        /// <returns></returns>
        public string ExecuteStoredProcedureReturnString(string sql, List <DbParameterEx> parameters, bool hasReturnParameter, OracleDbType returnType)
        {
            string result = "";

            try {
                var           connection = CreateConnection();
                OracleCommand command    = new OracleCommand(sql, (OracleConnection)connection); //ken
                command.CommandType = CommandType.StoredProcedure;                               //ken

                if (parameters != null)
                {
                    foreach (DbParameterEx everyPara in parameters)
                    {
                        DbParameter dbParam = command.CreateParameter();

                        dbParam = TransformToDbParameter(dbParam, everyPara);

                        command.Parameters.Add(dbParam);
                    }
                }

                DbParameter dbParamReturn = null;

                if (hasReturnParameter)
                {
                    if (command is OracleCommand)
                    {
                        dbParamReturn = command.CreateParameter();
                        dbParamReturn.ParameterName = "RETURNPARAMETER";
                        dbParamReturn.Direction     = ParameterDirection.Output;
                        ((OracleParameter)dbParamReturn).OracleDbType = returnType;//David
                        command.Parameters.Add(dbParamReturn);
                    }
                }

                command.ExecuteNonQuery();
                result = command.Parameters["RETURNPARAMETER"].Value.ToString();

                return(result);
            } catch (Exception ex) {
                string errorStr = "";

                if (ex is AseException)
                {
                    AseException aseEx = ((AseException)ex);

                    foreach (AseError error in aseEx.Errors)
                    {
                        errorStr += Environment.NewLine + error.ProcName + Environment.NewLine +
                                    error.MessageNumber + Environment.NewLine +
                                    "LineNum:" + error.LineNum;
                    }
                }

                Exception exNew = new Exception(ExceptionHelper.TranformException(ex).Message + errorStr);

                throw exNew;
            }
        }
示例#2
0
        public override int GetExceptionCode(Exception dbException)
        {
            if (dbException is Sybase.Data.AseClient.AseException)
            {
                AseException ae = dbException as AseException;
                if (ae.Errors != null)
                {
                    AseError aseError = ae.Errors[0];
                    Console.WriteLine("Sybase Exception code" + aseError.MessageNumber);
                    return(aseError.MessageNumber);
                }
            }

            return(base.GetExceptionCode(dbException));
        }
示例#3
0
        /// <summary>
        /// Determines if a database exception is a transient exception and if the operation could be retried.
        /// </summary>
        /// <param name="exception">The exception to test.</param>
        /// <returns>True if the exception is transient.</returns>
        public override bool IsTransientException(Exception exception)
        {
            AseException aseException = (AseException)exception;

            return(aseException.Errors.OfType <AseError>().Any(e => e.MessageNumber == 30012));
        }
示例#4
0
        public override object Convert(object value, ConvertType convertType)
        {
            switch (convertType)
            {
            case ConvertType.NameToQueryParameter:
            case ConvertType.NameToParameter:
                return("@" + value);

            case ConvertType.NameToQueryField:
            {
                string name = value.ToString();

                if (name.Length > 0 && name[0] == '[')
                {
                    return(value);
                }
            }

                return("[" + value + "]");

            case ConvertType.NameToQueryTable:
            {
                string name = value.ToString();

                if (name.Length > 0 && name[0] == '[')
                {
                    return(value);
                }

                if (name.IndexOf('.') > 0)
                {
                    value = string.Join("].[", name.Split('.'));
                }
            }

                return("[" + value + "]");

            case ConvertType.ParameterToName:
                if (value != null)
                {
                    string str = value.ToString();
                    return(str.Length > 0 && str[0] == '@'? str.Substring(1): str);
                }

                break;

            case ConvertType.ExceptionToErrorNumber:
                if (value is AseException)
                {
                    AseException ex = (AseException)value;

                    if (ex.Errors.Count > 0)
                    {
                        return(ex.Errors[0].MessageNumber);
                    }
                }

                break;
            }

            return(value);
        }
示例#5
0
        public override object Convert(object value, ConvertType convertType)
        {
            switch (convertType)
            {
            case ConvertType.NameToQueryParameter:
            case ConvertType.NameToCommandParameter:
            case ConvertType.NameToSprocParameter:
                return("@" + value);

            case ConvertType.NameToQueryField:
            case ConvertType.NameToQueryFieldAlias:
            case ConvertType.NameToQueryTableAlias:
            {
                string name = value.ToString();

                if (name.Length > 0 && name[0] == '[')
                {
                    return(value);
                }
            }

                return("[" + value + "]");

            case ConvertType.NameToDatabase:
            case ConvertType.NameToOwner:
            case ConvertType.NameToQueryTable:
            {
                string name = value.ToString();

                if (name.Length > 0 && name[0] == '[')
                {
                    return(value);
                }

                if (name.IndexOf('.') > 0)
                {
                    value = string.Join("].[", name.Split('.'));
                }
            }

                return("[" + value + "]");

            case ConvertType.SprocParameterToName:
                if (value != null)
                {
                    string str = value.ToString();
                    return(str.Length > 0 && str[0] == '@'? str.Substring(1): str);
                }

                break;

            case ConvertType.ExceptionToErrorNumber:
                if (value is AseException)
                {
                    AseException ex = (AseException)value;

                    foreach (AseError error in ex.Errors)
                    {
                        if (error.IsError)
                        {
                            return(error.MessageNumber);
                        }
                    }

                    foreach (AseError error in ex.Errors)
                    {
                        if (error.MessageNumber != 0)
                        {
                            return(error.MessageNumber);
                        }
                    }

                    return(0);
                }

                break;

            case ConvertType.ExceptionToErrorMessage:
                if (value is AseException)
                {
                    try
                    {
                        AseException  ex = (AseException)value;
                        StringBuilder sb = new StringBuilder();

                        foreach (AseError error in ex.Errors)
                        {
                            if (error.IsError)
                            {
                                sb.AppendFormat("{0} Ln: {1}{2}",
                                                error.Message.TrimEnd('\n', '\r'), error.LineNum, Environment.NewLine);
                            }
                        }

                        foreach (AseError error in ex.Errors)
                        {
                            if (!error.IsError)
                            {
                                sb.AppendFormat("* {0}{1}", error.Message, Environment.NewLine);
                            }
                        }

                        return(sb.Length == 0 ? ex.Message : sb.ToString());
                    }
                    catch
                    {
                    }
                }

                break;
            }

            return(value);
        }
示例#6
0
        /// <summary>
        /// call StoredProcedure 但是參數可有output
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="hasReturnParameter"></param>
        /// <returns></returns>
        public DataTable ExecuteStoredProcedureEx(string sql, List <DbParameterEx> parameters, bool hasReturnParameter)
        {
            ResultData resultData = new ResultData();

            resultData.Status = ResultStatus.Fail;

            try {
                var           connection = CreateConnection();
                OracleCommand command    = new OracleCommand(sql, (OracleConnection)connection); //ken
                command.CommandType = CommandType.StoredProcedure;                               //ken

                if (parameters != null)
                {
                    foreach (DbParameterEx everyPara in parameters)
                    {
                        DbParameter dbParam = command.CreateParameter();

                        dbParam = TransformToDbParameter(dbParam, everyPara);

                        command.Parameters.Add(dbParam);
                    }
                }

                DbParameter dbParamReturn = null;

                if (hasReturnParameter)
                {
                    if (command is OracleCommand)
                    {
                        dbParamReturn = command.CreateParameter();
                        dbParamReturn.ParameterName = "RETURNPARAMETER";
                        dbParamReturn.Direction     = ParameterDirection.Output;
                        ((OracleParameter)dbParamReturn).OracleDbType = OracleDbType.RefCursor;//ken
                        command.Parameters.Add(dbParamReturn);
                    }
                }

                command.ExecuteNonQuery();                                   //ken
                OracleDataAdapter da       = new OracleDataAdapter(command); //ken
                DataTable         dtResult = new DataTable();
                da.Fill(dtResult);

                //ken,如果有多個output cursor,則需要用以下寫法
                //DataTable dtResult = (DataTable)command.Parameters[2];

                return(dtResult);
            } catch (Exception ex) {
                string errorStr = "";

                if (ex is AseException)
                {
                    AseException aseEx = ((AseException)ex);

                    foreach (AseError error in aseEx.Errors)
                    {
                        errorStr += Environment.NewLine + error.ProcName + Environment.NewLine +
                                    error.MessageNumber + Environment.NewLine +
                                    "LineNum:" + error.LineNum;
                    }
                }

                Exception exNew = new Exception(ExceptionHelper.TranformException(ex).Message + errorStr);

                throw exNew;
            }
        }
示例#7
0
        /// <summary>
        /// call StoredProcedure 但是參數可以有一個int16的output參數
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="hasReturnParameter"></param>
        /// <returns></returns>
        public ResultData ExecuteStoredProcedure(string sql, List <DbParameterEx> parameters, bool hasReturnParameter)
        {
            ResultData resultData = new ResultData();

            resultData.Status = ResultStatus.Fail;

            try {
                var connection = CreateConnection();

                using (var command = CreateCommand(sql, connection, CommandType.StoredProcedure)) {
                    if (parameters != null)
                    {
                        foreach (DbParameterEx everyPara in parameters)
                        {
                            DbParameter dbParam = command.CreateParameter();

                            dbParam = TransformToDbParameter(dbParam, everyPara);

                            command.Parameters.Add(dbParam);
                        }
                    }

                    DbParameter dbParamReturn = null;

                    if (hasReturnParameter)
                    {
                        if (command is OracleCommand)
                        {
                            dbParamReturn = command.CreateParameter();
                            dbParamReturn.ParameterName = "RETURNPARAMETER";
                            dbParamReturn.Direction     = ParameterDirection.Output;
                            dbParamReturn.DbType        = DbType.Int16;
                            command.Parameters.Add(dbParamReturn);
                        }
                        else if (command is AseCommand)
                        {
                            dbParamReturn           = command.CreateParameter();
                            dbParamReturn.Direction = ParameterDirection.ReturnValue;
                            dbParamReturn.DbType    = DbType.Int32;
                            command.Parameters.Add(dbParamReturn);
                        }
                    }

                    using (var adapter = CreateAdapter(command)) {
                        DataTable dt = new DataTable();
                        adapter.Fill(dt);
                        resultData.ReturnData = dt;

                        if (dbParamReturn != null)
                        {
                            resultData.ReturnObject = dbParamReturn.Value;
                        }
                    }

                    int returnValueInt = 0;
                    int.TryParse((resultData.ReturnObject is null) ? "0" : resultData.ReturnObject.ToString(), out returnValueInt);

                    if (returnValueInt == 0)
                    {
                        resultData.Status = ResultStatus.Success;
                    }
                    else
                    {
                        resultData.Status = ResultStatus.Fail;
                    }

                    // 如果是外部Transaction的話這裡會有值,就先不要關閉連線
                    if (!IsTransaction)
                    {
                        connection.Close();
                    }

                    return(resultData);
                }
            } catch (Exception ex) {
                string errorStr = "";

                if (ex is AseException)
                {
                    AseException aseEx = ((AseException)ex);

                    foreach (AseError error in aseEx.Errors)
                    {
                        errorStr += Environment.NewLine + error.ProcName + Environment.NewLine +
                                    error.MessageNumber + Environment.NewLine +
                                    "LineNum:" + error.LineNum;
                    }
                }

                Exception exNew = new Exception(ExceptionHelper.TranformException(ex).Message + errorStr);

                throw exNew;
            }
        }