/// <summary>
/// Add a parameter to the Sql Command
/// </summary>
/// <param name="name">Parameter name</param>
/// <param name="value">Value for the parameter</param>
/// <param name="dbType">Type of parameter in SQL Server</param>
/// <param name="typeName">Name of a user-defined type, if using one. Otherwise, leave empty.</param>
/// <param name="direction">Parameter direction (default is input)</param>
public void AddParameter(string name, object value, OracleDbType dbType, string typeName = "", ParameterDirection direction = ParameterDirection.Input)
{
OracleParameter parameter = new OracleParameter();
parameter.UdtTypeName = typeName;
parameter.Direction = direction;
parameter.ParameterName = name;
parameter.OracleDbType = dbType;
parameter.Value = value;
if (!Parameters.Contains(parameter))
{
Parameters.Add(parameter);
}
}
public Parameter(string name, Type type, object value)
{
this.Name = name;
this.Type = DbTypeMap[type];
this.Value = value;
}
/// <summary>
/// Initializes a new instance of the <see cref="Field"/> class.
/// </summary>
/// <param name="propertyName">
/// The property name.
/// </param>
/// <param name="columnName">
/// The column name.
/// </param>
/// <param name="oracleDbType">
/// The oracle db type.
/// </param>
/// <param name="primary">
/// The primary.
/// </param>
public Field(string propertyName, string columnName, OracleDbType oracleDbType, bool primary)
{
this.PropertyName = propertyName;
this.ColumnName = columnName;
this.OracleDbType = oracleDbType;
this.Primary = primary;
}
public DbParameter AddParameter(string ParameterName, OracleDbType dbType, int size, object value, ParameterDirection direction)
{
OracleParameter param = new OracleParameter(ParameterName, dbType, size);
param.Direction = direction;
param.Value = value;
Command.Parameters.Add(param);
return param;
}
/// <summary>
/// Binds a parameter to the command with the given value and type.
/// </summary>
/// <param name="parameterName"></param>
/// <param name="value"></param>
/// <param name="type"></param>
public void AddParameter(string parameterName, object value, OracleDbType type)
{
OracleParameter parameter = new OracleParameter();
parameter.ParameterName = parameterName;
parameter.Value = value;
// Fix later
//parameter.OracleDbType = type;
Command.Parameters.Add(parameter);
}
public static Type ToNetType(OracleDbType oracleDbType)
{
foreach (TypeMapping entry in _DbTypeMappings)
{
if (entry.OracleDbType == oracleDbType)
{
return entry.Type;
}
}
throw new ApplicationException("Referenced an unsupported OracleDbType {" + Enum.GetName(typeof(OracleDbType), oracleDbType) + "}");
}
public void AddParameter(string pName, OracleDbType pOracleDbType, int pSize, object pVal, ParameterDirection pDirection)
{
try
{
if(objCommand != null)
{
if (pDirection == ParameterDirection.Input)
objCommand.Parameters.Add(pName, pOracleDbType, pSize, (pVal == null) ? DBNull.Value : pVal, pDirection);
else
objCommand.Parameters.Add(pName, pOracleDbType).Direction = ParameterDirection.Output;
}
}
catch (Exception ex)
{
throw ex;
}
}
internal static int GetDbTypeSize(OracleDbType type)
{
switch (type)
{
case OracleDbType.Char:
case OracleDbType.Byte:
return 1;
case OracleDbType.Int16:
return 2;
case OracleDbType.Int32:
return 4;
case OracleDbType.Int64:
return 8;
case OracleDbType.Varchar2:
return 2048;
default:
return 0;
}
}
public OracleParameter AddOutputParameter(string name, OracleDbType type, int size)
{
OracleParameter prm = new OracleParameter();
prm.Direction = ParameterDirection.Output;
prm.ParameterName = name;
prm.OracleDbType = type;
prm.Size = size;
_parameters.Add(prm);
return prm;
}
public static void AddOutVariable(OracleCommand Command, OracleDbType VarType)
{
OracleParameter Variable = new OracleParameter();
Variable.OracleDbType = VarType;
Variable.Direction = ParameterDirection.Output;
Command.Parameters.Add(Variable);
}
public EventParameter(string name, OracleDbType dbType)
: this(name, dbType, 0)
{
}
/// <summary>
/// Insert an array of rows into Mobius warehouse table
/// </summary>
/// <param name="voList"></param>
/// <returns></returns>
public bool Insert(
List <AnnotationVo> voList)
{
// This insert uses the APPEND_VALUES hint if there are 10 or more rows inserted.
// This means that inserts will go into new blocks and be physically associated with each other
// which will result in significantly faster retrieval for individual annotation tables since fewer
// disk reads will be needed. Otherwise tables that are reloaded multiple times will tend to be spread over
// a larger number of reused blocks resulting in more reads and slower performance.
//
// From a web article:
// Each of the following is a benefit in some cases
// Each of the following is a disaster in other cases
// Append does a direct path load (if it can, it is not a promise, you are requesting and we may or may not do it for you - silently)
// if you direct path load, the transaction that did the direct path load CANNOT query that segment -but other transactions can, they just cannot see the newly loaded data.
// if you direct path load, you never use any existing free space, it always writes above the high water mark.
// if you direct path load, we bypass UNDO on the table -only the table -modifications
// if you direct path load, you'll maintain indexes - we build mini indexes on the newly loaded data and merge them into the 'real' indexes in bulk. A direct path load of large amounts of data will maintain indexes very efficiently.
// if you direct path load you can bypass redo on the TABLE in archivelog mode, if the database is set up to allow nologging and you have the segment set to nologging
// direct path loading bypasses the buffer cache, you write directly to the datafiles.
// direct path loading is only appropriate for the first load of a segment or an increment load of lots of data - or an increment load into a table that never has any deletes(so there is no free space to consider)
//
// transactional systems - you probably won't use it.
// warehouses - a tool you'll use a lot
AnnotationVo vo;
if (voList == null || voList.Count == 0)
{
return(false);
}
//CheckForDuplicateInsert(voList); // debug
try
{
string sql =
"insert /*+ APPEND_VALUES */ into " + TableName + " " +
"(rslt_id, " +
"rslt_grp_id, " +
"ext_cmpnd_id_txt, " +
"ext_cmpnd_id_nbr, " +
"src_db_id, " +
"mthd_vrsn_id, " +
"rslt_typ_id, " +
"rslt_val_prfx_txt, " +
"rslt_val_nbr, " +
"uom_id, " +
"rslt_val_txt, " +
"rslt_val_dt, " +
"cmnt_txt, " +
"dc_lnk, " +
"chng_op_cd, " +
"chng_usr_id, " +
"sts_id, " +
"sts_dt, " +
"crt_dt, " +
"updt_dt) " +
"values (nvl(:0," + SeqName + ".nextval)" + // if rslt_id not null use it otherwise call nextval locally
",:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,1,sysdate(),sysdate(),sysdate())";
if (voList.Count < 10) // require minimum number of rows to use APPEND_VALUESd
{
sql = Lex.Replace(sql, "APPEND_VALUES", "");
}
OracleDbType[] pa = new OracleDbType[16];
pa[0] = OracleDbType.Long; // rslt_id
pa[1] = OracleDbType.Long; // rslt_grp_id
pa[2] = OracleDbType.Varchar2;
pa[3] = OracleDbType.Int32;
pa[4] = OracleDbType.Int32;
pa[5] = OracleDbType.Int32; // mthd_vrsn_id
pa[6] = OracleDbType.Long; // rslt_typ_id
pa[7] = OracleDbType.Varchar2;
pa[8] = OracleDbType.Double;
pa[9] = OracleDbType.Int32;
pa[10] = OracleDbType.Varchar2;
pa[11] = OracleDbType.Date;
pa[12] = OracleDbType.Varchar2;
pa[13] = OracleDbType.Varchar2;
pa[14] = OracleDbType.Varchar2;
pa[15] = OracleDbType.Varchar2;
DbCmd.Prepare(sql, pa);
int cnt = voList.Count;
object[] p = new object[16]; // parameter values
object[] rslt_idA = new object[cnt]; p[0] = rslt_idA; // allocate arrays to hold values
long[] rslt_grp_idA = new long[cnt]; p[1] = rslt_grp_idA;
string[] ext_cmpnd_id_txtA = new string[cnt]; p[2] = ext_cmpnd_id_txtA;
int[] ext_cmpnd_id_nbrA = new int[cnt]; p[3] = ext_cmpnd_id_nbrA;
int[] src_db_idA = new int[cnt]; p[4] = src_db_idA;
int[] mthd_vrsn_idA = new int[cnt]; p[5] = mthd_vrsn_idA;
long[] rslt_typ_idA = new long[cnt]; p[6] = rslt_typ_idA;
string[] rslt_val_prfx_txtA = new string[cnt]; p[7] = rslt_val_prfx_txtA;
OracleDecimal[] rslt_val_nbrA = new OracleDecimal[cnt]; p[8] = rslt_val_nbrA;
int[] uom_idA = new int[cnt]; p[9] = uom_idA;
string[] rslt_val_txtA = new string[cnt]; p[10] = rslt_val_txtA;
OracleDate[] rslt_val_dtA = new OracleDate[cnt]; p[11] = rslt_val_dtA;
string[] cmnt_txtA = new string[cnt]; p[12] = cmnt_txtA;
string[] dc_lnkA = new string[cnt]; p[13] = dc_lnkA;
string[] chng_op_cdA = new string[cnt]; p[14] = chng_op_cdA;
string[] chng_usr_idA = new string[cnt]; p[15] = chng_usr_idA;
for (int li = 0; li < cnt; li++)
{ // copy values to parameter arrays
vo = voList[li];
try { vo.ext_cmpnd_id_nbr = Int32.Parse(vo.ext_cmpnd_id_txt); } // try to store text ext_cmpnd_id_txt value also as integer in ext_cmpnd_id_nbr
catch (Exception ex) { }
if (vo.rslt_id <= 0 && voList.Count == 1) // assign seq no if not already assigned if inserting only 1 row
{
vo.rslt_id = SequenceDao.NextValLong(SeqName);
}
if (vo.rslt_id > 0)
{
rslt_idA[li] = vo.rslt_id; // is result_id defined?
}
else
{
rslt_idA[li] = DBNull.Value; // if not defined send as null value so sequence is used in nvl function in insert
}
rslt_grp_idA[li] = vo.rslt_grp_id;
string txt = vo.ext_cmpnd_id_txt;
if (txt != null && txt.Length > 32) // truncate to 32 chars if needed
{
txt = txt.Substring(0, 32);
}
ext_cmpnd_id_txtA[li] = txt;
ext_cmpnd_id_nbrA[li] = vo.ext_cmpnd_id_nbr;
src_db_idA[li] = vo.src_db_id;
mthd_vrsn_idA[li] = vo.mthd_vrsn_id;
rslt_typ_idA[li] = vo.rslt_typ_id;
rslt_val_prfx_txtA[li] = vo.rslt_val_prfx_txt;
if (vo.rslt_val_nbr != NullValue.NullNumber)
{
rslt_val_nbrA[li] = new OracleDecimal(vo.rslt_val_nbr);
}
uom_idA[li] = vo.uom_id;
rslt_val_txtA[li] = vo.rslt_val_txt;
if (rslt_val_txtA[li] != null && rslt_val_txtA[li].Length > 3900) // avoid overflow error, must leave space for catenating
{
rslt_val_txtA[li] = rslt_val_txtA[li].Substring(0, 3897) + "..."; // link info & keeping total <= 4000
}
if (vo.rslt_val_txt.Contains(","))
{
vo.rslt_val_txt = vo.rslt_val_txt; // debug
}
if (vo.rslt_val_dt != DateTime.MinValue) // non-null date?
{
rslt_val_dtA[li] = new OracleDate(vo.rslt_val_dt);
}
cmnt_txtA[li] = vo.cmnt_txt;
dc_lnkA[li] = vo.dc_lnk;
chng_op_cdA[li] = vo.chng_op_cd;
chng_usr_idA[li] = vo.chng_usr_id;
}
int t0 = TimeOfDay.Milliseconds();
DbCmd.OracleCmd.ArrayBindCount = cnt;
int count = DbCmd.ExecuteNonReader(p); // do insert
t0 = TimeOfDay.Milliseconds() - t0;
// DebugLog.Message("MobiusDwDao insert rows, count = " + count.ToString() + ", Time(ms) = " + t0.ToString());
return(true);
}
catch (Exception e)
{
DebugLog.Message("MobiusDwDao.Insert - Error inserting into " + TableName + ": " + e.Message);
return(false);
}
}
public DataSet ExecuteFunction(string query, string outName, OracleDbType returnType, int?returnSize = null)
{
using (OracleConnection cnx = new OracleConnection(ConfigurationManager.ConnectionStrings["Banner"].ConnectionString))
{
OracleCommand cmd = new OracleCommand(query, cnx);
DataSet dataSet = new DataSet();
cmd.CommandText = query;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
using (OracleDataAdapter dataAdapter = new OracleDataAdapter())
{
try
{
cnx.Open();
OracleParameter salida = new OracleParameter(outName,
returnType, DBNull.Value,
ParameterDirection.ReturnValue);
if (returnSize != null)
{
salida.Size = (int)returnSize;
}
cmd.Parameters.Add(salida);
foreach (OracleParameter parameter in parameterList)
{
cmd.Parameters.Add(parameter);
}
dataAdapter.SelectCommand = cmd;
cmd.ExecuteNonQuery();
dataSet.Tables.Add("parameters");
if (returnType == OracleDbType.RefCursor)
{
dataAdapter.AcceptChangesDuringFill = true;
dataAdapter.Fill(dataSet, outName, (OracleRefCursor)(cmd.Parameters[outName].Value));
}
else
{
dataSet.Tables["parameters"].Columns.Add(outName);
dataSet.Tables["parameters"].Rows.Add(cmd.Parameters[outName].Value);
}
if (outParameters.Count() > 0)
{
foreach (String name in outParameters.Keys)
{
if (dbTypeList[name] == OracleDbType.RefCursor)
{
dataAdapter.AcceptChangesDuringFill = true;
dataAdapter.Fill(dataSet, name, (OracleRefCursor)(cmd.Parameters[name].Value));
}
else
{
dataSet.Tables["parameters"].Columns.Add(name);
dataSet.Tables["parameters"].Rows.Add(cmd.Parameters[name].Value);
}
}
}
//dataSet.Tables.Add(dataTableTemp);
this.result = dataSet.Tables["parameters"];
}
catch (Exception ex)
{
throw new HttpException((int)HttpStatusCode.InternalServerError, ex.Message);
}
finally
{
cnx.Close();
}
return(dataSet);
}
}
}
// ReSharper restore UnusedMember.Global
/// <summary>
/// Overriding type constructor for any direction parameters
/// </summary>
/// <param name="pName"></param>
/// <param name="oDb"></param>
/// <param name="val"></param>
/// <param name="pDir"></param>
/// <param name="sZ"></param>
/// <param name="preserveType"></param>
// ReSharper disable UnusedMember.Global
public OracleProcParam(string pName, OracleDbType oDb, object val, ParameterDirection pDir, int sZ, bool preserveType)
{
if (oDb == OracleDbType.Clob)
{
this.paramIsClob = true;
}
this.Direction = pDir;
this.dataTypeName = DataTypeConstants.PawnDataType.WHOLENUMBER;
this.Name = pName;
this.parameterValues = new List<object>(1);
this.OracleType = oDb;
if (this.Direction == ParameterDirection.Input && val != null)
{
if (preserveType)
{
this.AddValue(val);
}
else
{
this.AddValue(val.ToString());
}
}
else if (val != null)
{
this.AddValue(DBNull.Value);
}
this.arraySize = sZ;
}
/// <summary>
/// Преобразует строку в тип OracleDbType
/// </summary>
/// <param name="value">Значение</param>
/// <param name="oraType">Тип</param>
/// <returns></returns>
internal static Object StrAsOraValue(String value, OracleDbType oraType) {
Object rslt = null;
try {
var culture = new CultureInfo(CultureInfo.CurrentCulture.Name, true) {
NumberFormat = { NumberDecimalSeparator = "." }
};
switch (oraType) {
case OracleDbType.Varchar2:
case OracleDbType.Clob:
rslt = value;
break;
case OracleDbType.Decimal:
rslt = Double.Parse(value.Replace(",", ".").Replace(" ", ""), culture);
break;
case OracleDbType.Date:
rslt = DateTimeParser.Instance.ParsDateTime(value);
break;
case OracleDbType.Blob:
rslt = Convert.FromBase64String(value);
break;
}
} catch (Exception ex) {
throw new EBioException(
"При приведении значения \"" + value + "\" к типу " +
enumHelper.NameOfValue(oraType) + " ", ex);
}
return rslt;
}
/// <summary>
/// 添加输出(OUT)参数
/// </summary>
/// <param name="paramName">参数名</param>
/// <param name="dbType">数据类型</param>
/// <param name="size">类型长度（可选：默认为0）</param>
/// <returns>配置好的Oracle参数</returns>
public static OracleParameter AddOutputParameter(string paramName, OracleDbType dbType, int size = 0)
{
return(AddParameter(paramName, DBNull.Value, dbType, size, ParameterDirection.Output));
}
/// <summary>
/// 添加输入输出(INOUT)参数
/// </summary>
/// <param name="paramName">参数名</param>
/// <param name="value">输入值</param>
/// <param name="dbType">数据类型</param>
/// <param name="size">类型长度（可选：默认为0）</param>
/// <returns></returns>
public static OracleParameter AddInOutputParameter(string paramName, object value, OracleDbType dbType, int size = 0)
{
return(AddParameter(paramName, value, dbType, size, ParameterDirection.InputOutput));
}
/// <summary>
/// Add
/// </summary>
/// <param name="name"></param>
/// <param name="value"></param>
/// <param name="oracleDbType"></param>
public void Add(string name, object value, OracleDbType oracleDbType)
{
this.Add(name, oracleDbType, ParameterDirection.Input, value);
}
private void MapOracleIdentityField(DataTable dt, out bool isIdentity, out string identityField, out OracleDbType identityType)
{
DataColumn identityColumn = dt.Columns.Cast <DataColumn>().FirstOrDefault(x => x.AutoIncrement);
isIdentity = identityColumn != null;
identityField = identityColumn != null ? identityColumn.ColumnName : string.Empty;
identityType = OracleDbType.Int32;
if (identityColumn != null)
{
if (identityColumn.DataType == typeof(int))
{
identityType = OracleDbType.Int32;
}
else if (identityColumn.DataType == typeof(long))
{
identityType = OracleDbType.Int64;
}
else if (identityColumn.DataType == typeof(decimal))
{
identityType = OracleDbType.Decimal;
}
}
}
// ----------------------------------------------------------------------------------
public static OracleParameter OraOutParamFactory(string parameterName, OracleDbType parmType)
{
return(OraParamFactory(parameterName, parmType, ParameterDirection.Output, null));
}
/// <summary>
/// Sync the Mobius CorpMoltable replicate used to retrieve Smiles
/// Syntax: UpdateCorpDbMoltableMx [ ByDateRange | ByCorpIdRange | LoadMissing | <singleCorpId>]
/// </summary>
/// <returns></returns>
public static string UpdateCorpDbMoltableMx(
string args)
{
DateTime moleculeDateTime = DateTime.MinValue;
double mw;
string msg = "", sql = "", maxCorpIdSql, mf = "", chime = "", smiles = "", checkPointDate = "", helm = "", sequence = "", svg = "";
object[][] pva = null;
int pvaCount = 0, CorpId, lowCorpId = 0, highCorpId = 0, srcMaxCorpId = 0;
int SelectChunkSize = 20; // small chunks
int InsertBufferSize = 10;
//int SelectChunkSize = 100000; // big chunks
//int InsertBufferSize = 1000;
// Select data from corp_moltable by CorpId range
const string SelectByCorpIdRange = @"
SELECT
m.corp_nbr,
chime(m.ctab),
m.molformula,
m.molweight,
null molsmiles,
s.helm_txt,
s.sequence_txt,
m.molecule_date
FROM
corp_owner.corp_moltable m,
corp_owner.corp_substance s
where
m.corp_nbr > 0
and s.corp_nbr = m.corp_nbr
and (s.status_code is null or s.status_code = 'A')
ORDER BY corp_nbr";
// Select data from corp_moltable by date range comparing to corp_moltable_mx
const string SelectByDateRange = @"
select
m.corp_nbr,
chime(m.ctab),
m.molformula,
m.molweight,
null molsmiles,
s.helm_txt,
s.sequence_txt,
m.molecule_date,
m2.molecule_date
from
corp_owner.corp_moltable m,
corp_owner.corp_substance s,
corp_moltable_mx m2
where
m.molecule_date > to_date('1-jan-1900 000000','DD-MON-YYYY HH24MISS')
and s.corp_nbr = M.CORP_NBR
and (s.status_code is null or s.status_code = 'A')
and m2.corp_nbr (+) = m.corp_nbr
and m2.molecule_date (+) != m.molecule_date
order by m.molecule_date" ;
// Select for missing smiles strings, ex: Update CorpDbMoltableMx LoadMissing mx.molecule_date > '1-jan-2014'
const string SelectMissingSmilesFix = @"
select /* check for CorpIds in corp_moltable not in corp_moltable_mx */
corp_nbr,
chime(ctab),
molformula,
molweight,
null molsmiles,
helm_txt,
sequence_txt,
molecule_date
from
(
select
m.*,
s.helm_txt,
s.sequence_txt,
mx.molsmiles molsmiles_mx
from
corp_owner.corp_moltable m,
corp_owner.corp_substance s,
corp_moltable_mx mx
where
s.corp_nbr = M.CORP_NBR
and (s.status_code is null or s.status_code = 'A')
and mx.corp_nbr (+) = m.corp_nbr
and 1=1 /* condition to substitute */
) m
where molsmiles_mx is null /* extra condition */
order by corp_nbr" ;
// Insert missing helm info
const string SelectMissingHelmFix = @"
select /* check for CorpIds in corp_moltable not in corp_moltable_mx */
corp_nbr,
chime(ctab),
molformula,
molweight,
null molsmiles,
helm_txt,
sequence_txt,
molecule_date
from
(
select
m.*,
s.helm_txt,
s.sequence_txt,
mx.molsmiles molsmiles_mx
from
corp_owner.corp_moltable m,
corp_owner.corp_substance s,
corp_moltable_mx mx
where
s.corp_nbr = M.CORP_NBR
and (s.status_code is null or s.status_code = 'A')
and mx.corp_nbr (+) = m.corp_nbr
and 1=1 /* condition to substitute */
) m
where length(helm_txt) > 0 /* extra condition */
order by corp_nbr" ;
// Secondary "large" structure table (~5k mols)
const string SelectLargeMols = @"
select
corp_nbr,
to_clob(molstructure),
to_clob(molformula),
molweight,
molsmiles,
null helm_txt,
null sequence_txt,
molecule_date
from
(select
corp_srl_nbr corp_nbr,
'CompoundId=' || corp_srl_nbr molstructure,
null ctab,
mlclr_frml_txt molformula,
mlclr_wgt molweight,
null molsmiles,
null molecule_date
from rdm_owner.rdm_sbstnc
where rdw_src_cd = 'LRG'" ;
// Insert statement
const string InsertSql = @"
insert into mbs_owner.corp_moltable_mx (
corp_nbr,
molstructure,
molformula,
molweight,
molsmiles,
molecule_date)
values (:0, :1, :2, :3, :4, :5)" ;
// Build select sql
bool byDateRange = false, byCorpIdRange = false, missingFix = true, deleteExisting = true;
string missingFixCriteria = "";
if (Lex.IsUndefined(args) || Lex.Eq(args, "ByDateRange"))
{
byDateRange = true;
}
else if (Lex.Eq(args, "ByCorpIdRange"))
{
byCorpIdRange = true;
Progress.Show("Getting range of CorpIds to insert...");
maxCorpIdSql = "select max(corp_nbr) from corp_owner.corp_moltable"; // get highest CorpId in source db
srcMaxCorpId = SelectSingleValueDao.SelectInt(maxCorpIdSql);
if (srcMaxCorpId < 0)
{
srcMaxCorpId = 0;
}
maxCorpIdSql = "select max(corp_nbr) from mbs_owner.corp_moltable_mx"; // get highest CorpId in dest db
highCorpId = SelectSingleValueDao.SelectInt(maxCorpIdSql);
if (highCorpId < 0)
{
highCorpId = 0;
}
}
else if (Lex.StartsWith(args, "LoadMissing"))
{
missingFix = true;
if (args.Contains(" "))
{
missingFixCriteria = args.Substring(10).Trim();
}
}
else if (int.TryParse(args, out srcMaxCorpId)) // single CorpId
{
byCorpIdRange = true;
highCorpId = srcMaxCorpId - 1; // say 1 less is the max we have
}
else
{
return("Syntax: UpdateCorpDbMoltableMx [ ByDateRange | ByCorpIdRange | LoadMissing | <singleCorpId>]");
}
Log("UpdateCorpDbMoltableMx started: " + args);
int readCount = 0, insCount = 0, insertCount = 0, updateCount = 0, undefinedStructures = 0, smilesSuccess = 0, smilesFails = 0, helmStructures = 0;
List <string> CorpIdList = new List <string>();
for (int chunk = 1; ; chunk++) // loop over chunks
{
if (byDateRange) // single chunk
{
if (chunk > 1)
{
break; // break 2nd time through
}
checkPointDate = UserObjectDao.GetUserParameter("MOBIUS", "UpdateCorpDbMoltableMxCheckpointDate", "01-sep-2013 000000");
//UserObjectDao.SetUserParameter("MOBIUS", "UpdateCorpDbMoltableMxCheckpointDate", checkPointDate);
sql = Lex.Replace(SelectByDateRange, "1-jan-1900 000000", checkPointDate);
msg = "Reading where date >= " + checkPointDate;
}
else if (byCorpIdRange) // by CorpId range
{
if (highCorpId >= srcMaxCorpId)
{
break; // done
}
lowCorpId = highCorpId + 1; // start of next chunk
highCorpId = lowCorpId + SelectChunkSize;
if (highCorpId >= srcMaxCorpId)
{
highCorpId = srcMaxCorpId;
}
sql = Lex.Replace(SelectByCorpIdRange, "corp_nbr > 0", "corp_nbr between " + lowCorpId + " and " + highCorpId);
msg = "Reading: " + lowCorpId + " to " + highCorpId + ", Reads: " + readCount + ", Inserts: " + insertCount;
}
else if (missingFix)
{
if (chunk > 1)
{
break; // break 2nd time through
}
sql = SelectMissingHelmFix;
if (Lex.IsDefined(missingFixCriteria)) // substitute any criteria
{
sql = Lex.Replace(sql, "1=1", missingFixCriteria);
}
msg = "Fixing missing data";
}
Progress.Show(msg);
DbCommandMx readCmd = new DbCommandMx();
readCmd.MxConn = DbConnectionMx.GetConnection("prd123");
readCmd.PrepareUsingDefinedConnection(sql, null);
DbDataReader rdr = readCmd.ExecuteReader();
DbCommandMx insertCmd = new DbCommandMx();
OracleDbType[] pta = new OracleDbType[6];
pta[0] = OracleDbType.Int32; // corp_nbr
pta[1] = OracleDbType.Clob; // molstructure
pta[2] = OracleDbType.Clob; // molformula
pta[3] = OracleDbType.Double; // molweight
pta[4] = OracleDbType.Clob; // smiles
pta[5] = OracleDbType.Date; // molecule_date
insertCmd.Prepare(InsertSql, pta);
insertCmd.BeginTransaction(); // be sure we have a transaction going
pva = DbCommandMx.NewObjectArrayArray(6, InsertBufferSize); // alloc insert row array
object[] vo = new object[6];
while (true)
{
bool readOk = rdr.Read();
if (readOk)
{
rdr.GetValues(vo);
CorpId = readCmd.GetInt(0); // corp_nbr
vo[0] = CorpId;
CorpIdList.Add(CorpId.ToString());
if (!readCmd.IsNull(1)) // molstructure
{
chime = readCmd.GetClob(1);
chime = OracleMx.ClearStringIfExceedsMaxStringSize(chime);
vo[1] = chime;
}
else
{
chime = "";
}
if (!readCmd.IsNull(2)) // molformula
{
mf = readCmd.GetClob(2);
mf = OracleMx.ClearStringIfExceedsMaxStringSize(mf);
vo[2] = mf;
}
if (!readCmd.IsNull(3)) // molweight
{
mw = readCmd.GetDouble(3);
vo[3] = mw;
}
if (Lex.IsDefined(chime)) // molsmiles - calculate from chime string
{
MoleculeMx cs = new MoleculeMx(MoleculeFormat.Chime, chime);
if (cs.AtomCount > 1) // need more than one atom
{
MoleculeMx cs2 = cs.ConvertTo(MoleculeFormat.Smiles);
smiles = cs2.GetSmilesString();
if (Lex.IsDefined(smiles))
{
smilesSuccess++;
}
else
{
Log("Smiles conversion failure for CorpId: " + CorpId);
smilesFails++;
}
smiles = OracleMx.ClearStringIfExceedsMaxStringSize(smiles);
vo[4] = smiles;
}
else
{
undefinedStructures++;
}
}
else
{
undefinedStructures++;
}
if (!readCmd.IsNull(5))
{
helm = readCmd.GetClob(5);
if (Lex.IsDefined(helm))
{
svg = HelmControl.GetSvg(helm);
vo[1] = SvgUtil.CompressSvgString(svg); // store compressed svg in molstructure column for now
helmStructures++;
}
}
if (!readCmd.IsNull(6))
{
sequence = readCmd.GetClob(6);
if (Lex.IsDefined(sequence))
{
// nothing yet
}
}
moleculeDateTime = DateTime.MinValue;
if (!readCmd.IsNull(7)) // molecule_date
{
moleculeDateTime = readCmd.GetDateTime(7);
vo[5] = moleculeDateTime;
}
for (int pi = 0; pi < 6; pi++) // invert for insert
{
pva[pi][pvaCount] = vo[pi];
}
if (Debug)
{
msg = String.Format("CorpId: {0}, mf: {1}, chime: {2}, smiles: {3}", CorpId.ToString(), mf.Length, chime.Length, smiles.Length);
Log(msg);
}
pvaCount++;
}
if (pvaCount >= InsertBufferSize || (!readOk && pvaCount > 0)) // write if buffer full or at end
{
try
{
if (deleteExisting)
{
int delCount = DoDeletes(CorpIdList);
updateCount += delCount; // count deletes as updates
insertCount -= delCount; // subtract from inserts
}
CorpIdList.Clear();
insCount = insertCmd.ExecuteArrayNonReader(pva, ref pvaCount);
insertCmd.Commit();
insertCmd.BeginTransaction();
insertCount += insCount;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
if (byDateRange)
{
string checkPointDate2 = String.Format("{0:dd-MMM-yyyy HHmmss}", moleculeDateTime); // format date time that will work with oracle
UserObjectDao.SetUserParameter("MOBIUS", "UpdateCorpDbMoltableMxCheckpointDate", checkPointDate2);
msg = "Processing where date >= " + checkPointDate + ", Reads: " + readCount + ", Inserts: " + insertCount + ", Updates: " + updateCount;
}
else if (byCorpIdRange) // CorpId range
{
msg = "Processing: " + lowCorpId + " to " + highCorpId + ", Reads: " + readCount + ", Inserts: " + insertCount;
}
else if (missingFix)
{
msg = "Fixing missing smiles, Updates: " + updateCount;
}
msg += String.Format(", Undefined structures: {0} , Smiles failures: {1}, Helms: {2}", undefinedStructures, smilesFails, helmStructures);
Progress.Show(msg);
}
if (!readOk)
{
break;
}
readCount++;
}
readCmd.Dispose();
insertCmd.Dispose();
} // end for select chunk
msg = "UpdateCorpDbMoltableMx - Inserts: " + insertCount + ", Updates: " + updateCount;
msg += String.Format(", Undefined structures: {0} , Smiles failures: {1}, Helms: {2}", undefinedStructures, smilesFails, helmStructures);
Log(msg);
return(msg);
}
private static void AddParameter(OracleCommand command, string parameterName, OracleDbType parameterType, object value)
{
var parameter = command.CreateParameter();
parameter.ParameterName = parameterName;
parameter.OracleDbType = parameterType;
parameter.Value = value;
command.Parameters.Add(parameter);
}
public OracleParameter AddParameter(string name, OracleDbType type, object value, int size, ParameterDirection direction)
{
OracleParameter prm = new OracleParameter();
prm.Direction = direction;
prm.ParameterName = name;
prm.OracleDbType = type;
prm.Size = size;
prm.Value = this.PrepareSqlValue(value);
_parameters.Add(prm);
return prm;
}
/// <summary>
/// 添加返回值
/// </summary>
/// <param name="paramName">返回值名</param>
/// <param name="dbType">数据类型</param>
/// <param name="size">类型长度（可选：默认为0）</param>
/// <returns>配置好的Oracle参数</returns>
public static OracleParameter AddReturnValue(string retValName, OracleDbType dbType, int size = 0)
{
return(AddParameter(retValName, DBNull.Value, dbType, size, ParameterDirection.ReturnValue));
}
public OracleDbType ConvertDbType2OraDbyType(DbType dbType)
{
OracleDbType res = OracleDbType.Int32;
switch (dbType)
{
case DbType.AnsiString:
throw new NotSupportedException();
// break;
case DbType.Binary:
res = OracleDbType.Raw;
break;
case DbType.Byte:
res = OracleDbType.Byte;
break;
case DbType.Boolean:
res = OracleDbType.Boolean;
break;
case DbType.Currency:
throw new NotSupportedException();
// break;
case DbType.Date:
res = OracleDbType.Date;
break;
case DbType.DateTime:
res = OracleDbType.TimeStamp;
break;
case DbType.Decimal:
res = OracleDbType.Decimal;
break;
case DbType.Double:
res = OracleDbType.Double;
break;
case DbType.Guid:
throw new NotSupportedException();
case DbType.Int16:
res = OracleDbType.Int16;
break;
case DbType.Int32:
res = OracleDbType.Int32;
break;
case DbType.Int64:
res = OracleDbType.Int64;
break;
//TODO: Falta resolver este problema
/*
* case DbType.Object:
* res = OracleDbType.Object;
* break;*/
case DbType.SByte:
throw new NotSupportedException();
case DbType.Single:
res = OracleDbType.Single;
break;
case DbType.String:
res = OracleDbType.Varchar2;
break;
case DbType.Time:
res = OracleDbType.TimeStamp;
break;
case DbType.UInt16:
throw new NotSupportedException();
case DbType.UInt32:
throw new NotSupportedException();
case DbType.UInt64:
throw new NotSupportedException();
case DbType.VarNumeric:
throw new NotSupportedException();
case DbType.AnsiStringFixedLength:
res = OracleDbType.NChar;
break;
case DbType.StringFixedLength:
res = OracleDbType.Char;
break;
case DbType.Xml:
throw new NotSupportedException();
case DbType.DateTime2:
throw new NotSupportedException();
case DbType.DateTimeOffset:
throw new NotSupportedException();
default:
throw new NotSupportedException();
}
return(res);
}
public Param(string name, OracleDbType DbType, int Size)
{
Name = name;
OracleParam = new OracleParameter(name, DbType, Size, null, System.Data.ParameterDirection.Output);
}
private void InitializeParameter(OracleParameter parameter, string name, OracleDbType dbType)
{
parameter.ParameterName = FormatNameForParameter(name);
parameter.OracleDbType = dbType;
}
public static OracleParameter GetOraParam(string paramName, object paramValue, OracleDbType paramDBType, ParameterDirection paramDirection)
{
return(new OracleParameter(paramName, paramDBType, paramValue, paramDirection));
}
public static OracleParameter CreateInputParameter(string paramName, OracleDbType dbtype, int size, object value)
{
return CreateParameter(ParameterDirection.Input, paramName, dbtype, size, value);
}
protected override bool BulkInsert <T>(List <T> objList, ref int effectRows)
{
lock (locker)
{
string table_name = string.Empty;
int loop_for = 0;
bool isSuccessful = false;
ErrorMessage = string.Empty;
StringBuilder sb = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
if (objList.Count > 0)
{
loop_for = (int)Math.Ceiling(Math.Round((double)objList.Count / 30000, 10));
for (int i = 0; i < (loop_for); i++)
{
if (CheckConnection(DbConnection, DbCommand, testString))
{
DbCommand = DbConnection.CreateCommand();
DbCommand.BindByName = true;
List <T> TempList = objList.GetRange(i * 30000, Math.Min(30000, objList.Count - i * 30000));
//ArrayBindCount一定要填入, 否則會跳ora-01404 insert value too large for column
DbCommand.ArrayBindCount = TempList.Count;
//if (sb.Length == 0 && sb2.Length == 0)
//{
foreach (PropertyInfo prop in TempList[0].GetType().GetProperties())
{
table_name = TempList[0].GetType().Name;
if (prop.Name != "ROWID")
{
sb.Append(prop.Name.ToUpper() + ",");
sb2.Append(":" + prop.Name.ToUpper() + ",");
var propertyType = prop.PropertyType;
if (prop.PropertyType.IsGenericType &&
prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
propertyType = prop.PropertyType.GetGenericArguments()[0];
}
//var ttt = prop.GetType();
var expression = DynamicSelect <T, dynamic>(prop);
var valueList = TempList.Select(expression).ToArray();
OracleDbType dbType = OracleDbType.Varchar2;
_OracleDbTypeDic.TryGetValue(propertyType.Name.ToUpper(), out dbType);
DbCommand.Parameters.Add(":" + prop.Name.ToUpper(), dbType, valueList, ParameterDirection.Input);
}
}
sb.Remove(sb.Length - 1, 1);
sb2.Remove(sb2.Length - 1, 1);
try
{
DbCommand.CommandText = "INSERT INTO " + table_name + " (" + sb + ")" + " values (" + sb2 + ")";
//}
effectRows += DbCommand.ExecuteNonQuery();
isSuccessful = true;
}
catch (Exception exception)
{
isSuccessful = false;
ErrorMessage = exception.ToString();
}
sb.Clear();
sb2.Clear();
}
}
CloseConnection();
}
return(isSuccessful);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="Field"/> class.
/// </summary>
/// <param name="propertyName">
/// The property name.
/// </param>
/// <param name="columnName">
/// The column name.
/// </param>
/// <param name="oracleDbType">
/// The oracle db type.
/// </param>
public Field(string propertyName, string columnName, OracleDbType oracleDbType)
{
this.PropertyName = propertyName;
this.ColumnName = columnName;
this.OracleDbType = oracleDbType;
}
protected IDataParameter FromSqlParamToOraParam(SqlParameter param)
{
OracleDbType oraDbType = OracleDbType.Varchar2;
object oraValue = param.Value;
string paramName = param.ParameterName;
if (param.Direction == ParameterDirection.Input || param.Direction == ParameterDirection.InputOutput)
{
paramName = paramName.Insert(1, "IN_");
}
switch (param.SqlDbType)
{
case SqlDbType.BigInt:
oraDbType = OracleDbType.Int64;
break;
case SqlDbType.Binary:
oraDbType = OracleDbType.Blob;
break;
case SqlDbType.Bit:
oraDbType = OracleDbType.Char;
if (param.Value != DBNull.Value)
{
oraValue = Convert.ToBoolean(param.Value) ? "T" : "F";
}
break;
case SqlDbType.Char:
oraDbType = OracleDbType.Char;
break;
case SqlDbType.Date:
oraDbType = OracleDbType.Date;
break;
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.DateTimeOffset:
oraDbType = OracleDbType.TimeStamp;
break;
case SqlDbType.Decimal:
oraDbType = OracleDbType.Decimal;
break;
case SqlDbType.Float:
oraDbType = OracleDbType.Single;
break;
case SqlDbType.Image:
oraDbType = OracleDbType.Blob;
break;
case SqlDbType.Int:
oraDbType = OracleDbType.Int32;
break;
case SqlDbType.Money:
oraDbType = OracleDbType.Decimal;
break;
case SqlDbType.NChar:
oraDbType = OracleDbType.NChar;
break;
case SqlDbType.NText:
oraDbType = OracleDbType.NClob;
break;
case SqlDbType.NVarChar:
oraDbType = OracleDbType.NVarchar2;
break;
case SqlDbType.Real:
oraDbType = OracleDbType.Double;
break;
case SqlDbType.SmallDateTime:
oraDbType = OracleDbType.Date;
break;
case SqlDbType.SmallInt:
oraDbType = OracleDbType.Int16;
break;
case SqlDbType.SmallMoney:
oraDbType = OracleDbType.Decimal;
break;
case SqlDbType.Structured:
break;
case SqlDbType.Text:
oraDbType = OracleDbType.Clob;
break;
case SqlDbType.Time:
case SqlDbType.Timestamp:
oraDbType = OracleDbType.TimeStamp;
break;
case SqlDbType.TinyInt:
oraDbType = OracleDbType.Byte;
break;
case SqlDbType.Udt:
break;
case SqlDbType.UniqueIdentifier:
oraDbType = OracleDbType.Varchar2;
break;
case SqlDbType.VarBinary:
oraDbType = OracleDbType.Blob;
break;
case SqlDbType.VarChar:
oraDbType = OracleDbType.Varchar2;
break;
case SqlDbType.Variant:
oraDbType = OracleDbType.Clob;
break;
case SqlDbType.Xml:
oraDbType = OracleDbType.XmlType;
break;
default:
throw new ArgumentOutOfRangeException();
}
IDataParameter oraParam = new OracleParameter(paramName, oraDbType, param.Size)
{
Value = oraValue,
Direction = param.Direction
};
return(oraParam);
}
public static void AddInVariable(OracleCommand Command, OracleDbType VarType, string VarValue)
{
OracleParameter Variable = new OracleParameter();
Variable.OracleDbType = VarType;
Variable.Direction = ParameterDirection.Input;
Variable.Value = VarValue;
Command.Parameters.Add(Variable);
}
static int GetSizeForType(OracleDbType dbType)
{
switch (dbType)
{
case OracleDbType.BFile:
case OracleDbType.Blob:
case OracleDbType.Clob:
return(int.MaxValue);
case OracleDbType.Byte:
return(8);
case OracleDbType.Char:
return(1);
case OracleDbType.Date:
return(100);
case OracleDbType.Decimal:
case OracleDbType.Double:
return(int.MaxValue);
case OracleDbType.Long:
case OracleDbType.LongRaw:
return(int.MaxValue);
case OracleDbType.Int16:
return(Int16.MaxValue);
case OracleDbType.Int32:
return(int.MaxValue);
case OracleDbType.Int64:
return(int.MaxValue);
case OracleDbType.IntervalDS:
break;
case OracleDbType.IntervalYM:
break;
case OracleDbType.NClob:
case OracleDbType.NChar:
return(3000);
case OracleDbType.NVarchar2:
return(3000);
case OracleDbType.Raw:
break;
case OracleDbType.Single:
break;
case OracleDbType.TimeStamp:
case OracleDbType.TimeStampLTZ:
case OracleDbType.TimeStampTZ:
return(100);
case OracleDbType.XmlType:
case OracleDbType.Varchar2:
return(4000);
case OracleDbType.BinaryDouble:
break;
case OracleDbType.BinaryFloat:
break;
case OracleDbType.RefCursor:
break;
default:
return(4000);
}
return(4000);
}
public EventParameter(string name, OracleDbType dbType, int size)
{
_name = name;
_dbType = dbType;
_size = size;
}
/// <summary>
/// 转换参数
/// </summary>
/// <param name="ParamName">存储过程名称或命令文本</param>
/// <param name="DbType">参数类型</param></param>
/// <param name="Size">参数大小</param>
/// <param name="Value">参数值</param>
/// <returns>新的 parameter 对象</returns>
public OracleParameter MakeInParam(string ParamName, OracleDbType DbType, int Size, object Value)
{
return(MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value));
}
/// <summary>
/// Needed only for Oracle to get/set the OracleDbType used by 'OracleCommand.Parameters'.
/// </summary>
/// <param name="oracleDbTypeProp">OracleDbType to be set to.</param>
public DBModelsOracleDbTypeAttribute(OracleDbType oracleDbTypeProp)
{
OracleDbTypeProp = oracleDbTypeProp;
}
/// <summary>
/// OracleDbType
/// </summary>
/// <param name="enmDbType"></param>
/// <returns></returns>
private OracleDbType[] ConvertDbTypeToOracleDbType(DbType[] enmDbType)
{
OracleDbType[] enmOracleDbType = new OracleDbType[enmDbType.Length];
for (int i = 0; i < enmDbType.Length; i++)
{
switch (enmDbType[i])
{
case DbType.Date:
enmOracleDbType[i] = OracleDbType.Date;
break;
case DbType.DateTime:
enmOracleDbType[i] = OracleDbType.Date;
break;
case DbType.StringFixedLength:
enmOracleDbType[i] = OracleDbType.Char;
break;
case DbType.String:
enmOracleDbType[i] = OracleDbType.Varchar2;
break;
case DbType.AnsiString:
enmOracleDbType[i] = OracleDbType.Varchar2;
break;
case DbType.Byte:
enmOracleDbType[i] = OracleDbType.Blob;
break;
case DbType.Int32:
enmOracleDbType[i] = OracleDbType.Int32;
break;
case DbType.Int64:
enmOracleDbType[i] = OracleDbType.Int64;
break;
case DbType.Double:
enmOracleDbType[i] = OracleDbType.Double;
break;
case DbType.Binary:
enmOracleDbType[i] = OracleDbType.Blob;
break;
case DbType.Object:
enmOracleDbType[i] = OracleDbType.Blob;
break;
case DbType.Xml:
enmOracleDbType[i] = OracleDbType.XmlType;
break;
default:
enmOracleDbType[i] = OracleDbType.Varchar2;
break;
}
}
return(enmOracleDbType);
}
public OracleParameter AddParameter(string name, OracleDbType type, object value, bool convertZeroToDBNull)
{
OracleParameter prm = new OracleParameter();
prm.Direction = ParameterDirection.Input;
prm.ParameterName = name;
prm.OracleDbType = type;
prm.Value = this.PrepareSqlValue(value, convertZeroToDBNull);
_parameters.Add(prm);
return prm;
}
/**
* 批量插入数据
* @tableName 表名称
* @columnRowData 键-值存储的批量数据：键是列名称，值是对应的数据集合
* @conStr 连接字符串
* @len 每次批处理数据的大小
*/
public static int BatchInsert(string tableName, Dictionary <string, object> columnRowData, string conStr, int len)
{
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentException("必须指定批量插入的表名称", "tableName");
}
if (columnRowData == null || columnRowData.Count < 1)
{
throw new ArgumentException("必须指定批量插入的字段名称", "columnRowData");
}
int iResult = 0;
string[] dbColumns = columnRowData.Keys.ToArray();
StringBuilder sbCmdText = new StringBuilder();
if (columnRowData.Count > 0)
{
//准备插入的SQL
sbCmdText.AppendFormat("INSERT INTO {0}(", tableName);
sbCmdText.Append(string.Join(",", dbColumns));
sbCmdText.Append(") VALUES (");
sbCmdText.Append(":" + string.Join(",:", dbColumns));
sbCmdText.Append(")");
using (OracleConnection conn = new OracleConnection(conStr))
{
using (OracleCommand cmd = conn.CreateCommand())
{
//绑定批处理的行数
cmd.ArrayBindCount = len;
cmd.BindByName = true;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sbCmdText.ToString();
cmd.CommandTimeout = 1800;//100分钟
//创建参数
OracleParameter oraParam;
List <IDbDataParameter> cacher = new List <IDbDataParameter>();
OracleDbType dbType = OracleDbType.Varchar2;
foreach (string colName in dbColumns)
{
dbType = GetOracleDbType(columnRowData[colName]);
oraParam = new OracleParameter(colName, dbType);
oraParam.Direction = ParameterDirection.Input;
oraParam.OracleDbTypeEx = dbType;
oraParam.Value = columnRowData[colName];
cmd.Parameters.Add(oraParam);
}
//打开连接
conn.Open();
/*执行批处理*/
var trans = conn.BeginTransaction();
try
{
cmd.Transaction = trans;
iResult = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
}
}
return(iResult);
}
public OracleParameter AddStreamParameter(string name, Stream value, OracleDbType type)
{
OracleParameter prm = new OracleParameter();
prm.Direction = ParameterDirection.Input;
prm.ParameterName = name;
prm.OracleDbType = type;
value.Position = 0;
byte[] data = new byte[value.Length];
value.Read(data, 0, (int)value.Length);
prm.Value = data;
_parameters.Add(prm);
return prm;
}
#pragma warning disable S1541 // Methods and properties should not be too complex
static int GetSizeForType(OracleDbType dbType)
#pragma warning restore S1541 // Methods and properties should not be too complex
{
switch (dbType)
{
case OracleDbType.BFile:
case OracleDbType.Blob:
case OracleDbType.Clob:
return(int.MaxValue);
case OracleDbType.Byte:
return(8);
case OracleDbType.Char:
return(1);
case OracleDbType.Date:
return(100);
case OracleDbType.Decimal:
case OracleDbType.Double:
return(int.MaxValue);
case OracleDbType.Long:
case OracleDbType.LongRaw:
return(int.MaxValue);
case OracleDbType.Int16:
return(Int16.MaxValue);
case OracleDbType.Int32:
return(int.MaxValue);
case OracleDbType.Int64:
return(int.MaxValue);
case OracleDbType.IntervalDS:
break;
case OracleDbType.IntervalYM:
break;
case OracleDbType.NClob:
case OracleDbType.NChar:
return(3000);
case OracleDbType.NVarchar2:
return(3000);
case OracleDbType.Raw:
break;
case OracleDbType.Single:
break;
case OracleDbType.TimeStamp:
case OracleDbType.TimeStampLTZ:
case OracleDbType.TimeStampTZ:
return(100);
case OracleDbType.XmlType:
case OracleDbType.Varchar2:
return(4000);
case OracleDbType.BinaryDouble:
break;
case OracleDbType.BinaryFloat:
break;
case OracleDbType.RefCursor:
break;
default:
return(4000);
}
return(4000);
}
/// <summary>
/// Overriding type constructor for input parameters
/// </summary>
/// <param name="pName"></param>
/// <param name="oDb"></param>
/// <param name="val"></param>
public OracleProcParam(string pName, OracleDbType oDb, string val)
{
if (oDb == OracleDbType.Clob)
{
this.paramIsClob = true;
}
this.Direction = ParameterDirection.Input;
this.dataTypeName = DataTypeConstants.PawnDataType.WHOLENUMBER;
this.Name = pName;
this.parameterValues = new List<object>(1);
this.OracleType = oDb;
this.arraySize = 1;
this.AddValue(val);
}
public static OracleParameter SetOracleType(this OracleParameter oracleParameter, OracleDbType oraType)
{
oracleParameter.OracleDbType = oraType;
return(oracleParameter);
}
private void ComputeDataType()
{
//Initialize oracle data type
var dtVOMapper = DataTypeVOMapper.Instance;
var orSQMapper = OracleSqlDbTypeMapper.Instance;
var sqlType = dtVOMapper[this.dataTypeName].Right;
this.OracleType = orSQMapper[sqlType];
}
public DbParameter AddParameter(string ParameterName, OracleDbType type, int size, object value)
{
return AddParameter(ParameterName, type, size, value, ParameterDirection.Input);
}
/// <summary>
/// Определяет тип объект по типу данных
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static Type DetectTypeByOraType(OracleDbType type) {
Type v_result;
switch (type) {
case OracleDbType.Varchar2:
case OracleDbType.Char:
case OracleDbType.NChar:
case OracleDbType.NVarchar2:
case OracleDbType.Clob:
v_result = typeof(String);
break;
case OracleDbType.Byte:
case OracleDbType.Double:
case OracleDbType.Decimal:
case OracleDbType.Int16:
case OracleDbType.Int32:
case OracleDbType.Int64:
v_result = typeof(Decimal);
break;
case OracleDbType.Date:
case OracleDbType.TimeStamp:
v_result = typeof(DateTime);
break;
case OracleDbType.Blob:
v_result = typeof(Byte[]);
break;
default:
throw new NotSupportedException("Невозможно преобразовать тип \"" + type + "\".");
}
return v_result;
}
public void TupleForOracleParameter(List <Tuple <string, OracleDbType, int, object, ParameterDirection> > LstTupParameters, string paraname, OracleDbType OraclePara, int lenght, string value, ParameterDirection paraDirection)
{
LstTupParameters.Add(new Tuple <string, OracleDbType, int, object, ParameterDirection>(paraname, OraclePara, lenght, value, paraDirection));
}
private static ClassNameType GetClassNameType(OracleDbType sqlType)
{
throw new NotImplementedException();
//switch (sqlType)
//{
// case Oracle.DataAccess.Client.OracleDbType.Array:
// break;
// case Oracle.DataAccess.Client.OracleDbType.BFile:
// break;
// case Oracle.DataAccess.Client.OracleDbType.BinaryDouble:
// break;
// case Oracle.DataAccess.Client.OracleDbType.BinaryFloat:
// break;
// case Oracle.DataAccess.Client.OracleDbType.Blob:
// break;
// case Oracle.DataAccess.Client.OracleDbType.Byte:
// break;
// case Oracle.DataAccess.Client.OracleDbType.Char:
// break;
// case Oracle.DataAccess.Client.OracleDbType.Clob:
// break;
// case Oracle.DataAccess.Client.OracleDbType.Date:
// break;
// case Oracle.DataAccess.Client.OracleDbType.Decimal:
// break;
// case Oracle.DataAccess.Client.OracleDbType.Double:
// break;
// case Oracle.DataAccess.Client.OracleDbType.Int16:
// break;
// case Oracle.DataAccess.Client.OracleDbType.Int32:
// break;
// case Oracle.DataAccess.Client.OracleDbType.Int64:
// break;
// case Oracle.DataAccess.Client.OracleDbType.IntervalDS:
// break;
// case Oracle.DataAccess.Client.OracleDbType.IntervalYM:
// break;
// case Oracle.DataAccess.Client.OracleDbType.Long:
// break;
// case Oracle.DataAccess.Client.OracleDbType.LongRaw:
// break;
// case Oracle.DataAccess.Client.OracleDbType.NChar:
// break;
// case Oracle.DataAccess.Client.OracleDbType.NClob:
// break;
// case Oracle.DataAccess.Client.OracleDbType.NVarchar2:
// break;
// case Oracle.DataAccess.Client.OracleDbType.Object:
// break;
// case Oracle.DataAccess.Client.OracleDbType.Raw:
// break;
// case Oracle.DataAccess.Client.OracleDbType.Ref:
// break;
// case Oracle.DataAccess.Client.OracleDbType.RefCursor:
// break;
// case Oracle.DataAccess.Client.OracleDbType.Single:
// break;
// case Oracle.DataAccess.Client.OracleDbType.TimeStamp:
// break;
// case Oracle.DataAccess.Client.OracleDbType.TimeStampLTZ:
// break;
// case Oracle.DataAccess.Client.OracleDbType.TimeStampTZ:
// break;
// case Oracle.DataAccess.Client.OracleDbType.Varchar2:
// break;
// case Oracle.DataAccess.Client.OracleDbType.XmlType:
// break;
// default:
// break;
//}
}
public void Add(string name, OracleDbType dbType, object val, ParameterDirection dir)
{
}
public static OracleParameter CreateOutputParameter(string paramName, OracleDbType dbtype, int size)
{
return CreateParameter(ParameterDirection.Output, paramName, dbtype, size, DBNull.Value);
}
/// <summary>
/// 为一个 DbCommand 实例添加一个参数。
/// </summary>
/// <param name="command">表示要对数据源执行的 SQL 语句或存储过程。</param>
/// <param name="name">参数名。</param>
/// <param name="oracleType">数据类型。</param>
/// <param name="direction">参数类型。</param>
/// <param name="sourceColumn">源列的名称。</param>
/// <param name="sourceVersion">数据行的版本。</param>
/// <param name="value">参数值。</param>
public void AddParameter(DbCommand command, string name, OracleDbType oracleType, ParameterDirection direction, string sourceColumn, DataRowVersion sourceVersion, object value)
{
AddParameter(command, name, oracleType, 0, direction, false, sourceColumn, sourceVersion, value);
}
public OracleParameter(string parameterName, OracleDbType type, ParameterDirection direction)
{
this.ParameterName = parameterName;
this.OracleDbType = type;
this.Direction = direction;
}
/// <summary>
/// 为一个 DbCommand 实例添加一个输入参数。
/// </summary>
/// <param name="command">表示要对数据源执行的 SQL 语句或存储过程。</param>
/// <param name="name">参数名。</param>
/// <param name="oracleType">数据类型。</param>
/// <param name="value">参数值。</param>
public void AddInParameter(DbCommand command, string name, OracleDbType oracleType, object value)
{
AddParameter(command, name, oracleType, ParameterDirection.Input, String.Empty, DataRowVersion.Default, value);
}
public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction)
{
var oracleParameter = new OracleParameter(name, oracleDbType, direction);
oracleParameters.Add(oracleParameter);
}
// ----------------------------------------------------------------------------------
private static OracleParameter OraParamFactory(string parameterName,
OracleDbType ParamType, ParameterDirection Direction, object value)
{
return(OraParamFactory(parameterName, ParamType, null, Direction, value));
}
public MetaDataEventParameter(string name, OracleDbType dbType, Func<EventMetaData, object> valueFromMetaData)
: this(name, dbType, 0, valueFromMetaData)
{
}
public MetaDataEventParameter(string name, OracleDbType dbType, int size, Func<EventMetaData, object> valueFromMetaData)
: base(name, dbType, size)
{
_valueFromMetaData = valueFromMetaData;
}
/// <summary>
/// 为一个 DbCommand 实例添加一个参数。
/// </summary>
/// <param name="command">表示要对数据源执行的 SQL 语句或存储过程。</param>
/// <param name="name">参数名。</param>
/// <param name="oracleType">数据类型。</param>
/// <param name="size">长度。</param>
/// <param name="direction">参数类型。</param>
/// <param name="nullable">表示参数是否可以接受null值。</param>
/// <param name="sourceColumn">源列的名称。</param>
/// <param name="sourceVersion">数据行的版本。</param>
/// <param name="value">参数值。</param>
public void AddParameter(DbCommand command, string name, OracleDbType oracleType, int size, ParameterDirection direction, bool nullable, string sourceColumn, DataRowVersion sourceVersion, object value)
{
DbParameter parameter = CreateParameter(name, oracleType, size, direction, nullable, sourceColumn, sourceVersion, value);
command.Parameters.Add(parameter);
}
public static OracleParameter CreateParameter(ParameterDirection direction, string paramName, OracleDbType dbType, int size, object value)
{
OracleParameter param = new OracleParameter(paramName, dbType, size);
param.Value = value;
param.Direction = direction;
return param;
}