Click here to Skip to main content
11,647,170 members (70,853 online)
Click here to Skip to main content

Reading and Writing BLOB Data to Microsoft SQL or Oracle Database

, 22 Dec 2009 CPOL 91.4K 47
Rate this:
Please Sign up or sign in to vote.
In this article, I will examine how to store and retrieve binary files such as image or PDF into Microsoft SQL or Oracle database.

Introduction

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

Using the Code

Reading a File into a Byte Array

byte[] byteArray = null;

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

   byteArray = new byte[fs.Length];

   int iBytesRead = fs.Read(byteArray, 0, (int)fs.Length);
}  

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.

CREATE TABLE BlobStore 
( 
    ID number, 
    BLOBFILE BLOB, 
    DESCRIPTION varchar2(100) 
);

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

string sql = " INSERT INTO BlobStore(ID,BLOBFILE,DESCRIPTION) _
		VALUES(:ID, :BLOBFILE, :DESCRIPTION) "; 
string strconn = ConfigurationManager.ConnectionStrings_
		["ConnectionString"].ConnectionString; 

using (OracleConnection conn = new OracleConnection(strconn)) 
{ 
    conn.Open(); 

     using (OracleCommand cmd = new OracleCommand(sql, conn)) 
    { 
        cmd.Parameters.Add("ID", OracleDbType.Int32, 1, ParameterDirection.Input); 
        cmd.Parameters.Add("BLOBFILE", OracleDbType.Blob, 
			byteArray , ParameterDirection.Input); 
        cmd.Parameters.Add("DESCRIPTION", OracleDbType.Varchar2, 
			"any thing here", ParameterDirection.Input); 
        cmd.ExecuteNonQuery(); 
    } 
}

In the next step, we would like to load data from Oracle to file.

string sql = " select * from BlobStore "; 
string strconn = ConfigurationManager.ConnectionStrings_
		["ConnectionString"].ConnectionString; 
using (OracleConnection conn = new OracleConnection(strconn)) 
{ 
  conn.Open(); 
  using (OracleCommand cmd = new OracleCommand(sql, conn)) 
  { 
      using (IDataReader dataReader = cmd.ExecuteReader()) 
      { 
          while (dataReader.Read()) 
          { 
             byte[] byteArray= (Byte[])dataReader["BLOBFILE"]; 
             using (FileStream fs = new FileStream
			(strfn, FileMode.CreateNew, FileAccess.Write)) 
             { 
                fs.Write(byteArray, 0, byteArray.Length); 
             } 
          } 
      } 
   } 
}

Saving BLOB Data from a File to Microsoft 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.

CREATE TABLE TestTable 
( 
    ID int, 
    BlobData varbinary(max), 
    DESCRIPTION nvarchar(100) 
)

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

using (SqlConnection connection = new SqlConnection("ConnectionString")) 
     { 
               connection.Open(); 
               using (SqlCommand command = 
		new SqlCommand("select BlobData from TestTable", connection)) 
               { 
                      byte[] buffer = (byte[])command.ExecuteScalar(); 
                       using (FileStream fs = new FileStream
					(@"C:\test.pdf", FileMode.Create)) 
                       { 
                           fs.Write(buffer, 0, buffer.Length); 
                       } 
                } 
     }

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

using (SqlConnection connection = new SqlConnection("ConnectionString")) 
{ 
    connection.Open(); 
    using(SqlCommand cmd = new SqlCommand("INSERT INTO TestTable_
	(ID, BlobData, DESCRIPTION) VALUES (@ID, @BlobData, @DESCRIPTION)", conn)) 
    { 

        cmd.Parameters.Add("@ID", SqlDbType.int).Value = 1; 
        cmd.Parameters.Add("@BlobData", SqlDbType.VarBinary).Value = ByteArray; 
        cmd.Parameters.Add("@DESCRIPTION", SqlDbType.NVarchar).Value = _
						"Any text Description"; 
        cmd.ExecuteNonQuery();         
    } 
}   

Summary

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

History

  • 22nd December, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Farooq Kaiser
Software Developer (Senior) http://www.Fairnet.com
Canada Canada
12+ years of complete software development life cycle experience for web based applications and multi-tier client-server desktop, primarily using LINQ, WCF, WWF, C#, ASP.NET, XML, XSLT, AJAX, Winforms,Visual Basic, JavaScript, JQuery, Google APIs, C++, VB.NET, C, ATL/COM, Open XML. Extensively involved in the requirement analysis, feasibility study, conceptualization, planning, architecture/design, configuration, development, quality assurance, implementation and release of the software products.

You may also be interested in...

Comments and Discussions

 
QuestionThat worked for SQL Pin
david E allday3-Jul-15 3:53
memberdavid E allday3-Jul-15 3:53 
QuestionUpdated for VB 2013 (but still using old MS / Oracle driver) Pin
Member 1163053921-Apr-15 7:37
memberMember 1163053921-Apr-15 7:37 
GeneralThank you so much! Pin
Member 113600431-Feb-15 17:57
memberMember 113600431-Feb-15 17:57 
QuestionJust a Thanks! Pin
Member 1120946325-Jan-15 13:48
memberMember 1120946325-Jan-15 13:48 
GeneralMy vote of 5 Pin
manoj kumar choubey9-Feb-12 22:08
membermanoj kumar choubey9-Feb-12 22:08 
GeneralUpdate requires a valid UpdateCommand when passed DataRow collection with modified rows.Update failed Pin
Nosa Osayamwen1-Feb-10 12:26
memberNosa Osayamwen1-Feb-10 12:26 
Hi Alaric,

I do not have problem with reading, writing and updating the blob in SQL. The problem I am having is that if I need to update information on the oracle database I cannot do it. I keep getting the following error message:

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.Update failed

I am going to try your method of connection and reading the blob data.

Could you shed some light on using the Update Command with Oracle Blob Data please? Thanks.

Nosa
GeneralOr you could use the providerName correctly Pin
Alaric Dailey30-Jan-10 15:00
memberAlaric Dailey30-Jan-10 15:00 
GeneralProblems with Editing or Updating the Oracle Database Pin
Nosa Osayamwen20-Jan-10 10:23
memberNosa Osayamwen20-Jan-10 10:23 
QuestionSaving emails from Exchange server to and from MS Sql. Pin
JanBorup4-Jan-10 11:47
memberJanBorup4-Jan-10 11:47 
GeneralVB Version Pin
Bob Carter30-Dec-09 7:50
memberBob Carter30-Dec-09 7:50 
GeneralMy vote of 1 Pin
g0got222-Dec-09 9:14
memberg0got222-Dec-09 9:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150804.2 | Last Updated 22 Dec 2009
Article Copyright 2009 by Farooq Kaiser
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid