SQLite usage in. NET and SQLite database cleanup

Original: SQLite's use in. NET and SQLite database cleanup

Sqlite is a lightweight relational database, and I will not be in the details of her benefits. The purpose of this article is to help users of the. NET Platform.

SQLite has developed a special package for the VS2010, you can go to System.Data.SQLite Download Page down to download, note is: the "the only setup packages that iscapable O F Installing the Design-time components for Visual Studio.  

After installation, a new number of library links in VS2010 will see links to System.Data.SQLite, where you can create and open the SQLite database:

In the SQLite link, you can set the password and other configuration for your SQLite database. Once determined, a data file with no extension is generated.

It is generally understood that sqlite data files are. db or. SQLite or other extensions end, and the SQLite database file created with VS2010 has no extension, and its encryption ability is particularly high, the General SQLite program in the case of no password, it is difficult to open.

Using VS2010 to create a SQLite database, you can operate like SQL Server SQLite, such as: build tables, queries, etc. are very convenient, interested can try, here is not explained.

In the project, you need to add SQLite references: System.Data.SQLite.dll and System.Data.SQLite.Linq.dll

SQLite database cleanup

Finally, to illustrate a bad place in SQLite, when one or more data tables in the database have a large number of inserts, updates, and deletions, there will be a lot of disk space occupied by the deleted data, and before the vacuum command was executed, SQLite did not return them to the operating system.

Because the data storage in this kind of data table is very scattered, it is not possible to get better bulk io reading effect when querying, thus affecting the query efficiency.

In SQLite, only the primary database in the current connection is supported, not the other attached databases. The vacuum command uses the same policy as PostgreSQL when it finishes the data cleanup, creating a new database file of the same size as the current database file, and then importing the data from that database file into the new file, where the deleted data block will not be imported, after the import is complete , shrink the size of the new database file to the appropriate size. You can use the vacuum method to clean up the SQLite database:

New Sqliteconnection (@ "Data Source=c:\cache; version=3; password=123");                Sqlconn. Open ();                 = sqlconn. CreateCommand ();                 " VACUUM " ;                Sqlcom.executenonquery ();

Hope this article is helpful to you, Thank you!

SQLite usage in. NET and SQLite database cleanup