selecting all photographs from sql database

Question

I want to export all photographs from our database into a data table. I will then loop through the table and save each image to disk. There are approx 7000 photos.

When I start the process I can retrieve around 4000 photographs before I start to get error messages like, Exception of type 'System.OutOfMemoryException' was thrown.

If I change the SQL query to retrieve half as many photographs, say 3500 then the process completes successfully. While I have now achieved what I wanted by modifying the SQL each time I run the code, I would like to improve my code so that all 7000 photographs are returned. Could somebody please advise on a better process.

Here is my method

 public static DataTable GetAllPhotos()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("personId", typeof(string));
        dt.Columns.Add("Photo", typeof(Bitmap));

        string SQL = "";
        byte[] getImg = new byte[0];
        byte[] BitmapImg = new byte[0];
        string personId = "";
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = _connString;

        SQL = @"select per.person_id,pho.photo
                from person as per
                left join photo as pho on per.photo_id = pho.photo_id
                where photo is not null";


        conn.Open();
        SqlDataReader dr = null;
        SqlCommand cmd = new SqlCommand(SQL, conn);
        dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            try
            {
                getImg = (byte[])dr["Photo"];
                personId = Convert.ToString(dr["person_id"]);
                MemoryStream str = new MemoryStream(getImg);
                Bitmap bitmap = new Bitmap(Image.FromStream(str));
                BitmapImg = ImageToByte(bitmap);

                dt.Rows.Add(personId, bitmap);
            }
            catch (Exception ex)
            {

                LogWriter.WriteLine(personId + ex.Message.ToString());
            }


        }

        conn.Close();
        return dt;
    }

Answer 1

If your intent is saving images to disk, then why would you get them into an intermediate datatable? Wouldn't it be better if you directly write out to disk as you read them? If so, assuming those are .bmp files:

public static void DumpAllPhotos()
{
    string sql = @"select per.person_id,pho.photo
                from person as per
                inner join photo as pho on per.photo_id = pho.photo_id";
    string folder = @"c:\MyFolder"; // output folder
    using (SqlConnection con = new SqlConnection(_connString))
    using (SqlCommand cmd = new SqlCommand(sql,con))
    {
        con.Open();
        var rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            var bytes = (byte[])rdr["photo"];
            var path = Path.Combine(folder, $"{rdr["person_id"].ToString()}.bmp");
            File.WriteAllBytes(path, bytes);
        }
        con.Close();
    }
}

Answer 2

Bitmap has to be disposed, you're using too many handles. So your while loop should be something like this:

 while (dr.Read())
    {
        try
        {
            getImg = (byte[])dr["Photograph"];
            personId = Convert.ToString(dr["person_id"]);
            MemoryStream str = new MemoryStream(getImg);
            Bitmap bitmap = new Bitmap(Image.FromStream(str));
            BitmapImg = ImageToByte(bitmap);

            dt.Rows.Add(personId, bitmap);
            bitmap.Dipose(); // <--- DISPOSE!!
        }
        catch (Exception ex)
        {

            LogWriter.WriteLine(personId + ex.Message.ToString());
        }
    }

or maybe even better:

 while (dr.Read())
    {
        try
        {
            getImg = (byte[])dr["Photograph"];
            personId = Convert.ToString(dr["person_id"]);
            MemoryStream str = new MemoryStream(getImg);
            using (Bitmap bitmap = new Bitmap(Image.FromStream(str))) {
                BitmapImg = ImageToByte(bitmap);
                dt.Rows.Add(personId, bitmap);
           }
        }
        catch (Exception ex)
        {

            LogWriter.WriteLine(personId + ex.Message.ToString());
        }
    }