Streaming into SQLCE

Question

Hi,
I need to store large (dozens of megabytes) files into an SqlCe database.
The goal is to do it without first loading the entire file into memory.
The strategy i chose is to use to read a filestream and write into SqlCe in chunks using SqlCeResultSet.SetBytes.
This works except for one problem, it takes up as much memory as the size of the file.
After some investigating i found out that just calling the line:

resultSet.SetBytes(1, position, buffer, 0, amountReadFromFile);

Where "position" is "someSize" takes "someSize" memory immediately.

The question is how to do this without taking up the memory?

Here is a sample that demonstrates the problem, paste into a console application and use perfmon with .NET memory -> bytes in all heaps.
Thanks

using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using System.Linq;
using System.Text;
using System.IO;

namespace SqlCeStreaming
{
    public class SqlCeTester
    {
        private const string StoreFileName = @"c:\temp\MyFile.rar";
        private const string DBFileName = "MyDB.sdf";
        private const string ConnectionString = "DataSource=" + DBFileName;
        private const int ChunkSize = 65536;
        private static byte[] buffer = new byte[ChunkSize];

        public static void Main()
        {
            CreateDB();
            StoreFile();
        }

        private static void CreateDB()
        {
            if (File.Exists(DBFileName))
                File.Delete(DBFileName);
            SqlCeEngine engine = new SqlCeEngine(ConnectionString);
            engine.CreateDatabase();

            SqlCeConnection connection = new SqlCeConnection(ConnectionString);

            SqlCeCommand cmd = new SqlCeCommand("CREATE TABLE Files(ID bigint identity(1,1) primary key, Data image)", connection);
            connection.Open();
            cmd.ExecuteNonQuery();
            connection.Close();
        }

        private static void StoreFile()
        {
            SqlCeConnection connection = new SqlCeConnection(ConnectionString);
            connection.Open();

            //Create a row for the file
            SqlCeCommand insertCommand = new SqlCeCommand("INSERT INTO Files (Data) VALUES(NULL)", connection);
            insertCommand.ExecuteNonQuery();
            connection.Close();

            //create a resultset
            SqlCeCommand resultSetCommand = new SqlCeCommand("SELECT Id, Data FROM Files WHERE ID=1", connection);
            connection.Open();
            SqlCeResultSet resultSet = resultSetCommand.ExecuteResultSet(ResultSetOptions.Updatable);
            resultSet.Read();
            using (Stream fileStream = File.OpenRead(StoreFileName))
            {
                int read;
                int position = 0;
                while ((read = fileStream.Read(buffer, 0, ChunkSize)) > 0)
                {
                    resultSet.SetBytes(1, position, buffer, 0, read);
                    position += read;
                }
                resultSet.Update();
            }

            connection.Close();
        }
    }
}

Answer 1

Maybe calling resultset.update per chunk, rather than reading the whole file before issuing the .Update()

---

http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.