Store image in database and retrieve it

Question

My code for inserting image in database is as follows:

MemoryStream ms =new MemoryStream();
byte[] PhotoByte=null;
PhotoByte=ms.ToArray();
pictureBox1.Image.Save(ms, ImageFormat.Jpeg);
PhotoByte =ms.ToArray();
Str = "insert into Experimmm Values('" + PhotoByte + "','" + textBox1.Text + "')";
Conn.Open();
cmd.Connection = Conn;
cmd.CommandText = Str;
cmd.ExecuteNonQuery();
Conn.Close();

Which is going well. I can see binary data in ma database table like <Binary Data> My code for retrieving data is:

Str ="select * from Experimmm where id = '" +textBox2.Text + "'";
Conn.Open();
cmd.Connection = Conn;
cmd.CommandText = Str;
dr = cmd.ExecuteReader();
if (dr.Read())
{ label1.Text = dr.GetValue(1).ToString();
byte[] PhotoByte = (byte[])dr.GetValue(0);
MemoryStream mem = new MemoryStream(PhotoByte, 0, PhotoByte.Length);
//but an error takes place on next line "Parameter is not valid."             
pictureBox2.Image = Image.FromStream(mem);
} Conn.Close();

I'm using visual studio 10 , C# ,sql server 2005

Answer 1

You have several issues with your code. I'll address it line-by-line:

MemoryStream ms =new MemoryStream();
byte[] PhotoByte=null;
PhotoByte=ms.ToArray();
pictureBox1.Image.Save(ms, ImageFormat.Jpeg);
PhotoByte =ms.ToArray();

While it's not a problem, you have needless assignments here. The code above could be more clearly written this way:

MemoryStream ms =new MemoryStream();
pictureBox1.Image.Save(ms, ImageFormat.Jpeg);
byte[] PhotoByte =ms.ToArray();

Next, the following code is not using parameters. Always, always, ALWAYS parameterize your SQL queries instead of dynamically building the SQL. No, seriously, always. Yes, even then. (Also, what is the Str variable? Some sort of reused instance variable? Don't do that.)

Str = "insert into Experimmm Values('" + PhotoByte + "','" + textBox1.Text + "')";
Conn.Open();
cmd.Connection = Conn;
cmd.CommandText = Str;
cmd.ExecuteNonQuery();
Conn.Close();

Instead, it should be this:

Conn.Open();
using(SqlCommand cmd = connection.CreateCommand())
{
    cmd.CommandText = "insert into Experimmm (column list) values(@data, @name)";

    cmd.Parameters.Add("@data", SqlDbType.VarBinary).Value = PhotoByte;
    cmd.Parameters.Add("@name", SqlDbType.VarChar, yourlength).Value = textBox1.Text;

    cmd.ExecuteNonQuery();
}
Conn.Close();

Next, we'll move onto your retrieval. Again with the Str variable, don't do this sort of thing. Also, you need to parameterize this query as well.

byte[] data;
string name;

Conn.Open();
using(SqlCommand cmd = Conn.CreateCommand())
{    
    cmd.CommandText = "select column_list from Experimmm where id = @id";

    cmd.Parameters.Add("@id", SqlDbType.VarChar, field_length).Value = textBox2.Text;

    using(SqlDataReader dr = cmd.ExecuteReader())
    {
        if (dr.Read())
        {
            data = (byte[])dr.GetValue(0); 
            name = (string)dr.GetValue(1);
        }
    }
}
Conn.Close();

label1.Text = name;
pictureBox2.Image = Image.FromStream(new MemoryStream(data));

Answer 2

It is possible of course to store your images in db. However it is not recommended. It is better to store them in a file system.

Your code is a bit messy. The following is a better example.

MemoryStream ms =new MemoryStream(); 
byte[] PhotoByte=null; 
pictureBox1.Image.Save(ms, ImageFormat.Jpeg); 
PhotoByte =ms.ToArray(); 
// I'm not sure whether you are able to create an sql by simply concating the string
Str = "insert into Experimmm Values('@PhotoBytes','@MyTextValue')"; 
// You have to parametrize your query
cmd.Parameters.AddWithValue("PhotoBytes", PhotoByte);
// This also helps you to avoid syntactical corruption in case of ' and sql injection
cmd.Parameters.AddWithValue("MyTextValue", textBox1.Text );
Conn.Open(); 
cmd.Connection = Conn; 
cmd.CommandText = Str; 
cmd.ExecuteNonQuery(); 
Conn.Close(); 

When you retrieve you could use a binary writer in some handler

namespace TestNS
{
   public class MyHttpHandler : IHttpHandler
   {
      // Override the ProcessRequest method.
      public void ProcessRequest(HttpContext context)
      {
         // Your preparations, i.e. querystring or something
         var conn = new SqlConnection("Your connectionstring");
         var command = new SqlCommand("Your sql for retrieval of the bytes", conn);
         conn.Open();
         var data = (Byte[])command.ExecuteScalar();
         conn.Close();
         context.Response.BinaryWrite(data);      }

      public Boolean IsReusable
      {
         get { return false; }
      }
   }
}

Answer 3

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\KUTTY\Real_Project\Royalty_Pro\DbRoyalty_Access\Buzz_Loyalty.mdb");
DataSet ds = new DataSet();
con.Open();
OleDbCommand cmd = new OleDbCommand("select pic from test_table where id_image='123'",con);
OleDbDataAdapter da=new OleDbDataAdapter(cmd);
da.Fill(ds,"test_table");
//con.Close();

//ds = new DataSet();
//da.Fill(ds, "test_table");
FileStream FS1 = new FileStream("image.jpg", FileMode.Create);
if (ds.Tables["test_table"].Rows.Count > 0)
{
    byte[] blob = (byte[])ds.Tables["test_table"].Rows[0]["pic"];
    FS1.Write(blob, 0, blob.Length);
    FS1.Close();
    FS1 = null;

    byte[] imageData = (byte[])cmd.ExecuteScalar();
    MemoryStream ms = new MemoryStream(imageData, 0, imageData.Length);
    pictureBox2.Image = Image.FromStream(ms);



    pictureBox2.Image = Image.FromFile("image.jpg");
    pictureBox2.SizeMode = PictureBoxSizeMode.StretchImage;
    pictureBox2.Refresh();

    pictureBox2.Image = Image.FromStream(new MemoryStream(blob));  
    pictureBox2.Image = Image.FromFile("image.jpg");
    pictureBox2.SizeMode = PictureBoxSizeMode.StretchImage;
    pictureBox2.Refresh();
}

Answer 4

You should be using a parameterized query instead of concatenating a SQL string.

Apart from fixing the obvious SQL Injection vulnerability, this will enable you to properly insert an image into the database.

There are many questions and answers on how to insert into a SQL Server image/binary field - you should look at them.