Commit changes to the database

[mehranrezaei]

I have a simple table like below:

public class SampleRecord {
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
}

I create a new record in this table using the following code:

var record = new SampleRecord() {
    Name = "Test"
};

_repository.Create(record);

Immediately in another request I make sure that record is created using the code below:

var query = _repository.Table.OrderByDescending(x => x.Id).FirstOrDefault(); 

Everything seems fine, but if I terminate the IIS Worker Process through Windows Task Manager immediately (a few seconds) after creating the record, the record that was apparently created in the database no longer exists!
It seems that the changes in the database have not been committed.

Why is the transaction to create a new record in the database still not committed after several seconds?
For any reason, the web server process may be stopped. How to prevent data loss?
I even tried _repository.Flush() or _orchardServices.TransactionManager.RequireNew() but didn't work.

I'm using Orchard 1.10.3 with SQL CE database.

@Piedone
@LombiqTechnologies
@sebastienros

[BenedekFarkas]

Can you please check what happens with the SQL CE DB directly (after commit, after next request that returns the record and after the worker process is shut down)? You can use https://erikej.github.io/SqlCeToolbox/ for that.

[mehranrezaei]

@BenedekFarkas
It seems that in any case if I access the database (even a query on other tables) using SqlCeToolbox or SQL Compact Query Analyzer before the web server process is stopped, the data is there and will remain after the process is closed!