Original text: The use of Sqlite under .NET and the cleanup of Sqlite database
Sqlite is a lightweight relational database, and I will not go into details about its benefits. The original intention of this article is to provide help for the users of the .net platform.
Sqlite has a package specially developed for VS2010. You can download it from the System.Data.SQLite Download Page. Note: This is the only setup package that is capable of installing the design-time components for Visual Studio 2010.
After installation, you will see the newly created database link in VS2010 There is a link to System.Data.SQLite, where you can create and open a Sqlite database:
In the sqlite link, you can Set the password and other configuration for your sqlite database. After confirming, a data file without extension will be generated.
As everyone knows, sqlite data files end with .db or .sqlite or other extensions, and sqlite created with VS2010 The database file does not have an extension, and its encryption capability is particularly high. It is difficult to open a normal sqlite program without a password.
After creating a sqlite database with VS2010, you can operate sqlite like sql server, such as creating tables, querying, etc. Convenient, you can try it if you are interested, here is not a screenshot.
In the project, you need to add sqlite references: System.Data.SQLite.dll and System.Data.SQLite.Linq .dll
Sqlite database cleanup
Finally, explain a downside of SQLite. When there are a large number of insert, update, and delete operations in one or more data tables in the database, a large amount of disk space will be occupied by deleted data. Before executing the VACUUM command, SQLite did not return them to the operating system.
Because the data storage in this type of data table is very scattered, it is impossible to get a better batch IO reading effect when querying, which affects the query efficiency.
In SQLite, it only supports cleaning up the main database currently connected, but not other attached databases. The VACUUM command adopts the same strategy as PostgreSQL when completing data cleaning, that is, to create a new database file with the same size as the current database file, and then import the data in the database file into the new file in an organized manner, which has been deleted The data block will not be imported. After the import is completed, shrink the size of the new database file to an appropriate size. You can use the VACUUM method to clean up the sqlite database:
SQLiteConnection sqlconn = new SQLiteConnection(@"Data Source=C:\cache;Version=3;Password=123");
sqlconn.Open();
SQLiteCommand sqlCom = sqlconn.CreateCommand();
sqlCom.CommandText = "VACUUM";
sqlCom.ExecuteNonQuery();
I hope this article is helpful to you, Thank you!
SQLiteConnection sqlconn = new SQLiteConnection(@"Data Source=C:\cache;Version=3;Password=123" );
sqlconn.Open();
SQLiteCommand sqlCom = sqlconn.CreateCommand();
sqlCom.CommandText = "VACUUM";
sqlCom.ExecuteNonQuery();