protected override void RunIteration(Stopwatch sw)
{
this.TruncateTables();
sw.Start();
List <decimal> idList = new List <decimal>(this._rowCount);
List <OracleClob> nclobList = new List <OracleClob>(this._rowCount);
for (int i = 0; i < this._rowCount; i++)
{
idList.Add(++this._id);
OracleClob c = new OracleClob(this._conn, false, true);
char[] ca = this._nclob.ToCharArray();
c.Write(ca, 0, ca.Length);
nclobList.Add(c);
}
_idParam.Value = idList.ToArray();
_nclobParam.Value = nclobList.ToArray();
this._savePosition.ArrayBindCount = this._rowCount;
this._savePosition.ExecuteNonQuery();
sw.Stop();
}
private void button2_Click(object sender, EventArgs e)
{
//We first read the full contents of the file into a byte array
string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;";
try
{
OracleConnection _connObj = new OracleConnection(_connstring);
OracleDataReader _rdrObj;
_connObj.Open();
OracleCommand _cmdObj = _connObj.CreateCommand();
_cmdObj.CommandText = "SELECT Remarks FROM ProductFiles WHERE ProductID=:ProductID";
_cmdObj.Parameters.Add(new OracleParameter("ProductID", txtProductID.Text));
_rdrObj = _cmdObj.ExecuteReader();
if (_rdrObj.HasRows)
{
if (_rdrObj.Read())
{
OracleClob _clobObj = _rdrObj.GetOracleClob(_rdrObj.GetOrdinal("Remarks"));
txtRemarks.Text = _clobObj.Value;
}
}
else
{
MessageBox.Show("An item with the matching product ID was not found!");
}
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public string GetTaxInterfaceBySLBH(string slbh)
{
OracleConnection connection = null;
string content = string.Empty;
int actual = 0;
try
{
connection = DBHelper.Connection;
connection.Open();
OracleCommand cmd = new OracleCommand("", connection);
cmd.CommandText = string.Format("Select XML From DJ_TaxInfo Where SLBH='{0}'", slbh);
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
OracleClob myOracleClob = reader.GetOracleClob(0);
StreamReader streamreader = new StreamReader(myOracleClob, Encoding.Unicode);
char[] cbuffer = new char[100];
while ((actual = streamreader.Read(cbuffer, 0, cbuffer.Length)) > 0)
{
content += new string(cbuffer, 0, actual);
}
break;
}
}
catch (Exception ex) {
throw ex;
}
finally {
connection.Close();
}
return(content);
}
/// <summary>
/// Fetch next log object for reader opened by Select method
/// </summary>
/// <param name="drd"></param>
/// <returns></returns>
public static UserObject Read(
DbCommandMx drd)
{
OracleDataReader dr = drd.OracleRdr;
if (!dr.Read())
{
return(null);
}
UserObject uo = new UserObject();
if (!dr.IsDBNull(0))
{
uo.Id = (int)dr.GetOracleDecimal(0);
}
if (!dr.IsDBNull(1))
{
uo.Type = (UserObjectType)(int)dr.GetOracleDecimal(1);
}
if (!dr.IsDBNull(2))
{
uo.Owner = dr.GetString(2);
}
if (!dr.IsDBNull(3))
{
uo.Name = dr.GetString(3);
}
if (!dr.IsDBNull(4))
{
uo.Description = dr.GetString(4);
}
if (!dr.IsDBNull(5))
{
uo.ParentFolderType = (FolderTypeEnum)(int)dr.GetOracleDecimal(5);
}
if (!dr.IsDBNull(6))
{
uo.ParentFolder = dr.GetString(6);
}
if (!dr.IsDBNull(7))
{
uo.AccessLevel = (UserObjectAccess)(int)dr.GetOracleDecimal(7);
}
if (!dr.IsDBNull(8))
{
uo.Count = (int)dr.GetOracleDecimal(8);
}
if (!dr.IsDBNull(9))
{
OracleClob ol = dr.GetOracleClob(9);
uo.Content = ol.Value.ToString();
}
if (!dr.IsDBNull(10))
{
uo.UpdateDateTime = dr.GetDateTime(10);
}
return(uo);
}
private void display_properties(OracleConnection con)
{
Console.WriteLine("Retrieving clob and displaying properties...");
// this method simply displays the properties
// and the values for the clob
string sql = "select clob_data from clob_test where clob_id = 1";
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataReader dataReader = cmd.ExecuteReader();
// read the 1 row result
dataReader.Read();
// use typed accessor that does not lock row
OracleClob clob = dataReader.GetOracleClob(0);
// display each property value
Console.WriteLine(" CanRead = " + clob.CanRead.ToString());
Console.WriteLine(" CanSeek = " + clob.CanSeek.ToString());
Console.WriteLine(" CanWrite = " + clob.CanWrite.ToString());
Console.WriteLine(" Connection = " + clob.Connection.ConnectionString);
Console.WriteLine(" IsEmpty = " + clob.IsEmpty.ToString());
Console.WriteLine(" IsInChunkWriteMode = " + clob.IsInChunkWriteMode.ToString());
Console.WriteLine(" IsNCLOB = " + clob.IsNClob.ToString());
Console.WriteLine(" IsTemporary = " + clob.IsTemporary.ToString());
Console.WriteLine(" Length = " + clob.Length.ToString());
Console.WriteLine(" OptimumChunkSize = " + clob.OptimumChunkSize.ToString());
Console.WriteLine(" Position = " + clob.Position.ToString());
Console.WriteLine(" Value = " + clob.Value);
Console.WriteLine("Completed displaying properties...");
Console.WriteLine();
}
public string ObtenerValor(int pcod_cliente_N)
{
List <OracleParameter> lst = new List <OracleParameter>();
string res = string.Empty;
try
{
// add parameters
OracleParameter param;
param = new OracleParameter("pcod_cliente_n", OracleDbType.Int32);
param.Value = 10;
lst.Add(param);
OracleClob data = (OracleClob)MyOracleUtils.execOracleSf2("pckTest.ObtValor3", lst, OracleDbType.Clob, this.conn);
res = (string)data.Value;
}
catch (Exception)
{
throw;
}
return(res);
}
public string Reader(string commandText)
{
string readerXX = "";
int actual = 0;
Cmd.CommandText = commandText;
var reader = Cmd.ExecuteReader();
try
{
while (reader.Read())
{
OracleClob myOracleClob = reader.GetOracleClob(0);
StreamReader streamreader = new StreamReader(myOracleClob, Encoding.Unicode);
char[] cbuffer = new char[10000];
while ((actual = streamreader.Read(cbuffer, 0, cbuffer.Length)) > 0)
{
readerXX = new string(cbuffer, 0, actual);
}
}
}
catch (Exception e)
{
return("");
}
return(readerXX);
}
public string GetXdbResourceText()
{
logger.Debug("Getting XDB resource " + XdbResourceName);
string sql = "select xdburitype(:b1).getClob() from dual";
OracleCommand cmd = new OracleCommand(sql, _conn);
OracleParameter p = cmd.Parameters.Add("b1", OracleDbType.Varchar2, 2000, XdbResourceName, ParameterDirection.Input);
logger.Debug("Executing SQL: " + cmd.CommandText);
try
{
OracleDataReader dr = cmd.ExecuteReader();
string s = "";
while (dr.Read())
{
OracleClob clob = dr.GetOracleClob(0);
s = (string)clob.Value;
}
_lastError = "";
return(s);
}
catch (OracleException e)
{
logger.Error("Command failed: " + e.Message);
_lastError = e.Message;
return("");
}
}
private void button2_Click(object sender, EventArgs e)
{
string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;";
int _recordsAffected;
try
{
OracleConnection _connObj = new OracleConnection(_connstring);
_connObj.Open();
OracleCommand _cmdObj = _connObj.CreateCommand();
_cmdObj.CommandText = "INSERT INTO ProductFiles(ProductID, Remarks) VALUES(:ProductID,:Remarks)";
_cmdObj.Parameters.Add(new OracleParameter("ProductID", txtProductID.Text));
OracleClob _clobObj = new OracleClob(_connObj);
_clobObj.Write(txtRemarks.Text.ToCharArray(), 0, txtRemarks.Text.Length);
_cmdObj.Parameters.Add(new OracleParameter("Remarks", _clobObj));
_recordsAffected = _cmdObj.ExecuteNonQuery();
if (_recordsAffected == 1)
{
MessageBox.Show("CLOB saved!");
}
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// 更新大文本字符串保存到数据库
/// 王方圆添加 2017-5-16
/// </summary>
/// <param name="tableName">表明</param>
/// <param name="id">唯一标识ID</param>
/// <param name="longTextFieldName">字段名称</param>
/// <param name="longTextContent">更新文本内容</param>
public static void UpdateLongText(string tableName, long id, string longTextFieldName, string longTextContent)
{
string connectStr = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultDB"].ConnectionString;
using (Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connectStr))
{
conn.Open();
//OracleTransaction trans = conn.BeginTransaction();
Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand();
//cmd.Transaction = trans;
cmd.CommandText = "declare xx clob; begin dbms_lob.createtemporary(xx, false, 0); :templob := xx; end;";
cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter(":templob", Oracle.DataAccess.Client.OracleDbType.Clob)).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
OracleClob tmplob = (OracleClob)cmd.Parameters[0].Value;
byte[] buffer = System.Text.Encoding.Unicode.GetBytes(longTextContent);
tmplob.Write(buffer, 0, buffer.Length);
tmplob.Position = 0;
cmd.Parameters.Clear();
string cmdText = "update {0} set {1} = :lob where EMAIL_ID= :id";
cmdText = string.Format(cmdText, tableName, longTextFieldName);
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter(":lob", Oracle.DataAccess.Client.OracleDbType.Clob)).Value = tmplob;
cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter(":id", id));
cmd.ExecuteNonQuery();
}
}
private static void AssignParameters(OracleCommand comm, params object[] values)
{
try
{
DiscoveryParameters(comm);
// assign value
var index = 0;
foreach (OracleParameter param in comm.Parameters)
{
if (param.OracleDbType == OracleDbType.RefCursor)
{
param.Direction = ParameterDirection.Output;
}
else if (param.Direction == ParameterDirection.Input || param.Direction == ParameterDirection.InputOutput)
{
if (values[index] == null || (values[index] is string && (string)values[index] == string.Empty))
{
param.Value = DBNull.Value;
}
else if (param.OracleDbType == OracleDbType.NClob)
{
var lob = new OracleClob(comm.Connection);
var buffer = Encoding.Unicode.GetBytes(values[index].ToString());
lob.Write(buffer, 0, buffer.Length);
param.Value = lob;
}
else
{
switch (param.OracleDbType)
{
case OracleDbType.Date:
//param.Value = Convert.ToDateTime(values[index], Culture);
//break;
case OracleDbType.Byte:
case OracleDbType.Int16:
case OracleDbType.Int32:
case OracleDbType.Int64:
case OracleDbType.Single:
case OracleDbType.Double:
case OracleDbType.Decimal:
//param.Value = Convert.ToDecimal(values[index], App.Environment.ServerInfo.Culture);
//break;
default:
param.Value = values[index];
break;
}
}
index++;
}
}
}
catch (Exception ex)
{
//throw ErrorUtils.CreateErrorWithSubMessage(
// ERR_SQL.ERR_SQL_ASSIGN_PARAMS_FAIL, ex.Message,
// comm.CommandText, values);
}
}
/// <summary>
/// Select an Oracle Clob value
/// </summary>
/// <param name="table"></param>
/// <param name="matchCol"></param>
/// <param name="typeCol"></param>
/// <param name="contentCol"></param>
/// <param name="matchVal"></param>
/// <param name="typeVal"></param>
/// <param name="clobString"></param>
public static void SelectOracleClob(
string table,
string matchCol,
string typeCol,
string contentCol,
string matchVal,
out string typeVal,
out string clobString)
{
typeVal = null;
clobString = null;
string sql =
"select " + typeCol + ", " + contentCol + " " +
"from " + table + " " +
"where " + matchCol + " = :0";
DbCommandMx drd = new DbCommandMx();
drd.PrepareMultipleParameter(sql, 1);
for (int step = 1; step <= 2; step++) // try two different forms of matchVal
{
if (step == 2) // try alternate form of spaces
{
if (matchVal.Contains("%20"))
{
matchVal = matchVal.Replace("%20", " "); // convert html spaces to regular spaces
}
else
{
matchVal = matchVal.Replace(" ", "%20"); // convert regular spaces to html spaces
}
}
drd.ExecuteReader(matchVal);
if (!drd.Read())
{
continue;
}
typeVal = drd.GetString(0);
if (drd.Rdr.IsDBNull(1))
{
break;
}
OracleClob oc = drd.OracleRdr.GetOracleClob(1);
if (oc != null && oc.Length >= 0)
{
clobString = oc.Value;
}
break; // have value
}
drd.Dispose();
return;
}
private void EnsureLobIsNotNull()
{
if (lob != null)
{
return;
}
lob = new OracleClob(connection, false, true);
}
private void FreeLob()
{
if (lob == null)
{
return;
}
lob.Close();
lob.Dispose();
lob = null;
}
protected override void FromOracleParamInternal(OracleParameter param)
{
if (IsNull(param.Value) == true)
{
ParamValue = null;
}
else
{
OracleClob clob = (OracleClob)param.Value;
ParamValue = clob.Value;
}
}
/// <summary>
/// The main entry point for the application.
/// </summary>
static void Main(string[] args)
{
// Connect
string constr = "User Id=scott;Password=tiger;Data Source=oracle";
OracleConnection con = Connect(constr);
// Setup
Setup(con);
OracleTransaction txn = con.BeginTransaction();
OracleCommand cmd = new OracleCommand("", con);
try
{
// Select the LOB with the primary key
// The primary key will be used for row-level locking
cmd.CommandText = "select STORY, THEKEY from multimedia_tab where THEKEY = 1";
OracleDataReader reader = cmd.ExecuteReader();
reader.Read();
OracleClob clob = reader.GetOracleClobForUpdate(0); // Lock the row
Console.WriteLine("Old Data: {0}", clob.Value);
// Modify the CLOB column of the row
string ending = " The end.";
clob.Append(ending.ToCharArray(), 0, ending.Length);
// Release the lock
txn.Commit();
// Fetch the new data; transaction or locking not required.
cmd.CommandText = "select STORY from multimedia_tab where THEKEY = 1";
reader = cmd.ExecuteReader();
reader.Read();
clob = reader.GetOracleClob(0);
Console.WriteLine("New Data: {0}", clob.Value);
}
catch (Exception e)
{
Console.WriteLine("Error: {0}", e.Message);
}
finally
{
// Dispose OracleCommand object
cmd.Dispose();
// Close and Dispose OracleConnection object
con.Close();
con.Dispose();
}
}
public static OracleClob StringToOracleClob(string config, string key)
{
OracleConnection conn = null;
OracleClob clob = null;
try
{
conn = new OracleConnection(config);
conn.Open();
clob = new OracleClob(conn);
if (string.IsNullOrEmpty(key))
{
char[] writeBuffer = string.Empty.ToCharArray();
clob.Write(writeBuffer, 0, writeBuffer.Length);
}
else
{
char[] writeBuffer = key.ToCharArray();
clob.Write(writeBuffer, 0, writeBuffer.Length);
}
}
catch (OracleException ex)
{
string message = ex.Message;
throw new Exception(message);
}
finally
{
if (clob != null)
{
if (!clob.IsEmpty || !clob.IsNull)
{
clob.Close();
}
//clob.Dispose();
}
if (conn != null)
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
//conn.Dispose();
}
}
return(clob);
}
private IEnumerable <AUDIT_LOG_DB> ConvertToTankReadings(DataTable dataTable)
{
foreach (DataRow row in dataTable.Rows)
{
OracleTimeStamp?ocLOG_TIME = string.IsNullOrEmpty(row["LOG_TIME"].ToString()) ? OracleTimeStamp.Null : (OracleTimeStamp)(row["LOG_TIME"]);
OracleClob ocERR_LOG_CONTENT = string.IsNullOrEmpty(row["ERR_LOG_CONTENT"].ToString()) ? OracleClob.Null : (OracleClob)(row["ERR_LOG_CONTENT"]);
yield return(new AUDIT_LOG_DB
{
NO = Convert.ToDecimal(row["NO"].ToString()),
MSGID = Convert.ToString(row["MSGID"]),
LOG_TIME = ocLOG_TIME,
ERR_LOG_CONTENT = ocERR_LOG_CONTENT
});
}
}
/// <summary>
/// DB 쿼리 실행.(CLOB 데이터 입력 용)
/// BeginTransaction에 의한 관리 외의 경우, 자동으로 COMMIT 합니다.
/// </summary>
/// <param name="querySelectForUpdate"></param>
/// <param name="clobData"></param>
/// <returns>쿼리 실행 결과 데이터</returns>
public QueryResultDataInfo ExecuteQueryForClob(string querySelectForUpdate, string clobData)
{
QueryResultDataInfo resultData = null;
OracleDataReader reader = null;
try
{
this.oracleDB.QueryData(querySelectForUpdate, out reader);
if (reader == null)
{
return(null);
}
resultData = new QueryResultDataInfo();
resultData.AffectedRecordCnt = reader.RecordsAffected;
reader.Read();
OracleClob clob = reader.GetOracleClob(1);
clob.Erase();
Encoding utf16Encoding = Encoding.GetEncoding("utf-16");
byte[] clobDataByteArray = utf16Encoding.GetBytes(clobData);
clob.Write(clobDataByteArray, 0, clobDataByteArray.Length);
clob.Close();
clob.Dispose();
clob = null;
}
catch (Exception ex)
{
System.Console.WriteLine("[DBConnector] ExecuteQueryForClob( " + ex.ToString() + " )");
FileLogManager.GetInstance().WriteLog("[DBConnector] ExecuteQueryForClob( Exception=[" + ex.ToString() + "] )");
return(null);
}
finally
{
if (reader != null)
{
reader.Close();
reader.Dispose();
reader = null;
}
}
return(resultData);
}
public void AddParameter(IDbCommand command, string name)
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
// accesing the connection in open state.
if (command.Connection is OracleConnection)
{
var clob = new OracleClob(command.Connection as OracleConnection);
// It should be Unicode oracle throws an exception when
// the length is not even.
var bytes = System.Text.Encoding.Unicode.GetBytes(value);
var length = System.Text.Encoding.Unicode.GetByteCount(value);
int pos = 0;
int chunkSize = 1024; // Oracle does not allow large chunks.
while (pos < length)
{
chunkSize = chunkSize > (length - pos) ? chunkSize = length - pos : chunkSize;
clob.Write(bytes, pos, chunkSize);
pos += chunkSize;
}
var param = new OracleParameter(name, OracleDbType.Clob);
param.Value = clob;
command.Parameters.Add(param);
return;
}
//else if (command.Connection is System.Data.OracleClient.OracleConnection)
//{
// var clob = value;
// var param = new System.Data.OracleClient.OracleParameter(name, System.Data.OracleClient.OracleType.Clob);
// param.Value = clob;
// command.Parameters.Add(param);
// return;
//}
}
/// <summary>
/// 取HTML格式的报表
/// </summary>
/// <param name="_reportItem"></param>
/// <returns></returns>
private byte[] GetSinoSZDefineReport_HTML(MD_ReportItem _reportItem)
{
int actual = 0;
string _resStr = "";
using (OracleConnection cn = OracleHelper.OpenConnection())
{
OracleCommand _cmd = new OracleCommand();
_cmd.CommandText = "select BBJG_C from TJ_ZDYBBFJXXB where BBMC = :BBMC AND TJDW=:TJDW AND KSRQ=:KSRQ AND JZRQ=:JZRQ ";
_cmd.CommandType = CommandType.Text;
_cmd.Connection = cn;
_cmd.Parameters.Add(":BBMC", _reportItem.ReportName.ReportName);
_cmd.Parameters.Add(":TJDW", _reportItem.ReportDWID);
_cmd.Parameters.Add(":KSRQ", _reportItem.StartDate);
_cmd.Parameters.Add(":JZRQ", _reportItem.EndDate);
using (OracleDataReader myOracleDataReader = _cmd.ExecuteReader())
{
myOracleDataReader.Read();
OracleClob myOracleClob = myOracleDataReader.IsDBNull(0) ? null : myOracleDataReader.GetOracleClob(0);
if (myOracleClob == null)
{
return(null);
}
StreamReader streamreader = new StreamReader(myOracleClob, System.Text.Encoding.Unicode);
// step 3: get the CLOB data using the Read() method
char[] cbuffer = new char[100];
while ((actual = streamreader.Read(cbuffer, 0 /*buffer offset*/, cbuffer.Length /*count*/)) > 0)
{
_resStr += new string(cbuffer, 0, actual);
}
myOracleDataReader.Close();
}
cn.Close();
System.Text.UnicodeEncoding converter = new System.Text.UnicodeEncoding();
byte[] returnBytes = converter.GetBytes(_resStr);
return(returnBytes);
}
}
public Message GetById(string id)
{
Message msg = null;
try
{
using (OracleCommand cmd = base.CurrentConnection.CreateCommand())
{
cmd.CommandText = "SELECT ID_MAIL, MAIL_FILE, MAIL_SERVER_ID FROM MAIL_INBOX WHERE ID_MAIL = :p_id_mail";
cmd.Parameters.Add("p_id_mail", id);
using (OracleDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
msg = new Message();
msg.Id = (int)r.GetInt64("ID_MAIL");
msg.Uid = r.GetString("MAIL_SERVER_ID");
long lobSize = r.GetBytes(r.GetOrdinal("MAIL_FILE"), 0, null, 0, 0);
OracleClob file = r.GetOracleClob(r.GetOrdinal("MAIL_FILE"));
msg.OriginalData = new byte[lobSize];
file.Read(msg.OriginalData, 0, (int)lobSize);
}
}
}
}
catch (Exception e)
{
//TASK: Allineamento log - Ciro
if (!e.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException("Errore nel metodo GetById(string id). Dettaglio: " + e.Message,
"E004", string.Empty, string.Empty, e.InnerException);
ErrorLogInfo err = new ErrorLogInfo(mEx);
//err.userID = id;
_log.Error(err);
}
//ManagedException m = new ManagedException(e.Message, "E004", id, "GetById", e);
//ErrorLog error = new ErrorLog(m);
//_log.Error(error);
}
return(msg);
}
public string ExecuteApi(string p)
{
var cmd = new OracleCommand();
cmd.Connection = connection;
cmd.CommandText = "c.web.Api";
cmd.CommandType = CommandType.StoredProcedure;
string res = $"{{ \"State\": -1, \"TextError\":\"Rez=|>NULL\"}}";
cmd.Parameters.Add("res", OracleDbType.Clob, res, ParameterDirection.ReturnValue);
cmd.Parameters.Add("parData", OracleDbType.Clob, p, ParameterDirection.Input);
cmd.Parameters.Add("is_utf8", OracleDbType.Int64, (object)1, ParameterDirection.Input);
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
return($"{{ \"State\": -1, \"TextError\":\"{e.Message}\" }}");
}
OracleClob aa = (OracleClob)cmd.Parameters["res"].Value;
if (aa == null || aa.Value == null)
{
FileLogger.WriteLogMessage($"Oracle\\ExecuteApi\\{this.ConectionString}\\{p} \\ Res=> NULL");
}
else
{
res = aa.Value.ToString();
}
cmd.Connection.Close();
return(res);
}
public void AddParameter(IDbCommand command, String nomeDoParametro)
{
var clob = new OracleClob(command.Connection as OracleConnection);
var bytes = Encoding.Unicode.GetBytes(valorDoParametro);
var tamanhoParametro = Encoding.Unicode.GetByteCount(valorDoParametro);
var posicaoAtual = 0;
var tamanhoChunk = 1024; // Oracle não suporta chunks muito grandes
while (posicaoAtual < tamanhoParametro)
{
tamanhoChunk = tamanhoChunk > tamanhoParametro - posicaoAtual ? tamanhoParametro - posicaoAtual : tamanhoChunk;
clob.Write(bytes, posicaoAtual, tamanhoChunk);
posicaoAtual += tamanhoChunk;
}
var parametro = new OracleParameter(nomeDoParametro, OracleDbType.Clob)
{
Value = clob
};
command.Parameters.Add(parametro);
}
/// <summary>
/// The main entry point for the application.
/// </summary>
static void Main(string[] args)
{
// Connect
string constr = "User Id=scott;Password=tiger;Data Source=oracle";
OracleConnection con = Connect(constr);
// Setup
Setup(con);
// Set the command
OracleCommand cmd = new OracleCommand(
"update multimedia_tab set story = :1 where thekey = 1");
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
// Create an OracleClob object, specifying no caching and not a NCLOB
OracleClob clob = new OracleClob(con, false, false);
// Write data to the OracleClob object, clob, which is a temporary LOB
string str = "this is a new story";
clob.Write(str.ToCharArray(), 0, str.Length);
// Bind a parameter with OracleDbType.Clob
cmd.Parameters.Add("clobdata",
OracleDbType.Clob,
clob,
ParameterDirection.Input);
try
{
// Execute command
cmd.ExecuteNonQuery();
// A new command text
cmd.CommandText = "select thekey, story from multimedia_tab where thekey = 1";
// Create DataReader
OracleDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
}
// Read the first row
reader.Read();
// Get an OracleClob object from the DataReader. Column index is 0-based.
// clob is no more a temporary LOB. It's now a persistent LOB.
clob = reader.GetOracleClob(1);
// Display the new data using Value property
Console.WriteLine(clob.Value);
}
catch (Exception e)
{
Console.WriteLine("Exception:" + e.Message);
}
finally
{
// Dispose OracleClob object
clob.Dispose();
// Dispose OracleCommand object
cmd.Dispose();
// Close and Dispose OracleConnection object
con.Close();
con.Dispose();
}
}
/// <summary>
/// Compares the packages on the database with those in the CodeSource folder.
/// </summary>
/// <param name="con">The Oracle connection to check with.</param>
/// <param name="codeSourceDirectory">The Code Source directory to start from.</param>
private static void PackageCheck(OracleConnection con, DirectoryInfo codeSourceDirectory)
{
Console.WriteLine(@"
#################################################
# Database Source
# ");
DirectoryInfo datasouceDirectory = new DirectoryInfo(Path.Combine(codeSourceDirectory.FullName, "DatabaseSource", "CoreSource"));
foreach (DirectoryInfo dirInfo in datasouceDirectory.GetDirectories())
{
string packageOwner = dirInfo.Name;
foreach (FileInfo fileInfo in dirInfo.GetFiles())
{
string packageName = Path.GetFileNameWithoutExtension(fileInfo.Name);
string packageType = null;
switch (fileInfo.Extension)
{
case ".pks":
packageType = "PACKAGE";
break;
case ".pkb":
packageType = "PACKAGE_BODY";
break;
case ".vw":
packageType = "VIEW";
break;
case ".tps":
packageType = "TYPE";
break;
case ".trg":
packageType = "TRIGGER";
break;
case ".fnc":
packageType = "FUNCTION";
break;
case ".tpb":
packageType = "TYPE_BODY";
break;
case ".prc":
packageType = "PROCEDURE";
break;
default:
Debug.Assert(false, $"Unknown extension {fileInfo.Extension}");
break;
}
string result;
OracleCommand cmd = con.CreateCommand();
if (packageType == "PACKAGE" || packageType == "PACKAGE_BODY")
{
cmd.CommandText = $@"SELECT text
FROM dba_source
WHERE type = '{(packageType == "PACKAGE" ? "PACKAGE" : "PACKAGE BODY")}'
AND owner = '{packageOwner}'
AND name = '{packageName}'
ORDER BY line ASC";
OracleDataReader reader = cmd.ExecuteReader();
result = string.Empty;
while (reader.Read())
{
result += reader.GetString(0);
}
reader.Close();
}
else
{
cmd.CommandText = $"SELECT DBMS_METADATA.GET_DDL( object_type => '{packageType}', name => '{packageName.ToUpper()}', schema => '{packageOwner.ToUpper()}' ) FROM DUAL";
try
{
OracleDataReader reader = cmd.ExecuteReader();
reader.Read();
OracleClob clob = reader.GetOracleClob(0);
result = clob.Value;
reader.Close();
}
catch (OracleException ex)
{
if (ex.Number == 31603)
{
Console.WriteLine($"Adding new package {packageName}");
continue;
}
throw;
}
}
string databaseContents = CleanPackageSource(result, packageName);
string fileContents = File.ReadAllText(fileInfo.FullName);
fileContents = CleanPackageSource(fileContents, packageName);
if (databaseContents != fileContents)
{
File.WriteAllText("database.sql", databaseContents);
File.WriteAllText("file.sql", fileContents);
Console.WriteLine($"{packageType} object {fileInfo.Name} is different.");
}
cmd.Dispose();
}
}
}
public string Run()
{
error = string.Empty;
var STUDY_KEY = string.Empty;
try
{
string conn = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
using (OracleConnection connection = new OracleConnection(conn))
{
connection.Open();
OracleCommand command = new OracleCommand(@"SELECT report.*, study.PATIENT_NAME, TO_CHAR(study.CREATION_DTTM, 'YYYY-MM-DD hh24:mi:ss') CREATION_DTTM FROM MOVEREPORT report
inner join study
on report.study_key = study.study_key WHERE STATUS = 0 and ROWNUM < 500 AND SOURCE_AETITLE not IN ('DSVR5','DSVR9')", connection);
var transaction = connection.BeginTransaction();
try
{
/*var da = new OracleDataAdapter(command);
* var cb = new OracleCommandBuilder(da);
* var ds = new DataSet();
* da.Fill(ds);*/
OracleDataReader reader;
command.Transaction = transaction;
reader = command.ExecuteReader();
while (reader.Read())
{
OracleClob clob = reader.GetOracleClob(2);
var cellValue = (string)clob.Value;
var ACCESSNUMBER = reader["ACCESSNUMBER"].ToString();
STUDY_KEY = reader["STUDY_KEY"].ToString();
var report = reader["REPORT_TEXT_LOB"].ToString().Replace("====== [Conclusion] ======", "");
var patient_name = reader["PATIENT_NAME"].ToString();
var creation_data = reader["CREATION_DTTM"].ToString().Replace(" ", "T");
var CRM = reader["CRM"].ToString();
var regex = new Regex(@"[^\d]");
CRM = regex.Replace(CRM, "");
using (OracleConnection connection1 = new OracleConnection(conn))
{
connection1.Open();
var transaction1 = connection1.BeginTransaction();
RunAsync(ACCESSNUMBER, STUDY_KEY, report, patient_name, creation_data, null, connection1, CRM, transaction1).Wait();
//====== [Conclusion] ======
}
}
}
catch (Exception ex)
{
error += $" error {{ studykey: {STUDY_KEY}, ex: {ex} }}";
command.Connection = connection;
command.CommandText = $"UPDATE MOVEREPORT SET STATUS = 1, ERROR = :clobparam WHERE STUDY_KEY = '{STUDY_KEY}'";
OracleParameter clobparam = new OracleParameter("clobparam", OracleDbType.Clob, error.Length);
clobparam.Direction = ParameterDirection.Input;
clobparam.Value = error;
command.Parameters.Add(clobparam);
command.Transaction = transaction;
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
}
catch
{
return(error);
}
return(error);
}
public override void WriteValue(IDbCommand command, IDataParameter parameter, object value, DBColumn column)
{
base.WriteValue(command, parameter, value, column);
if (value == null)
{
if (column.IsPrimaryKey)
{
parameter.Direction = ParameterDirection.InputOutput;
}
return;
}
var dbParameter = (OracleParameter)parameter;
switch (column.DBDataType)
{
case DBDataType.ByteArray:
dbParameter.Direction = ParameterDirection.Input;
dbParameter.OracleDbType = OracleDbType.Raw;
if (value != null)
{
dbParameter.Value = new OracleBinary((byte[])value);
}
break;
case DBDataType.Blob:
dbParameter.Direction = ParameterDirection.Input;
dbParameter.OracleDbType = OracleDbType.Blob;
if (value != null)
{
var blob = new OracleBlob((OracleConnection)command.Connection);
blob.Write((byte[])value, 0, ((byte[])value).Length);
blob.Position = 0L;
dbParameter.Value = blob;
}
break;
case DBDataType.Clob:
dbParameter.Direction = ParameterDirection.Input;
dbParameter.OracleDbType = OracleDbType.NClob;
if (value != null)
{
var clob = new OracleClob((OracleConnection)command.Connection, false, true);
clob.Write(((string)value).ToCharArray(), 0, ((string)value).Length);
clob.Position = 0L;
dbParameter.Value = clob;
}
break;
case DBDataType.String:
dbParameter.Size = column.Size;
break;
case DBDataType.Decimal:
if (column.Size > 0)
{
dbParameter.Precision = (byte)column.Size;
}
if (column.Scale > 0)
{
dbParameter.Scale = (byte)column.Scale;
}
break;
case DBDataType.Float:
dbParameter.OracleDbType = OracleDbType.BinaryFloat;
break;
case DBDataType.Double:
dbParameter.OracleDbType = OracleDbType.BinaryDouble;
break;
case DBDataType.Bool:
dbParameter.DbType = DbType.Decimal;
dbParameter.Precision = 1;
dbParameter.Value = (bool)value ? 1 : 0;
break;
////case DBDataType.ShortInt:
//dbParameter.DbType = DbType.Decimal;
//dbParameter.Precision = 5;
//break;
//case DBDataType.Int:
//dbParameter.DbType = DbType.Decimal;
//dbParameter.Precision = 10;
//break;
}
}
public void AddToCommand(ref OracleCommand oraCom)
{
if (oraCom == null)
return;
if (this.Direction == ParameterDirection.Output)
{
if (this.OracleType != OracleDbType.Varchar2)
{
oraCom.Parameters.Add(
this.Name,
this.OracleType,
DBNull.Value,
this.Direction);
}
else
{
oraCom.Parameters.Add(
this.Name,
this.OracleType,
this.arraySize,
DBNull.Value,
this.Direction);
}
return;
}
if (this.IsNullPawnDataType)
{
oraCom.Parameters.Add(
this.Name,
this.OracleType,
DBNull.Value,
this.Direction);
return;
}
if (this.paramIsClob)
{
OracleParameter oraClobParam = oraCom.Parameters.Add(
this.Name,
this.OracleType,
this.Direction);
//SR 5/13/2010 Pass the data as clob
OracleClob clobColumn = new OracleClob(oraCom.Connection,false,false);
clobColumn.Write(((string)this.parameterValues[0]).ToCharArray(), 0, ((string)this.parameterValues[0]).Length);
oraClobParam.Value = clobColumn;
return;
}
//Setup standard oracle parameter
OracleParameter oraParam =
oraCom.Parameters.Add(
this.Name,
this.OracleType,
this.Direction);
//Check if we are to setup an array type
if (this.IsArray)
{
//Set standard array type parameter values
oraParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
oraParam.Size = this.parameterValues.Count;
switch (this.OracleType)
{
case OracleDbType.Int32:
{
//Convert string array into int32 array
var paramValuesInt = new int[this.parameterValues.Count];
for (int i = 0; i < this.parameterValues.Count; ++i)
{
paramValuesInt[i] = Int32.Parse(this.parameterValues[i].ToString());
}
//Set value of param to the array
oraParam.Value = paramValuesInt;
}
break;
case OracleDbType.Int64:
{
//Convert string array into int64 array
var paramValuesInt64 = new Int64[this.parameterValues.Count];
for (int i = 0; i < this.parameterValues.Count; ++i)
{
paramValuesInt64[i] = Int64.Parse(this.parameterValues[i].ToString());
}
//Set value of param to the array
oraParam.Value = paramValuesInt64;
}
break;
case OracleDbType.Double:
{
//Parse double based parameter strings into their
//double equivalents
var paramValuesDouble = new double[this.parameterValues.Count];
for (int i = 0; i < parameterValues.Count; ++i)
{
paramValuesDouble[i] = Double.Parse(this.parameterValues[i].ToString());
}
//Set value of param to the array
oraParam.Value = paramValuesDouble;
}
break;
case OracleDbType.Decimal:
{
//Parse decimal based parameter strings into their
//decimal equivalents
var paramValuesDecimal = new decimal[this.parameterValues.Count];
for (int i = 0; i < parameterValues.Count; ++i)
{
paramValuesDecimal[i] = Decimal.Parse(this.parameterValues[i].ToString());
}
//Set value of param to the array
oraParam.Value = paramValuesDecimal;
}
break;
case OracleDbType.TimeStampTZ:
case OracleDbType.TimeStamp:
//Just put the timestamp string values into the oracle param
var paramTZValuesString = new string[this.parameterValues.Count];
for (int i = 0; i < parameterValues.Count; ++i)
{
paramTZValuesString[i] = (string)this.parameterValues[i];
}
//Set value of param to the array
oraParam.Value = paramTZValuesString;
break;
default:
if (this.parameterValues != null && this.parameterValues.Count > 0)
{
var paramValuesString = new string[this.parameterValues.Count];
for (int i = 0; i < parameterValues.Count; ++i)
{
paramValuesString[i] = (this.parameterValues[i] == null ? string.Empty : this.parameterValues[i].ToString());
}
oraParam.Value = paramValuesString;
}
else
{
var paramValuesString = new string[1];
paramValuesString[0] = string.Empty;
oraParam.Value = paramValuesString;
}
break;
}
}
//If this is not an array, just set the value to the first string / converted value
else
{
if (CollectionUtilities.isEmpty(this.parameterValues))
{
oraParam.Value = DBNull.Value;
return;
}
switch (OracleType)
{
case OracleDbType.Int32:
oraParam.Value = Int32.Parse(this.parameterValues[0].ToString());
break;
case OracleDbType.Int64:
oraParam.Value = Int64.Parse(this.parameterValues[0].ToString());
break;
case OracleDbType.Double:
oraParam.Value = Double.Parse(this.parameterValues[0].ToString());
break;
case OracleDbType.Decimal:
oraParam.Value = Decimal.Parse(this.parameterValues[0].ToString());
break;
default:
oraParam.Value = this.parameterValues[0];
break;
}
}
return;
}
public static Task <int> ReadAsynchronous(this OracleClob clob, char[] buffer, int offset, int count, CancellationToken cancellationToken)
{
return(App.ExecuteAsynchronous(delegate { }, () => clob.Read(buffer, offset, count), cancellationToken));
}
/// <summary>
/// Calls Oracle stored procedures that will do the actual dataset comparisons
/// </summary>
public void FindPatterns(string q1, string q2, DataGrid myDataGrid, DataGrid myDataGrid2)
{
int colcount;
OracleConnection con = setConnection();
if (con.State.ToString() == "Closed")
{
con.Open();
}
OracleCommand cmd = new OracleCommand("PAT2.find_patterns", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.BindByName = true;
OracleParameter sql_stmt = new OracleParameter();
sql_stmt.OracleDbType = OracleDbType.Varchar2;
sql_stmt.ParameterName = "sql_stmt";
sql_stmt.Direction = ParameterDirection.Input;
sql_stmt.Value = q1;
cmd.Parameters.Add(sql_stmt);
OracleParameter sql_stmt2 = new OracleParameter();
sql_stmt2.OracleDbType = OracleDbType.Varchar2;
sql_stmt2.ParameterName = "sql_stmt2";
sql_stmt2.Direction = ParameterDirection.Input;
sql_stmt2.Value = q2;
cmd.Parameters.Add(sql_stmt2);
OracleParameter collec1 = new OracleParameter();
collec1.OracleDbType = OracleDbType.Clob;
collec1.ParameterName = "collec1";
collec1.Direction = ParameterDirection.Output;
cmd.Parameters.Add(collec1);
OracleParameter collec2 = new OracleParameter();
collec2.OracleDbType = OracleDbType.Clob;
collec2.ParameterName = "collec2";
collec2.Direction = ParameterDirection.Output;
cmd.Parameters.Add(collec2);
cmd.ExecuteNonQuery();
OracleClob xmldata = (OracleClob)cmd.Parameters["collec1"].Value;
string convert = (string)xmldata.Value;
OracleClob xmldata2 = (OracleClob)cmd.Parameters["collec2"].Value;
string convert2 = (string)xmldata2.Value;
//turn xml file that represents subject record from Oracle into C# objects
Matches mcol = new Matches();
mcol = Deserializer.deserialize(convert, mcol);
DataGrid recDatagrid = myDataGrid2;
//build tables using searialized xml objects
BuildTables bTables1 = new BuildTables();
bTables1.buildComparisonRec(mcol, recDatagrid);
//turn xml file that represents comaprison dataset from Oracle into C# objects
Matches mcol2 = new Matches();
mcol2 = Deserializer.deserialize(convert2, mcol2);
DataGrid recDatagrid2 = myDataGrid;
this.stats = new Stats(mcol2);
//build tables using searialized xml objects
BuildTables bTables2 = new BuildTables();
colcount = bTables2.buildComparisonRec(mcol2, recDatagrid2);
//change color of cells based on if the cell has a match with the subject record
ColorCode.colorCode(colcount, mcol, recDatagrid2);
con.Close();
}
