How to insert and retrieve image from PostgreSql using C#

Question

I am trying to insert an image into Postgres and retrieve that image from postgresql using C#.

I have two columns in my table: memberid (character varying) and member_photo (bytea)

Here is my code to insert the image:

using (FileStream pgFileStream = new FileStream("D:\\Capture.jpg", FileMode.Open, FileAccess.Read))
{
    using (BinaryReader pgReader = new BinaryReader(new BufferedStream(pgFileStream)))
    {
        NpgsqlCommand command = new NpgsqlCommand();

        byte[] ImgByteA = pgReader.ReadBytes(Convert.ToInt32(pgFileStream.Length));
        command.CommandText = "insert into membermaster (memberid, member_photo) VALUES ('65', @Image)";
        command.Connection = Program.conn;

        Program.conn.Close();
        Program.conn.Open();

        //command.Parameters.Add(new NpgsqlParameter("Image", ImgByteA));
        command.Parameters.Add("@Image", ImgByteA).Value = ImgByteA;

        command.ExecuteNonQuery();

        Program.conn.Close();
    }
}

Here is my code to retrieve the image

NpgsqlCommand command = new NpgsqlCommand();
command.CommandText = "select member_photo from membermaster where memberid='65'";
command.Connection = Program.conn;

try
{
    Program.conn.Close();
    Program.conn.Open();

    byte[] productImageByte = command.ExecuteScalar() as byte[];

    if (productImageByte != null)
    {
        using (MemoryStream productImageStream = new System.IO.MemoryStream(productImageByte))
        {
            ImageConverter imageConverter = new System.Drawing.ImageConverter();
            pictureBox1.Image = imageConverter.ConvertFrom(productImageByte) as System.Drawing.Image;
            // image.Save(imageFullPath, System.Drawing.Imaging.ImageFormat.Jpeg);

            pictureBox1.Image = System.Drawing.Image.FromStream(productImageStream);
        }
    }
}
catch
{
    Program.conn.Close();
    throw;
}

The code to insert the image is working fine, but I can't show this image in a picturebox.

I get an error :

Parameter is not valid

Please help me solve this problem

Jonathan Willcock · Accepted Answer · 2017-09-09 08:51:59Z

9

AFAIK you cannot retrieve a byte[] using ExecuteScalar. You should use ExecuteReader instead. Just to be on the safe side when inserting parameters, I prefer to specify types myself, so my insert looks like this:

using (var conn = new NpgsqlConnection(connString))
{
    string sQL = "insert into picturetable (id, photo) VALUES(65, @Image)";
    using (var command = new NpgsqlCommand(sQL, conn))
    {
        NpgsqlParameter param = command.CreateParameter();
        param.ParameterName = "@Image";
        param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bytea;
        param.Value = ImgByteA;
        command.Parameters.Add(param);

        conn.Open();
        command.ExecuteNonQuery();
    }
}

I can then retrieve and load the image like this:

using (var conn = new NpgsqlConnection(connString))
{
    string sQL = "SELECT photo from picturetable WHERE id = 65";
    using (var command = new NpgsqlCommand(sQL, conn))
    {
        byte[] productImageByte = null;
        conn.Open();
        var rdr = command.ExecuteReader();
        if (rdr.Read())
        {
            productImageByte = (byte[])rdr[0];
        }
        rdr.Close();
        if (productImageByte != null)
        {
            using (MemoryStream productImageStream = new System.IO.MemoryStream(productImageByte))
            {
                ImageConverter imageConverter = new System.Drawing.ImageConverter();
                pictureBox1.Image = imageConverter.ConvertFrom(productImageByte) as System.Drawing.Image;
            }
        }
    }
}

I do not know if specifying the datatype on insert makes any difference, so try just retrieving using a Reader first. If that doesn't work, then I suggest changing your insert routine to something like mine.

Please note in my example id is an integer, not a character varying!

answered Sep 9, 2017 at 8:51

Jonathan Willcock

5,4303 gold badges25 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Sivashankar Over a year ago

sir thank you for your reply.i am getting the same error again..parameter is not valid

Jonathan Willcock Over a year ago

@Sivashankar Have you tried changing your insert like mine?

Sivashankar Over a year ago

string sQL = "insert into membermaster (memberid, member_photo) VALUES('655', @Image)"; using (var command = new NpgsqlCommand(sQL, Program.conn)) { NpgsqlParameter param = command.CreateParameter(); param.ParameterName = "@Image"; param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bytea; param.Value = ImgByteA; command.Parameters.Add(param); Program.conn.Close(); Program.conn.Open(); command.ExecuteNonQuery(); }

Sivashankar Over a year ago

in this line it shows error pictureBox1.Image = imageConverter.ConvertFrom(productImageByte) as System.Drawing.Image;

Jonathan Willcock Over a year ago

Have you checked that the byte[] retrieved is the same as you inserted?

|

Steven McCormack · Accepted Answer · 2023-02-01 13:04:52Z

While the code in the accepted answer is working, it is not true that you cannot use ExecuteScalar. It might have been an issue in older versions, but using Npgsql 6.0.7 the following works:

 public static async Task<byte[]?> GetByteaAsync(int id, string connectionString) {
    byte[]? result = null;
    await using (var con = new NpgsqlConnection(connectionString)) {
        await con.OpenAsync();
        await using (var cmd = new NpgsqlCommand("SELECT field_name from table_name where id=@id", con) {CommandType = CommandType.Text}) {
            cmd.Parameters.Add("@id", NpgsqlDbType.Integer).Value = id;

            result = ((byte[]?) await cmd.ExecuteScalarAsync());
        }

        await con.CloseAsync();
    }

    return result;
}

Collectives™ on Stack Overflow

How to insert and retrieve image from PostgreSql using C#

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related