Some experiences in using Sqlite.

Recently, I took a small project in the United States. The main requirement is to combine the data exported from the disk (DBF format) with other data (csv format) to generate a new data table, which facilitates query and error correction. The other party does not install ms Access. Considering the convenience of use by the other party, sqlite is used and. net 2.0 is used as the development language. The following is a brief list of some experiences in the use process.

Provider: Provider decides to use System. Data. SQLite by using other open-source software and online search.

 

Transaction (DbTransaction): Data import and merge, batch insertion, and update are required. transactions are not explicitly called at the beginning, so the speed is not too slow, therefore, it is assumed that sqlite is slower than access. Later, the search found that SQLite started a transaction for each operation by default. In fact, this is the basic knowledge of database operations. Not only does Sqlite need to pay attention to this, of course, Access and other transactions are not supported here.

Index): Data import and merge. when inserting and updating data, you must check whether the data is duplicated and exists. Therefore, indexes cannot be missing.

SQLiteParameter: When batch inserts are performed, string is used to save the trouble. format to format the insert. The result is "near" s ": syntex error. ", after tracking, it turns out to be a single quotation mark (') problem, it seems that it is cool to use SQLiteParameter to standardize the import, and can avoid many character problems.

Update... From (Cross Join in Update): Sqlite does not support similar

"UPDATE tbl1 SET col2 = tbl2.col2
FROM table1 tbl1 inner join table2 tbl2 ON tbl1.col1 = tbl2.col1"

The alternative solution is:

UPDATE table1 SET col2 = (select col2 from table2 where table2.col1 = table1.col1 limit 1)

Where exists (select * from table2 where table2.col1 = table1.col1 );

Query Analyzer: I chose SQLite Administrator. Later I found that the Firefox plugin (addons) Sqlite Manager is also a good choice.

Some SQLite-related projects developed by DotNet are listed below:

Convert SQL Server DB to SQLite DB: C # utility to automatically do the conversion from SQL Server DB to SQLite DB (a tool that automatically converts ms SQL Server database to SQLite DB, including source code ).

SQLite Membership, Role, and Profile Providers: Complete,
Production-ready Membership, Role, and Profile providers for SQLite.
Includes instructions for migrating data between SQL Server and SQLite.