. Net SQlite Development Tips

Recently in the development of SQLite, the development environment is vs2010+ sqlite ADO data Provider. This Data provider program is based on the System.Data.SQLite 1.0.66 release, which summarizes the experience in developing SQLite.

1. Program Deployment Error

Because SQLite ADO data provider will register common DLLs with the GAC, there is generally no problem with native computers, but deploying to other machines will cause errors. You need to do two things at this point:

• Copy the System.Data.SQLite.dll, System.Data.SQLite.Linq.dll, and several related DLLs into the application directory.
• Change the connection stirng absolute path in *.exe.config to a relative path.
• The *.exe.config file needs to add the following information:

 
 
<system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite"/>
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite,Culture=neutral, PublicKeyToken=db937bc2d44ff139″ />
    </DbProviderFactories>
  </system.data>

2. Database Cleanup (VACUUM) when there is a large number of inserts, updates, and deletions in one or more data tables in the database, there will be a large amount of disk space occupied by the deleted data. SQLite did not return them to the operating system until the vacuum command was executed. 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. 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. In SQLite, only the primary database in the current connection is supported, not the other attached databases. You can use the vacuum method to clean up the SQLite database. 3. Delete Cascade for foreign keys does not work

The Cascade information for foreign keys was not imported correctly when importing *.db files using the designer Tools of SQLite ADO data provider. You need to manually make changes to the EDMX editing interface. Select the connector in the *.EDMX window and manually modify the End1 OnDelete property in the Properties window to cascade. After the modification is completed, it is found that cascade sometimes takes effect. This is because if the *.DB library is not loaded into memory, Cascade does not take effect. Therefore, if you want the cascade to take effect, you also need to manually load the associated table (a bit like the hibernate mechanism), the code is as follows:

if (! t1.t11.IsLoaded) 
T1.t11.Load ();

When a row in the T1 table is deleted, the related data in the T11 table is then deleted. However, this also caused the performance of sqlite decline, in particular, according to business needs to make trade-offs.

4.nullable<long> non-null type if not specifically set in SQLite, the Property object for the numeric column map is Nullabe type and needs to be set to non-empty in the database.

. Net SQlite Development Tips