【Introduction to SQLite Management Tools】
The following 2 models are recommended:
Navicat for SQLite: It has very powerful functions, including almost all the necessary functions of database management tools, which are simple to operate and easy to get started. The only disadvantage is that the encrypted database cannot be opened.
: The all-round database management tool developed by * people using .net can manage a variety of databases, including more than ten databases (or data interfaces) such as MSSQL, MYSQL, IBM DB2, Oracle, Access, Excel, OleDb, Odbc, etc. The functions are not as many as Navicat, and only contain the most basic functions. For SQLite, the biggest advantage is that it supports opening encrypted databases and can set passwords to the database at any time. It is a necessary gadget for developing SQLite under .net. Download address:/My download addresshttps:///database/
It is recommended to use Navicat for SQLite as the main and auxiliary, and use the latter only when database encryption is involved.
【Operating SQLite instance】
The method of operating SQlite is basically the same as other databases, but there are some differences:
"Example 1" The integers seem to be of Int64.
Query the total number of records in the city table in the "Provincial and Municipal.db" database under the website App_Data directory
SQLiteConnection cn = new SQLiteConnection("Data Source=|DataDirectory|Province and City.db;Version=3");
SQLiteCommand cmd = new SQLiteCommand("select count(*) from city", cn);
();
int recordCount = (int)(Int64)();
();
(recordCount);
The count function in SQLite returns an Int64 integer, which is different from MSSQL, Access, etc. In fact, after limited use discovery, it seems that the return value of all INTEGER fields is Int64, which has not been effectively proven. The ExecuteScalar method returns an object instance. According to C# regulations, standard conversion is performed when unboxing, and it must be converted into the format actually stored by the object instance. Therefore, it is divided into two steps, first converted to Int64, and then converted to int. Of course, using some advanced converters such as Convert.ToInt32 in .net is only one step away.
"Example 2" When adding, deleting and modifying batches, transactions are required, otherwise the efficiency will be very low.
Insert 1000 records in batches, each record has only three fields: id, name, and password:
SQLiteConnection cn = new SQLiteConnection("Data Source=c:\\Test.db3;Version=3;password=12345");
SQLiteCommand cmd = new SQLiteCommand("select count(*) from test", cn);
();
int recordCount = (int)(Int64)();
("Current total number of records:" + recordCount + "<br/>");
for (int i = 0; i < 1000; i++)
{
= "insert into test values(@id,@name,@password)";
("@id", i);
("@name", "name" + i);
("@password", (i * 2).ToString());
();
}
= "select count(*) from test";
recordCount = (int)(Int64)();
();
("Current total number of records:" + recordCount + "<br/>");
After testing, the for loop in this code took 70,000 to 90,000 milliseconds, more than one minute!
Use transaction execution instead:
SQLiteConnection cn = new SQLiteConnection("Data Source=c:\\Test.db3;Version=3;password=12345");
SQLiteCommand cmd = new SQLiteCommand("select count(*) from test", cn);
();
int recordCount = (int)(Int64)();
("Current total number of records:" + recordCount + "<br/>");
SQLiteTransaction tran = ();
= tran;
try
{
for (int i = 0; i < 1000; i++)
{
= "insert into test values(@id,@name,@password)";
("@id", i);
("@name", "name" + i);
("@password", (i * 2).ToString());
();
}
();
}
catch
{
();
("Execution error!");
}
finally
{
= "select count(*) from test";
recordCount = (int)(Int64)();
();
("Current total number of records:" + recordCount + "<br/>");
}
After testing, the try part in this code only took 100 to 150 milliseconds! After starting the transaction, the efficiency is very high!
"Example 3" Generally, in development, you can write your own database general operation class and further encapsulate it.
For example, the above code that uses transaction operations, use the database general operation class instead:
SQLiteData md = new SQLiteData("Data Source=c:\\Test.db3;Version=3;password=12345");
int recordCount = (int)(Int64)("select count(*) from test");
("Current total number of records:" + recordCount + "<br/>");
();
try
{
for (int i = 0; i < 1000; i++)
("insert into test values(@id,@name,@password)", "@id", i, "@name", "name" + i, "@password", (i * 2).ToString());
();
}
catch
{
();
("Execution error!");
}
finally
{
recordCount = (int)(Int64)("select count(*) from test");
();
("Current total number of records:" + recordCount + "<br/>");
}
You can see that the code has been much thinner.
The following 2 models are recommended:
Navicat for SQLite: It has very powerful functions, including almost all the necessary functions of database management tools, which are simple to operate and easy to get started. The only disadvantage is that the encrypted database cannot be opened.
: The all-round database management tool developed by * people using .net can manage a variety of databases, including more than ten databases (or data interfaces) such as MSSQL, MYSQL, IBM DB2, Oracle, Access, Excel, OleDb, Odbc, etc. The functions are not as many as Navicat, and only contain the most basic functions. For SQLite, the biggest advantage is that it supports opening encrypted databases and can set passwords to the database at any time. It is a necessary gadget for developing SQLite under .net. Download address:/My download addresshttps:///database/
It is recommended to use Navicat for SQLite as the main and auxiliary, and use the latter only when database encryption is involved.
【Operating SQLite instance】
The method of operating SQlite is basically the same as other databases, but there are some differences:
"Example 1" The integers seem to be of Int64.
Query the total number of records in the city table in the "Provincial and Municipal.db" database under the website App_Data directory
Copy the codeThe code is as follows:
SQLiteConnection cn = new SQLiteConnection("Data Source=|DataDirectory|Province and City.db;Version=3");
SQLiteCommand cmd = new SQLiteCommand("select count(*) from city", cn);
();
int recordCount = (int)(Int64)();
();
(recordCount);
The count function in SQLite returns an Int64 integer, which is different from MSSQL, Access, etc. In fact, after limited use discovery, it seems that the return value of all INTEGER fields is Int64, which has not been effectively proven. The ExecuteScalar method returns an object instance. According to C# regulations, standard conversion is performed when unboxing, and it must be converted into the format actually stored by the object instance. Therefore, it is divided into two steps, first converted to Int64, and then converted to int. Of course, using some advanced converters such as Convert.ToInt32 in .net is only one step away.
"Example 2" When adding, deleting and modifying batches, transactions are required, otherwise the efficiency will be very low.
Insert 1000 records in batches, each record has only three fields: id, name, and password:
Copy the codeThe code is as follows:
SQLiteConnection cn = new SQLiteConnection("Data Source=c:\\Test.db3;Version=3;password=12345");
SQLiteCommand cmd = new SQLiteCommand("select count(*) from test", cn);
();
int recordCount = (int)(Int64)();
("Current total number of records:" + recordCount + "<br/>");
for (int i = 0; i < 1000; i++)
{
= "insert into test values(@id,@name,@password)";
("@id", i);
("@name", "name" + i);
("@password", (i * 2).ToString());
();
}
= "select count(*) from test";
recordCount = (int)(Int64)();
();
("Current total number of records:" + recordCount + "<br/>");
After testing, the for loop in this code took 70,000 to 90,000 milliseconds, more than one minute!
Use transaction execution instead:
Copy the codeThe code is as follows:
SQLiteConnection cn = new SQLiteConnection("Data Source=c:\\Test.db3;Version=3;password=12345");
SQLiteCommand cmd = new SQLiteCommand("select count(*) from test", cn);
();
int recordCount = (int)(Int64)();
("Current total number of records:" + recordCount + "<br/>");
SQLiteTransaction tran = ();
= tran;
try
{
for (int i = 0; i < 1000; i++)
{
= "insert into test values(@id,@name,@password)";
("@id", i);
("@name", "name" + i);
("@password", (i * 2).ToString());
();
}
();
}
catch
{
();
("Execution error!");
}
finally
{
= "select count(*) from test";
recordCount = (int)(Int64)();
();
("Current total number of records:" + recordCount + "<br/>");
}
After testing, the try part in this code only took 100 to 150 milliseconds! After starting the transaction, the efficiency is very high!
"Example 3" Generally, in development, you can write your own database general operation class and further encapsulate it.
For example, the above code that uses transaction operations, use the database general operation class instead:
Copy the codeThe code is as follows:
SQLiteData md = new SQLiteData("Data Source=c:\\Test.db3;Version=3;password=12345");
int recordCount = (int)(Int64)("select count(*) from test");
("Current total number of records:" + recordCount + "<br/>");
();
try
{
for (int i = 0; i < 1000; i++)
("insert into test values(@id,@name,@password)", "@id", i, "@name", "name" + i, "@password", (i * 2).ToString());
();
}
catch
{
();
("Execution error!");
}
finally
{
recordCount = (int)(Int64)("select count(*) from test");
();
("Current total number of records:" + recordCount + "<br/>");
}
You can see that the code has been much thinner.
【A useful link address related to SQLite】
SQLite official website:/
SQLite built-in core function reference documentation:/lang_corefunc.html
SQLite datetime function reference documentation:/lang_datefunc.html
SQLite mathematical function reference documentation:/lang_aggfunc.html
SQLite-related SQL syntax reference documentation:/
Data access driver download address://doc/trunk/www/