"Introduction to SQLite management Tools"
The following 2 sections are recommended:
Navicat for SQLite: Very powerful, almost contains all the necessary functions of database management tools, easy to operate. The only disadvantage is that you cannot open a database that has been encrypted by System.Data.SQLite.dll.
Database.net: Taiwanese people use. Net developed universal database management tools, can manage a variety of databases, including MSSQL, MYSQL, IBM DB2, Oracle, Access, Excel, OLE DB, ODBC, etc. more than 10 kinds of database (or data interface), Features are not navicat so much, only the most basic functions. For SQLite, the biggest advantage of database.net is that it supports opening a database that is encrypted by System.Data.SQLite.dll and can set a password for the database at any time, and is a necessary gadget for developing SQLite under. Net. Download Address: http://fishcodelib.com/Database.htm cloud-dwelling community download address http://www.jb51.net/database/41238.html
It is recommended to Navicat for SQLite, Database.net as a supplement, as long as the database encryption is involved in the latter.
"Action SQLite instance"
The method of manipulating SQLite is basically the same as for other databases, but there are some differences:
"Example 1" integers all seem to be Int64.
Query the total number of records in the city table in the "Provincial. db" Database under the App_Data directory of the Web site
Copy Code code as follows:
Sqliteconnection cn = New Sqliteconnection ("Data source=| datadirectory| provinces. 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);
The Count function in SQLite returns a Int64 integer, which is different from MSSQL, access, and so on. In fact, after a limited usage discovery, it seems that all the integer fields return values are Int64, which has not been validated. The ExecuteScalar method returns an instance of object, which, according to C #, makes a standard conversion when unpacking, and must be converted to the actual stored format of the object instance, so it is divided into two steps, first converted to Int64, and then Int. Of course, there are some advanced converters in. NET such as the Convert.ToInt32 method just one step.
"Example 2" batch additions and deletions need to use the transaction, otherwise the efficiency is very low.
BULK Insert 1000 records, each record has only a simple ID, name, password three fields:
Copy Code code as follows:
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 record number:" + 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", "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 record number:" + RecordCount + "<br/>");
After testing, the for loop in this code took 70000~90000 milliseconds for more than a minute!
Use transaction execution instead:
Copy Code code as follows:
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 record number:" + 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", "name" + i);
Cmd. Parameters.addwithvalue ("@password", (i * 2). ToString ());
Cmd. ExecuteNonQuery ();
}
Tran.commit ();
}
Catch
{
Tran. Rollback ();
Response.Write ("Wrong execution!") ");
}
Finally
{
Cmd.commandtext = "SELECT count (*) from Test";
RecordCount = (int) (Int64) cmd. ExecuteScalar ();
cn. Close ();
Response.Write ("Current total record number:" + RecordCount + "<br/>");
}
After testing, the try part of the code uses only 100~150 milliseconds! After opening the transaction, the efficiency is very high!
"Example 3" in general development can write their own database generic operation class, further encapsulation ado.net.
For example, use transaction-operated code above, instead of the database generic operation class:
Copy Code code as follows:
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 record number:" + RecordCount + "<br/>");
Md. CreateTransaction ();
Try
{
for (int i = 0; i < 1000; i++)
Md. ExecuteNonQuery ("INSERT into test values (@id, @name, @password)", "@id", I, "@name", "name" + I, "@password", (i * 2). ToString ());
Md.committransaction ();
}
Catch
{
Md. RollBack ();
Response.Write ("Wrong execution!") ");
}
Finally
{
RecordCount = (int) (INT64) Md. ExecuteScalar ("SELECT count (*) from Test");
Md. Close ();
Response.Write ("Current total record number:" + RecordCount + "<br/>");
}
You can see that the code is a lot simpler.
"SQLite related useful link address"
SQLite official website: http://www.sqlite.org/
SQLite built-in core Function reference documentation: http://www.sqlite.org/lang_corefunc.html
SQLite Date-time Function Reference Document: http://www.sqlite.org/lang_datefunc.html
SQLite mathematical Function Reference Document: http://www.sqlite.org/lang_aggfunc.html
SQLite related SQL Syntax 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
