Click here to Skip to main content
Click here to Skip to main content

Reading and Writing BLOB Data to Microsoft SQL or Oracle Database

, 22 Dec 2009
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)

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.

Comments and Discussions

 
GeneralMy vote of 5 Pinmembermanoj kumar choubey9-Feb-12 22:08 
GeneralUpdate requires a valid UpdateCommand when passed DataRow collection with modified rows.Update failed PinmemberNosa Osayamwen1-Feb-10 12:26 
GeneralOr you could use the providerName correctly PinmemberAlaric Dailey30-Jan-10 15:00 
GeneralProblems with Editing or Updating the Oracle Database PinmemberNosa Osayamwen20-Jan-10 10:23 
QuestionSaving emails from Exchange server to and from MS Sql. PinmemberJanBorup4-Jan-10 11:47 
GeneralVB Version PinmemberBob Carter30-Dec-09 7:50 
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
'save pdf or images etc (bytes) to db
'
Dim sFileName As String = ""
sFileName = "c:\inetpub\wwwroot\TestBlobToDb8v1\Reading and Writing BLOB Data to MS SQL or Oracle Database.pdf"
On Error Resume Next
Dim oFileStream As New System.IO.FileStream(sFileName, IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.Read)
If Err.Number <> 0 Then
Response.Write("
Err making file steam> " & Err.Description)
Response.Flush()
Exit Sub
End If
Dim iLenStream As Int32 = oFileStream.Length
Dim barray(iLenStream) As Byte
'read the file bytes
On Error Resume Next
Dim iNumBytesRead As Int32 = 0
iNumBytesRead = oFileStream.Read(barray, 0, oFileStream.Length)
If Err.Number <> 0 Then
Response.Write("
Err reading file into byte array> " & Err.Description)
Response.Flush()
Exit Sub
End If
'
'tblBlobs
' id autonum
' BlobData varbinary(max)
' [Desc] varchar(max)
'''''''''''''''''''''''''
Dim sConnSringLocalSql2005 As String = ""
sConnSringLocalSql2005 = "Data Source=(local);Initial Catalog=Test1;Persist Security Info=True;User ID=User9;Password=redacted"
'
Dim oConnLocal As New System.Data.SqlClient.SqlConnection
oConnLocal.ConnectionString = sConnSringLocalSql2005
On Error Resume Next
oConnLocal.Open()
If Err.Number <> 0 Then
Response.Write("
Err open conn local sql2005> " & Err.Description)
Response.Flush()
oConnLocal.Close()
oFileStream.Close()
Exit Sub
End If
Dim oCmd As New System.Data.SqlClient.SqlCommand
oCmd.Connection = oConnLocal
oCmd.CommandType = Data.CommandType.StoredProcedure
oCmd.CommandTimeout = 6000
oCmd.CommandText = "spPostBlob"
'
Dim Param1 As System.Data.SqlClient.SqlParameter
Param1 = oCmd.Parameters.Add("@BlobData", Data.SqlDbType.VarBinary, -1)
Param1.Direction = Data.ParameterDirection.Input
Param1.Value = barray
'
Dim Param2 As System.Data.SqlClient.SqlParameter
Param2 = oCmd.Parameters.Add("@Desc", Data.SqlDbType.VarChar, -1)
Param2.Direction = Data.ParameterDirection.Input
Param2.Value = "TestOnPDF"
On Error Resume Next
oCmd.ExecuteNonQuery()
If Err.Number <> 0 Then
Response.Write("
Err> " & oCmd.CommandText & " - " & Err.Description)
Response.Flush()
oConnLocal.Close()
oFileStream.Close()
Else
Response.Write("
OK post blob!")
Response.Flush()
End If
oConnLocal.Close()
oFileStream.Close()
 
'''''''''''''''''''''''''''''''''''''
'create procedure spPostBlob
'
'@BlobData varbinary(max),
'@Desc varchar(max)
'as
'
'insert into tblBlobs
'(BlobData, [Desc])
'values()
'(@BlobData, @Desc)
'''''''''''''''''''''''''''''''''
End Sub
 
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
'''''''''''''''''''
' Read blob back from db
'''''''''''''''''''''''''''
'Dim iLenStream As Int32 =
'Dim barray(iLenStream) As Byte
Dim sConnSringLocalSql2005 As String = ""
sConnSringLocalSql2005 = "Data Source=(local);Initial Catalog=Test1;Persist Security Info=True;User ID=9;Password=redacted"
'
Dim oConnLocal As New System.Data.SqlClient.SqlConnection
oConnLocal.ConnectionString = sConnSringLocalSql2005
On Error Resume Next
oConnLocal.Open()
If Err.Number <> 0 Then
Response.Write("
Err open conn local sql2005> " & Err.Description)
Response.Flush()
oConnLocal.Close()
'oFileStream.Close()
Exit Sub
End If
Dim oCmd As New System.Data.SqlClient.SqlCommand
oCmd.Connection = oConnLocal
oCmd.CommandType = Data.CommandType.StoredProcedure
oCmd.CommandTimeout = 6000
oCmd.CommandText = "spGetBlob2"
Dim Param1 As System.Data.SqlClient.SqlParameter
Param1 = oCmd.Parameters.Add("@BlobData", Data.SqlDbType.VarBinary, -1)
Param1.Direction = Data.ParameterDirection.Output
Param1.Value = Nothing
'
Dim Param2 As System.Data.SqlClient.SqlParameter
Param2 = oCmd.Parameters.Add("@Desc", Data.SqlDbType.VarChar, -1)
Param2.Direction = Data.ParameterDirection.Output
Param2.Value = Nothing
'
Dim Param3 As System.Data.SqlClient.SqlParameter
Param3 = oCmd.Parameters.Add("@idToFetch", Data.SqlDbType.Int, 4)
Param3.Direction = Data.ParameterDirection.Input
Param3.Value = 1
'
Dim Param4 As System.Data.SqlClient.SqlParameter
Param4 = oCmd.Parameters.Add("@iLenBlob", Data.SqlDbType.Int, 4)
Param4.Direction = Data.ParameterDirection.Output
Param4.Value = 0
'
On Error Resume Next
oCmd.ExecuteNonQuery()
If Err.Number <> 0 Then
Response.Write("
Err > " & oCmd.CommandText & " - " & Err.Description)
Response.Flush()
oConnLocal.Close()
Exit Sub
End If
'
Dim barray(Param4.Value) As Byte
barray = Param1.Value
Dim sFileName As String = "c:\windows\temp\test.pdf" 'Must be a folder where Network System writes
If Err.Number <> 0 Then
Response.Write("
FileStream Err > " & Err.Description)
Response.Flush()
End If
Dim oFS As New System.IO.FileStream(sFileName, IO.FileMode.Create, IO.FileAccess.Write, IO.FileShare.Write)
 
On Error Resume Next
ofs.Write(barray, 0, Param4.Value)
If Err.Number <> 0 Then
Response.Write("
FileStream Err > " & Err.Description)
Response.Flush()
End If
oConnLocal.Close()
oFS.Close()
''''''''''''''''''''''''''''''''''''''''''''''''
'create procedure spGetBlob2
 
'@BlobData varbinary(max) output,
'@Desc varchar(max) output,
'@idToFetch int,
'@iLenBlob int output
 

'as
 
'select @BlobData = BlobData,
' @Desc = [Desc],
' @iLenBlob = len(BlobData)
'from tblBlobs
'where id = @idToFetch
''''''''''''''''''''''''''''''''''''''''''''''''
End Sub
GeneralMy vote of 1 Pinmemberg0got222-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 | Mobile
Web03 | 2.8.140721.1 | Last Updated 22 Dec 2009
Article Copyright 2009 by Farooq Kaiser
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid