/// <summary>
/// 获取Oracle参数的值
/// </summary>
/// <param name="oraParam"></param>
/// <returns></returns>
protected virtual object GetOraParamVal(OracleParameter oraParam)
{
if (oraParam.Value == null || (oraParam.Value is INullable && (oraParam.Value as INullable).IsNull))
{
return(DBNull.Value);
}
object val = DBNull.Value;
if (oraParam.Value is OracleXmlType)
{
OracleXmlType xmltype = (OracleXmlType)oraParam.Value;
if (!xmltype.IsEmpty)
{
val = xmltype.Value;
}
}
else if (oraParam.Value is OracleBlob)
{
OracleBlob blobVal = (OracleBlob)oraParam.Value;
if (!blobVal.IsNull)
{
val = (oraParam.Value as OracleBlob).Value;
}
}
else if (oraParam.Value is OracleClob)
{
OracleClob clobVal = (OracleClob)oraParam.Value;
if (!clobVal.IsNull)
{
val = clobVal.Value;
}
}
else if (oraParam.Value is OracleDecimal)
{
OracleDecimal decimalVal = (OracleDecimal)oraParam.Value;
if (!decimalVal.IsNull)
{
val = decimalVal.Value;
}
}
else if (oraParam.Value is OracleDate)
{
OracleDate dateVal = (OracleDate)oraParam.Value;
if (!dateVal.IsNull)
{
val = dateVal.Value;
}
}
else if (oraParam.Value is OracleString)
{
OracleString stringVal = (OracleString)oraParam.Value;
if (!stringVal.IsNull)
{
val = stringVal.Value;
}
}
else if (oraParam.Value is OracleBFile)
{
OracleBFile fileVal = oraParam.Value as OracleBFile;
if (!fileVal.IsNull)
{
val = fileVal.Value;
}
}
else if (oraParam.Value is OracleBinary)
{
OracleBinary binaryVal = (OracleBinary)oraParam.Value;
if (!binaryVal.IsNull)
{
val = binaryVal.Value;
}
}
else if (oraParam.Value is OracleTimeStamp)
{
OracleTimeStamp timeStampVal = (OracleTimeStamp)oraParam.Value;
if (!timeStampVal.IsNull)
{
val = timeStampVal.Value;
}
}
else if (oraParam.Value is OracleRefCursor)
{
using (OracleRefCursor timeStampVal = (OracleRefCursor)oraParam.Value)
{
if (timeStampVal.IsNull)
{
return(null);
}
OracleDataReader dataReader = timeStampVal.GetDataReader();
DataTable datatable = new DataTable();
datatable.Load(dataReader);
return(datatable);
}
}
else
{
val = oraParam.Value;
}
return(val);
}
/// <summary>
/// Return a generic list of objects fetched from a data reader using type-specific read method
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader"></param>
/// <param name="readMethod"></param>
/// <returns></returns>
//public static List<T> ReadResult<T>(OracleDataReader reader, Func<OracleDataReader, T> readMethod) {
// // iterate reader and create list of BOs using they type's reader method
// List<T> list = new List<T>();
// if (reader.HasRows) while (reader.Read() == true) list.Add(readMethod(reader));
// return list;
//}
/// <summary>
/// Return a Datatable fetched from a data reader
/// </summary>
/// <param name="reader">Reader prepared for fetching result set</param>
/// <returns></returns>
public static DataTable ReadResult(OracleDataReader reader, bool convertColumnNameToTitleCaseInCaption = false, UInt32?optionalMaximumNumberOfRowsToRead = null)
{
// determine name and type of each column in result set and build empty datatable
DataTable dt = new DataTable();
DataColumn dc;
List <string> colName = new List <string>();
// column names and respective Oracle type from the reader
List <Column> readerColumns = GetReaderColumnTypes(reader);
// build empty datatable with column names and correct C# type
foreach (Column col in readerColumns)
{
// add column to list
colName.Add(col.ColumnName);
// create data column object based on Oracle column type, save column type to array
if (col.ColumnType == typeof(OracleString))
{
dc = new DataColumn(col.ColumnName, typeof(System.String));
}
else if (col.OracleDataTypeName.Equals(OracleDbType.BinaryDouble.ToString()))
{
dc = new DataColumn(col.ColumnName, typeof(System.Double));
}
else if (col.OracleDataTypeName.Equals(OracleDbType.BinaryFloat.ToString()))
{
dc = new DataColumn(col.ColumnName, typeof(System.Single));
}
else if (col.ColumnType == typeof(OracleDecimal))
{
dc = new DataColumn(col.ColumnName, typeof(OracleDecimal));
}
else if (col.ColumnType == typeof(OracleDate))
{
dc = new DataColumn(col.ColumnName, typeof(OracleDate));
}
else if (col.ColumnType == typeof(OracleTimeStamp))
{
dc = new DataColumn(col.ColumnName, typeof(OracleTimeStamp));
}
else if (col.ColumnType == typeof(OracleTimeStampLTZ))
{
dc = new DataColumn(col.ColumnName, typeof(OracleTimeStampLTZ));
}
else if (col.ColumnType == typeof(OracleTimeStampTZ))
{
dc = new DataColumn(col.ColumnName, typeof(OracleTimeStampTZ));
}
else
{
dc = new DataColumn(col.ColumnName, typeof(Object));
}
//throw new Exception("Oracle column type not recognized in 'DataTable Hydrator.ReadResult()' for database column " + col.ColumnName);
dc.Caption =
convertColumnNameToTitleCaseInCaption ?
CaseConverter.ConvertSnakeCaseToLabel(col.ColumnName)
: col.ColumnName
;
dt.Columns.Add(dc);
}
// add rows to data table
DataRow drow;
Int32 numRowsRead = 0;
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
// create the row and set column data based on result set row
drow = dt.NewRow();
for (int c = 0; c < colName.Count; c++)
{
if (reader.IsDBNull(reader.GetOrdinal(colName[c])))
{
drow[colName[c]] = DBNull.Value;
}
else if (readerColumns[c].OracleDataTypeName.Equals(OracleDbType.BinaryDouble.ToString()))
{
drow[colName[c]] = (Double?)reader.GetDouble(reader.GetOrdinal(colName[c]));
}
else if (readerColumns[c].OracleDataTypeName.Equals(OracleDbType.BinaryFloat.ToString()))
{
drow[colName[c]] = (Single?)reader.GetFloat(reader.GetOrdinal(colName[c]));
}
else if (readerColumns[c].ColumnType == typeof(OracleString))
{
drow[colName[c]] = (String)reader.GetOracleString(reader.GetOrdinal(colName[c]));
}
else if (readerColumns[c].ColumnType == typeof(OracleDecimal))
{
drow[colName[c]] = (OracleDecimal?)OracleDecimal.SetPrecision(reader.GetOracleDecimal(reader.GetOrdinal(colName[c])), 28);
}
else if (readerColumns[c].ColumnType == typeof(OracleDate))
{
drow[colName[c]] = (OracleDate?)reader.GetOracleDate(reader.GetOrdinal(colName[c]));
}
else if (readerColumns[c].ColumnType == typeof(OracleTimeStamp))
{
drow[colName[c]] = (OracleTimeStamp?)reader.GetOracleTimeStamp(reader.GetOrdinal(colName[c]));
}
else if (readerColumns[c].ColumnType == typeof(OracleTimeStampLTZ))
{
drow[colName[c]] = (OracleTimeStampLTZ?)reader.GetOracleTimeStampLTZ(reader.GetOrdinal(colName[c]));
}
else if (readerColumns[c].ColumnType == typeof(OracleTimeStampTZ))
{
drow[colName[c]] = (OracleTimeStampTZ?)reader.GetOracleTimeStampTZ(reader.GetOrdinal(colName[c]));
}
else
{
drow[colName[c]] = reader.GetValue(reader.GetOrdinal(colName[c]));
}
}
// Add the hydrated row to the datatable
dt.Rows.Add(drow);
if (optionalMaximumNumberOfRowsToRead != null)
{
numRowsRead++;
if (numRowsRead >= optionalMaximumNumberOfRowsToRead)
{
break;
}
}
}
}
// this is necessary in order for DataRowVersion status to be correct
dt.AcceptChanges();
return(dt);
}
/// <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);
}
}
private void wyswietlProfilZwierzeciaClick(object sender, EventArgs e)
{
if (nrZwierzecia_textBox.Text.Length == 0)
{
String message_error_login = "******";
MessageBox.Show(message_error_login);
}
else
{
this.Hide();
try
{
nowe_polaczenie.Connection();
string kot;
kot = "select count(nr_zwierzecia) from koty where nr_zwierzecia ='" + nrZwierzecia_textBox.Text + "'";
control_manager_dialog = new OracleCommand(kot, nowe_polaczenie.nowe_polaczenie);
control_manager_dialog.CommandType = CommandType.Text;
OracleDataReader dr = control_manager_dialog.ExecuteReader();
dr.Read();
OracleDecimal ileKotow = dr.GetOracleDecimal(0);
if (ileKotow.ToString().Equals("1"))
{
profilKot ss = new profilKot(nrZwierzecia_textBox.Text, user_name_login);
ss.Show();
}
else
{
profilPies ss = new profilPies(nrZwierzecia_textBox.Text, user_name_login);
ss.Show();
}
}
catch (OracleException ex)
{
switch (ex.Number)
{
case 1:
MessageBox.Show("Error attempting to insert duplicate data.");
break;
case 12560:
MessageBox.Show("The database is unvaliable.");
break;
default:
MessageBox.Show("Database error: " + ex.Message.ToString());
break;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
//nowe_polaczenie.Dispose();
}
}
}
private static OracleProviderAdapter CreateAdapter(string assemblyName, string clientNamespace, string typesNamespace, string?factoryName)
{
var assembly = Common.Tools.TryLoadAssembly(assemblyName, factoryName);
if (assembly == null)
{
throw new InvalidOperationException($"Cannot load assembly {assemblyName}");
}
var connectionType = assembly.GetType($"{clientNamespace}.OracleConnection", true) !;
var parameterType = assembly.GetType($"{clientNamespace}.OracleParameter", true) !;
var dataReaderType = assembly.GetType($"{clientNamespace}.OracleDataReader", true) !;
var transactionType = assembly.GetType($"{clientNamespace}.OracleTransaction", true) !;
var dbType = assembly.GetType($"{clientNamespace}.OracleDbType", true) !;
var commandType = assembly.GetType($"{clientNamespace}.OracleCommand", true) !;
var mappingSchema = new MappingSchema();
// do not set default conversion for BFile as it could be converted to file name, byte[], Stream and we don't know what user needs
var oracleBFileType = loadType("OracleBFile", DataType.BFile, skipConvertExpression: true) !;
var oracleBinaryType = loadType("OracleBinary", DataType.VarBinary) !;
var oracleBlobType = loadType("OracleBlob", DataType.Blob) !;
var oracleClobType = loadType("OracleClob", DataType.NText) !;
var oracleDateType = loadType("OracleDate", DataType.DateTime) !;
var oracleDecimalType = loadType("OracleDecimal", DataType.Decimal) !;
var oracleIntervalDSType = loadType("OracleIntervalDS", DataType.Time) !;
var oracleIntervalYMType = loadType("OracleIntervalYM", DataType.Date) !;
var oracleStringType = loadType("OracleString", DataType.NVarChar) !;
var oracleTimeStampType = loadType("OracleTimeStamp", DataType.DateTime2) !;
var oracleTimeStampLTZType = loadType("OracleTimeStampLTZ", DataType.DateTimeOffset) !;
var oracleTimeStampTZType = loadType("OracleTimeStampTZ", DataType.DateTimeOffset) !;
var oracleXmlTypeType = loadType("OracleXmlType", DataType.Xml) !;
var oracleXmlStreamType = loadType("OracleXmlStream", DataType.Xml, true, false) !;
var oracleRefCursorType = loadType("OracleRefCursor", DataType.Binary, hasValue: false) !;
var oracleRefType = loadType("OracleRef", DataType.Binary, true);
BulkCopyAdapter?bulkCopy = null;
var typeMapper = new TypeMapper();
typeMapper.RegisterTypeWrapper <OracleConnection>(connectionType);
typeMapper.RegisterTypeWrapper <OracleParameter>(parameterType);
typeMapper.RegisterTypeWrapper <OracleDbType>(dbType);
typeMapper.RegisterTypeWrapper <OracleCommand>(commandType);
typeMapper.RegisterTypeWrapper <OracleDataReader>(dataReaderType);
typeMapper.RegisterTypeWrapper <OracleTimeStampTZ>(oracleTimeStampTZType);
typeMapper.RegisterTypeWrapper <OracleTimeStampLTZ>(oracleTimeStampLTZType);
typeMapper.RegisterTypeWrapper <OracleDecimal>(oracleDecimalType);
var bulkCopyType = assembly.GetType($"{clientNamespace}.OracleBulkCopy", false);
if (bulkCopyType != null)
{
var bulkCopyOptionsType = assembly.GetType($"{clientNamespace}.OracleBulkCopyOptions", true) !;
var bulkRowsCopiedEventHandlerType = assembly.GetType($"{clientNamespace}.OracleRowsCopiedEventHandler", true) !;
var bulkCopyColumnMappingType = assembly.GetType($"{clientNamespace}.OracleBulkCopyColumnMapping", true) !;
var bulkCopyColumnMappingCollectionType = assembly.GetType($"{clientNamespace}.OracleBulkCopyColumnMappingCollection", true) !;
var rowsCopiedEventArgsType = assembly.GetType($"{clientNamespace}.OracleRowsCopiedEventArgs", true) !;
// bulk copy types
typeMapper.RegisterTypeWrapper <OracleBulkCopy>(bulkCopyType);
typeMapper.RegisterTypeWrapper <OracleBulkCopyOptions>(bulkCopyOptionsType);
typeMapper.RegisterTypeWrapper <OracleRowsCopiedEventHandler>(bulkRowsCopiedEventHandlerType);
typeMapper.RegisterTypeWrapper <OracleBulkCopyColumnMapping>(bulkCopyColumnMappingType);
typeMapper.RegisterTypeWrapper <OracleBulkCopyColumnMappingCollection>(bulkCopyColumnMappingCollectionType);
typeMapper.RegisterTypeWrapper <OracleRowsCopiedEventArgs>(rowsCopiedEventArgsType);
typeMapper.FinalizeMappings();
bulkCopy = new BulkCopyAdapter(
typeMapper.BuildWrappedFactory((IDbConnection connection, OracleBulkCopyOptions options) => new OracleBulkCopy((OracleConnection)connection, options)),
typeMapper.BuildWrappedFactory((int source, string destination) => new OracleBulkCopyColumnMapping(source, destination)));
}
else
{
typeMapper.FinalizeMappings();
}
var paramMapper = typeMapper.Type <OracleParameter>();
var dbTypeBuilder = paramMapper.Member(p => p.OracleDbType);
var connectionMapper = typeMapper.Type <OracleConnection>();
var commandMapper = typeMapper.Type <OracleCommand>();
// data reader expressions
// rd.GetOracleTimeStampTZ(i) => DateTimeOffset
var generator = new ExpressionGenerator(typeMapper);
var rdParam = Expression.Parameter(typeof(IDataReader), "rd");
var indexParam = Expression.Parameter(typeof(int), "i");
var tstzExpr = generator.MapExpression((IDataReader rd, int i) => ((OracleDataReader)rd).GetOracleTimeStampTZ(i), rdParam, indexParam);
var tstzVariable = generator.AssignToVariable(tstzExpr, "tstz");
var expr = generator.MapExpression((OracleTimeStampTZ tstz) => new DateTimeOffset(
tstz.Year, tstz.Month, tstz.Day,
tstz.Hour, tstz.Minute, tstz.Second,
tstz.GetTimeZoneOffset()).AddTicks(tstz.Nanosecond / NanosecondsPerTick), tstzVariable);
generator.AddExpression(expr);
var body = generator.Build();
var readDateTimeOffsetFromOracleTimeStampTZ = (Expression <Func <IDataReader, int, DateTimeOffset> >)Expression.Lambda(body, rdParam, indexParam);
// rd.GetOracleTimeStampLTZ(i) => DateTimeOffset
generator = new ExpressionGenerator(typeMapper);
tstzExpr = generator.MapExpression((IDataReader rd, int i) => ((OracleDataReader)rd).GetOracleTimeStampLTZ(i).ToOracleTimeStampTZ(), rdParam, indexParam);
tstzVariable = generator.AssignToVariable(tstzExpr, "tstz");
expr = generator.MapExpression((OracleTimeStampTZ tstz) => new DateTimeOffset(
tstz.Year, tstz.Month, tstz.Day,
tstz.Hour, tstz.Minute, tstz.Second,
tstz.GetTimeZoneOffset()).AddTicks(tstz.Nanosecond / NanosecondsPerTick), tstzVariable);
generator.AddExpression(expr);
body = generator.Build();
var readDateTimeOffsetFromOracleTimeStampLTZ = (Expression <Func <IDataReader, int, DateTimeOffset> >)Expression.Lambda(body, rdParam, indexParam);
// rd.GetOracleDecimal(i) => decimal
var readOracleDecimal = typeMapper.MapLambda <IDataReader, int, OracleDecimal>((rd, i) => ((OracleDataReader)rd).GetOracleDecimal(i));
var oracleDecimalParam = Expression.Parameter(readOracleDecimal.ReturnType, "dec");
generator = new ExpressionGenerator(typeMapper);
var precision = generator.AssignToVariable(Expression.Constant(29), "precision");
var decimalVar = generator.AddVariable(Expression.Parameter(typeof(decimal), "dec"));
var label = Expression.Label(typeof(decimal));
generator.AddExpression(
Expression.Loop(
Expression.TryCatch(
Expression.Block(
Expression.Assign(oracleDecimalParam, generator.MapExpression((OracleDecimal d, int p) => OracleDecimal.SetPrecision(d, p), oracleDecimalParam, precision)),
Expression.Assign(decimalVar, Expression.Convert(oracleDecimalParam, typeof(decimal))),
Expression.Break(label, decimalVar)),
Expression.Catch(
typeof(OverflowException),
Expression.Block(
Expression.IfThen(
Expression.LessThanOrEqual(Expression.SubtractAssign(precision, Expression.Constant(1)), Expression.Constant(26)),
Expression.Rethrow())))),
label));
body = generator.Build();
// workaround for mapper issue with complex reader expressions handling
// https://github.com/linq2db/linq2db/issues/2032
var compiledReader = Expression.Lambda(body, oracleDecimalParam).CompileExpression();
var readOracleDecimalToDecimalAdv = (Expression <Func <IDataReader, int, decimal> >)Expression.Lambda(
Expression.Invoke(
Expression.Constant(compiledReader),
readOracleDecimal.GetBody(rdParam, indexParam)),
rdParam,
indexParam);
var readOracleDecimalToInt = (Expression <Func <IDataReader, int, int> >)typeMapper.MapLambda <IDataReader, int, int>((rd, i) => (int)(decimal)OracleDecimal.SetPrecision(((OracleDataReader)rd).GetOracleDecimal(i), 27));
var readOracleDecimalToLong = (Expression <Func <IDataReader, int, long> >)typeMapper.MapLambda <IDataReader, int, long>((rd, i) => (long)(decimal)OracleDecimal.SetPrecision(((OracleDataReader)rd).GetOracleDecimal(i), 27));
var readOracleDecimalToDecimal = (Expression <Func <IDataReader, int, decimal> >)typeMapper.MapLambda <IDataReader, int, decimal>((rd, i) => (decimal)OracleDecimal.SetPrecision(((OracleDataReader)rd).GetOracleDecimal(i), 27));
return(new OracleProviderAdapter(
connectionType,
dataReaderType,
parameterType,
commandType,
transactionType,
mappingSchema,
oracleBFileType,
oracleBinaryType,
oracleBlobType,
oracleClobType,
oracleDateType,
oracleDecimalType,
oracleIntervalDSType,
oracleIntervalYMType,
oracleStringType,
oracleTimeStampType,
oracleTimeStampLTZType,
oracleTimeStampTZType,
oracleXmlTypeType,
oracleXmlStreamType,
oracleRefCursorType,
oracleRefType,
typeMapper.BuildWrappedFactory((string connectionString) => new OracleConnection(connectionString)),
typesNamespace,
dbTypeBuilder.BuildSetter <IDbDataParameter>(),
dbTypeBuilder.BuildGetter <IDbDataParameter>(),
connectionMapper.Member(c => c.HostName).BuildGetter <IDbConnection>(),
connectionMapper.Member(c => c.DatabaseName).BuildGetter <IDbConnection>(),
commandMapper.Member(p => p.BindByName).BuildSetter <IDbCommand>(),
commandMapper.Member(p => p.ArrayBindCount).BuildSetter <IDbCommand>(),
commandMapper.Member(p => p.InitialLONGFetchSize).BuildSetter <IDbCommand>(),
typeMapper.BuildFactory((DateTimeOffset dto, string offset) => new OracleTimeStampTZ(dto.Year, dto.Month, dto.Day, dto.Hour, dto.Minute, dto.Second, GetDateTimeOffsetNanoseconds(dto), offset)),
readDateTimeOffsetFromOracleTimeStampTZ,
readDateTimeOffsetFromOracleTimeStampLTZ,
readOracleDecimalToDecimalAdv,
readOracleDecimalToInt,
readOracleDecimalToLong,
readOracleDecimalToDecimal,
bulkCopy));
Type?loadType(string typeName, DataType dataType, bool optional = false, bool hasNull = true, bool hasValue = true, bool skipConvertExpression = false)
{
var type = assembly !.GetType($"{typesNamespace}.{typeName}", !optional);
if (type == null)
{
return(null);
}
if (hasNull)
{
// if native provider fails here, check that you have ODAC installed properly
var getNullValue = Expression.Lambda <Func <object> >(Expression.Convert(ExpressionHelper.Field(type, "Null"), typeof(object))).CompileExpression();
mappingSchema.AddScalarType(type, getNullValue(), true, dataType);
}
else
{
mappingSchema.AddScalarType(type, null, true, dataType);
}
if (skipConvertExpression)
{
return(type);
}
// conversion from provider-specific type
var valueParam = Expression.Parameter(type);
Expression memberExpression;
if (!hasValue)
{
memberExpression = valueParam;
}
else
{
memberExpression = ExpressionHelper.Property(valueParam, "Value");
}
var condition = Expression.Condition(
Expression.Equal(valueParam, ExpressionHelper.Field(type, "Null")),
Expression.Constant(null, typeof(object)),
Expression.Convert(memberExpression, typeof(object)));
var convertExpression = Expression.Lambda(condition, valueParam);
mappingSchema.SetConvertExpression(type, typeof(object), convertExpression);
return(type);
}
}
private object GetValue(OracleDecimal number, OracleDate date, OracleTimeStamp timestamp, OracleTimeStampTZ timestampWithTimezone, OracleTimeStampLTZ timestampWithLocalTimeZone, OracleIntervalYM yearToMonth, OracleIntervalDS dayToSecond)
{
if (!LimitValuesAvailable)
{
return null;
}
if (_valueType == typeof(OracleDecimal))
{
return number.IsNull ? null : new OracleNumber(number);
}
if (_valueType == typeof(OracleDate))
{
return date.IsNull ? null : new OracleDateTime(date);
}
if (_valueType == typeof(OracleTimeStamp))
{
return timestamp.IsNull ? null : new OracleTimestamp(timestamp);
}
if (_valueType == typeof(OracleTimeStampTZ))
{
return timestampWithTimezone.IsNull ? null : new OracleTimestampWithTimeZone(timestampWithTimezone);
}
if (_valueType == typeof(OracleTimeStampLTZ))
{
return timestampWithLocalTimeZone.IsNull ? null : new OracleTimestampWithLocalTimeZone(timestampWithLocalTimeZone);
}
if (_valueType == typeof(OracleIntervalYM))
{
return yearToMonth.IsNull ? null : new OracleIntervalYearToMonth(yearToMonth);
}
if (_valueType == typeof(OracleIntervalDS))
{
return dayToSecond.IsNull ? null : new OracleIntervalDayToSecond(dayToSecond);
}
return null;
}
private decimal GetDecimal(OracleDataReader reader, int index, PropertyInfo property)
{
return((decimal)(OracleDecimal.SetPrecision(reader.GetOracleDecimal(index), PrecisionCache.Instance[property])));
}
public static OracleDecimal SetPrecision(OracleDecimal value1, int precision) => OracleDecimal.Instance;
/// <summary>
/// Write query results to json string or file.
/// </summary>
/// <param name="command"></param>
/// <param name="queryOutput"></param>
/// <param name="cancellationToken"></param>
/// <returns>String</returns>
public static async Task <string> MultiQueryToCSVAsync(this OracleCommand command, QueryOutputProperties queryOutput, CancellationToken cancellationToken)
{
using (var reader = await command.ExecuteReaderAsync(cancellationToken) as OracleDataReader)
{
using (var writer = new StringWriter())
{
var headerWritten = false;
while (await reader.ReadAsync(cancellationToken))
{
// Initiate string builder for the line.
var sb = new StringBuilder();
// write csv header if necessary
if (!headerWritten && queryOutput.CsvOutput.IncludeHeaders)
{
var fieldNames = new object[reader.FieldCount];
for (var i = 0; i < reader.FieldCount; i++)
{
var fieldName = reader.GetName(i);
sb.Append(fieldName);
if (i < reader.FieldCount - 1)
{
sb.Append(queryOutput.CsvOutput.CsvSeparator);
}
cancellationToken.ThrowIfCancellationRequested();
}
await writer.WriteLineAsync(sb.ToString());
headerWritten = true;
}
sb = new StringBuilder();
var fieldValues = new object[reader.FieldCount];
for (var i = 0; i < reader.FieldCount; i++)
{
if (reader.GetDataTypeName(i).Equals("Decimal"))
{
var v = reader.GetOracleDecimal(i);
fieldValues[i] = OracleDecimal.SetPrecision(v, 28);
}
else
{
fieldValues[i] = reader.GetValue(i);
}
var fieldValue = fieldValues[i].ToString();
sb.Append(fieldValue);
if (i < reader.FieldCount - 1)
{
sb.Append(queryOutput.CsvOutput.CsvSeparator);
}
cancellationToken.ThrowIfCancellationRequested();
}
await writer.WriteLineAsync(sb.ToString());
// Write only complete rows, but stop if process was terminated.
cancellationToken.ThrowIfCancellationRequested();
}
return(writer.ToString());
}
}
}
/// <summary>
/// Write query results to csv string or file.
/// </summary>
/// <param name="command"></param>
/// <param name="queryOutput"></param>
/// <param name="cancellationToken"></param>
/// <returns>String</returns>
public static async Task <string> ToXmlAsync(this OracleCommand command, QueryOutputProperties queryOutput, CancellationToken cancellationToken)
{
var encoding = string.IsNullOrWhiteSpace(queryOutput.OutputFile?.Encoding) ? Encoding.UTF8 : Encoding.GetEncoding(queryOutput.OutputFile.Encoding);
using (var writer = queryOutput.OutputToFile ? new StreamWriter(queryOutput.OutputFile.Path, false, encoding) : new StringWriter() as TextWriter)
using (var reader = await command.ExecuteReaderAsync(cancellationToken) as OracleDataReader)
{
using (var xmlWriter = XmlWriter.Create(writer, new XmlWriterSettings {
Async = true, Indent = true
}))
{
await xmlWriter.WriteStartDocumentAsync();
await xmlWriter.WriteStartElementAsync("", queryOutput.XmlOutput.RootElementName, "");
while (await reader.ReadAsync(cancellationToken))
{
// single row element container.
await xmlWriter.WriteStartElementAsync("", queryOutput.XmlOutput.RowElementName, "");
for (var i = 0; i < reader.FieldCount; i++)
{
switch (reader.GetDataTypeName(i))
{
case "Decimal":
var v = reader.GetOracleDecimal(i);
var decimalValue = OracleDecimal.SetPrecision(v, 28);
string decimalString = decimalValue.ToString();
// Is decimal separator overwrite value given and query result value is not null?
if (!string.IsNullOrWhiteSpace(queryOutput.XmlOutput.DecimalSeparator))
{
decimalString = decimalString
.Replace(".", queryOutput.XmlOutput.DecimalSeparator)
.Replace(",", queryOutput.XmlOutput.DecimalSeparator);
}
await xmlWriter.WriteElementStringAsync("", reader.GetName(i), "", decimalString);
break;
case "Date":
case "TimeStamp":
case "TimeStampLTZ":
case "TimeStampTZ":
string dateString = ParseOracleDate(reader,
i,
queryOutput.XmlOutput.DateTimeFomat);
await xmlWriter.WriteElementStringAsync("", reader.GetName(i), "", dateString);
break;
default:
await xmlWriter.WriteElementStringAsync("", reader.GetName(i), "", reader.GetValue(i).ToString());
break;
}
}
// Close single row element container.
await xmlWriter.WriteEndElementAsync();
// Write only complete elements, but stop if process was terminated.
cancellationToken.ThrowIfCancellationRequested();
}
await xmlWriter.WriteEndElementAsync();
await xmlWriter.WriteEndDocumentAsync();
}
if (queryOutput.OutputToFile)
{
return(queryOutput.OutputFile.Path);
}
else
{
return(writer.ToString());
}
}
}
/// <summary>
/// Write query results to json string or file.
/// </summary>
/// <param name="command"></param>
/// <param name="queryOutput"></param>
/// <param name="cancellationToken"></param>
/// <returns>JToken</returns>
public static async Task <object> MultiQueryToJsonAsync(this OracleCommand command, QueryOutputProperties queryOutput, CancellationToken cancellationToken)
{
using (var reader = await command.ExecuteReaderAsync(cancellationToken) as OracleDataReader)
{
var culture = string.IsNullOrWhiteSpace(queryOutput.JsonOutput.CultureInfo) ? CultureInfo.InvariantCulture : new CultureInfo(queryOutput.JsonOutput.CultureInfo);
// UTF-8 as default encoding.
var encoding = string.IsNullOrWhiteSpace(queryOutput.OutputFile?.Encoding) ? Encoding.UTF8 : Encoding.GetEncoding(queryOutput.OutputFile.Encoding);
// Create json result.
using (var writer = new JTokenWriter() as JsonWriter)
{
writer.Formatting = Newtonsoft.Json.Formatting.Indented;
writer.Culture = culture;
// Start array.
await writer.WriteStartArrayAsync(cancellationToken);
while (reader.Read())
{
// Start row object.
await writer.WriteStartObjectAsync(cancellationToken);
for (var i = 0; i < reader.FieldCount; i++)
{
// Add row element name.
await writer.WritePropertyNameAsync(reader.GetName(i), cancellationToken);
// Add row element value.
switch (reader.GetDataTypeName(i))
{
case "Decimal":
// FCOM-204 fix; proper handling of decimal values and NULL values in decimal type fields.
var v = reader.GetOracleDecimal(i);
var FieldValue = OracleDecimal.SetPrecision(v, 28);
if (!FieldValue.IsNull)
{
await writer.WriteValueAsync((decimal)FieldValue, cancellationToken);
}
else
{
await writer.WriteValueAsync(string.Empty, cancellationToken);
}
break;
default:
await writer.WriteValueAsync(reader.GetValue(i) ?? string.Empty, cancellationToken);
break;
}
cancellationToken.ThrowIfCancellationRequested();
}
// End row object.
await writer.WriteEndObjectAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
}
// End array.
await writer.WriteEndArrayAsync(cancellationToken);
return(((JTokenWriter)writer).Token);
}
}
}
/// <summary>
/// Write query results to csv string or file.
/// </summary>
/// <param name="command"></param>
/// <param name="queryOutput"></param>
/// <param name="cancellationToken"></param>
/// <returns>String</returns>
public static async Task <string> ToCsvAsync(this OracleCommand command, QueryOutputProperties queryOutput, CancellationToken cancellationToken)
{
var encoding = string.IsNullOrWhiteSpace(queryOutput.OutputFile?.Encoding) ? Encoding.UTF8 : Encoding.GetEncoding(queryOutput.OutputFile.Encoding);
using (var reader = await command.ExecuteReaderAsync(cancellationToken) as OracleDataReader)
using (var w = queryOutput.OutputToFile ? new StreamWriter(queryOutput.OutputFile.Path, false, encoding) : new StringWriter() as TextWriter)
{
var headerWritten = false;
while (await reader.ReadAsync(cancellationToken))
{
// Initiate string builder for the line.
var sb = new StringBuilder();
// Write csv header if necessary.
if (!headerWritten && queryOutput.CsvOutput.IncludeHeaders)
{
var fieldNames = new object[reader.FieldCount];
for (var i = 0; i < reader.FieldCount; i++)
{
var fieldName = reader.GetName(i);
sb.Append(fieldName);
if (i < reader.FieldCount - 1)
{
sb.Append(queryOutput.CsvOutput.CsvSeparator);
}
cancellationToken.ThrowIfCancellationRequested();
}
await w.WriteLineAsync(sb.ToString());
headerWritten = true;
}
sb = new StringBuilder();
var fieldValues = new object[reader.FieldCount];
for (var i = 0; i < reader.FieldCount; i++)
{
switch (reader.GetDataTypeName(i))
{
case "Decimal":
var v = reader.GetOracleDecimal(i);
var decimalValue = OracleDecimal.SetPrecision(v, 28);
// Is decimal separator overwrite value given and query result value is not null?
if (!string.IsNullOrWhiteSpace(queryOutput.CsvOutput.DecimalSeparator) && !decimalValue.IsNull)
{
fieldValues[i] = decimalValue.ToString()
.Replace(".", queryOutput.CsvOutput.DecimalSeparator)
.Replace(",", queryOutput.CsvOutput.DecimalSeparator);
}
else
{
fieldValues[i] = decimalValue;
}
break;
case "Date":
case "TimeStamp":
case "TimeStampLTZ":
case "TimeStampTZ":
var dateString = ParseOracleDate(reader, i, queryOutput.CsvOutput.DateTimeFomat);
fieldValues[i] = dateString;
break;
default:
fieldValues[i] = reader.GetValue(i);
break;
}
var fieldValue = fieldValues[i].ToString();
// Remove possible line breaks.
fieldValue = fieldValue.Replace("\r\n", " ").Replace("\n", " ").Replace("\r", " ");
sb.Append(fieldValue);
if (i < reader.FieldCount - 1)
{
sb.Append(queryOutput.CsvOutput.CsvSeparator);
}
cancellationToken.ThrowIfCancellationRequested();
}
await w.WriteLineAsync(sb.ToString());
}
if (queryOutput.OutputToFile)
{
return(queryOutput.OutputFile.Path);
}
else
{
return(w.ToString());
}
}
}
private void SetIndeterminate()
{
AggregatedValuesAvailable = false;
LimitValuesAvailable = false;
_oracleNumberSum = OracleDecimal.Zero;
_oracleYearToMonthSum = OracleIntervalYM.Zero;
_oracleDayToSecondSum = OracleIntervalDS.Zero;
_typeValues.Clear();
_isIndeterminate = true;
}
public static void TextWrite(this TextWriter writer, OracleDecimal v)
{
writer.Write(v.Value);
}
/// <summary>
/// 取下一条要传送的报文
/// </summary>
/// <returns></returns>
public static MTSMessage GetNextMsg(string DeployID, ref string TargetAddr)
{
TargetAddr = "";
try
{
SqlParameter[] _param =
{
new SqlParameter("strdeployid", SqlDbType.NVarChar),
//new SqlParameter("strmsgguid",SqlDbType.NVarChar,ParameterDirection.Output),
//new SqlParameter("strtargetserviceip",SqlDbType.NVarChar, ParameterDirection.Output),
//new SqlParameter("nret",SqlDbType.Decimal, ParameterDirection.Output),
//new SqlParameter("strerr",SqlDbType.NVarChar, ParameterDirection.Output)
};
_param[0].Value = DeployID;
_param[1].Size = 50;
_param[2].Size = 1000;
_param[4].Size = 40000;
SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringProfile, CommandType.StoredProcedure, SQL_GetNextMTSMessage, _param);
decimal _ret = (((OracleDecimal)_param[3].Value).IsNull) ? (decimal)1 : (decimal)(OracleDecimal.SetPrecision((OracleDecimal)_param[3].Value, 28));
if (_ret > 0)
{
throw new Exception(string.Format("取待发报文失败! {0}", (_param[4].Value == DBNull.Value) ? "" : _param[4].Value.ToString()));
}
else
{
string _id = (((OracleString)_param[1].Value).IsNull) ? "" : _param[1].Value.ToString();
TargetAddr = (((OracleString)_param[2].Value).IsNull) ? "" : _param[2].Value.ToString();
if (_id == null || _id == "")
{
return(null);
}
else
{
//取报文内容
MTSMessage _retMsg = GetMTSMessgeByID(_id);
return(_retMsg);
}
}
}
catch (Exception ex)
{
LogWriter.WriteSystemLog(ex.Message, "ERROR");
throw ex;
}
}
public OracleNumber(OracleDecimal value) => _oracleDecimal = SetOutputFormat(value);
public override Decimal GetDecimal(object o, int index)
{
return(OracleDecimal.SetPrecision(_dataReader.GetOracleDecimal(Index), 28).Value);
}
public static OracleDecimal SetPrecision(OracleDecimal value1, int precision) => throw new NotImplementedException();
public override Decimal?GetNullableDecimal(object o, int index)
{
return(_dataReader.IsDBNull(index) ? (decimal?)null : OracleDecimal.SetPrecision(_dataReader.GetOracleDecimal(Index), 28).Value);
}
public OracleDecimalField(OracleDecimal oracleDecimal)
{
_oracleDecimal = oracleDecimal;
}
public void AddValue(object value)
{
if (value == null || value == DBNull.Value)
{
return;
}
var oracleValue = value as IValue;
if (oracleValue != null && oracleValue.IsNull)
{
return;
}
Type valueType;
var oracleNumber = value as OracleNumber;
if (value is int || value is long || value is decimal || value is short || value is uint || value is ulong || value is ushort)
{
value = oracleNumber = new OracleNumber(new OracleDecimal(Convert.ToDecimal(value)));
}
var oracleDate = value as OracleDateTime;
if (value is DateTime)
{
value = oracleDate = new OracleDateTime(new OracleDate(Convert.ToDateTime(value)));
}
Count++;
_distinctValues.Add(value);
var oracleTimestamp = value as OracleTimestamp;
var oracleTimestampTimezone = value as OracleTimestampWithTimeZone;
var oracleTimestampLocalTimezone = value as OracleTimestampWithLocalTimeZone;
var oracleIntervalYearToMonth = value as OracleIntervalYearToMonth;
var oracleIntervalDayToSecond = value as OracleIntervalDayToSecond;
if (oracleNumber != null)
{
valueType = typeof(OracleDecimal);
var typedValue = (OracleDecimal)oracleNumber.RawValue;
_oracleNumberSum += typedValue;
}
else if (oracleDate != null)
{
valueType = typeof(OracleDate);
AggregatedValuesAvailable = false;
}
else if (oracleTimestamp != null)
{
valueType = typeof(OracleTimeStamp);
AggregatedValuesAvailable = false;
}
else if (oracleTimestampTimezone != null)
{
valueType = typeof(OracleTimeStampTZ);
AggregatedValuesAvailable = false;
}
else if (oracleTimestampLocalTimezone != null)
{
valueType = typeof(OracleTimeStampLTZ);
AggregatedValuesAvailable = false;
}
else if (oracleIntervalYearToMonth != null)
{
valueType = typeof(OracleIntervalYM);
var typedValue = (OracleIntervalYM)oracleIntervalYearToMonth.RawValue;
_oracleYearToMonthSum += typedValue;
}
else if (oracleIntervalDayToSecond != null)
{
valueType = typeof(OracleIntervalDS);
var typedValue = (OracleIntervalDS)oracleIntervalDayToSecond.RawValue;
_oracleDayToSecondSum += typedValue;
}
else
{
valueType = typeof(object);
AggregatedValuesAvailable = false;
LimitValuesAvailable = false;
}
if (LimitValuesAvailable)
{
_typeValues.Add(Tuple.Create(valueType, value));
}
if (_valueType == null)
{
_valueType = valueType;
}
else if (_valueType != valueType)
{
SetIndeterminate();
}
}