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