private void button16_Click(object sender, EventArgs e)
{
string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;";
string _sql;
OracleDataReader _rdrObj;
try
{
OracleConnection _connObj = new OracleConnection(_connstring);
DataSet _ds = new DataSet();
_connObj.Open();
_sql = "SELECT GUID FROM GUIDTest";
OracleCommand _cmdObj = new OracleCommand(_sql, _connObj);
_rdrObj = _cmdObj.ExecuteReader();
if (_rdrObj.HasRows)
{
if (_rdrObj.Read())
{
OracleBinary _binaryObj = _rdrObj.GetOracleBinary(_rdrObj.GetOrdinal("GUID"));
System.Guid _GUIDObj = new System.Guid(_binaryObj.Value);
MessageBox.Show("The GUID retrieved is: " + _GUIDObj.ToString());
}
}
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
protected void obtienePDF(object sender, EventArgs e)
{
var dataTable = new DataTable();
UtilesOracle cv = new UtilesOracle();
Button lnk = sender as Button;
Button btn = (Button)sender;
GridViewRow rowUno = (GridViewRow)btn.NamingContainer;
// Response.Write(GridView1.DataKeys[rowUno.RowIndex].Value.ToString());
String valorID = GridView1.DataKeys[rowUno.RowIndex].Value.ToString();//lnk.Attributes["CommandArgument"];
//ID_PADRE,NOMBRE_ADJUNTO,CONTENTTYPE,THEDATA
DataTable curriculum = cv.obtieneCV(valorID);
foreach (DataRow row in curriculum.Rows)
{
OracleBinary obj = (byte[])row["THEDATA"];
byte[] bytes = obj.Value;
Response.AddHeader("Content-Disposition", "attachment; filename=" + row["NOMBRE_ADJUNTO"].ToString());
Response.BinaryWrite(bytes);
// myMemoryStream.WriteTo(Response.OutputStream); //works too
Response.Flush();
Response.Close();
}
}
public void GetOracleBinary()
{
var bytes = Encoding.UTF8.GetBytes("Lorem ipsum");
var oracleBytes = new OracleBinary(bytes);
var result = OracleValueConverter.Convert <byte[]>(oracleBytes);
result.Should().Equal(bytes);
}
public bool UserAuthentication(string userName, string password)
{
this.userid = string.Empty;
string result = string.Empty;
try
{
ProcParam procPara = new ProcParam(3);
procPara.ProcedureName = "ADMINISTRATOR_PACK.USER_LOGON";
procPara.AddParamReturn(0, "ReturnValue", OracleDbType.Raw, 255);
procPara.AddParamInput(1, "strVal", userName);
procPara.AddParamInput(2, "strPwd", password);
GlobalDB.Instance.DataAc.ExecuteNonQuery(procPara);
////this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
//OracleString objResult = (OracleString)procPara.Parameters[0].Value;
OracleBinary objResult = (OracleBinary)procPara.ReturnValue(0);
//byte[] resultByte = this.GetCurrectByteArray(objResult);
//result = System.Text.Encoding.Default.GetString(resultByte, 0, resultByte.Length);
if (!objResult.IsNull)
{
result = Encoding.Default.GetString(objResult.Value, 0, objResult.Length);
}
else
{
result = "";
}
}
catch (Exception ex)
{
throw ex;
}
if (result != "")
{
//Authentication pass
this.userid = result;
return(true);
}
else
{
//Authentication fail
return(false);
}
}
public OracleRawValue(OracleBinary value)
{
_oracleBinary = value;
if (value.IsNull)
{
_preview = String.Empty;
Value = new byte[0];
}
else
{
_preview = _oracleBinary.Value.ToHexString();
Value = _oracleBinary.Value;
}
}
private byte[] GetCurrectByteArray(OracleBinary oraValue)
{
string test = string.Empty;
int accLength = oraValue.Value.Length / 2;
byte[] result = new byte[accLength];
int j = 0;
for (int i = 0; i < oraValue.Value.Length; i++)
{
if (!oraValue.Value[i].Equals(0))
{
result[j] = oraValue.Value[i];
j++;
}
}
return(result);
}
public string FGPress_CheckValidationUser(string userid)
{
string result = string.Empty;
try
{
ProcParam procPara = new ProcParam(2)
{
ProcedureName = "SCANNER_PACK_FG_PRESS.USER_VALIDATION"
};
procPara.AddParamReturn(0, "ReturnValue", OracleDbType.Raw, 255);
procPara.AddParamInput(1, "strUSER_ID", userid);
GlobalDB.Instance.DataAc.ExecuteNonQuery(procPara);
if (GlobalDB.Instance.LastException != null)
{
throw GlobalDB.Instance.LastException;
}
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
//OracleString objResult = (OracleString)procPara.ReturnValue(0);
OracleBinary objResult = (OracleBinary)procPara.ReturnValue(0);
if (!objResult.IsNull)
{
result = UtilityBLL.GetReturnRawData(objResult);
}
else
{
result = string.Empty;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
private void button2_Click(object sender, EventArgs e)
{
string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;";
try
{
OracleConnection _connObj = new OracleConnection(_connstring);
// Create a new queue object
OracleAQQueue _queueObj = new OracleAQQueue("EDZEHOO.MY_JOBS_QUEUE", _connObj);
_connObj.Open();
OracleTransaction _txn = _connObj.BeginTransaction();
_queueObj.DequeueOptions.Visibility = OracleAQVisibilityMode.OnCommit;
_queueObj.DequeueOptions.Wait = 10;
_queueObj.DequeueOptions.ProviderSpecificType = true;
// Dequeue the messages – take note that you can specify the number of
// messages you wish to retrieve from the queue
OracleAQMessage[] _deqMsgs = _queueObj.DequeueArray(2);
for (int i = 0; i < _deqMsgs.Length; i++)
{
// If you enqueued a byte array, the dequeued object is an
// OracleBinary object. You can retrieve the byte array using the
// OracleBinary.Value property
OracleBinary _payload = (OracleBinary)_deqMsgs[i].Payload;
MessageBox.Show("Dequeued Payload Data: " +
ConvertFromByteArray(_payload.Value) + "\n"
+ "Dequeued Payload Hex: " +
ConvertToHexString(_payload.Value) + "\n");
}
_txn.Commit();
_queueObj.Dispose();
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public static OracleBinary Concat(OracleBinary x, OracleBinary y)
{
}
public static OracleBinary Concat(OracleBinary x, OracleBinary y) {}
public static string GetReturnRawData(OracleBinary oraValue)
{
string resultString = System.Text.Encoding.Default.GetString(oraValue.Value, 0, oraValue.Value.Length);
return(resultString);
}
public static OracleBoolean GreaterThan(OracleBinary x, OracleBinary y) {}
public static OracleBinary op_Addition(OracleBinary x, OracleBinary y) {}
public static OracleBinary op_Addition(OracleBinary x, OracleBinary y)
{
}
public static OracleBoolean NotEquals(OracleBinary x, OracleBinary y)
{
}
public static OracleBoolean LessThan(OracleBinary x, OracleBinary y) {}
public static OracleBoolean NotEquals(OracleBinary x, OracleBinary y) {}
/// <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);
}
public static OracleBoolean op_Inequality(OracleBinary x, OracleBinary y) {}
public static OracleBoolean op_LessThanOrEqual(OracleBinary x, OracleBinary y) {}
public static OracleBoolean op_GreaterThanOrEqual(OracleBinary x, OracleBinary y) {}
public static OracleBoolean GreaterThan(OracleBinary x, OracleBinary y)
{
}
public static OracleBoolean LessThan(OracleBinary x, OracleBinary y)
{
}
/// <inheritdoc />
public async Task <TransportMessage> Receive(ITransactionContext context, CancellationToken cancellationToken)
{
OracleParameter headerKeysParam, headerValuesParam, bodyRawParam, bodyBlobParam;
var connection = await GetConnection(context);
TransportMessage receivedTransportMessage;
using (var command = connection.Connection.CreateCommand())
{
command.CommandText = "PKG_REBUS.P_Dequeue_Rebus_Msg";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new OracleParameter("p_Queue_Name", OracleDbType.Varchar2, _options.InputQueueName, ParameterDirection.Input));
var consumersNamesParam = command.Parameters.Add(new OracleParameter("p_DO_Consumers_Names", OracleDbType.Varchar2));
consumersNamesParam.Direction = System.Data.ParameterDirection.Input;
consumersNamesParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
if (_options.DequeueOptions.ConsumersNames != null && _options.DequeueOptions.ConsumersNames.Count > 0)
{
consumersNamesParam.Value = _options.DequeueOptions.ConsumersNames.ToArray();
consumersNamesParam.Size = _options.DequeueOptions.ConsumersNames.Count();
consumersNamesParam.ArrayBindSize = _options.DequeueOptions.ConsumersNames.Select(key => key.Length).ToArray();
consumersNamesParam.ArrayBindStatus = Enumerable.Repeat(OracleParameterStatus.Success, _options.DequeueOptions.ConsumersNames.Count()).ToArray();
}
else
{
consumersNamesParam.Value = DBNull.Value;
consumersNamesParam.Size = 0;
consumersNamesParam.ArrayBindSize = new int[] { 1 };
consumersNamesParam.ArrayBindStatus = Enumerable.Repeat(OracleParameterStatus.Success, 1).ToArray();
}
command.Parameters.Add(new OracleParameter("p_DO_Visibility", OracleDbType.Byte, GetVisibility(_options.DequeueOptions.Visibility), ParameterDirection.Input));
command.Parameters.Add(new OracleParameter("p_DO_Wait", OracleDbType.Int32, _options.DequeueOptions.Wait, ParameterDirection.Input));
command.Parameters.Add(new OracleParameter("p_DO_Transformation", OracleDbType.Varchar2, _options.DequeueOptions.Tranformation, ParameterDirection.Input));
command.Parameters.Add(new OracleParameter("p_DO_Delivery_Mode", OracleDbType.Byte, GetDequeueDeliveryMode(_options.DequeueOptions.DeliveryMode), ParameterDirection.Input));
command.Parameters.Add(headerKeysParam = new OracleParameter("p_Headers_Keys", OracleDbType.Varchar2, ParameterDirection.Output));
headerKeysParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
headerKeysParam.Size = 100;
headerKeysParam.ArrayBindSize = Enumerable.Repeat(4000, 100).ToArray();
headerKeysParam.ArrayBindStatus = Enumerable.Repeat(OracleParameterStatus.Success, 100).ToArray();
command.Parameters.Add(headerValuesParam = new OracleParameter("p_Headers_Values", OracleDbType.Varchar2, ParameterDirection.Output));
headerValuesParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
headerValuesParam.Size = 100;
headerValuesParam.ArrayBindSize = Enumerable.Repeat(4000, 100).ToArray();
headerValuesParam.ArrayBindStatus = Enumerable.Repeat(OracleParameterStatus.Success, 100).ToArray();
command.Parameters.Add(bodyRawParam = new OracleParameter("p_Body_Raw", OracleDbType.Raw, ParameterDirection.Output));
bodyRawParam.Size = 2000;
command.Parameters.Add(bodyBlobParam = new OracleParameter("p_Body_Blob", OracleDbType.Blob, ParameterDirection.Output));
command.InitialLOBFetchSize = -1;
command.InitialLONGFetchSize = -1;
try
{
/*We are not using the cancellationToken, bacause the driver is not implementing true async support yet. On cancel the process hangs */
await command.ExecuteNonQueryAsync();
Dictionary <string, string> header = new Dictionary <string, string>();
byte[] body;
OracleBinary bodyBinary = ((OracleBinary)bodyRawParam.Value);
OracleBlob bodyBlob = (OracleBlob)bodyBlobParam.Value;
if (bodyBinary.IsNull && bodyBlob.IsNull)
{
receivedTransportMessage = null;
}
else
{
OracleString[] headerKeys = (OracleString[])headerKeysParam.Value;
OracleString[] headerValues = (OracleString[])headerValuesParam.Value;
for (int i = 0; i < headerKeys.Length; i++)
{
if (string.IsNullOrEmpty(headerKeys[i].Value))
{
break;
}
header.Add(headerKeys[i].Value, headerValues[i].Value);
}
body = bodyBinary.IsNull ? bodyBlob.Value : bodyBinary.Value;
receivedTransportMessage = new TransportMessage(header, body);
}
}
catch (SqlException sqlException) when(sqlException.Number == OperationCancelledNumber)
{
// ADO.NET does not throw the right exception when the task gets cancelled - therefore we need to do this:
throw new TaskCanceledException("Receive operation was cancelled", sqlException);
}
}
return(receivedTransportMessage);
}
public static byte[] op_Explicit(OracleBinary x)
{
}
public OracleLongRawValue(OracleBinary binary)
{
RawValue = binary;
Value = binary.IsNull ? new byte[0] : binary.Value;
IsNull = binary.IsNull;
}
Guid ConvertToGuid(OracleBinary binaryValue)
{
return new Guid(binaryValue.Value);
}
public static byte[] op_Explicit(OracleBinary x) {}
