ODP.NET

Hi,

I am relatively new to this type of development so apologies if this question is a bit basic.

I am trying to write a binary document (.doc) to a blob and read it back again, constructing the original word file. I have the following code for reading and writing the file:

private void save_addagreement_Click(object sender, EventArgs e)
{
// Save the agreement to the database

int test_setting = 0;

// create an OracleConnection object to connect to the
// database and open the connection
string constr;

if (test_setting == 0)
constr = "User Id=royalty;Password=royalty;data source=xe";
else
constr = "User ID=lob_user;Password=lob_password;data source=xe";

OracleConnection myOracleConnection = new OracleConnection(constr);

myOracleConnection.Open();

// create an OracleCommand object to hold a SQL statement
OracleCommand myOracleCommand = myOracleConnection.CreateCommand();

myOracleCommand.CommandText = "insert into blob_content(id, blob_column) values 2, empty_blob())";

OracleDataReader myOracleDataReader = myOracleCommand.ExecuteReader();

// step 2: read the row

OracleTransaction myOracleTransaction = myOracleConnection.BeginTransaction();

myOracleCommand.CommandText =
"SELECT id, blob_column FROM blob_content WHERE id = 2";

myOracleDataReader = myOracleCommand.ExecuteReader();

myOracleDataReader.Read();

Console.WriteLine("myOracleDataReadre[\"id\"] = " + myOracleDataReader["id"]);

OracleBlob myOracleBlob = myOracleDataReader.GetOracleBlobForUpdate(1);
Console.WriteLine("OracleBlob = " + myOracleBlob.Length);

myOracleBlob.Erase();

FileStream fs = new FileStream(agreement_filename.Text, FileMode.Open, FileAccess.Read);
Console.WriteLine("Opened " + agreement_filename.Text + " for reading");

int numBytesRead;

byte[] byteArray = new byte[fs.Length];
numBytesRead = fs.Read(byteArray, 0, (Int32)fs.Length);
Console.WriteLine(numBytesRead + " read from file");

myOracleBlob.Write(byteArray, 0, byteArray.Length);
Console.WriteLine(byteArray.Length + " written to blob object");
Console.WriteLine("Blob Length = " + myOracleBlob.Length);

fs.Close();

myOracleDataReader.Close();
myOracleConnection.Close();
}

This gives the following console output:

myOracleDataReadre["id"] = 2
OracleBlob = 0
Opened D:\sample_files\oly_in.doc for reading
56832 read from file
56832 written to blob object
Blob Length = 56832

My write to file code is:

private void save_agreement_to_disk_Click(object sender, EventArgs e)
{
string filename;

SaveFileDialog savedoc = new SaveFileDialog();

if (savedoc.ShowDialog() == DialogResult.OK)
{
filename = savedoc.FileName;

// create an OracleConnection object to connect to the
// database and open the connection

OracleConnection myOracleConnection = new OracleConnection("User ID=royalty;Password=royalty");

myOracleConnection.Open();

// create an OracleCommand object to hold a SQL statement
OracleCommand myOracleCommand = myOracleConnection.CreateCommand();

myOracleCommand.CommandText =
"SELECT id, blob_column " +
"FROM blob_content " +
"WHERE id = 2";

OracleDataReader myOracleDataReader = myOracleCommand.ExecuteReader();

myOracleDataReader.Read();

Console.WriteLine("myOracleDataReader[id] = " + myOracleDataReader["id"]);

//Step 2: Get the LOB locator
OracleBlob myOracleBlob = myOracleDataReader.GetOracleBlobForUpdate(1);

Console.WriteLine("Blob size = " + myOracleBlob.Length);

//Step 3: get the BLOB data using the read() method
byte[] byteArray = new byte[500];
int numBytesRead;
int totalBytes = 0;

FileStream fs = new FileStream(filename, FileMode.OpenOrCreate, FileAccess.Write);

while ((numBytesRead = myOracleBlob.Read(byteArray, 0, 500)) > 0)
{
totalBytes += numBytesRead;
fs.Write(byteArray, 0, byteArray.Length);
Console.WriteLine("numBytes = " + numBytesRead + " totalBytes = " + totalBytes);
}

Console.WriteLine((int)fs.Length + " bytes written to file");

fs.Close();

myOracleDataReader.Close();
myOracleConnection.Close();
}

This gives the following console output:

myOracleDataReader[id] = 2
Blob size = 0
0 bytes written to file

If I manually add the blob file using the following:

DECLARE
my_blob BLOB;

BEGIN

-- load the BLOB
my_bfile := BFILENAME('SAMPLE_FILES_DIR', 'binaryContent.doc');
SELECT blob_column
INTO my_blob
FROM blob_content
WHERE id = 1 FOR UPDATE;
DBMS_LOB.FILEOPEN(my_bfile, dbms_lob.file_readonly);
DBMS_LOB.LOADFROMFILE(my_blob, my_bfile, DBMS_LOB.GETLENGTH(my_bfile), 1, 1);

DBMS_LOB.FILECLOSEALL();
COMMIT;
END;
/

COMMIT;

The write to file works perfectly. This tells me that there must be something wrong with my code that is writing the blob to the database. I tried where possible to following the Oracle article using large objects in .NET but that (along with most things on the internet) focus on uploading text files.

Thanks in advance.

Chris.