sqlce memory issue

Miroslav_shrek

Hi all, I am querying an SqlCe Database on my PDA and noticed that the device is slowly running out of memory every time I execute a query. I am using the following code:

public  SqlCeDataReader ExecuteReader(string command, params SqlCeParameter[] commandParameters)
        {
            SqlCeDataReader reader = null;
            SqlCeCommand c = new SqlCeCommand(command, cn);
            try
            {
                for (int i = 0; i < commandParameters.Length; i++)
                    c.Parameters.Add(commandParameters);
                reader = c.ExecuteReader();
            }
            catch
            {
                // Release resources
                c.Parameters.Clear();
                reader.Close();
                throw;
            }
            c.Parameters.Clear();
            return reader;
        }

This method is a part of my 'SqlCeHelper' class that I have written.

I noticed that when I reach the line reader = c.ExecuteReader() , the memory of my device decreases by about 1%. The reader is returned to a class which makes use of it and then disposes of it by calling reader.Close(); The connection to the database is also closed properly at some point, but memory never gets released. Memory gets only released when the application terminates.

I am wondering if I am doing something wrong by not disposing of some resources. Please help!

(Also tried GC.Collect() , didn't help!)

Cheers.

Joao Paulo Figueira


Remember to also call Dispose on the SqlCeDataReader. This will effectively dispose all the managed and unmanaged resources and you should do this at the earliest.

Miroslav_shrek

Hi,

first of all - thanks for your reply!

I have called reader.Dispose() and nothing really improved. In the class that uses the reader returned by the above method, the code is as follows:

IDataReader reader = null;

try

{

reader = _dbHelper.ExecuteReader("SELECT * FROM MeasurementInstance");

while (reader.Read())

{

if (reader != null && !reader.IsClosed)

{

...get relevant data from the reader and store it...

}

}

reader.Close();

reader.Dispose();

Nothing seems to improve. Just as a side question - when I create parameters for the SqlCeCommand, for example:

SqlCeParameter taskParam = new SqlCeParameter("@TaskId", SqlDbType.BigInt);

taskParam.Value = taskID;

Is there a need to call dispose on the parameters at some point Any other ideas why my memory might be getting wasted

Cheers,

robertkjr3d

Are you actually running out of memory... getting a memory error

Or are you just worrying about a memory leak

I've noticed that with the PDA that memory is claimed and is not returned so quickly. However when it needs it the memory is returned. I have found no command such as Dispose... and dumping the garbage collection seems to not do much, if anything at all. However as stated I'd still use the good programming practice of always dispose objects that offer 'dispose' as an option.

I've got an extensive application running on a PDA out in the field. The nurses often express complaints over slowness. I have them do a soft-reset periodically that seems to clean up things.

I believe that the memory leaks are due to the .NET system, or SQL Ce. I don't think we can do much about them, aside from a Soft-Reset... or a microsoft reboot...LOL

I hope that in the new version of SqlCe that these problems will not persist.

Miroslav_shrek


Hi,

Yeah I was really suspicious of something like this... I have really tried everything, but the only way to realease the resources is to either kill the application or do a soft reset. There are really no particular problems with the memory issues, except that it gets slower and slower. I really hope this will get fixed at some point!

Thanks everyone!

Qiuwei


Did you resolve it yet

What exception did you get

Donsabras


I think the problem have not been solved for any of us.
This is an 8007000E exception: not enough memory for sql operation
We mostly encounter this error when we write large rows

Alain - CFLX

Qiuwei


This problem was very serious to me before and I had to get my user to do a soft reset 2-3 times a day which is not good at all. And quite a lot of my users corrupted their database when they reboot the device when application was writing data to the database

After I made some changes, now it works much better. Some users only need to reset device alternative days.

What I did is:
1. Cach those forms which are always needed. I got a very big improvement by doing this.

2. Have a "static" connection which is always "live", only close the connection after you use it. DONT dispose it!!! If you alway close sqlce connection and dispose it and then create a new connection when you need it,it will cause very very serious memory problem like that. It was a nightmare for me before.

3. Because only SqlCe only allows one connection at a time, you can split your SqlCe database into 2 or 3 or more as required.

4. Avoid using it if possible! I avoid using sqlce database for my my transactional data, a plain text file is enough.

cheer