Get varbinary image into C# DataSet with SqlDataReader

Question

SqlDataAdapter has the .Fill(dataset) function, but SqlDataReader doesn't.

This should extract the image and place it inside my byte[]

while (reader.Read())
        {
           listOfProfiles.Add(new Profile
             {
               ProfileImage = (byte[])profileReader["imageFile"]
              });

I've tried to create a DataSet and create a row where the image should be

DataSet ds = new DataSet();
byte[] MyData = new byte[0];
DataTable table0 = new DataTable("table0", "table0");         
table0.Columns.Add("imageFile"); // DB column name
table0.Rows.Add(listOfProfiles[0].ProfileImage);
ds.Tables.Add(table0);         
DataRow myRow;

After the image is in my DataSet, I tried converting it to a BitmapImage, but got the following error:

Unable to cast object of type 'System.String' to type 'System.Byte[]'.'

This is the code that should convert the varbinary image from the database to a BitmapImage:

if (ds.Tables[0].Rows.Count == 1)
        {
            myRow = ds.Tables[0].Rows[0];

            MyData = (byte[])myRow["imageFile"];

            MemoryStream stream = new MemoryStream(MyData);
            stream.Write(MyData, 0, MyData.Length);
            stream.Position = 0;
            System.Drawing.Image img = 
            System.Drawing.Image.FromStream(stream);
            BitmapImage bi = new BitmapImage();
            bi.BeginInit();
            MemoryStream ms = new MemoryStream();
            img.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp);
            ms.Seek(0, SeekOrigin.Begin);
            bi.StreamSource = ms;
            bi.EndInit();

            hpProfileImage.Source = bi;
        }

Any help is very appreciated

The error says it all..You are trying to use/convert a string as/to a Bitmapimage — Aousaf Rashid
– Aousaf Rashid, Commented Mar 13, 2018 at 15:22
what is it that you are actually looking for ? How to load image from db or how to convert ? — Aousaf Rashid
– Aousaf Rashid, Commented Mar 13, 2018 at 15:23
Images are stored as varbinary in my database, I'm trying to load it into a WPF Imagebox — VirtusO
– VirtusO, Commented Mar 13, 2018 at 15:24

Aousaf Rashid · Accepted Answer · 2018-03-13 15:34:22Z

1

A working example of loading image from database in WPF

 private void LoadLoggedInUsersDetails()
{
con.Open();
SqlCommand cmd2 = new SqlCommand("Select * from userinfo where ID='" + idStringHere + "'", con);
byte[] photo;
SqlDataReader dr2 = cmd2.ExecuteReader;
while (dr2.Read)
{
    photo = (byte[])dr2(11);
    MemoryStream strm = new MemoryStream(photo);
    BitmapImage bi = new BitmapImage;
    bi.BeginInit();
    strm.Seek(0, SeekOrigin.Begin);
    bi.StreamSource = strm;
    bi.EndInit();
    imageControl.ImageSource = bi;
}

con.Close();
}

A quick explanation : First i am declaring a byte[] variable.Then,as the SqlDatareader starts to read data from the database,the byte[] variable gets it's value from the datareader's specific column/cell.Then i declare a MemoryStream which reads the byte[].Now we declare a BitmapImage variable.BeginInIt and EndInIt are essential in this case.To make sure the Memorystream's position is at the beginning,we use strm.Seek(0, SeekOrigin.Begin).The bitmapImage uses that MemoryStream as a StreamSource.Finally we call EndInIt and do whatever we want to do with the bitmap , in this case,use it as a ImageBrush's ImageSource

edited Mar 13, 2018 at 15:34

answered Mar 13, 2018 at 15:28

Aousaf Rashid

5,7986 gold badges30 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

VirtusO Over a year ago

Thank you so much! I think this is exactly what I'm looking for :)

Aousaf Rashid Over a year ago

Please mark it as an answer and upvote it if it helped

Aousaf Rashid Over a year ago

Always happy to help. :)

VirtusO Over a year ago

I upvoted, but since my rep is below 15 it ins't publicly shown

Aousaf Rashid Over a year ago

It's okey,i'm happy to help

Collectives™ on Stack Overflow

Get varbinary image into C# DataSet with SqlDataReader

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related