Insert image in Oracle Database using OleDb from a C# application

0

I want to insert a PNG image into an OracleDatabase using OleDb and a C# application. The table looks like this:

CREATE TABLE Plant
(
    Id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    Name VARCHAR2(50) NOT NULL,
    Image BLOB null,
    CONSTRAINT plant_pk PRIMARY KEY (Id)
);

Below is the code:

public static void AddBinaryParameterToCommand(OleDbCommand cmd, string parameterColumn, object parameter)
        {
            if (cmd != null)
            {
                if (parameter != null && !parameter.ToString().Equals(""))
                {
                    OleDbParameter blobParameter = new OleDbParameter();
                    blobParameter.OleDbType = OleDbType.LongVarBinary;
                    blobParameter.Direction = ParameterDirection.InputOutput;
                    blobParameter.ParameterName = parameterColumn;
                    blobParameter.Value = parameter;

                    cmd.Parameters.Add(blobParameter);
                }

                else
                    cmd.Parameters.Add(new OleDbParameter(parameterColumn, DBNull.Value));
            }
        }

       public int InsertPlant(string name, byte[] image)
        {
            int id = 0;
            using (var connection = new OleDbConnection(ConnectionString))
            {
                var commandGetIdText = @"SELECT MAX(id) FROM PLANT";

                connection.Open();
                using (var command = new OleDbCommand(commandGetIdText, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        reader.Read();
                        id = int.Parse(reader[0].ToString()) + 1;
                    }
                }

                var commandText = string.Format("INSERT INTO PLANT(ID,NAME,IMAGE) VALUES (?, ?, ?)");
                using (var command = new OleDbCommand(commandText, connection))
                {
                    Utils.AddParameterToCommand(command, "ID", id);
                    Utils.AddParameterToCommand(command, "NAME", name);
                    Utils.AddBinaryParameterToCommand(command, "IMAGE", image);

                    command.ExecuteNonQuery();
                    connection.Close();
                }
            }


            return id;
        }

 private void button_UploadMap_Click(object sender, EventArgs e)
        {
            var openFileDialog = new OpenFileDialog
            {
                Multiselect = true
            };

            var path = string.Empty;
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                path = openFileDialog.FileName;
            }
            else
            {
                return;
            }

            byte[] imageArray = File.ReadAllBytes(path);

            var palletMapDL = new PalletMapDL("Data Source=192.168.1.21/orcl;Persist Security Info=True; Password=test;User ID=test; Provider=MSDAORA; OLEDB.NET=True; PLSQLRSet=1");
            palletMapDL.InsertPlant("Test Plant 01", imageArray);

When execute command.ExecuteNonQuery(); I got this error message:

System.InvalidOperationException: 'Command parameter[2] '' data value could not be converted for reasons other than sign mismatch or data overflow.

OleDbException: 'MSDAORA' failed with no error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21).

Do you know what could be the issue?

Thanks

c#
oracle
image
blob
oledb
asked on Stack Overflow Feb 7, 2020 by Oihc • edited Feb 7, 2020 by Oihc

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0