System.Data.SQLite

I need to delete all table data, and reset the Primary Key Id, but I found there is no any api for reset Primary key. So only way is use ExecuteSqlCommand .

So I write this code:
Example1:
using (songsEntities ctx = new songsEntities())
                    {
                        using (TransactionScope scope = new TransactionScope())
                        {

//clear and reset all data
ctx.Database.ExecuteSqlCommand("delete from tb_ssr;delete from tb_song;delete from tb_singer;delete from tb_songtype;delete from tb_singertype;delete from tb_country;delete from sqlite_sequence where name='tb_song' or name='tb_singer' or name='tb_ssr'");


//add data

foreach (var item in Ssrs)
{
 ctx.Ssrs.Add(item);
 ctx.Entry(item).State = EntityState.Added;
}
ctx.SaveChanges();
scope.Complete();

}
}

but It will throw exception System.Data.SQLite.SQLiteException: database is locked.


I change My code, use this way:
Example2:
using (songsEntities ctx = new songsEntities())
                    {
                        using (TransactionScope scope = new TransactionScope())
                        {
//delete all
foreach (var item in Utilitys.Ssrs)
                            {
                                ctx.Entry(item).State = EntityState.Deleted;
                            }

                            foreach (var item in Utilitys.SONGS)
                            {
                                ctx.Entry(item).State = EntityState.Deleted;
                            }

                            foreach (var item in Utilitys.SINGERS)
                            {
                                ctx.Entry(item).State = EntityState.Deleted;
                            }

                            foreach (var item in Utilitys.COUNTRYS)
                            {
                                ctx.Entry(item).State = EntityState.Deleted;
                            }

                            foreach (var item in Utilitys.SINGER_TYPES)
                            {
                                ctx.Entry(item).State = EntityState.Deleted;
                            }

                            foreach (var item in Utilitys.SONG_TYPES)
                            {
                                ctx.Entry(item).State = EntityState.Deleted;
                            }


                            ctx.Database.ExecuteSqlCommand("delete from sqlite_sequence where name='tb_song' or name='tb_singer' or name='tb_ssr'");


//add data

foreach (var item in Ssrs)
{
 ctx.Ssrs.Add(item);
 ctx.Entry(item).State = EntityState.Added;
}
ctx.SaveChanges();
scope.Complete();

}
}

but It still throw exception System.Data.SQLite.SQLiteException: database is locked.



if I'm not use ctx.Database.ExecuteSqlCommand("delete from sqlite_sequence where name='tb_song' or name='tb_singer' or name='tb_ssr'");  
it will success.

So My question is:
1.Why I can't use ExecuteSqlCommand? (if use database always will locked).
2.Is there any way can reset sqlite_sequence(when finish delete data, and not locked database when use TransactionScope)
3.which way can delete table more fast? I wan't to use ExecuteSqlCommand, but always locked.  I can foreach all data and delete like example 2, but very slow....

This issue may be related to ticket [56b42d99c1].  Can you please try again with
System.Data.SQLite version 1.0.90.0?

I cannot investigate further without additional information.  Closing.

Hi, Sorry for long time not reply to you.

I'm download 1.0.90.0 version, and try to use this code, but still display error:  ---> System.Data.SQLite.SQLiteException: database is locked



code:
using (songEntities ctx = new songEntities())
{
using (TransactionScope scope = new TransactionScope())
{

       ctx.Configuration.AutoDetectChangesEnabled = false;
       ctx.Configuration.ValidateOnSaveEnabled = false;

//Clear all data and sqlite_sequence 
ctx.Database.ExecuteSqlCommand("delete from tb_ssr;delete from tb_singer;delete from tb_song;delete from tb_songtype;delete from tb_singertype;delete from tb_country;delete from sqlite_sequence where name='tb_song' or name='tb_singer' or name='tb_ssr'");


//Add new Data

 foreach (var item in Ssrs)
{

    ctx.Ssrs.Add(item);
    ctx.Entry(item).State = EntityState.Added;
    ctx.SaveChanges();
 }

ctx.SaveChanges();
scope.Complete();
}

}

Can you try modifying the connection string to include the "Enlist=True;" 
property value?  This should allow the ExecuteSqlCommand method to make
use of the correct transaction context.

I try it , not working. Visual Studio still display:

database is locked
database is locked
System.Data.EntityException: 基础提供程序在 Open 上失败。 ---> System.Data.SQLite.SQLiteException: database is locked
database is locked
   在 System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
   在 System.Data.SQLite.SQLiteDataReader.NextResult()
   在 System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   在 System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   在 System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
   在 System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
   在 System.Data.SQLite.SQLiteTransaction..ctor(SQLiteConnection connection, Boolean deferredLock)
   在 System.Data.SQLite.SQLiteConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   在 System.Data.SQLite.SQLiteConnection.BeginTransaction(IsolationLevel isolationLevel)
   在 System.Data.SQLite.SQLiteEnlistment..ctor(SQLiteConnection cnn, Transaction scope)
   在 System.Data.SQLite.SQLiteConnection.EnlistTransaction(Transaction transaction)
   在 System.Data.SQLite.SQLiteConnection.Open()
   在 System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
   --- 内部异常堆栈跟踪的结尾 ---
   在 System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
   在 System.Data.EntityClient.EntityConnection.Open()
   在 System.Data.Objects.ObjectContext.EnsureConnection()
   在 System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
   在 System.Data.Entity.Internal.InternalContext.SaveChanges()
   在 System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
   在 System.Data.Entity.DbContext.SaveChanges()

My version is X86 .NET.4.0 I'm woring on Visual studio 2012(All feature fine without this problem)


I also disable lazy load.

and 

ctx.Configuration.AutoDetectChangesEnabled = false;
ctx.Configuration.ValidateOnSaveEnabled = false;

all for best speed. Is this a problem source? Or because any other problem?

It would seem that there is another connection to the database causing the 
locking issue.  Do you know what that other connection might be?

I think there is only 1 connction.

because it's a batch process. So I open transaction

using (songEntities ctx = new songEntities())
{
using (TransactionScope scope = new TransactionScope())
{
//ExecuteSqlCommand and Add code

ctx.SaveChanges();
scope.Complete();
}
}

Have you tried creating the TransactionScope before the database connection is
opened (i.e. before creating songEntities)?

not wroking. still locked.

I create the database use Navicat Premium. and this is my connnection str:
datasource=\\\\192.168.1.5\data.db;password=111111;baseschemaname=main;Enlist=True;Version=3

I'm use Entity framework5.0.0(net4.0)

So where is the problem?

Could you try exactly the following connection string:

     "Data Source=\\\\192.168.1.5\data.db;"

Hi, the connection string is :data source=
last time I'm not write correctly.
and I think it's not about the connection string. It's a Bug of ExecuteCommand and manually add operate together.

I think the table locked, because the two different operate way not on the one transaction.  So maybe you need to check it .

Or maybe the ExecuteCommand newer use the transaction. I don't know....

When creating the TransactionScope instance, you'll need to use the two parameter
method overload that allows specifying a TransactionOptions.  Also, when creating
the TransactionOptions to use, you'll need to make sure the IsolationLevel is
ReadCommitted.  By default, the isolation level is Serializable, which will not
work.

try it. but this time I'm wating it about 3-5minute. and it finally throw exception:

onstraint failed
UNIQUE constraint failed: tb_country.CountryId
System.Data.Entity.Infrastructure.DbUpdateException: Update Error please look inner exception。 ---> System.Data.UpdateException:  ---> System.Data.SQLite.SQLiteException: constraint failed
UNIQUE constraint failed: tb_country.CountryId
   在 System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
   在 System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
   在 System.Data.SQLite.SQLiteDataReader.NextResult()
   在 System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   在 System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   在 System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
   在 System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
   在 System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues)
   在 System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
   --- 内部异常堆栈跟踪的结尾 ---
   在 System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
   在 System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)
   在 System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
   在 System.Data.Entity.Internal.InternalContext.SaveChanges()
   --- 内部异常堆栈跟踪的结尾 ---
   在 System.Data.Entity.Internal.InternalContext.SaveChanges()
   在 System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
   在 System.Data.Entity.DbContext.SaveChanges()







------------------------Next----------------------------------






this is My code:

using (songEntities ctx = new songEntities(CONS, false))
{
  ((IObjectContextAdapter)ctx).ObjectContext.CommandTimeout = 300;

    TransactionOptions option = new TransactionOptions();
    option.Timeout = TimeSpan.FromSeconds(300);
    option.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, option, EnterpriseServicesInteropOption.Automatic))
      {

        ctx.Configuration.AutoDetectChangesEnabled = false;
        ctx.Configuration.ValidateOnSaveEnabled = false;



        //Clear original data
        ctx.Database.ExecuteSqlCommand("delete from tb_ssr;delete from tb_singer;delete from tb_song;delete from tb_songtype;delete from tb_singertype;delete from tb_country;delete from sqlite_sequence where name='tb_song' or name='tb_singer' or name='tb_ssr'");


       //Import data
        foreach (var item in Ssrs)
                            {

                                item.tb_song.SelectCount = Ranking.GetValueOrDefault(item.tb_song.FolderName + item.tb_song.FileName, item.tb_song.SelectCount);

                                ctx.Ssrs.Add(item);
                                ctx.Entry(item).State = EntityState.Added;
                            }

                            ctx.SaveChanges();
                            scope.Complete();
               }
 }












------------------------Next----------------------------------


And if I'm remove Enlist=True from connection string. databases still will locked.



------------------------Next----------------------------------

And if I'm remove Enlist=True, also remove ExecuteSqlCommand(" delete all"), use manually delete to replace . like this:

//remove execute command.
//ExecuteSqlCommand(" delete all ");

//delete before manually

foreach (var item in Utilitys.Ssrs)
{
    ctx.Entry(item).State = EntityState.Deleted;
}

foreach (var item in Utilitys.SONGS)
{
       ctx.Entry(item).State = EntityState.Deleted;
}

foreach (var item in Utilitys.SINGERS)
{
       ctx.Entry(item).State = EntityState.Deleted;
}

foreach (var item in Utilitys.COUNTRYS)
{
      ctx.Entry(item).State = EntityState.Deleted;
}

foreach (var item in Utilitys.SINGER_TYPES)
{
        ctx.Entry(item).State = EntityState.Deleted;
}

foreach (var item in Utilitys.SONG_TYPES)
{
           ctx.Entry(item).State = EntityState.Deleted;
}


//continue add 

foreach (var item in Ssrs)
{
          ctx.Ssrs.Add(item);
          ctx.Entry(item).State = EntityState.Added;
 }




This time no more error, not any different use IsolationLevel.ReadCommitted or not.






I still think execute command and manually add can't use together....there is some problem.....I still don't know....

The new error message "UNIQUE constraint failed" is a completely different issue.
The original issue was caused by using the wrong isolation level.  The current
behavior is correct and by design.

Are you sure? So why you don't explain to me when manually delete like this not has this problem?

foreach (var item in Utilitys.Ssrs)
{
    ctx.Entry(item).State = EntityState.Deleted;
}

....    Omission other delete .....

you said I have "UNIQUE constraint failed"  issue.  So why that's not happening when I'm manually delete and manually add. why when use execute command happening ?

Obviously has "UNIQUE constraint failed"  issue because execute command not delete the all country, so when manually add tb_ssr, it will add the all country again, but because country not delete . so it will happening "UNIQUE constraint failed" issue.  So I think there is still has some BUG.

It could be an issue with the ordering of the executed SQL commands.  The error
message in this case is coming from the SQLite core native library constraint
enforcement logic, which requires the data to be consistent with the schema.