Introduction to using the SQLite database in C

【 introduction to SQLite management tools 】
The following two are recommended:
Navicat for SQLite: very powerful, contains almost all the necessary functions of database management tools, easy to operate, easy to get started. The only disadvantage of 1 is that it cannot open a database encrypted by System.Data.SQLite.dll.
Database.Net: the comprehensive database management tool developed by net, which can manage a variety of databases, including MSSQL, MYSQL, IBM DB2, Oracle, Access, Excel, OleDb, Odbc and more than 10 kinds of databases (or data interfaces), has fewer functions than Navicat, and only contains the most basic functions. For SQLite Database. Net biggest advantage is to support open by System. Data. SQLite. dll encrypted database, and can at any time to set the password database, is. Develop SQLite net must-have gadgets. Download address: http: / / fishcodelib com/Database htm this site to download address / / www ofstack. com database / 41238. html
It is recommended to Navicat for SQLite as the main, Database.Net as the auxiliary, as long as the latter is involved in database encryption.
[example of operation SQLite]
SQlite operates in much the same way as other databases, but with a few differences:
The "example 1" integers seem to be all of Int64.
The total number of records in the city table in the database of "province.db" under the directory App_Data of the website is found

 
SQLiteConnection cn = new SQLiteConnection("Data Source=|DataDirectory| Provinces and cities .db;Version=3"); 
SQLiteCommand cmd = new SQLiteCommand("select count(*) from city", cn); 
cn.Open(); 
int recordCount = (int)(Int64)cmd.ExecuteScalar(); 
cn.Close(); 
Response.Write(recordCount); 

In SQLite, the count function returns an integer of Int64, which is different from MSSQL, Access, etc. In fact, after limited use, it appears that the return value of all the INTEGER fields is Int64, and this 1 point has not been validated. The ExecuteScalar method returns an instance of object. According to the rules of C#, the standard conversion during unboxing must be converted into the actual storage format of the object instance. Therefore, it is divided into two steps, first into Int64 and then into int. Of course, some of the advanced converters in.net such as the Convert.ToInt32 method are only 1 step away.
"Example 2" batch addition, deletion and modification need to use transactions, otherwise the efficiency is very low.
Batch insert 1000 records, each record only has the simple id, name, password3 fields:

 
SQLiteConnection cn = new SQLiteConnection("Data Source=c:\\ test .db3;Version=3;password=12345"); 
SQLiteCommand cmd = new SQLiteCommand("select count(*) from test", cn); 
cn.Open(); 
int recordCount = (int)(Int64)cmd.ExecuteScalar(); 
Response.Write(" Current total records: " + recordCount + "<br/>"); 
for (int i = 0; i < 1000; i++) 
{ 
cmd.CommandText = "insert into test values(@id,@name,@password)"; 
cmd.Parameters.AddWithValue("@id", i); 
cmd.Parameters.AddWithValue("@name", " The name " + i); 
cmd.Parameters.AddWithValue("@password", (i * 2).ToString()); 
cmd.ExecuteNonQuery(); 
} 
cmd.CommandText = "select count(*) from test"; 
recordCount = (int)(Int64)cmd.ExecuteScalar(); 
cn.Close(); 
Response.Write(" Current total records: " + recordCount + "<br/>"); 

After testing, the for loop in this code took between 70,000 and 90,000 milliseconds, or more than a minute!
Switch to transaction execution:

 
SQLiteConnection cn = new SQLiteConnection("Data Source=c:\\ test .db3;Version=3;password=12345"); 
SQLiteCommand cmd = new SQLiteCommand("select count(*) from test", cn); 
cn.Open(); 
int recordCount = (int)(Int64)cmd.ExecuteScalar(); 
Response.Write(" Current total records: " + recordCount + "<br/>"); 
SQLiteTransaction tran = cn.BeginTransaction(); 
cmd.Transaction = tran; 
try 
{ 
for (int i = 0; i < 1000; i++) 
{ 
cmd.CommandText = "insert into test values(@id,@name,@password)"; 
cmd.Parameters.AddWithValue("@id", i); 
cmd.Parameters.AddWithValue("@name", " The name " + i); 
cmd.Parameters.AddWithValue("@password", (i * 2).ToString()); 
cmd.ExecuteNonQuery(); 
} 
tran.Commit(); 
} 
catch 
{ 
tran.Rollback(); 
Response.Write(" Execution error! "); 
} 
finally 
{ 
cmd.CommandText = "select count(*) from test"; 
recordCount = (int)(Int64)cmd.ExecuteScalar(); 
cn.Close(); 
Response.Write(" Current total records: " + recordCount + "<br/>"); 
} 

After testing, the try part of this code only took between 100 and 150 milliseconds! When you start a transaction, you are very efficient!
You can write your own database common operation class in development like "example 3" 1, and encapsulate ADO.NET step by step.
If the above transaction operation code is used, after switching to the database common operation class:

 
SQLiteData md = new SQLiteData("Data Source=c:\\ test .db3;Version=3;password=12345"); 
int recordCount = (int)(Int64)md.ExecuteScalar("select count(*) from test"); 
Response.Write(" Current total records: " + recordCount + "<br/>"); 
md.CreateTransaction(); 
try 
{ 
for (int i = 0; i < 1000; i++) 
md.ExecuteNonQuery("insert into test values(@id,@name,@password)", "@id", i, "@name", " The name " + i, "@password", (i * 2).ToString()); 
md.CommitTransaction(); 
} 
catch 
{ 
md.RollBack(); 
Response.Write(" Execution error! "); 
} 
finally 
{ 
recordCount = (int)(Int64)md.ExecuteScalar("select count(*) from test"); 
md.Close(); 
Response.Write(" Current total records: " + recordCount + "<br/>"); 
} 

You can see the code is much more streamlined.

[useful links for SQLite]

SQLite's official website: http: / / www sqlite. org /

SQLite built-in core function reference documentation: http: / / www sqlite. org/lang_corefunc html

SQLite date/time functions reference documentation: http: / / www sqlite. org/lang_datefunc html

SQLite mathematical function reference documentation: http: / / www sqlite. org/lang_aggfunc html

SQLite related SQL grammar reference documentation: http: / / www sqlite. org/lang html

System. Data. SQLite. dll data access driver download address: http: / / system data. sqlite. org/index html/doc/trunk/www/downloads wiki