Retrieving an image from SQL Server

Question

I wrote this code but faced an exception that said: Parameter Not Valid.

    string connstr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\db.mdf;Integrated Security=True;User Instance=True";

    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = connstr;

    conn.Open();

    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;

    cmd.CommandText = "SELECT * FROM tbl";

    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;

    DataTable dt = new DataTable();
    da.Fill(dt);

    byte[] barrImg = (byte[])dt.Rows[1]["image"];

    MemoryStream mstream = new MemoryStream();

    mstream.Write(barrImg, 0, barrImg.Length);
    mstream.Seek(0, SeekOrigin.Begin);

    img.Image = Image.FromStream(mstream);
    mstream.Close();

The exception is:

Parameter is not valid.

in the Image.FromStream line of code.

I checked the value that in datatable was assigned to the image field. It was System.Byte[]. I traced the code. Every thing seems to be correct. But it does not work.

I searched around this problem. Another site preferred to set mstream.Position = 0. But that does not work.

I stored my image by this code.If it maybe that I saved this wrong!

    string connstr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\db.mdf;Integrated Security=True;User Instance=True";

    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = connstr;

    conn.Open();

    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;

    string sql = "INSERT INTO tbl (name, family, image) VALUES ('name', 'family', '{0}')";
    sql = string.Format(sql, Image.FromFile("test.jpg"));

    cmd.CommandText = sql;
    cmd.ExecuteNonQuery();
    conn.Close();

new code to save the image:

public byte[] ReadFile(string sPath)
{
    byte[] data = null;


    FileInfo fInfo = new FileInfo(sPath);
    long numBytes = fInfo.Length;

    FileStream fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read);

    BinaryReader br = new BinaryReader(fStream);

    data = br.ReadBytes((int)numBytes);

    return data;
}

and :

private void cmdSave_Click(object sender, EventArgs e)
{
    string connstr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\db.mdf;Integrated Security=True;User Instance=True";

      byte[] imageData = ReadFile("test.jpg");

      SqlConnection CN = new SqlConnection(connstr);

      string qry = "insert into tbl (name, family, image) VALUES ('name', 'family', '{0}')";
      qry = string.Format(qry, imageData);

      SqlCommand SqlCom = new SqlCommand(qry, CN);

      CN.Open();
      SqlCom.ExecuteNonQuery();
      CN.Close();
}

Answer 1

Um, that's not going to work.

Your code that "stores" the image, well, doesn't really do what you think it does.

It is putting the value "test.jpg" into the image field. That isn't the binary image data; only the filename.

So, when you go to pull it back out the Image.FromStream(mstream) call is going to blow chunks because the value "test.jpg" is not an image.. ergo: the parameter is not valid

Here is an example of what you need to be doing to actually put the image into the database:

http://www.codeproject.com/Articles/21208/Store-or-Save-images-in-SQL-Server

Answer 2

You close the stream then try to pull an image from the stream, hence the error "cannot access a closed stream."

Try swapping the order of your last two lines:

img.Image = Image.FromStream(mstream);
mstream.Close();

Answer 3

using (MemoryStream mstream = new MemoryStream((byte[])dt.Rows[1]["image"]))
{
    img.Image = Image.FromStream(mstream);
}

Would simplify things. I suspect your problem would be you need to call mStream.Flush() after write and before the seek.

To put an image in to a database, one way is.

string connstr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\db.mdf;Integrated Security=True;User Instance=True";

using(SqlConnection conn = new SqlConnection(connstr))
{
  using (SqlCommand comm = new SqlCommand(conn))
  {
    comm.CommandText = "INSERT INTO tbl (name, family, image) VALUES (@name,@family,@Content)";
    comm.Parameters.Add(new SqlParameter("name",DbType.String,"Fred"));
    comm.Parameters.Add(new SqlParameter("family",DbType.String,"Flintstone"));
    using(FileStream fs = new FileStream("FredWilmaBamBamAndDino.jpg",FileMode.Open,FileAccess.Read))
    {
      comm.Parameters.Add(new SqlParameter("content",DbType.Image,fs));
    }
    cmd.ExecuteNonQuery();
  }
}

Notice it puts the content of teh file in the database. Since in the case of a jpg that content is most definitely not a string, use a parameterised query. Which youi should be doing anyway, develop a good habit. You also need to suss out using, your code was leaking resources all over the place.