title: OutOfMemoryException when Inserting larger images in SQL Server CE
body: <p>Inserting smaller images work fine. However, when the image file gets bigger than about 2MB, I get an <code>OutOfMemoryException</code>. </p>
  
  <p>I tried with <code>SqlCeUpdatableRecord</code> and a parameterized <code>SqlCeCommand</code>. Using the <code>SqlCeCommand</code>, the exception is raised at <code>ExecuteNonQuery()</code>. </p>
  
  <p>With <code>SqlCeUpdatableRecord</code>, the exception is raised at <code>SqlCeUpdatableRecord.SetBytes()</code>. </p>
  
  <p>I’ve tried increasing buffer size and temp file size without it seeming to have an effect. I've debugged with <code>GetTotalMemory</code> and there seems to be plenty of resources available.</p>
  
  <p>Any tips would be highly appreciated. </p>
  
  <p>The SQL Server CE database is synchronized with a main SQL Server database and handling images as separate files would introduce a truckload of other issues. The solution has worked fine for years as most WM handhelds only capture images at 5MP or less. However, newer models with support for 8MP images is causing issues. </p>
  
  <p>Here is a simplified version of the <code>SqlCeUpdateblRecord</code> implementation:</p>
  
  <pre><code>System.Data.SqlServerCe.SqlCeUpdatableRecord newRecord = base.CreateRecord();
  newRecord["ImageId"] = ImageId;
  newRecord["ImageType"] = ImageType;
  //64kb buffer (Have tested with different buffer sizes (up to 8MB)
  newRecord.SetBytesFromFile("ImageFull", ImageFullFileName, 65536);
  newRecord["ImageThumb"] = ImageThumb;
  newRecord["ImageDate"] = ImageDate;
  base.Insert(newRecord); 
  
  .....
  public static void SetBytesFromFile(this SqlCeUpdatableRecord obj, string name, string filename, int buffersize)
  {
      int _column = obj.GetOrdinal(name);
      byte[] buffer = new byte[buffersize];            
      int bytesread;
      long offset = 0;
  
      using (FileStream fs = new FileStream(filename,FileMode.Open))
      {
          bytesread = fs.Read(buffer, 0, buffersize);
  
          while (bytesread &gt; 0)
          {
              //This will raise OutOfMemoryException for imagefiles larger than appx. 2mb, regardless of buffersize      
              obj.SetBytes(_column, offset, buffer, 0, bytesread);                    
              offset = offset + (long)bytesread;
              bytesread = fs.Read(buffer, 0, buffersize);           
          }
      }
  }
  </code></pre>
  
  <p>Here is a simplified version of the paramterized query:</p>
  
  <pre><code>using (var cmdInsert = new SqlCeCommand("INSERT INTO [Image_CF] ( [ImageId], [ImageType], [ImageFull], [ImageThumb], [ImageDate]) VALUES " +
                                                     " ( @ImageId, @ImageType, @ImageFull, @ImageThumb, @ImageDate)"))
  {
      cmdInsert.Connection = Database.IrisPdaConnection;
  
      cmdInsert.Parameters.Add("@ImageId", System.Data.SqlDbType.Int).Value = ImageId;
      cmdInsert.Parameters.Add("@ImageType", System.Data.SqlDbType.NVarChar).Value = ImageType;
      cmdInsert.Parameters.Add("@ImageFull", System.Data.SqlDbType.Image).Value = GetFileAsBytes(ImageFullFileName);
      cmdInsert.Parameters.Add("@ImageThumb", System.Data.SqlDbType.Image).Value = ImageThumb;
      cmdInsert.Parameters.Add("@ImageDate", System.Data.SqlDbType.NVarChar).Value = ImageDate;
  
      // OutOfMemoryException for images larger than appx. 2MB
      cmdInsert.ExecuteNonQuery();
  }
  
  public byte[] GetFileAsBytes(string filename)
  {
     FileStream fs;
     byte[] result;
  
     using (fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
     {
          // a byte array to read the image
          result = new byte[fs.Length];     
          fs.Read(result, 0, System.Convert.ToInt32(fs.Length));  
     }
  
     return result; 
  }
  </code></pre>