/// <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; } }
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)); }
/// <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)); }
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); }
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); }
/// <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; } }
/// <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; } }