Problem with SQLServerCE

Hi.

I'm facing a problem and I ask if anybody can help me solving it.
I developed an application in C# (compact framework 2), running on a
Windows CE 5 industrial pc, that uses SqlServer CE to connect to a
local DBF file. The Database is acessed from several processes and
each process uses a different connection. The method Open() is called
everytime needed, and after use the Close() method is called. These
connections objects are maintained in a Static Class.
I have the application installed in several PCs and in some of them,
after a long time, although I get no error or exception, the dabase
"freezes", that is, the inserts/updates/deletes don't change the DBF
file. The strange thing is that, even after a reboot to the PC, it
keeps like that, the commands are executed correctely (the number of
rows affected returned is correct, etc) but nothing changes on the
Database. The only solution that I could find, that worked, was to
copy the application folder to a new folder on the same location in
the disk, changed that new folder name to match the old one and, after
restart, the Database file starts to get updated again.

I guess it could be related to some Operating system problem? Any
Ideas?

Thanks in advance,

 

How do you know the data in the database didn't change? In other words,
you've told us a bit about how you are communicating with the database when
everything works, but little about the actual failure mode. What code is it
that's making a change that you say isn't getting to the database?

Also, we'll want to know what version of SQL Server Compact is on the
devices.

Paul T.

 

Hi Paul.
Thanks for your reply.
I had put this matter a little apart, as it happens rarely, and only getting
back to it now.

I'm using SQL Mobile 3 on the devices.
Whe in failure mode, the inserts and updates don't work but no error is
given. For example, the following code executes completely, the transaction
commits, the "id" value I get is the value on the DB, no exception is thrown,
but the only record in table "TransactionID" (has only 1 record) remains
unchanged (the column "id" isn't incremented as it is normally).

SqlCeConnection connection = null;
SqlCeTransaction transaction = null;

try
{
connection = GetConnection(process);
connection.Open();
transaction = connection.BeginTransaction();
SqlCeCommand command = new SqlCeCommand("SELECT id from
TransactionID;", connection);
command.CommandType = System.Data.CommandType.Text;
command.Transaction = transaction;
SqlCeDataReader dataReader =
command.ExecuteReader(System.Data.CommandBehavior.Default);
dataReader.Read();
long id = dataReader.GetInt64(0);
dataReader.Close();
id++;
SqlCeCommand command2 = new
SqlCeCommand(String.Format("Update TransactionID SET id = {0};", id),
connection);
command2.CommandType = System.Data.CommandType.Text;
command2.Transaction = transaction;
int rowAffected = command2.ExecuteNonQuery();
if (rowAffected <= 0)
throw new ApplicationException("Erro ao actualizar ID da
transacção na BD!");

transaction.Commit();
transaction.Dispose();
transaction = null;

command.Dispose();
command = null;
command2.Dispose();
command2 = null;
dataReader.Dispose();
dataReader = null;
return id;
}
catch (Exception ex)
{
transaction.Rollback();
}
finally
{
if (connection != null)
connection.Close();
}

Even if it isn't within a transaction, the result is the same, for instance
the following code executes with no problem but, when in failure mode, the
record with id=0 remains unchanged:

SqlCeConnection conn = GetConnection(process);
try
{
conn.Open();
//Prepare command
SqlCeCommand command = new
SqlCeCommand(String.Format("Update ActivityRecords set lastCommunication =
'{0}' where ID = 0 ",

lastCommunication.ToString("yyyy-MM-dd HH:mm"), conn);
command.CommandType = System.Data.CommandType.Text;

res = command.ExecuteNonQuery();

command.Dispose();
command = null;
}
catch (Exception ex)
{

throw;
}
finally
{
conn.Close();
}

 

If you are using VS for development, and there is a automatic deployment
happening; then the database attached to the project might be getting
deployed and overwriting the new and updated database file on the device.
So, you might perceive that as the changes are getting lost. You might want
to turn off 'Copy' of database file.

Thanks,
Laxmi

 

Thank you Laxmi, for you attention.
That isn't the case.
No automatic deployment is happening, for the application is already
installed and running in production.

But thanks anyway.

Best regards,

 

Hey Zowie,

There are thousands of applications running on SQL CE, and I believe we
are missing something here than a bug. Can you give us more details here to
get to the problem?

Thanks,
Laxmi

 

Hy Laxmi.

Unfortunately i can't provide more information other than that in the
previous messages...
It's a strange problem, indeed, and i believe it has to do (somehow...) with
the filesystem, but it happened only a few times and I can't reproduce it.

The fact is, all the operations done against the database work fine for some
time and, for no apparent reason, in some cases it stops working. For
instance: the "Update TransactionID SET id = 12345" command returns 1 row
affected but the value in the DB keeps as it was before... Every single
update/insert/delete commands are executed (no errors or exceptions raised)
but the changes aren't reflected in the Database -- It's like the file was
"frozen".

Thanks,

Zowie