Retrieving and writing into BLOB type column

This article explains ‘how to store and retrieve BLOB (Binary Large Objects) type data into BLOB data type column in database table.

BLOB is collection of ‘binary data’ stored as a single entity in database. BLOBs are session objects or images or audio files or multi media objects.

Note that when session object is stored into BLOB type column, you need to Serialize the session object and insert into database as shown in below source code. Serialization is the process of converting an object or data structure into sequence of bytes.

Retrieve BLOB type data

Note that in this example session object retrieval from BLOB type column is explained.

Below is the source code to read BLOB type data from database column. Note that ORACLE is the database management system in this example.

// <summary>Reading blob Type from Database example</summary>
// <param name="sender"></param>
// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
string connString = ConfigurationManager.ConnectionStrings
[Your ConnString key].ConnectionString;

//Connection object
OracleConnection conn = new OracleConnection(oradb);
//Open connection
conn.Open();

//Command 
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM YourTable";

//Error Handler
try
{
//OracleDataReader object
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
//Get your blob type column which is Index 4 
OracleBlob blob = dr.GetOracleBlob(4);
//Test 
Response.Write(blob.Value);
}

catch( Exception ex )
{
throw new Exception("error Reading data from blob Types Table", ex);
}

//Close connection
conn.Dispose();
}

Insert into BLOB type column

Session object should be serialized before inserting into BLOB type column. In the source code below how to Serialize a session object and then inserting  into BLOB type column is shown.

// <summary>
//on button click event to insert values 
//into BLOB Type column </summary>
// <param name="sender"></param>
// <param name="e"></param>
protected void button2_Click(object sender, EventArgs e)
{
//Connection string
string oradb = Read from web.config
//Connection 
OracleConnection conn = new OracleConnection(oradb);
//Open connection
conn.Open();

// Start a transaction
OracleTransaction txn = conn.BeginTransaction();
//create Command object 
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;

// Lock the result set using the "FOR UPDATE" clause
// Running this SQL Statement in SQL editor locks the row, 
causing code Step out

cmd.CommandText = "SELECT object FROM your Table 
WHERE <condition> FOR UPDATE ";

//Error Handler
try
{
//Reader
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
		
//Read BLOB from DataReader
OracleBlob blob = dr.GetOracleBlob(0);

//byte array
byte[] bytes;

	
//get the byte array
MemoryStream memStream1 = new MemoryStream();
BinaryFormatter formatter = new BinaryFormatter();

//Serializing the object
formatter.Serialize(memStream1, Your object);
bytes = memStream1.GetBuffer();
memStream1.Close();
//write blob object to Database 
blob.Write(bytes, 0, bytes.Length);

//Commit data and close connection
txn.Commit();
Response.Write("updated to new data");
}

catch( Exception ex )
{
throw new Exception("error writing session data to Databse", ex);
}
conn.Close();
}

NB:- To improve performance it is highly advisable to use ODP.NET (Oracle Data Provider for .NET)

//ODP.NET namespce
using Oracle.DataAccess.Types;

References

Reading blob Type from Database example
http://msdn.microsoft.com/en-us/library/cydxhzhz(VS.80).aspx

Transaction Lock with Blobs example http://www.oracle.com/technology/sample_code/tech/windows/odpnet/howto/rlock/index.html

Serialization Example:
http://progtutorials.tripod.com/C_Sharp.htm#_Toc65227888