private static int TakeStatspackSnaphot()
{
int snapshotId = -1;
String connectionString = ConfigurationManager.ConnectionStrings["STUDENT"].ConnectionString;;
using (OracleConnection con = new OracleConnection(connectionString))
{
con.Open();
using (OracleCommand cmd = con.CreateCommand())
{
cmd.CommandText = "statspack.snap";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter snapshotIdParm = new OracleParameter(":snapshot_id", OracleDbType.Decimal, ParameterDirection.ReturnValue);
cmd.Parameters.Add(snapshotIdParm);
cmd.ExecuteNonQuery();
OracleDecimal id = (OracleDecimal)snapshotIdParm.Value;
snapshotId = id.ToInt32();
}
}
return(snapshotId);
}
public int ProductBoxQty(string prodSeq)
{
try
{
ProcParam param = new ProcParam(2);
param.ProcedureName = "INFO.PRODUCT_BOX_QTY";
param.AddParamReturn(0, "ReturnValue", OracleDbType.Decimal, 100);
param.AddParamInput(1, "strNo", prodSeq);
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
OracleDecimal result = (OracleDecimal)param.ReturnValue(0);
return(result.ToInt32());
}
catch (Exception ex)
{
throw ex;
}
}
public string UpdateReturnProductCard(string qcReturnNo, string serialNo, string userid, out int totalBox)
{
totalBox = -1;
string resultMsg = string.Empty;
try
{
ProcParam procPara = new ProcParam(5);
procPara.ProcedureName = "SCANNER_RETRUN_PACK.UPD_PC_FG_RETURN";
procPara.AddParamInput(0, "strRT_NO", qcReturnNo);
procPara.AddParamInput(1, "strSERIAL_NO", serialNo);
procPara.AddParamOutput(2, "strTOTAL_BOX", OracleDbType.Decimal, 255);
procPara.AddParamOutput(3, "resultmsg", OracleDbType.Varchar2, 255);
procPara.AddParamInput(4, "strUser_id", userid);
GlobalDB.Instance.DataAc.ExecuteNonQuery(procPara);
if (GlobalDB.Instance.LastException != null)
{
throw GlobalDB.Instance.LastException;
}
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString result = (OracleString)procPara.ReturnValue(3);
OracleDecimal resultDB = (OracleDecimal)procPara.ReturnValue(2);
if (!result.IsNull)
{
resultMsg = result.Value;
totalBox = resultDB.ToInt32();
}
}
catch (Exception ex)
{
totalBox = -1;
throw ex;
}
return(resultMsg);
}
public static dynamic OracleDBTypeToNative(OracleParameter param)
{
switch (param.OracleDbTypeEx)
{
case OracleDbType.Array:
case OracleDbType.BFile:
case OracleDbType.BinaryDouble:
case OracleDbType.BinaryFloat:
case OracleDbType.Ref:
case OracleDbType.RefCursor:
case OracleDbType.XmlType:
return(param.Value);
case OracleDbType.LongRaw:
case OracleDbType.Raw:
case OracleDbType.Blob:
if (param.Value == null)
{
return(default(byte[]));
}
else
{
/*TODO: this is an extremely naive implementation, and will eat RAM if large BLOBS are used.
* Currently, I'm limiting at 10MB of data (defined in a const inside _Common.cs),
* and raising an error if this limit is exceeded. */
byte[] BlobBytes;
OracleBlob Blob = (OracleBlob)param.Value;
if (Blob.Length < Max_Blob_Size)
{
BlobBytes = new byte[Blob.Length];
Blob.Read(BlobBytes, 0, (int)Blob.Length);
return(BlobBytes);
}
else
{
throw new NotSupportedException("This function will return a maximum of " + Max_Blob_Size + " bytes to avoid excessive RAM consumption.");
}
}
//this case will probably never work, so I may as well ignore it
/*case OracleDbType.Byte:
* if(param.Value == null)
* {
* return default(byte);
* }
* else
* {
* return (byte)param.Value;
* }*/
case OracleDbType.Char:
case OracleDbType.NChar:
case OracleDbType.NVarchar2:
case OracleDbType.Varchar2:
OracleString paramValueString = (OracleString)param.Value;
if (paramValueString == null || paramValueString.IsNull)
{
return(string.Empty);
}
else
{
return(paramValueString.Value);
}
case OracleDbType.Clob:
case OracleDbType.NClob:
if (param.Value == null)
{
return(default(string));
}
else
{
return(((OracleClob)param.Value).Value);
}
case OracleDbType.Date:
OracleDate paramValueDate = (OracleDate)param.Value;
if (paramValueDate == null || paramValueDate.IsNull)
{
return(default(DateTime));
}
else
{
return(paramValueDate.Value);
}
case OracleDbType.IntervalDS:
if (param.Value == null)
{
return(default(TimeSpan));
}
else
{
return(((OracleIntervalDS)param.Value).Value);
}
case OracleDbType.IntervalYM:
if (param.Value == null)
{
return(default(TimeSpan));
}
else
{
return(((OracleIntervalYM)param.Value).Value);
}
case OracleDbType.TimeStamp:
if (param.Value == null)
{
return(default(DateTime));
}
else
{
return(((OracleTimeStamp)param.Value).Value);
}
case OracleDbType.TimeStampLTZ:
if (param.Value == null)
{
return(default(DateTime));
}
else
{
return(((OracleTimeStampLTZ)param.Value).Value);
}
case OracleDbType.TimeStampTZ:
if (param.Value == null)
{
return(default(DateTime));
}
else
{
return(((OracleTimeStampTZ)param.Value).Value);
}
case OracleDbType.Int16:
case OracleDbType.Int32:
OracleDecimal paramValueInt32 = (OracleDecimal)param.Value;
if (paramValueInt32 == null || paramValueInt32.IsNull)
{
return(default(int));
}
else
{
return(paramValueInt32.ToInt32());
}
case OracleDbType.Int64:
OracleDecimal paramValueInt64 = (OracleDecimal)param.Value;
if (paramValueInt64 == null || paramValueInt64.IsNull)
{
return(default(Int64));
}
else
{
return(paramValueInt64.ToInt64());
}
case OracleDbType.Decimal:
OracleDecimal paramValueDecimal = (OracleDecimal)param.Value;
if (paramValueDecimal == null || paramValueDecimal.IsNull)
{
return(default(decimal));
}
else
{
return(paramValueDecimal.Value);
}
case OracleDbType.Double:
case OracleDbType.Single: //we don't care internally about single.
if (param.Value == null)
{
return(default(double));
}
else
{
return(((OracleDecimal)param.Value).ToDouble());
}
default:
throw new NotImplementedException("Type not handled yet");
}
}