C# SQL to get a picture from the database

Question

I have this code:

private void LoginBTN_Click(object sender, EventArgs e)
{
    var loguser = AutorizeLoginBox.Text;
    var passuser = AutorizePasswordBox.Text;

    SqlDataAdapter adapter = new SqlDataAdapter();
    DataTable table = new DataTable();

    string querystring = $"select ID, USERLOGIN, USERPASSWORD, USERAVATAR from USERTESTDB where USERLOGIN='{loguser}' and USERPASSWORD='{passuser}'";
    SqlCommand command = new SqlCommand(querystring, sqldb.getConnection());

    adapter.SelectCommand = command;
    adapter.Fill(table);

    if (table.Rows.Count == 1)
    {
        byte[] arr;
        Image imgcur;
        ImageConverter converter = new ImageConverter();
        arr = (byte[])converter.ConvertFromString(table.Rows[0].ToString(), );
        MemoryStream ms = new MemoryStream(arr);
        Image i = Image.FromStream(ms);
        Program.programpg.avatarbox.Image = i;
    }
}

How to upload the image assigned to this user in the database after login. //symbolforfixdetails//symbolforfixdetails//symbolforfixdetails//symbolforfixdetails

This is how the database looks like:

Error is on this line:

arr = (byte[])converter.ConvertFromString(table.Rows[0].ToString() );

Error:

System.NotSupportedException: "ImageConverter cannot convert from System.String."

Via another instance of SqlCommand, using the same connection, you can execute an UPDATE or INSERT statement as shown in this post: https://stackoverflow.com/questions/30382870/how-do-you-save-a-byte-array-in-sql. — Rivo R., Jun 10 '22 at 12:21
Where exact is the problem ? Does the memorystream not gets populated ? Or does the Image not gets loaded ? Or something else ? — GuidoG, Jun 10 '22 at 12:32
I don't understand how to pull an image from the database and upload it to picturebox @GuidoG — xXKoksMenXx, Jun 10 '22 at 12:36
You need to post the code that stores the images. Or, specify whether the `USERAVATAR` Column is `VARBINARY(MAX)`, a string storing a base64 encoded array or, e.g., the path/name of a file on disk. — Jimi, Jun 10 '22 at 12:36
Have you debugged the code you have ? Can you tell where it goes wrong ? And how are the images stored in your database ? As a path or as a binary ? — GuidoG, Jun 10 '22 at 12:47
You shouldn't be using `IMAGE`, it should be replaced with `VARBINARY(MAX)`. It's a byte array anyway, so you have `var image = (Image)new ImageConverter().ConvertFrom(table.Rows[0]["USERAVATAR"]);` -- Assuming the Image was stored correctly and it's not DBNull. — Jimi, Jun 10 '22 at 13:05
The `image` datatype is deprecated, you should not use it, use varbinary(max) instead. — GuidoG, Jun 10 '22 at 13:11
The following may be helpful: https://stackoverflow.com/questions/66612039/why-is-my-image-from-database-not-displaying-properly-in-my-picturebox/66616751#66616751 — Tu deschizi eu inchid, Jun 10 '22 at 14:31

score 1 · Answer 1 · answered Jun 10 '22 at 13:05

Please Set your Db Column Data type to Varbinary(Max) which you store image's bytes.

Then read this binary to a MemoryStream() as you did but without convert.

If its a winforms project there should be an overload in PictureBox's DataSource (or whatever its name. if i don't remember wrong its name is Image or BackgroundImage) as it accepts directly byte array.

If Winforms, Either you can pass as byte array or Convert to an Image object and set it to datasource.

But if its a web project, then either you could set it as a base64 data/png string or Image object.

Hope this helps.

score 1 · Accepted Answer · edited Jun 30 '22 at 20:38

Try the following code to set the PictureBox.Image from image stored in SQL Server. The code is tested and works as intended.

Part 1: retrieve image from database and set it to PictureBox.Image:

DataTable table = new DataTable();
byte[] arr=null;

using (SqlConnection cn = new SqlConnection(cs))
{
   cn.Open();
   using (SqlDataAdapter ad = new SqlDataAdapter("select top 1 USERAVATAR from USERTESTDB", cn))  // Replace SQL query with yours
      {
         ad.Fill(table);
      }
 }

 arr = (byte[])table.Rows[0][0];

 // Replace it with [0][3] according to your posted query
 System.Drawing.Bitmap bitmap = null;
 ImageConverter converter = new ImageConverter();
 System.Drawing.Image img = 
 (System.Drawing.Image)converter.ConvertFrom(arr);
 bitmap = (System.Drawing.Bitmap)img;
 pictureBox1.Image = bitmap;

Part 2: store image in database:

openFileDialog1.ShowDialog();
string path = openFileDialog1.FileName;
byte[] img = null;
FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
img = br.ReadBytes((int)fs.Length);

using (SqlConnection cn = new SqlConnection(cs))
{
    cn.Open();

    using (SqlCommand cm = new SqlCommand("insert into USERTESTDB Values (@img)",cn))
        {
            cm.Parameters.Add(new SqlParameter("@img", img));
            cm.ExecuteNonQuery();
         }
  }

You didn't asked about Part2, but I included it to ensure that the image is stored correctly (as binary data) in the database.

You have two problems with your line of code

arr = byte[])converter.ConvertFromString(table.Rows[0].ToString());

You used table.Rows[0] (which returns the entire row) instead of table.Rows[0][x] where x = the index of image column.
You can't convert String to Image in this way, check question 3594239 for more details.

The name "cs" does not exist in the current context. ```using (SqlConnection cn = new SqlConnection(cs))``` — xXKoksMenXx, Jun 12 '22 at 09:07
@xXKoksMenXx, you are using sqldb.getConnection(), so you can replace cs with sqldb.getConnection().ConnectionString — ahmed, Jun 12 '22 at 09:23
I made a separate project where I tested your code, everything worked, thank you very much, it remains only to transfer it to my project — xXKoksMenXx, Jun 12 '22 at 09:34

C# SQL to get a picture from the database

2 Answers2

Related