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(); }