Pull SQL varbinary(max) to Xamarin image

Question

I have a Xamarin application that reads images from a SQL Server database, the column that holds the image data index is 4.

I have created a class that contain a member called image, which is an Image object.

I have a content page that has a listview. Inside this listview I have an image cell. So, what I want is to to read the image data from the SQL Server database, using the reader, convert the column which is currently being read to a byte array, then convert this byte array to memory stream, then fill the image with this memory stream, then the image which is in the listview will be bound to image int he object , but it looks like I am unable to achieve this. Any help?

Here is the code:

Class file:

internal class Attachments
{
    public int serial { get; set; }
    public string attachment_name { get; set; }
    public byte[] image_stream { get; set; }
    public string table_name { get; set; }
    public DateTime create_date { get; set; }
    public string related_serial { get; set; }
    public string created_by { get; set; }
    public Image class_image { get; set; } ? which one to use???
    public StreamImageSource class_source { get; set; }   ? which one to use???
}

Xaml file:

        <ListView x:Name="images_lv" HasUnevenRows="True">
            <ListView.ItemTemplate>
                <DataTemplate>
                    <ViewCell>
                        <StackLayout Orientation="Horizontal">
                            <Image Grid.RowSpan="2"
                   Source="{Binding class_source}"
                   Aspect="AspectFill"
                   HeightRequest="60"
                   WidthRequest="60" />
                        </StackLayout>
                    </ViewCell>
                </DataTemplate>
            </ListView.ItemTemplate>
        </ListView>

Xaml.cs file:

private async void get_image_from_db()
{
    List<Classes.Attachments> attachments = new List<Classes.Attachments>();
        
    string conn = "Server=******; database=******; user id=******; password=*****";
    string query = "select * from Attachments";

    SqlConnection connection = new SqlConnection(conn);

    try
    {
        connection.Open();

        SqlCommand cmd = new SqlCommand(query, connection);
        SqlDataReader reader = cmd.ExecuteReader();

        MemoryStream ms = new MemoryStream();

        while (reader.Read())
        {
            ms.Position = 0;

            int serial = reader.GetInt32(0);
            string attachemnt_name = reader.GetString(1);
            string table_name = reader.GetString(2);
            DateTime createdate = reader.GetDateTime(3);

            byte[] media = reader.GetSqlBytes(4).Value;
            ms.Write(media,0, media.Length);

            string related_serial = reader.GetString(5);
            string created_by = reader.GetInt32(6).ToString() ;

            attachments.Add(new Classes.Attachments
                    {
                        serial = serial,
                        attachment_name = attachemnt_name,
                        image_stream = media,
                        related_serial = related_serial,
                        created_by = created_by,
                        create_date = createdate,
                        class_source = (StreamImageSource)ImageSource.FromStream(() => ms),
                        class_image = new Image().Source()

                        //class_image.Source = ImageSource.FromStream(() => ms)
                    }) ;
        }

        images_lv.ItemsSource = attachments;
    }
    catch (Exception ex)
    {
        await DisplayAlert("", ex.ToString(), "");
    }
}

Thanks for your help

Answer 1

There's no reason to use an intermediate byte[], and after you write to the MemoryStream you have to rewind the stream again.

  byte[] media = reader.GetSqlBytes(4).Value;
  ms.Write(media,0, media.Length);

to

  reader.GetStream(4).CopyTo(ms);
  ms.Position=0;