// <Snippet1> private void GetOracleBFile(string connectionString) { //Create and open the connection. using (OracleConnection connection = new OracleConnection(connectionString)) { connection.Open(); //Create and execute the commands. OracleCommand command = connection.CreateCommand(); command.CommandText = "CREATE OR REPLACE DIRECTORY TestDir AS 'c:\\bfiles'"; command.ExecuteNonQuery(); command.CommandText = "CREATE TABLE TestTable(col1 number, col2 BFILE)"; command.ExecuteNonQuery(); command.CommandText = "INSERT INTO TestTable VALUES ('2', BFILENAME('TESTDIR', 'File.jpg'))"; command.ExecuteNonQuery(); command.CommandText = "SELECT * FROM TestTable"; //Read the BFile data. byte[] buffer = new byte[100]; OracleDataReader dataReader = command.ExecuteReader(); using (dataReader) { if (dataReader.Read()) { OracleBFile BFile = dataReader.GetOracleBFile(1); using (BFile) { BFile.Seek(0, SeekOrigin.Begin); BFile.Read(buffer, 0, 100); } } } } return; }
private void get_bfile(OracleConnection con) { // this helper gets the bfile from the database // table, displays the property values and // writes the content of the file to the console string sqlText = "select file_loc from bfile_test"; // the command object OracleCommand cmd = new OracleCommand(sqlText, con); // get a data reader for the command object OracleDataReader dataReader = cmd.ExecuteReader(); OracleBFile bfile = null; if (dataReader.Read()) { // use the typed accessor to get the bfile bfile = dataReader.GetOracleBFile(0); } // open the file // try // { // bfile.OpenFile(); // } // catch(OracleException ex) // { // Console.WriteLine(ex.ToString()); // } // display the property values Console.WriteLine("Retrieved bfile from database..."); Console.WriteLine(" CanRead = " + bfile.CanRead.ToString()); Console.WriteLine(" CanSeek = " + bfile.CanSeek.ToString()); Console.WriteLine(" CanWrite = " + bfile.CanWrite.ToString()); Console.WriteLine(" Connection = " + bfile.Connection.ConnectionString); Console.WriteLine(" DirectoryName = " + bfile.DirectoryName.ToString()); Console.WriteLine(" FileExists = " + bfile.FileExists.ToString()); Console.WriteLine(" FileName = " + bfile.FileName.ToString()); Console.WriteLine(" Length = " + bfile.Length.ToString()); Console.WriteLine(" Position = " + bfile.Position.ToString()); Console.WriteLine(" Value = " + bfile.Value.ToString()); // convert the byte array to a string UTF7Encoding utf = new UTF7Encoding(); byte[] bf = new byte[bfile.Length]; bfile.Read(bf, 0, Convert.ToInt16(bfile.Length)); bfile.Close(); Console.WriteLine(" Value = \n" + utf.GetString(bf)); }
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;"; byte[] _fileContents; try { OracleConnection _connObj = new OracleConnection(_connstring); OracleDataReader _rdrObj; _connObj.Open(); OracleCommand _cmdObj = _connObj.CreateCommand(); _cmdObj.CommandText = "SELECT FileAttachment2 FROM ProductFiles WHERE ProductID=:ProductID"; _cmdObj.Parameters.Add(new OracleParameter("ProductID", txtProductID.Text)); _rdrObj = _cmdObj.ExecuteReader(); if (_rdrObj.HasRows) { if (_rdrObj.Read()) { OracleBFile _bfileObj = _rdrObj.GetOracleBFile(_rdrObj.GetOrdinal("FileAttachment2")); if (_bfileObj.FileExists) { _fileContents = _bfileObj.Value; //_fileContents now holds the array of bytes //representing the BFILE MessageBox.Show("The name of the file is: " + _bfileObj.FileName + "\nThe length of the file is :" + _bfileObj.Length); } } } 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 OracleExternalBinaryFile(OracleBFile bfile) { _bfile = bfile; bfile.OpenFile(); IsNull = bfile.IsNull || bfile.IsEmpty; }
/// <summary> /// 获取Oracle参数的值 /// </summary> /// <param name="oraParam"></param> /// <returns></returns> protected virtual object GetOraParamVal(OracleParameter oraParam) { if (oraParam.Value == null || (oraParam.Value is INullable && (oraParam.Value as INullable).IsNull)) { return(DBNull.Value); } object val = DBNull.Value; if (oraParam.Value is OracleXmlType) { OracleXmlType xmltype = (OracleXmlType)oraParam.Value; if (!xmltype.IsEmpty) { val = xmltype.Value; } } else if (oraParam.Value is OracleBlob) { OracleBlob blobVal = (OracleBlob)oraParam.Value; if (!blobVal.IsNull) { val = (oraParam.Value as OracleBlob).Value; } } else if (oraParam.Value is OracleClob) { OracleClob clobVal = (OracleClob)oraParam.Value; if (!clobVal.IsNull) { val = clobVal.Value; } } else if (oraParam.Value is OracleDecimal) { OracleDecimal decimalVal = (OracleDecimal)oraParam.Value; if (!decimalVal.IsNull) { val = decimalVal.Value; } } else if (oraParam.Value is OracleDate) { OracleDate dateVal = (OracleDate)oraParam.Value; if (!dateVal.IsNull) { val = dateVal.Value; } } else if (oraParam.Value is OracleString) { OracleString stringVal = (OracleString)oraParam.Value; if (!stringVal.IsNull) { val = stringVal.Value; } } else if (oraParam.Value is OracleBFile) { OracleBFile fileVal = oraParam.Value as OracleBFile; if (!fileVal.IsNull) { val = fileVal.Value; } } else if (oraParam.Value is OracleBinary) { OracleBinary binaryVal = (OracleBinary)oraParam.Value; if (!binaryVal.IsNull) { val = binaryVal.Value; } } else if (oraParam.Value is OracleTimeStamp) { OracleTimeStamp timeStampVal = (OracleTimeStamp)oraParam.Value; if (!timeStampVal.IsNull) { val = timeStampVal.Value; } } else if (oraParam.Value is OracleRefCursor) { using (OracleRefCursor timeStampVal = (OracleRefCursor)oraParam.Value) { if (timeStampVal.IsNull) { return(null); } OracleDataReader dataReader = timeStampVal.GetDataReader(); DataTable datatable = new DataTable(); datatable.Load(dataReader); return(datatable); } } else { val = oraParam.Value; } return(val); }
public virtual void ToCustomObject(OracleConnection con, IntPtr pUdt) { Id = (int)OracleUdt.GetValue( con, pUdt, "ID" ); Documento= (Documento)OracleUdt.GetValue( con, pUdt, "DOCUMENTO" ); archivo = (Archivo)OracleUdt.GetValue( con, pUdt, "ARCHIVO" ); asunto = (string)OracleUdt.GetValue(con, pUdt, "ASUNTO"); contenido = (OracleBFile)OracleUdt.GetValue( con, pUdt, "CONTENIDO" ); }
/********************************************************************************** ********************************************************************************** * * OVERWRITTEN METHODS * **********************************************************************************/ public virtual void FromCustomObject(OracleConnection con, IntPtr pUdt) { OracleUdt.SetValue( con, pUdt, "ID", Id ); OracleUdt.SetValue( con, pUdt, "DOCUMENTO", Documento); OracleUdt.SetValue( con, pUdt, "ARCHIVO", Archivo ); if (asunto != null) OracleUdt.SetValue(con, pUdt, "ASUNTO", Asunto); if (contenido != null) { OracleUdt.SetValue(con, pUdt, "CONTENIDO", contenido); } else { // TODO - create a oracle bfile to save var oracleDirName = ConfigurationManager.AppSettings["oracleDir"].ToString(); Contenido = new OracleBFile( con, oracleDirName, Archivo.Nombre + ".txt" ); OracleUdt.SetValue( con, pUdt, "CONTENIDO", Contenido ); } // end else }