// <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
}
