How to reset the Primary key and delete the SQLite Db created

Question

This code will create the Database in : LocalFolder

string DBPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "customers.sqlite");
using (var db = new SQLite.SQLiteConnection(DBPath))
{
    // Create the tables if they don't exist
    db.CreateTable<Customer>();
    db.CreateTable<Item>();
}

The problem:

The code below here will not reset the Primary Key to 0 (or starting number ) for each of the table Once each of them has been used before. Is this code only empty the table but not reset the Primary Key?

How to delete the customers.sqlite ( Database created ) so that this will reset all Primary Key to starting number like 0.

using (var db = new SQLite.SQLiteConnection(DBPath))
{
    db.DeleteAll<Customer>();
    db.DeleteAll<Item>();
}

This has been answered here http://stackoverflow.com/questions/1601697/sqlite-reset-primary-key-field — n00b, Oct 09 '13 at 21:24

score 1 · Answer 1 · answered Oct 17 '13 at 05:29

This code can reset Primary key, but if you need delete db, just delete sqlite-db file.

using (var db = new SQLite.SQLiteConnection(localBaseConnection))
                {
                    db.DropTable<YourTableName>();
                    db.CreateTable<YourTableName>();
                    db.Dispose();
                    db.Close();
                }

score 0 · Answer 2 · answered Oct 09 '13 at 11:12

0

To ensure that autoincrement valures are reset, you can either delete the database file, or drop the tables.

answered Oct 09 '13 at 11:12

CL.

173,858
17
217
259

How to delete the database and recreate a new one? this code db.DeleteAll() means empty the table. How to drop the table? – MilkBottle Oct 10 '13 at 01:13
To delete the database, just delete the *file*. To drop a table, execute the SQL statement `DROP TABLE xxx`. – CL. Oct 10 '13 at 07:51
To ask a new question, use the "Ask Question" button. However, StackOverflow is not a replacement for googling "winrt delete file". – CL. Oct 10 '13 at 08:02

score 0 · Accepted Answer · answered Oct 09 '13 at 11:49

0

With this queries you can clear the table & then reset auto increment column.

using (var db = new SQLiteConnection(ApplicationData.Current.LocalFolder.Path + "\\db.sqlite"))
{
    db.Query<your_table_name>("delete from your_table_name", "");
    db.Query<sqlite_sequence>("delete from sqlite_sequence where name = ?", "your_table_name");
}

You also have to add this class.

public class sqlite_sequence
{
    public string name { get; set; }
    public int seq { get; set; }
}

answered Oct 09 '13 at 11:49

Farhan Ghumra

15,180
6
50
115

My Table name is Customer: 1) db.Query("delete from Customer", ""); 2) db.Query("delete from sqlite_sequence where name = ?", "Customer"); I include the sqlite_sequence class. I dont understand the delete statement in (2). name=? , is this a must? – MilkBottle Oct 10 '13 at 01:10
If you [browse the SQLite](http://sourceforge.net/projects/sqlitebrowser/), you will see sqlite_sequence table which take care about the starting of primary key. In that there will be entry for `Customer` table. – Farhan Ghumra Oct 10 '13 at 03:41
delete from sqlite_sequence where name = ?, what is this: name=? is this name refers to table name or field name inside the table. I am not sure but I think it refers to table name. – MilkBottle Oct 10 '13 at 07:58
Yes it's table name and `seq` is next ID. – Farhan Ghumra Oct 10 '13 at 08:30

How to reset the Primary key and delete the SQLite Db created

3 Answers3