In this article, i will examine how to store and retrieve binary files such as image or PDF into MS SQL or Oracle database.

1. Reading a File into a Byte Array

   		   1: byte[] m_barrImg = null;	
   	

   		   2: using (FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.Read))	
   	

   		   3: {	
   	

   		   4:      m_barrImg = new byte[fs.Length];	
   	

   		   5:      int iBytesRead = fs.Read(m_barrImg, 0, (int)fs.Length);	
   	

   		   6:      fs.Close();	
   	

   		   7: }	
   	

   
   
2. Saving BLOB data from a file to Oracle
   For oracle, you will have to download ODP.NET from Oracle.  The following script will create a table that will hold the Blob data in Oracle.
   

   		   1: CREATE TABLE BlobStore	
   	

   		   2: (	
   	

   		   3:  ID number,	
   	

   		   4:  BLOBFILE BLOB,	
   	

   		   5:  DESCRIPTION varchar2(100)	
   	

   		   6: );	
   	

   Now, we would like to write Blob in Oracle using c#.
   

   		   1: string sql = " INSERT INTO BlobStore(ID,BLOBFILE,DESCRIPTION) VALUES(:ID, :BLOBFILE, :DESCRIPTION) ";	
   	

   		   2:  	
   	

   		   3: string strconn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;	
   	

   		   4: using (OracleConnection conn = new OracleConnection(strconn))	
   	

   		   5: {	
   	

   		   6:   conn.Open();	
   	

   		   7:  	
   	

   		   8:   using (OracleCommand cmd = new OracleCommand(sql, conn))	
   	

   		   9:    {	
   	

   		  10:       cmd.Parameters.Add("ID", OracleDbType.Int32, 1, ParameterDirection.Input);	
   	

   		  11:       cmd.Parameters.Add("BLOBFILE", OracleDbType.Blob, m_barrImg , ParameterDirection.Input);	
   	

   		  12:       cmd.Parameters.Add("DESCRIPTION", OracleDbType.Varchar2, "any thing here", ParameterDirection.Input);	
   	

   		  13:       cmd.ExecuteNonQuery();	
   	

   		  14:    }	
   	

   		  15: }	
   	

   		  16:  	
   	

   In next step, we would like to load a data from oracle to file.

   		   1: string sql = " select * from BlobStore ";	
   	

   		   2: string strconn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;	
   	

   		   3:  	
   	

   		   4: using (OracleConnection conn = new OracleConnection(strconn))	
   	

   		   5: {	
   	

   		   6:     conn.Open();	
   	

   		   7:  	
   	

   		   8:         using (OracleCommand cmd = new OracleCommand(sql, conn))	
   	

   		   9:         {	
   	

   		  10:                     using (IDataReader dataReader = cmd.ExecuteReader())	
   	

   		  11:                     {	
   	

   		  12:                         while (dataReader.Read())	
   	

   		  13:                         {	
   	

   		  14:                             byte[] barrImg = (Byte[])dataReader["PDF_FILE"];	
   	

   		  15:                             using (FileStream fs = new FileStream(strfn, FileMode.CreateNew, FileAccess.Write))	
   	

   		  16:                             {	
   	

   		  17:                                 fs.Write(barrImg, 0, barrImg.Length);	
   	

   		  18:                             }	
   	

   		  19:  	
   	

   		  20:                         }	
   	

   		  21:                     }	
   	

   		  22:          }	
   	

   		  23:  	
   	

   		  24: }	
   	

3. Saving BLOB data from a file to MS SQL
   Storing and retrieving Blob data in SQL Server is similar to Oracle. Here are code snippets that show saving and loading in SQL server.  The following script will create a table that will hold the Blob data in SQL server. 
   

   		   1: CREATE TABLE TestTable	
   	

   		   2: (	
   	

   		   3:  ID int,	
   	

   		   4:  BlobData varbinary(max),	
   	

   		   5:  DESCRIPTION nvarchar(100)	
   	

   		   6: )	
   	

   
   The following code shows how to Load from SQL Server to file.

   		   1: using (SqlConnection connection = new SqlConnection("ConnectionString"))	
   	

   		   2: {	
   	

   		   3:          connection.Open();	
   	

   		   4:          using (SqlCommand command = new SqlCommand("select BlobData from TestTable", connection))	
   	

   		   5:           {	
   	

   		   6:                  byte[] buffer = (byte[])command.ExecuteScalar();	
   	

   		   7:                  using (FileStream fs = new FileStream(@"C:\test.pdf", FileMode.Create))	
   	

   		   8:                  {	
   	

   		   9:                      fs.Write(buffer, 0, buffer.Length);	
   	

   		  10:                  }	
   	

   		  11:           }	
   	

   		  12: }	
   	

   
   The following code shows how to save from byte array to SQL Server.

		   1: using (SqlConnection connection = new SqlConnection("ConnectionString"))	
	

		   2: {	
	

		   3:    connection.Open();	
	

		   4:    using(SqlCommand cmd = new SqlCommand("INSERT INTO TestTable(ID, BlobData, DESCRIPTION) VALUES (@ID, @BlobData, @DESCRIPTION)", conn))	
	

		   5:    {    	
	

		   6:      cmd.Parameters.Add("@ID", SqlDbType.int).Value = 1;  	
	

		   7:      cmd.Parameters.Add("@BlobData", SqlDbType.VarBinary).Value = ByteArray;    	
	

		   8:      cmd.Parameters.Add("@DESCRIPTION", SqlDbType.NVarchar).Value = "Any text Description";    	
	

		   9:      cmd.ExecuteNonQuery();	
	

		  10:    }	
	

		  11: }	
	

Summary

In this article, we examined how to store and retrieve binary files such as image or PDF into Oracle or MS SQL database.