How to solve the problem that database memory is not changed after SQLite data table/record is deleted in C #

When you use the SQLite database in C #, you find that when you delete a data table or a large amount of data, the database does not become smaller.

This is because when a large amount of data is deleted in the database, the original data space (the free data page) is left, and the database does not automatically release the space by default. You can free up space by using the VACUUM command.

Two methods are found online (manual/Automatic):

1. Free space Manually

Write a function that executes the SQL statement first:

1 Private voidExecuteSQL (stringsDBPath,stringsqlstr)2 {3     using(Sqliteconnection conn =NewSqliteconnection ("Data Source ="+sDBPath))4     {5         using(Sqlitecommand cmd =NewSqlitecommand ())6         {7Cmd. Connection =Conn;8 9 Conn. Open ();TenCmd.commandtext =sqlstr; One cmd. ExecuteNonQuery (); A  - Conn. Close (); -         } the     } -}

After deleting the data table/large amount of data, call the above function (DBPath is the address of the database).

" VACUUM ");

2. Set up the database to automatically free up space

When there is no data table in the database, set its properties:

" PRAGMA auto_vacuum = 1; ");

Testing the results of the discovery is also very good.

Compare two methods, automatic more convenient. However, it is important to note that the database generates a large amount of memory fragmentation when frequent inserts, updates, and deletions occur. Freeing up space automatically frees up free data pages, but does not defragment memory fragments, which in turn generates additional memory fragmentation, while manually freeing free space and defragmenting memory fragments.

How to solve the problem that database memory is not changed after SQLite data table/record is deleted in C #