Insert blob in oracle database with C#

Question

I have to persist a .csv in my database, but for a more testable application I prefer don't use procedures. Basically I just generate a file and the next instruction is put this in database.

Someone have some clue about best way to do this in code?

For clarification, are you looking for the SQL string for inserting the BLOB or, the C# datatype to use when handling BLOB data? If the later, I believe its a byte array. — Graham Conzett
– Graham Conzett, Commented Feb 4, 2011 at 19:52
You should use Oracle Data Provider for .NET in your C# project. There are many of useful classes with all needed functionality. oracle.com/technetwork/topics/dotnet/index-085163.html — Johnny
– Johnny, Commented Feb 4, 2011 at 20:01

ipavlic · Accepted Answer · 2012-07-23 13:02:57Z

Here is an example to insert blob data in oracle using c# and procedures (you said prefer that means you may).

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.IO;
using System.Text;

//Step 1
// Connect to database
// Note: Modify User Id, Password, Data Source as per your database setup
string constr = "User Id=Scott;Password=tiger;Data Source=orcl9i";

OracleConnection con = new OracleConnection(constr);
con.Open();
Console.WriteLine("Connected to database!");

// Step 2
// Note: Modify the Source and Destination location
// of the image as per your machine settings
String SourceLoc  = "D:/Images/photo.jpg";
String DestinationLoc = "D:/Images/TestImage.jpg";

// provide read access to the file

FileStream fs = new FileStream(SourceLoc, FileMode.Open,FileAccess.Read);

// Create a byte array of file stream length
byte[] ImageData = new byte[fs.Length];

//Read block of bytes from stream into the byte array
fs.Read(ImageData,0,System.Convert.ToInt32(fs.Length));

//Close the File Stream
fs.Close();

// Step 3
// Create Anonymous PL/SQL block string
String block = " BEGIN " +
               " INSERT INTO testblob (id, photo) VALUES (100, :1); " +
               " SELECT photo into :2 from testblob WHERE id = 100; " +
               " END; ";

// Set command to create Anonymous PL/SQL Block
OracleCommand cmd = new OracleCommand();
cmd.CommandText = block;
cmd.Connection = con;


// Since executing an anonymous PL/SQL block, setting the command type
// as Text instead of StoredProcedure
cmd.CommandType = CommandType.Text;

// Step 4
// Setting Oracle parameters

// Bind the parameter as OracleDbType.Blob to command for inserting image
OracleParameter param = cmd.Parameters.Add("blobtodb", OracleDbType.Blob);
param.Direction = ParameterDirection.Input;


// Assign Byte Array to Oracle Parameter
param.Value = ImageData;

// Bind the parameter as OracleDbType.Blob to command for retrieving the image
OracleParameter param2 = cmd.Parameters.Add("blobfromdb", OracleDbType.Blob);
param2.Direction = ParameterDirection.Output;

// Step 5
// Execute the Anonymous PL/SQL Block

// The anonymous PL/SQL block inserts the image to the
// database and then retrieves the images as an output parameter
cmd.ExecuteNonQuery();
Console.WriteLine("Image file inserted to database from " + SourceLoc);

// Step 6
// Save the retrieved image to the DestinationLoc in the file system

// Create a byte array
byte[] byteData = new byte[0];

// fetch the value of Oracle parameter into the byte array
byteData = (byte[])((OracleBlob)(cmd.Parameters[1].Value)).Value;

// get the length of the byte array
int ArraySize = new int();
ArraySize = byteData.GetUpperBound(0);

// Write the Blob data fetched from database to the filesystem at the
// destination location
FileStream fs1 = new FileStream(@DestinationLoc,
                                FileMode.OpenOrCreate, FileAccess.Write);
fs1.Write(byteData, 0,ArraySize);
fs1.Close();

Console.WriteLine("Image saved to " + DestinationLoc + " successfully !");
Console.WriteLine("");
Console.WriteLine("***********************************************************");
Console.WriteLine("Before running this application again, execute 'Listing 1' ");

I dont have hard coded source location as the file is uploaded by the client. So how can I open that file as I dont have the complete path to open the file with filestream object?

vc 74 · Accepted Answer · 2013-05-30 11:52:01Z

9

    private void btnSave_Click(object sender, EventArgs e)
    {
        try
        {
            //Read Image Bytes into a byte array
            byte[] blob = ReadFile(txtPath.Text);

            //Initialize Oracle Server Connection
            con = new OracleConnection(conString);

            //Set insert query
            string qry = "insert into Imgpn (imgpath,photo) values('" + txtPath.Text + "'," + " :BlobParameter )";
            OracleParameter blobParameter = new OracleParameter();
            blobParameter.OracleType = OracleType.Blob;
            blobParameter.ParameterName = "BlobParameter";
            blobParameter.Value = blob;

            //Initialize OracleCommand object for insert.
            cmd = new OracleCommand(qry, con);

            //We are passing Name and Blob byte data as Oracle parameters.
            cmd.Parameters.Add(blobParameter);

            //Open connection and execute insert query.
            con.Open();
            cmd.ExecuteNonQuery();

            MessageBox.Show("Image added to blob field");
            GetImagesFromDatabase();
            cmd.Dispose();
            con.Close();
            //this.Close();

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }
    byte[] ReadFile(string sPath)
    {
        //Initialize byte array with a null value initially.
        byte[] data = null;

        //Use FileInfo object to get file size.
        FileInfo fInfo = new FileInfo(sPath);
        long numBytes = fInfo.Length;

        //Open FileStream to read file
        FileStream fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read);

        //Use BinaryReader to read file stream into byte array.
        BinaryReader br = new BinaryReader(fStream);

        //When you use BinaryReader, you need to supply number of bytes to read from file.
        //In this case we want to read entire file. So supplying total number of bytes.
        data = br.ReadBytes((int)numBytes);
        return data;
    }
    void GetImagesFromDatabase()
    {
        try
        {
            //Initialize Oracle  connection.
            con = new OracleConnection(conString);
            //MessageBox.Show("Connection Successfull");
            //Initialize Oracle adapter.
            OracleDataAdapter oda = new OracleDataAdapter("Select * from Imgpn", con);

            //Initialize Dataset.
            DataSet DS = new DataSet();

            //Fill dataset with ImagesStore table.
            oda.Fill(DS, "Imgpn");

            //Fill Grid with dataset.

            dataGridView1.DataSource = DS.Tables["Imgpn"];
            //
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }

here is the simple way to insert image into oracle database ane retrieve ane show in datagridview

edited May 30, 2013 at 11:52

vc 74

38.3k7 gold badges80 silver badges96 bronze badges

answered Jun 2, 2011 at 13:24

Vikrant Jadhav

1171 silver badge3 bronze badges

3 Comments

vapcguy Over a year ago

Should be .OracleDbType, not .OracleType, on blobParameter.

Richard Deeming Over a year ago

Your code is vulnerable to SQL Injection. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Nyerguds Over a year ago

BinaryReader is IDisposable. It should be used in a using block.

Collectives™ on Stack Overflow

Insert blob in oracle database with C#

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related