public bool CreateDocument(string name, string type, decimal? parent_document_id, int? sequenceNo, string username, string filename, Stream input) { if (String.IsNullOrEmpty(name)) { name = filename; } using (OracleConnection myConnection = new OracleConnection(ConnectionString)) { using (BinaryReader r = new BinaryReader(input)) { myConnection.Open(); OracleLob myLob = new OracleLob(myConnection, OracleDbType.Blob); int streamLength = (int)input.Length; myLob.Write(r.ReadBytes(streamLength), 0, streamLength); using (OracleCommand dbcmd = myConnection.CreateCommand()) { OracleParameter myParam1 = dbcmd.Parameters.Add("DOCUMENT", OracleDbType.Blob); myParam1.OracleValue = myLob; OracleParameter myParam2 = dbcmd.Parameters.Add("PARENT_DOCUMENT_ID", OracleDbType.Number); myParam2.OracleValue = parent_document_id; OracleParameter myParam3 = dbcmd.Parameters.Add("DOCUMENT_NAME", OracleDbType.VarChar); myParam3.OracleValue = name; OracleParameter myParam4 = dbcmd.Parameters.Add("SEQUENCE_NO", OracleDbType.Integer); myParam4.OracleValue = sequenceNo; OracleParameter myParamType = dbcmd.Parameters.Add("TYPE", OracleDbType.VarChar); myParamType.OracleValue = type; OracleParameter myParamUser = dbcmd.Parameters.Add("UPDATE_USER", OracleDbType.VarChar, 50); myParamUser.OracleValue = username; OracleParameter myParamDate = dbcmd.Parameters.Add("UPDATE_DATE", OracleDbType.TimeStampLTZ); myParamDate.OracleValue = DateTime.UtcNow; OracleParameter myParamFilename = dbcmd.Parameters.Add("FILENAME", OracleDbType.VarChar); myParamFilename.OracleValue = filename; dbcmd.CommandText = @"INSERT INTO TDOCUMENT (NAME, PARENT_DOCUMENT_ID,SEQUENCE_NO, DOCUMENT, TYPE, UPDATE_USER, UPDATE_DATE, FILENAME ) VALUES(:DOCUMENT_NAME, :PARENT_DOCUMENT_ID,:SEQUENCE_NO, :DOCUMENT, :TYPE, :UPDATE_USER, :UPDATE_DATE, :FILENAME)"; var result = dbcmd.ExecuteNonQuery(); } } } return true; }
public long CopyTo(OracleLob destination, long destinationOffset) { }
public string writeClob() { this.dboperator_0 = this.connectionConfig_0.getDBOperator(); this.dboperator_0.Open(); string str2 = ""; string strSour = new StreamReader(this.httpRequest_0.InputStream).ReadToEnd(); IDbCommand myComm = null; IDbTransaction transaction = null; try { myComm = this.dboperator_0.Connection.CreateCommand(); myComm.Connection = (this.dboperator_0.Connection); transaction = this.dboperator_0.Connection.BeginTransaction(); myComm.Transaction = (transaction); if (myComm is OleDbCommand) { str2 = "Update " + this.string_0 + " set " + this.string_1 + "=? where " + this.string_2 + "='" + this.string_3 + "'"; myComm.CommandText = (str2); object[] objParams = new object[] { strSour }; this.dboperator_0.AddParams(myComm, objParams); } else if (myComm is OracleCommand) { myComm.Parameters.Clear(); OracleLob lob = PubFunction.strToClob((OracleCommand)myComm, strSour); myComm.Parameters.Clear(); str2 = "Update " + this.string_0 + " set " + this.string_1 + "=:imgParam where " + this.string_2 + "='" + this.string_3 + "'"; myComm.CommandText = (str2); OracleParameter parameter = new OracleParameter(); parameter.ParameterName = ("imgParam"); parameter.OracleType = (OracleType)(4); parameter.Value = (lob); myComm.Parameters.Add(parameter); } myComm.ExecuteNonQuery(); transaction.Commit(); myComm.Dispose(); this.dboperator_0.Close(); } catch (Exception exception) { try { transaction.Rollback(); myComm.Dispose(); } catch (Exception) { } throw exception; } finally { try { this.dboperator_0.Close(); } catch (Exception) { } } return(""); }
public static void Main(string[] args) { string connectionString = "Data Source=testdb;" + "User ID=scott;" + "Password=tiger;"; OracleConnection connection = null; connection = new OracleConnection(connectionString); connection.Open(); Console.WriteLine("Setup test package and data..."); OracleCommand cmddrop = connection.CreateCommand(); cmddrop.CommandText = "DROP TABLE TESTTABLE"; try { cmddrop.ExecuteNonQuery(); } catch (OracleException e) { Console.WriteLine("Ignore this error: " + e.Message); } cmddrop.Dispose(); cmddrop = null; Console.WriteLine("Create table TESTTABLE..."); OracleCommand cmd = connection.CreateCommand(); // create table TESTTABLE cmd.CommandText = "create table TESTTABLE (\n" + " col1 numeric(18,0),\n" + " col2 varchar(32),\n" + " col3 date, col4 blob)"; cmd.ExecuteNonQuery(); Console.WriteLine("Insert 3 rows..."); // insert some rows into TESTTABLE cmd.CommandText = "insert into TESTTABLE\n" + "(col1, col2, col3, col4)\n" + "values(45, 'Mono', sysdate, EMPTY_BLOB())"; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into TESTTABLE\n" + "(col1, col2, col3, col4)\n" + "values(136, 'Fun', sysdate, EMPTY_BLOB())"; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into TESTTABLE\n" + "(col1, col2, col3, col4)\n" + "values(526, 'System.Data.OracleClient', sysdate, EMPTY_BLOB())"; cmd.ExecuteNonQuery(); Console.WriteLine("commit..."); cmd.CommandText = "commit"; cmd.ExecuteNonQuery(); Console.WriteLine("Update blob..."); // update BLOB and CLOB columns OracleCommand select = connection.CreateCommand(); select.Transaction = connection.BeginTransaction(); select.CommandText = "SELECT col1, col4 FROM testtable FOR UPDATE"; OracleDataReader readerz = select.ExecuteReader(); if (!readerz.Read()) { Console.WriteLine("ERROR: RECORD NOT FOUND"); } // update blob_value Console.WriteLine(" Update BLOB column on table testtable..."); OracleLob blob = readerz.GetOracleLob(1); byte[] bytes = new byte[6] { 0x31, 0x32, 0x33, 0x34, 0x35, 0x036 }; blob.Write(bytes, 0, bytes.Length); blob.Close(); readerz.Close(); select.Transaction.Commit(); select.Dispose(); select = null; cmd.CommandText = "commit"; cmd.ExecuteNonQuery(); Console.WriteLine("Create package..."); // create Oracle package TestTablePkg cmd.CommandText = "CREATE OR REPLACE PACKAGE TestTablePkg\n" + "AS\n" + " TYPE T_CURSOR IS REF CURSOR;\n"+ "\n" + " PROCEDURE GetData(tableCursor OUT T_CURSOR);\n"+ "END TestTablePkg;"; cmd.ExecuteNonQuery(); // create Oracle package body for package TestTablePkg cmd.CommandText = "CREATE OR REPLACE PACKAGE BODY TestTablePkg AS\n" + " PROCEDURE GetData(tableCursor OUT T_CURSOR)\n" + " IS\n" + " BEGIN\n" + " OPEN tableCursor FOR\n" + " SELECT *\n" + " FROM TestTable;\n" + " END GetData;\n" + "END TestTablePkg;"; cmd.ExecuteNonQuery(); cmd.Dispose(); cmd = null; Console.WriteLine("Set up command and parameters to call stored proc..."); OracleCommand command = new OracleCommand("TestTablePkg.GetData", connection); command.CommandType = CommandType.StoredProcedure; OracleParameter parameter = new OracleParameter("tableCursor", OracleType.Cursor); parameter.Direction = ParameterDirection.Output; command.Parameters.Add(parameter); Console.WriteLine("Execute..."); command.ExecuteNonQuery(); Console.WriteLine("Get OracleDataReader for cursor output parameter..."); OracleDataReader reader = (OracleDataReader)parameter.Value; Console.WriteLine("Read data***..."); int r = 0; while (reader.Read()) { Console.WriteLine("Row {0}", r); for (int f = 0; f < reader.FieldCount; f++) { Console.WriteLine("FieldType: " + reader.GetFieldType(f).ToString()); object val = ""; if (f == 3) { Console.WriteLine("blob"); //OracleLob lob = reader.GetOracleLob (f); //val = lob.Value; val = reader.GetValue(f); if (((byte[])val).Length == 0) { val = "Empty Blob (Not Null)"; } else { val = BitConverter.ToString((byte[])val); } } else { val = reader.GetOracleValue(f); } Console.WriteLine(" Field {0} Value: {1}", f, val); } r++; } Console.WriteLine("Rows retrieved: {0}", r); Console.WriteLine("Clean up..."); reader.Close(); reader = null; command.Dispose(); command = null; connection.Close(); connection = null; }
static public void PackageLogon(OracleConnection connection, string username, string password) { XmlDocument logonDoc = new XmlDocument(); XmlElement scanworks = logonDoc.CreateElement("Scanworks"); logonDoc.AppendChild(scanworks); XmlElement erp = logonDoc.CreateElement("ERP"); scanworks.AppendChild(erp); XmlText ifs = logonDoc.CreateTextNode("IFS"); erp.AppendChild(ifs); XmlElement api = logonDoc.CreateElement("API"); scanworks.AppendChild(api); XmlAttribute name = logonDoc.CreateAttribute("Name"); name.Value = "esi_scanworks_logon.Logon"; api.Attributes.Append(name); addParameter(api, "status", ""); addParameter(api, "username_", username); addParameter(api, "password_", password); XmlElement method = logonDoc.CreateElement("Method"); api.AppendChild(method); XmlText methodName = logonDoc.CreateTextNode("Logon"); method.AppendChild(methodName); StringWriter sw = new StringWriter(); logonDoc.Save(sw); string xmlString = sw.ToString(); StringBuilder sql = new StringBuilder("begin :result := ").Append("esi_scanworks_logon").Append(".ApiCall(:parameters_); end; "); OracleCommand cmd = new OracleCommand(); OracleParameter returnParameter = new OracleParameter(); returnParameter.ParameterName = "result"; returnParameter.OracleType = OracleType.Clob; returnParameter.Direction = System.Data.ParameterDirection.Output; // returnParameter.Size = 32000; cmd.Parameters.Add(returnParameter); OracleParameter oraParameter = new OracleParameter(); oraParameter.ParameterName = "parameters_"; oraParameter.Value = xmlString; oraParameter.OracleType = OracleType.LongVarChar; oraParameter.Direction = System.Data.ParameterDirection.Input; oraParameter.Size = 32000; cmd.Parameters.Add(oraParameter); cmd.CommandText = sql.ToString(); cmd.Connection = connection; cmd.ExecuteNonQuery(); string result = ""; if (returnParameter.Value.GetType().ToString().Equals("System.Data.OracleClient.OracleLob")) { OracleLob lob = (OracleLob)returnParameter.Value; result = (string)lob.Value; } else { result = (string)returnParameter.Value; } logonDoc = new XmlDocument(); logonDoc.LoadXml(result); }
public long CopyTo(OracleLob destination) {}
/// <summary> /// This function takes somes inputs and uploads the file to database. /// </summary> /// <param name="pUserLandId">User windows id</param> /// <param name="pApp">application id</param> /// <param name="pFileName">file name that is going to be uploaded</param> /// <param name="pFileContent">binary content of file</param> public void InsertFileIntoDB(string pUserLandId, string pApp, string pFileName, byte[] pFileContent, ControlName pControlName) { string lQuery = string.Empty; OracleProvider lProvider = new OracleProvider(); string lFileId = string.Empty; string lVersionId = string.Empty; OracleCommand lCommand = null; string lControl = string.Empty; try { if (pControlName == ControlName.UploadOrganizationGovernance) { lControl = "Organization Governance"; } else if (pControlName == ControlName.UploadOrganizationBackground) { lControl = "Organization Background"; } //Get File and Version Id lFileId = lProvider.FetchDataFromDBUsingExecuteScaler("Select SEQ_DOCUMENT_ID.Nextval from dual"); lVersionId = lProvider.FetchDataFromDBUsingExecuteScaler("Select SEQ_VERSION_ID.Nextval from dual"); //Begin Transaction lCommand = lProvider.OpenConnectionAndBeginTransaction(); lQuery = "Insert into T_APPLICATION_DOCS (DOCUMENT_ID,APPLICATION_ID, DOC_TITLE,DOC_TYPE,DOC_ASSOCIATE,UPDATED_ON, UPDATED_BY) " + "values (" + lFileId + ",'" + pApp + "','" + lControl + "','I','G'," + "SYSDATE,'" + pUserLandId + "')"; //Insert into Application Docs lProvider.FExecuteNonQuery(lCommand, lQuery, null); lQuery = "Insert into T_APPLICATION_DOCS_VERSIONS (DOCUMENT_ID,APPLICATION_ID,VERSION_ID,DOC_EXT,UPLOADED_ON, UPLOADED_BY) " + "VALUES (" + lFileId + ",'" + pApp + "'," + lVersionId + ",'" + pFileName + "', SYSDATE,'" + pUserLandId + "')"; //Insert into Application_docs_version lProvider.FExecuteNonQuery(lCommand, lQuery, null); lQuery = "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;"; OracleParameter lParam = new OracleParameter("tempblob", OracleType.Blob); lParam.Direction = ParameterDirection.Output; OracleParameterCollection lParamCollection = new OracleParameterCollection(); lParamCollection.Add(lParam); lProvider.FExecuteNonQuery(lCommand, lQuery, lParamCollection); OracleLob tempLob = (OracleLob)lCommand.Parameters[0].Value; //Create temporary BLOB OracleLob lLob = (OracleLob)lCommand.Parameters[0].Value; int streamLength = pFileContent.Length; //Transfer data to server lLob.Write(pFileContent, 0, streamLength); lParam = new OracleParameter("data", OracleType.Blob); lParam.Value = lLob; //Create a parameter collection lParamCollection = new OracleParameterCollection(); lParamCollection.Add(lParam); lQuery = "Update T_APPLICATION_DOCS_VERSIONS set DOC_CONTENT=:data where DOCUMENT_ID=" + lFileId; //Execue the update query lProvider.FExecuteNonQuery(lCommand, lQuery, lParamCollection); //Commit lProvider.CommitTransaction(lCommand); } catch (Exception ex) { if (lProvider != null && lCommand != null) { lProvider.RollBackTransaction(lCommand); } } finally { lProvider = null; lCommand = null; } }
public long CopyTo(long sourceOffset, OracleLob destination, long destinationOffset, long amount) {}
public void Insert(IList <Document> docs) { if (docs.Count <= 0) { return; } StringBuilder insertString = new StringBuilder(); //insertString.AppendLine("DECLARE"); insertString.Append("BEGIN "); foreach (Hubble.Core.Data.Document doc in docs) { insertString.AppendFormat("Insert into {0} (DocId", _Table.DBTableName); foreach (Data.FieldValue fv in doc.FieldValues) { if (fv.Value == null) { continue; } insertString.AppendFormat(", {0}", GetFieldName(fv.FieldName)); } insertString.AppendFormat(") Values({0}", doc.DocId); foreach (Data.FieldValue fv in doc.FieldValues) { if (fv.Value == null) { continue; } switch (fv.Type) { case Hubble.Core.Data.DataType.NVarchar: case Hubble.Core.Data.DataType.NChar: if (fv.DataLength < 0) { insertString.Append(",N'A'"); } else { insertString.AppendFormat(",N'{0}'", ReplaceTextValue(fv.Value.Replace("'", "''"))); } break; case Hubble.Core.Data.DataType.Varchar: case Hubble.Core.Data.DataType.Char: case Hubble.Core.Data.DataType.Data: if (fv.DataLength < 0) { insertString.Append(",N'A'"); } else { insertString.AppendFormat(",'{0}'", ReplaceTextValue(fv.Value.Replace("'", "''"))); } break; case Hubble.Core.Data.DataType.DateTime: case Hubble.Core.Data.DataType.Date: case Hubble.Core.Data.DataType.SmallDateTime: DateTime dateTime; if (!DateTime.TryParseExact(fv.Value, "yyyy-MM-dd HH:mm:ss", null, System.Globalization.DateTimeStyles.None, out dateTime)) { dateTime = DateTime.Parse(fv.Value); } insertString.AppendFormat(",to_date('{0}','yyyy-mm-dd HH24:MI:SS')", dateTime.ToString("yyyy-MM-dd HH:mm:ss")); break; default: insertString.AppendFormat(",{0}", fv.Value); break; } } insertString.Append("); "); } insertString.Append(" END;"); using (OracleDataProvider sqlData = new OracleDataProvider()) { sqlData.Connect(Table.ConnectionString); sqlData.ExcuteSql(insertString.ToString()); string sql = string.Format("select * from {0} where docid >= {1} and docid <= {2} order by docid FOR UPDATE", _Table.DBTableName, docs[0].DocId, docs[docs.Count - 1].DocId); OracleCommand cmd; using (OracleDataReader reader = sqlData.ExecuteReader(sql, out cmd)) { cmd.Transaction = cmd.Connection.BeginTransaction(); for (int i = 0; i < docs.Count; i++) { reader.Read(); for (int j = 0; j < docs[i].FieldValues.Count; j++) { switch (docs[i].FieldValues[j].Type) { case Hubble.Core.Data.DataType.NVarchar: case Hubble.Core.Data.DataType.NChar: case Hubble.Core.Data.DataType.Varchar: case Hubble.Core.Data.DataType.Char: if (docs[i].FieldValues[j].DataLength < 0) { OracleLob clob = reader.GetOracleLob(reader.GetOrdinal(docs[i].FieldValues[j].FieldName)); byte[] head = new byte[2]; //head[0] = 0xCC; //head[1] = 0xDD; //clob.Write(head, 0, 2); //clob.Position = 0; byte[] buffer = Encoding.Unicode.GetBytes(docs[i].FieldValues[j].Value); clob.Write(buffer, 0, buffer.Length); //Console.WriteLine(clob.LobType + ".Write(" + buffer + ", 0, 0) => " + clob.Value); //OracleLob templob = sqlData.CreateTempLob(clob.LobType); //long actual = clob.CopyTo(templob); //Console.WriteLine(clob.LobType + ".CopyTo(" + templob.Value + ") => " + actual); } break; } } } cmd.Transaction.Commit(); } } }
/// <summary> /// Execute an oracle stored procedure using the /// parameters passed in /// </summary> /// <param name="strSPName"></param> /// <param name="ParamList"></param> /// <param name="lStatusCode"></param> /// <param name="strStatus"></param> /// <returns></returns> private bool ExecuteOracleSP(bool bAudit, string strSPName, CParameterList ParamList, out k_STATUS_CODE lStatusCode, out string strStatus) { lStatusCode = k_STATUS_CODE.Success; strStatus = String.Empty; //return null if no conn if (m_OracleConnection == null) { lStatusCode = k_STATUS_CODE.Failed; strStatus = "Unable to connect to data source, Data Connection is null"; return(false); } //create a new command object and set the command objects connection, text and type //must use OracleCommand or you cannot get back a ref cur out param which is how //we do things in medbase OracleCommand cmd = new OracleCommand(); cmd.Connection = m_OracleConnection; cmd.CommandText = strSPName; cmd.CommandType = CommandType.StoredProcedure; //add the parameters from the parameter list to the command parameter list for (int i = 0; i < ParamList.Count; i++) { CParameter parameter = ParamList.GetItemByIndex(i); if (parameter != null) { //create a new oledb param from our param and add it to the list //this follows how we currently do it in medbase OracleParameter oraParameter = new OracleParameter(); oraParameter.ParameterName = parameter.ParameterName; //set the parameter value, default to string. Probably a better way than the //if then else, but this works and we can find it later, if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.StringParameter) { oraParameter.Value = parameter.StringParameterValue; oraParameter.OracleType = OracleType.VarChar; oraParameter.Size = 4000; } else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.LongParameter) { oraParameter.Value = parameter.LongParameterValue; oraParameter.OracleType = OracleType.Int32; } else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.DoubleParameter) { oraParameter.Value = parameter.DoubleParameterValue; oraParameter.OracleType = OracleType.Double; } else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.IntParameter) { oraParameter.Value = parameter.IntParameterValue; oraParameter.OracleType = OracleType.Int32; } else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.DateParameter) { oraParameter.Value = parameter.DateParameterValue; if (!CDataUtils.IsDateNull(parameter.DateParameterValue)) { //if the date is not null then set the value oraParameter.Value = parameter.DateParameterValue; } else { //set value to DBNull if date is null oraParameter.Value = DBNull.Value; } oraParameter.OracleType = OracleType.DateTime; } else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.CLOBParameter) { //You must begin a transaction before obtaining a temporary LOB. //Otherwise, the OracleDataReader may fail to obtain data later. OracleTransaction transaction = m_OracleConnection.BeginTransaction(); //make a new command OracleCommand command = m_OracleConnection.CreateCommand(); command.Connection = m_OracleConnection; command.Transaction = transaction; command.CommandText = "declare xx clob; begin dbms_lob.createtemporary(xx, false, 0); :tempclob := xx; end;"; command.Parameters.Add(new OracleParameter("tempclob", OracleType.Clob)).Direction = ParameterDirection.Output; command.ExecuteNonQuery(); //get a temp lob OracleLob tempLob = (OracleLob)command.Parameters[0].Value; //begin batch tempLob.BeginBatch(OracleLobOpenMode.ReadWrite); //convert string to byte array and write to lob, //note:encoding must be set to match oracle encoding //default encoding for oracle can be found by running //SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ; System.Text.ASCIIEncoding encASCII = new System.Text.ASCIIEncoding(); //ascii chars byte[] buffAsciiBytes = encASCII.GetBytes(parameter.CLOBParameterValue); //destination to convert to. convert from ascii to unicode UnicodeEncoding encDest = new UnicodeEncoding(); byte[] buffDest = Encoding.Convert(encASCII, encDest, buffAsciiBytes); //write the converted data to the lob tempLob.Write(buffDest, 0, buffDest.Length); //end batch tempLob.EndBatch(); //set the value of the param = lob oraParameter.OracleType = OracleType.Clob; //oraParameter.OracleType = OracleType.NClob; oraParameter.Value = tempLob; //all done so commit; transaction.Commit(); } else { oraParameter.Value = parameter.StringParameterValue; oraParameter.OracleType = OracleType.VarChar; oraParameter.Size = 4000; } oraParameter.Direction = parameter.Direction; cmd.Parameters.Add(oraParameter); } } //add in out params for stored proc, all sp's will return a status 0 = good, 1 = bad // //status ParamList.AddOutputParameter("po_nStatusCode", 0); OracleParameter oraStatusParameter = new OracleParameter("po_nStatusCode", OracleType.Int32); oraStatusParameter.Direction = ParameterDirection.Output; cmd.Parameters.Add(oraStatusParameter); // //comment ParamList.AddOutputParameter("po_vStatusComment", String.Empty); OracleParameter oraCommentParameter = new OracleParameter("po_vStatusComment", OracleType.VarChar, 4000); oraCommentParameter.Direction = ParameterDirection.Output; cmd.Parameters.Add(oraCommentParameter); // try { //execute the stored proc and move the out param values back into //our list cmd.ExecuteNonQuery(); for (int i = 0; i < ParamList.Count; i++) { CParameter parameter = ParamList.GetItemByIndex(i); if (parameter != null) { if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.InputOutput) { foreach (OracleParameter oP in cmd.Parameters) { if (oP.ParameterName.Equals(parameter.ParameterName)) { if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.StringParameter) { if (oP.Value != null) { parameter.StringParameterValue = oP.Value.ToString(); parameter.StringParameterValue = parameter.StringParameterValue.Trim(); } } else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.LongParameter) { if (oP.Value != null) { if (oP.Value.ToString() != String.Empty) { parameter.LongParameterValue = Convert.ToInt64(oP.Value); } } } else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.DoubleParameter) { if (oP.Value != null) { if (oP.Value.ToString() != String.Empty) { parameter.DoubleParameterValue = Convert.ToDouble(oP.Value); } } } else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.BoolParameter) { if (oP.Value != null) { if (oP.Value.ToString() != String.Empty) { parameter.BoolParameterValue = Convert.ToBoolean(oP.Value); } } } else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.IntParameter) { if (oP.Value != null) { if (oP.Value.ToString() != String.Empty) { parameter.IntParameterValue = Convert.ToInt32(oP.Value); } } } else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.DateParameter) { if (oP.Value != null) { if (oP.Value.ToString() != String.Empty) { if (!oP.Value.ToString().Equals(String.Empty)) { parameter.DateParameterValue = Convert.ToDateTime(oP.Value); } } } } else { if (oP.Value != null) { parameter.StringParameterValue = oP.Value.ToString(); parameter.StringParameterValue = parameter.StringParameterValue.Trim(); } } } } } } } lStatusCode = ParamList.GetStatusCode(); strStatus = ParamList.GetStatusComment(); if (lStatusCode == k_STATUS_CODE.Success) { return(true); } return(false); } catch (InvalidOperationException e) { strStatus = e.Message; lStatusCode = k_STATUS_CODE.Failed; } catch (OracleException e) { strStatus = e.Message; lStatusCode = k_STATUS_CODE.Failed; } return(false); }
public static void BLOBTest(OracleConnection connection) { Console.WriteLine(" BEGIN TRANSACTION ..."); OracleTransaction transaction = connection.BeginTransaction(); Console.WriteLine(" Drop table BLOBTEST ..."); try { OracleCommand cmd2 = connection.CreateCommand(); cmd2.Transaction = transaction; cmd2.CommandText = "DROP TABLE BLOBTEST"; cmd2.ExecuteNonQuery(); } catch (OracleException) { // ignore if table already exists } Console.WriteLine(" CREATE TABLE ..."); OracleCommand create = connection.CreateCommand(); create.Transaction = transaction; create.CommandText = "CREATE TABLE BLOBTEST (BLOB_COLUMN BLOB)"; create.ExecuteNonQuery(); Console.WriteLine(" INSERT RECORD ..."); OracleCommand insert = connection.CreateCommand(); insert.Transaction = transaction; insert.CommandText = "INSERT INTO BLOBTEST VALUES (EMPTY_BLOB())"; insert.ExecuteNonQuery(); OracleCommand select = connection.CreateCommand(); select.Transaction = transaction; select.CommandText = "SELECT BLOB_COLUMN FROM BLOBTEST FOR UPDATE"; Console.WriteLine(" SELECTING A BLOB (Binary Large Object) VALUE FROM BLOBTEST"); OracleDataReader reader = select.ExecuteReader(); if (!reader.Read()) { Console.WriteLine("ERROR: RECORD NOT FOUND"); } Console.WriteLine(" TESTING OracleLob OBJECT ..."); OracleLob lob = reader.GetOracleLob(0); Console.WriteLine(" LENGTH: {0}", lob.Length); Console.WriteLine(" CHUNK SIZE: {0}", lob.ChunkSize); //try { if (File.Exists(infilename) == false) { Console.WriteLine("Filename does not exist: " + infilename); return; } FileStream fs = new FileStream(infilename, FileMode.Open, FileAccess.Read); BinaryReader r = new BinaryReader(fs); byte[] bytes = null; int bufferLen = 8192; bytes = r.ReadBytes(bufferLen); while (bytes.Length > 0) { Console.WriteLine("byte count: " + bytes.Length.ToString()); lob.Write(bytes, 0, bytes.Length); bytes1 = ByteArrayCombine(bytes1, bytes); if (bytes.Length < bufferLen) { break; } bytes = r.ReadBytes(bufferLen); } r.Close(); fs.Close(); //} //catch (Exception e) { // Console.WriteLine("The file could not be read:"); // Console.WriteLine(e.Message); //} lob.Close(); Console.WriteLine(" CLOSING READER..."); reader.Close(); transaction.Commit(); transaction = null; lob = null; reader.Dispose(); reader = null; create = null; insert = null; select = null; }
public decimal? CreateListingDocument(string name, string type, int? sequenceNo, string username, string filename, Stream input) { if (String.IsNullOrEmpty(name)) { name = filename; } using (OracleConnection myConnection = new OracleConnection(ConnectionString)) { using (BinaryReader r = new BinaryReader(input)) { myConnection.Open(); OracleLob myLob = new OracleLob(myConnection, OracleDbType.Blob); int streamLength = (int)input.Length; myLob.Write(r.ReadBytes(streamLength), 0, streamLength); using (OracleCommand dbcmd = myConnection.CreateCommand()) { OracleParameter myParam1 = dbcmd.Parameters.Add("DOCUMENT", OracleDbType.Blob); myParam1.OracleValue = myLob; OracleParameter myParam3 = dbcmd.Parameters.Add("NAME", OracleDbType.VarChar); myParam3.OracleValue = name; OracleParameter myParam4 = dbcmd.Parameters.Add("SEQUENCE_NO", OracleDbType.Integer); myParam4.OracleValue = sequenceNo; OracleParameter myParamType = dbcmd.Parameters.Add("TYPE", OracleDbType.VarChar); myParamType.OracleValue = type; OracleParameter myParamUser = dbcmd.Parameters.Add("CURRENT_USER", OracleDbType.VarChar, 50); myParamUser.OracleValue = username; OracleParameter myParamDate = dbcmd.Parameters.Add("CURRENT_DATE", OracleDbType.TimeStampLTZ); myParamDate.OracleValue = DateTime.UtcNow; OracleParameter myParamFilename = dbcmd.Parameters.Add("FILENAME", OracleDbType.VarChar); myParamFilename.OracleValue = filename; OracleParameter pkParam = dbcmd.Parameters.Add("RETPK", OracleDbType.Number, ParameterDirection.Output); dbcmd.CommandText = @"INSERT INTO TLISTING_DOCUMENT (NAME, SEQUENCE_NO, DOCUMENT, TYPE, CREATE_USER, CREATE_DATE, FILENAME ) VALUES(:NAME,:SEQUENCE_NO, :DOCUMENT, :TYPE, :CURRENT_USER, :CURRENT_DATE, :FILENAME) RETURNING ID INTO :RETPK"; var result = dbcmd.ExecuteNonQuery(); if (result ==1) { return pkParam.Value as decimal?; } } } } return null; }
public bool InsertOrUpdateBuildingPicture(decimal id, string username, string filename, Stream picture) { using (OracleConnection myConnection = new OracleConnection(ConnectionString)) { myConnection.Open(); using (BinaryReader r = new BinaryReader(picture)) { OracleLob myLob = new OracleLob(myConnection, OracleDbType.Blob); int streamLength = (int)picture.Length; myLob.Write(r.ReadBytes(streamLength), 0, streamLength); using (OracleCommand dbcmd = myConnection.CreateCommand()) { OracleParameter myParam1 = dbcmd.Parameters.Add("ID", OracleDbType.Number); myParam1.OracleValue = id; dbcmd.CommandText = "UPDATE TBUILDING_PICTURES SET STATUS='D' WHERE BUILDING_ID = :ID AND FLOOR_NO IS NULL"; dbcmd.ExecuteNonQuery(); } string qry = @"INSERT INTO TBUILDING_PICTURES (BUILDING_ID,PICTURE, CREATE_DATE, CREATE_USER, FILENAME ) VALUES(:ID, :PICTURE, :ADATE, :AUSER, :FILENAME)"; using (OracleCommand myCommand = myConnection.CreateCommand(qry)) { OracleParameter myParam1 = myCommand.Parameters.Add("ID", OracleDbType.Number); myParam1.OracleValue = id; OracleParameter myParam2 = myCommand.Parameters.Add("PICTURE", OracleDbType.Blob); myParam2.OracleValue = myLob; OracleParameter myParam3 = myCommand.Parameters.Add("FILENAME", OracleDbType.VarChar, 200); myParam3.OracleValue = filename; OracleParameter myParam4 = myCommand.Parameters.Add("AUSER", OracleDbType.VarChar, 50); myParam4.OracleValue = username; OracleParameter myParam5 = myCommand.Parameters.Add("ADATE", OracleDbType.TimeStampLTZ); myParam5.OracleValue = DateTime.Now; return myCommand.ExecuteNonQuery() == 1; } } } }
public long CopyTo(long sourceOffset, OracleLob destination, long destinationOffset, long amount) { }
public IReturnType execBlob(string sqlString, List <DBParams> arrParam, byte[] Image, string ImageName = "arg_file", bool useStandard = true) { List <DBParams> standardParams = new List <DBParams>(); if (useStandard) { standardParams.Add(new DBParams("intreturnCode", "I")); standardParams.Add(new DBParams("strReturnMsg", "V")); } foreach (DBParams db in standardParams) { arrParam.Add(db); } cmd = SetCommand(); OracleTransaction tx = cmd.Connection.BeginTransaction(); cmd.Transaction = tx; StringBuilder strCommand = new StringBuilder("declare xx blob; begin dbms_lob.createtemporary(xx, false, 0);"); strCommand.Append(" :tempblob1:= xx;"); cmd.Parameters.Add(new OracleParameter("tempblob1", OracleType.Blob)).Direction = ParameterDirection.Output; strCommand.Append(" end;"); cmd.CommandType = CommandType.Text; cmd.CommandText = strCommand.ToString(); cmd.ExecuteNonQuery(); OracleLob blob = cmd.Parameters["tempblob1"].Value as OracleLob; blob.Write(Image, 0, Image.Length); cmd.Parameters.Clear(); cmd.CommandText = sqlString; cmd.CommandType = CommandType.StoredProcedure; AddParameters(arrParam); cmd.Parameters.Add(ImageName, OracleType.Blob).Value = blob; IReturnType rt = new DALReturnType(); cmd.ExecuteNonQuery(); if (cmd.Parameters["intreturnCode"].Value.ToString() != "") { rt.ReturnCode = (int?)cmd.Parameters["intreturnCode"].Value; } rt.ReturnMessage = cmd.Parameters["strReturnMsg"].Value.ToString(); tx.Commit(); conn.Close(); return(rt); }
public long CopyTo(OracleLob destination) { }
public static void CLOBTest(OracleConnection connection) { Console.WriteLine(" BEGIN TRANSACTION ..."); OracleTransaction transaction = connection.BeginTransaction(); Console.WriteLine(" Drop table CLOBTEST ..."); try { OracleCommand cmd2 = connection.CreateCommand(); cmd2.Transaction = transaction; cmd2.CommandText = "DROP TABLE CLOBTEST"; cmd2.ExecuteNonQuery(); } catch (OracleException oe1) { // ignore if table already exists } Console.WriteLine(" CREATE TABLE ..."); OracleCommand create = connection.CreateCommand(); create.Transaction = transaction; create.CommandText = "CREATE TABLE CLOBTEST (CLOB_COLUMN CLOB)"; create.ExecuteNonQuery(); Console.WriteLine(" INSERT RECORD ..."); OracleCommand insert = connection.CreateCommand(); insert.Transaction = transaction; insert.CommandText = "INSERT INTO CLOBTEST VALUES (EMPTY_CLOB())"; insert.ExecuteNonQuery(); OracleCommand select = connection.CreateCommand(); select.Transaction = transaction; select.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST FOR UPDATE"; Console.WriteLine(" SELECTING A CLOB (CHARACTER) VALUE FROM CLOBTEST"); OracleDataReader reader = select.ExecuteReader(); if (!reader.Read()) { Console.WriteLine("ERROR: RECORD NOT FOUND"); } Console.WriteLine(" TESTING OracleLob OBJECT ..."); OracleLob lob = reader.GetOracleLob(0); Console.WriteLine(" LENGTH: {0}", lob.Length); Console.WriteLine(" CHUNK SIZE: {0}", lob.ChunkSize); UnicodeEncoding encoding = new UnicodeEncoding(); try { // read file "cs-parser.cs" into the oracle clob using (StreamReader sr = new StreamReader(infilename)) { string sbuff = sr.ReadToEnd(); byte[] evalue = encoding.GetBytes(sbuff); lob.Write(evalue, 0, evalue.Length); } } catch (Exception e) { Console.WriteLine("The file could not be read:"); Console.WriteLine(e.Message); } lob.Close(); Console.WriteLine(" CLOSING READER..."); reader.Close(); transaction.Commit(); }
// Methods public void Append(OracleLob source) {}
public static string[] Save(DBOperator oDb, XmlDocument xdoc, string djid, string djsn, string dj_name, string djlx, string djposition, string designtext, string xmltext, string userfunc, string sAddHtml, string databasetype, string userType) { int num = int.Parse(djid); string message = ""; oDb.Open(); string str2 = "select djid from FC_BILLZL where djsn='" + djsn + "' and djid <> " + djid; int num2 = num; try { IDbCommand myComm = oDb.Connection.CreateCommand(); myComm.Connection = (oDb.Connection); myComm.CommandText = (str2); IDataReader reader = myComm.ExecuteReader(); if (reader.Read()) { message = "保存成功!不过此表单的表单SN重名,请修改此表单的表单SN。"; } reader.Close(); myComm.Dispose(); string str3 = "1"; myComm = oDb.Connection.CreateCommand(); IDbTransaction transaction = oDb.Connection.BeginTransaction(); myComm.Connection = (oDb.Connection); myComm.Transaction = (transaction); try { string str4 = ""; string str5 = ""; if (databasetype == "oracle") { str4 = "'',''"; str5 = ""; } else { str4 = "'" + xmltext + "','" + designtext + "'"; str5 = ",xmltext='" + xmltext + "',designtext='" + designtext + "'"; } if (num == 0) { num2 = Tools.getRecNumNo("BIL", myComm); str2 = "insert into FC_BILLZL (djid,djsn,dj_name,djlx,djposition,userType,xmltext,designtext) values (" + Convert.ToString(num2) + ",'" + djsn + "','" + dj_name + "','" + djlx + "','" + djposition + "','" + userType + "'," + str4 + ")"; myComm.CommandText = (str2); myComm.ExecuteNonQuery(); str3 = Convert.ToString(num2); } else { str2 = "update FC_BILLZL set djsn='" + djsn + "',dj_name='" + dj_name + "',djlx='" + djlx + "',djposition='" + djposition + "',userType='" + userType + "'" + str5 + " where djid=" + djid; myComm.CommandText = (str2); myComm.ExecuteNonQuery(); str3 = djid; } if (databasetype == "oracle") { if (myComm is OleDbCommand) { str2 = "update FC_BILLZL set designtext=?,xmltext=? where djid=? "; myComm.CommandText = (str2); object[] objParams = new object[] { designtext, xmltext, str3 }; oDb.AddParams(myComm, objParams); } else if (myComm is OracleCommand) { str2 = "update FC_BILLZL set designtext=:pdesigntext,xmltext=:pxmltext where djid=" + str3; myComm.Parameters.Clear(); OracleLob lob = Tools.strToClob((OracleCommand)myComm, designtext); myComm.Parameters.Clear(); OracleLob lob2 = Tools.strToClob((OracleCommand)myComm, xmltext); myComm.Parameters.Clear(); myComm.CommandText = (str2); OracleParameter parameter = new OracleParameter(); parameter.ParameterName = ("pdesigntext"); parameter.OracleType = (OracleType)(4); parameter.Value = (lob); myComm.Parameters.Add(parameter); OracleParameter parameter2 = new OracleParameter(); parameter2.ParameterName = ("pxmltext"); parameter2.OracleType = (OracleType)(4); parameter2.Value = (lob2); myComm.Parameters.Add(parameter2); } myComm.ExecuteNonQuery(); } transaction.Commit(); myComm.Dispose(); } catch (Exception exception) { try { transaction.Rollback(); myComm.Dispose(); } catch (Exception) { } message = exception.Message; num2 = 0; } } catch (Exception exception2) { message = exception2.Message; num2 = 0; } finally { try { oDb.Close(); } catch (Exception) { } } return(new string[] { Convert.ToString(num2), message }); }
public long CopyTo(OracleLob destination, long destinationOffset) {}
public bool InsertOrUpdatePicture(decimal building_id, decimal? building_vacancy_id, string filename, string floor, string username, Stream input) { using (OracleConnection myConnection = new OracleConnection(ConnectionString)) { using (BinaryReader r = new BinaryReader(input)) { myConnection.Open(); OracleLob myLob = new OracleLob(myConnection, OracleDbType.Blob); int streamLength = (int)input.Length; myLob.Write(r.ReadBytes(streamLength), 0, streamLength); using (OracleCommand dbcmd = myConnection.CreateCommand()) { OracleParameter myParam1 = dbcmd.Parameters.Add("ID", OracleDbType.Number); myParam1.OracleValue = building_id; OracleParameter floorParam = dbcmd.Parameters.Add("BUILDING_VACANCY_ID", OracleDbType.Number); floorParam.OracleValue = building_vacancy_id; dbcmd.CommandText = "UPDATE TBUILDING_PICTURES SET STATUS='D' WHERE BUILDING_ID = :ID AND BUILDING_VACANCY_ID =:BUILDING_VACANCY_ID"; dbcmd.ExecuteNonQuery(); } string qry = @"INSERT INTO TBUILDING_PICTURES (BUILDING_ID,PICTURE, CREATE_DATE, CREATE_USER, FILENAME, FLOOR_NO, BUILDING_VACANCY_ID ) VALUES(:ID, :PICTURE, :ADATE, :AUSER, :FILENAME, :FLOOR_NO, :BUILDING_VACANCY_ID)"; using (OracleCommand dbcmd = myConnection.CreateCommand()) { OracleParameter myParam1 = dbcmd.Parameters.Add("PICTURE", OracleDbType.Blob); myParam1.OracleValue = myLob; OracleParameter myParam2 = dbcmd.Parameters.Add("ID", OracleDbType.Number); myParam2.OracleValue = building_id; OracleParameter myParam3 = dbcmd.Parameters.Add("FLOOR_NO", OracleDbType.VarChar, 50); myParam3.OracleValue = floor; OracleParameter myParam4 = dbcmd.Parameters.Add("AUSER", OracleDbType.VarChar, 50); myParam4.OracleValue = username; OracleParameter myParam5 = dbcmd.Parameters.Add("ADATE", OracleDbType.TimeStampLTZ); myParam5.OracleValue = DateTime.Now; OracleParameter myParam6 = dbcmd.Parameters.Add("FILENAME", OracleDbType.VarChar, 200); myParam6.OracleValue = filename; OracleParameter floorParam = dbcmd.Parameters.Add("BUILDING_VACANCY_ID", OracleDbType.Number); floorParam.OracleValue = building_vacancy_id; dbcmd.CommandText = qry; var result = dbcmd.ExecuteNonQuery(); } } } return false; }