C# Insert Picture into Ms Access

Question

I would like to thanks to all who helped in last question. but now, i have problem with another statement which is saveimageto MS access. First, I would like to ask, on ms access database, Datatype should put attachment?

my code:

private void button2_Click(object sender, EventArgs e)
        {

            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "insert into Table1 (id,picture) values ('" + textBox1.Text +  "')";

            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            System.Windows.Forms.MessageBox.Show("Created", "Caption", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
            con.Close();

        }

I insert my picture to picturebox using openFIledialog.

Answer 1

First, use parameters. NEVER concat strings for SQL commands, as it opens itself to SQL Injection. That's an easy to follow good practice that'll avoid you a lot of trouble in the future.

That said, something like this should work:

// You've got the filename from the result of your OpenDialog operation
var pic = File.ReadAllBytes(yourFileName);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into Table1 (id, picture) values (@p1, @p2)";
cmd.Parameters.AddWithValue("@p1", TextBox1.Text);
cmd.Parameters.AddWithValue("@p2", pic);
cmd.ExecuteNonQuery();

Citing from memory here, but let me know if that code gives you trouble. If I remember correctly, something like that should work.

EDIT.- If you're preloading the image on a PictureBox control, convert that image to a byte array and then use that byte array as the second parameter.

EDIT (2).- A little clarification. If you're are getting your image from a file, you have a path to it; then you can use File.ReadAllBytes(string path). In my example I was assuming that yourFileName was the file and path name of the selected file after a successful OpenDialog operation. So you can use it like this:

byte[] fromPath = File.ReadAllBytes(@"C:\walls\aurora.jpg");

and you'd store into the byte array fromPath the image, converted to bytes and ready to be used in an insertion command as seen above.

BUT if you're getting your image from a picture box control, things are a little different:

MemoryStream ms = new MemoryStream();
pictureBox1.Image.Save(ms, Imaging.ImageFormat.Jpeg);
byte[] fromControl = ms.GetBuffer();

In that example I've created a MemoryStream, filled it with the content of the picturebox control and then passed it to the byte array, and again we're ready to use it as a parameter for our insertion query.

Oh, don't forget to add

using System.IO;
using System.Drawing;

to your usings.