CODEWITHSUNDEEP
c#sql
Emilia
Emilia

Reputation: 49

Get pictures that stored in database to picture box

I am trying to get back the image that I stored in my database to a picture box 

SqlConnection conect = new SqlConnection("Data Source=DESKTOP-R34C6VV\\SQL;Initial Catalog=Restaurant;Integrated Security=True");
conect.Open();

string sql = "select Image from Menu where Name=@name";

SqlCommand cmd = new SqlCommand(sql,conect);
cmd.Parameters.AddWithValue("@name",Invoice_combo.Text);

SqlDataReader read = cmd.ExecuteReader();

read.Read();

if (read.HasRows)
{
    byte[] img = ((byte[])read[0]);

    if (img == null)
    {
        pictureBox1.Image = null;
    }
    else
    {
        MemoryStream mystream = new MemoryStream(img);
        pictureBox1.Image = System.Drawing.Image.FromStream(mystream);
        cmd.Dispose();
    }
}
else
{
    MessageBox.Show("data not available");
}
conect.Close();

but every time I run the program, I get this error on this line 

pictureBox1.Image = System.Drawing.Image.FromStream(mystream);

Additional information: Parameter is not valid

When I searched I saw a lot people have this problem but I did not find any solution to the problem; I think it is a problem that happens to beginners.

Can anyone help me with this?

This is my database:

SELECT TOP 1000 
    [MenuID],
    [Category],
    [Name],
    [Price],
    [Image]
FROM 
    [Restaurant].[dbo].[Menu]

This is the code I used for inserting the image:

OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "png files(*.png)|*.png|jpg files(*.jpg)|*.jpg|ALL files(*.*)|*.*";

if (dialog.ShowDialog() == DialogResult.OK)
{
    imglocation = dialog.FileName.ToString();
    pictureBox1.ImageLocation = imglocation;
}

on the add button 

byte[] image = null;

FileStream stream = new FileStream(imglocation, FileMode.Open, FileAccess.Read);
BinaryReader brs = new BinaryReader(stream);
image = brs.ReadBytes((int)stream.Length);

String str = "Data Source=DESKTOP-R34C6VV\\SQL;Initial Catalog=Restaurant;Integrated Security=True";

String query1 = "Insert into Menu values ( '" + category + "','" + itemname + "'," + price + ",'"+image+"')";

String query2 = "Select  MenuID from Menu where Name='" + itemname + "'";

SqlConnection con = null;
con = new SqlConnection(str);

SqlCommand cmd1 = new SqlCommand(query1, con);
SqlCommand cmd2 = new SqlCommand(query2, con);

SqlDataReader MyReader1;
SqlDataReader MyReader2;

con.Open();
MyReader1 = cmd1.ExecuteReader();
con.Close();

MessageBox.Show("Menu added successfully");

Thread.Sleep(2000);

con.Open();
MyReader2 = cmd2.ExecuteReader();

if (MyReader2.Read())
{
    String id = System.Convert.ToString(MyReader2.GetInt32(0));
    MessageBox.Show(itemname + " was given with MenuID - " + id);
}

con.Close();

Upvotes: 1

Views: 131

Answers (2)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236308

Your problem source is the way you save pictures to the database. When you build insert command, here is what will be inserted as the image:

"Insert into Menu values ( 'someCategory','someItemname',42,'System.Byte[]')"
                                                                   ^

When you call ToString() of the array variable, you will get the type name of your array. So you are not saving any images to the database - you only save same string over and over again.

To fix this issue stop injecting values into your SQL command text. Use SQL parameters instead:

var insertMenuQuery = "INSERT INTO Menu VALUES(@category, @itemName, @price, @image)";
SqlCommand cmd = new SqlCommand(insertMenuQuery, con);
cmd.Parameters.AddWithValue("@category", category);
cmd.Parameters.AddWithValue("@itemName", itemName);
cmd.Parameters.AddWithValue("@price", price);
cmd.Parameters.AddWithValue("@image", image);
// or cmd.Parameters.Add("@image", SqlDbType.VarBinary).Value = image;

This way proper binary data of image will be stored in your database.

NOTE: If images are bigger than 1Mb then consider to use FILESTREAM instead of VARBINARY.

Upvotes: 1

blackforest-tom
blackforest-tom

Reputation: 487

The MemoryStream goes out of context after your else path finished. So i guess it would be undefined behaviour to keep using it. Put your MemoryStream into a class member variable and .Dispose() it before the end of the application.

Also on a side note, your SqlCommand is not being disposed of correctly. It only gets disposed of in your else {} block, which is not guaranteed to happen. Put it into a using statement, surrounding everything that needs access to it to guarantee correct disposal.

Upvotes: 1

Related Questions