SQLite Introduction
In the introduction of System. data. SQLite is a single-host database management system similar to Access. It defines all databases (including definitions, tables, indexes, and data) are saved in a single file. In addition, SQLite is a class library implemented in C. It has good performance in terms of memory consumption, file size, and simplicity. If the data size is less than 10 million, the query speed is also quite fast.
SQLite has the following features:
Standard for most SQL92 implementations, including transactions (atomicity, consistency, isolation and persistence), triggers, and most complex queries.
If you do not check the type of the inserted or updated data, you can insert the string into the integer column (this may make some users not suitable ).
Supports mainstream systems such as Windows, Linux, and Unix, as well as embedded systems such as Android and Windows Mobile.
System. Data. SQLite
System. data. SQLite is an enhanced version of SQLite. NET Framework, because it contains an ADO. NET 2.0 engine, so. NET developers can use System. data. SQLite is easy to develop.. NET program.
System. data. SQLite and SQLite also have some restrictions. For example, row-level and table-level locks are not supported. When a connection locks the database for writing data, other database connections can only be read and written after the connection operation is complete, SQLite. NET has tried many times in the ultra-short period.
In fact, for large applications, we will choose some large and professional databases. System. Data. SQLite and SQLite are suitable for some limited occasions, such as mobile phones. Here I am going to talk about a real experience. Before that, I had a small system and wanted to analyze three Excel files, two of which had about 400 records, the other one is about 10 thousand. If such a system uses a database, even with the single-host Access, it is relatively simple to use the database features for analysis after import, because we can use connection queries in the database and database functions for records, the information provided by the other party is that although the Office is installed on the deployed machine, however, only Word, Excel, and Outlook are installed without Access, and the other party does not want to install other software, because I am not sure whether Access can be accessed through OleDB on the machine without Access. mdb file, so there is no way, only in the form of a memory table, will read the data in Excel to the DataTable, and then analyze the three DataTable, despite a lot of optimization, however, the efficiency is still not ideal. In this case, if I knew. data. SQLite is much easier to do. It imports data from three Excel files to System. data. SQLite, and then use System. data. the functions provided by SQLite are easy to process. For System. Data. SQLite, you do not need to install it during deployment. Only one System. Data. SQLite. dll is required. This dll is only 866 kb! In addition, it does not need to be registered as it uses Com components.
Operate System. Data. SQLite in VS2008
To facilitate developers, System. data. SQLite provides support for VS2005 and VS2008, and even supports. NET 3.5 SP1 Entity Framework, the following is the use of System in VS2008. data. SQLite designer:
First open the server resource manager in VS2008, such:
Right-click the data connection, as shown in:
Then select "add connection", as shown in:
In this case, select System. data. the database file used by SQLite. The default file suffix is. db3, you can also click the "test connection" button below. If no problem exists, the correct dialog box is displayed, after you click "OK", the following error occurs in the server resource manager:
In this way, we can operate the tables in System. Data. SQLite just like the database in SQL Server.
System. Data. SQLite Common Database Class
Database operations can be divided into the following situations:
Create Database files;
Return DataTable;
Return DataReader;
Add, delete, modify, and return the number of affected rows;
Execute the query and return the first column of the First row (usually used for queries with row functions, such as SUM/AVG/COUNT );
Returns all tables in the database;
Because in System. data. SQLite does not contain stored procedures. Therefore, all operations are text-based SQL statements. To avoid SQL injection, parameterized SQL statements are used. The common database classes are as follows:
Copy codeThe Code is as follows: using System;
Using System. Data;
Using System. Data. Common;
Using System. Data. SQLite;
Namespace SQLiteQueryBrowser
{
/// <Summary>
/// Description: This is a general class encapsulated by conventional database operations for System. Data. SQLite.
/// Author: zhoufoxcn (Zhou Gong)
/// Date: 2010-04-01
// Blog: http://zhoufoxcn.blog.51cto.com or http://blog.csdn.net/zhoufoxcn
/// Version: 0.1
/// </Summary>
Public class SQLiteDBHelper
{
Private string connectionString = string. Empty;
/// <Summary>
/// Constructor
/// </Summary>
/// <Param name = "dbPath"> SQLite database file path </param>
Public SQLiteDBHelper (string dbPath)
{
This. connectionString = "Data Source =" + dbPath;
}
/// <Summary>
/// Create an SQLite database file
/// </Summary>
/// <Param name = "dbPath"> path of the SQLite database file to be created </param>
Public static void CreateDB (string dbPath)
{
Using (SQLiteConnection connection = new SQLiteConnection ("Data Source =" + dbPath ))
{
Connection. Open ();
Using (SQLiteCommand command = new SQLiteCommand (connection ))
{
Command. CommandText = "create table Demo (id integer not null primary key autoincrement unique )";
Command. ExecuteNonQuery ();
Command. CommandText = "drop table Demo ";
Command. ExecuteNonQuery ();
}
}
}
/// <Summary>
/// Add, delete, modify, and return the number of affected rows.
/// </Summary>
/// <Param name = "SQL"> SQL statement to be executed </param>
/// <Param name = "parameters"> parameters required for executing the add, delete, and modify statements. The parameters must follow the order in the SQL statements. </param>
/// <Returns> </returns>
Public int ExecuteNonQuery (string SQL, SQLiteParameter [] parameters)
{
Int affectedRows = 0;
Using (SQLiteConnection connection = new SQLiteConnection (connectionString ))
{
Connection. Open ();
Using (DbTransaction transaction = connection. BeginTransaction ())
{
Using (SQLiteCommand command = new SQLiteCommand (connection ))
{
Command. CommandText = SQL;
If (parameters! = Null)
{
Command. Parameters. AddRange (parameters );
}
AffectedRows = command. ExecuteNonQuery ();
}
Transaction. Commit ();
}
}
Return affectedRows;
}
/// <Summary>
/// Execute a query statement and return an associated SQLiteDataReader instance
/// </Summary>
/// <Param name = "SQL"> query statement to be executed </param>
/// <Param name = "parameters"> the parameters required to execute an SQL query statement must follow the order of the parameters in the SQL statement. </param>
/// <Returns> </returns>
Public SQLiteDataReader ExecuteReader (string SQL, SQLiteParameter [] parameters)
{
SQLiteConnection connection = new SQLiteConnection (connectionString );
SQLiteCommand command = new SQLiteCommand (SQL, connection );
If (parameters! = Null)
{
Command. Parameters. AddRange (parameters );
}
Connection. Open ();
Return command. ExecuteReader (CommandBehavior. CloseConnection );
}
/// <Summary>
/// Execute a query statement and return a DataTable containing the query result
/// </Summary>
/// <Param name = "SQL"> query statement to be executed </param>
/// <Param name = "parameters"> the parameters required to execute an SQL query statement must follow the order of the parameters in the SQL statement. </param>
/// <Returns> </returns>
Public DataTable ExecuteDataTable (string SQL, SQLiteParameter [] parameters)
{
Using (SQLiteConnection connection = new SQLiteConnection (connectionString ))
{
Using (SQLiteCommand command = new SQLiteCommand (SQL, connection ))
{
If (parameters! = Null)
{
Command. Parameters. AddRange (parameters );
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter (command );
DataTable data = new DataTable ();
Adapter. Fill (data );
Return data;
}
}
}
/// <Summary>
/// Execute a query statement and return the first column of the first row of the query result
/// </Summary>
/// <Param name = "SQL"> query statement to be executed </param>
/// <Param name = "parameters"> the parameters required to execute an SQL query statement must follow the order of the parameters in the SQL statement. </param>
/// <Returns> </returns>
Public Object ExecuteScalar (string SQL, SQLiteParameter [] parameters)
{
Using (SQLiteConnection connection = new SQLiteConnection (connectionString ))
{
Using (SQLiteCommand command = new SQLiteCommand (SQL, connection ))
{
If (parameters! = Null)
{
Command. Parameters. AddRange (parameters );
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter (command );
DataTable data = new DataTable ();
Adapter. Fill (data );
Return data;
}
}
}
/// <Summary>
/// Query all data types in the database
/// </Summary>
/// <Returns> </returns>
Public DataTable GetSchema ()
{
Using (SQLiteConnection connection = new SQLiteConnection (connectionString ))
{
Connection. Open ();
DataTable data = connection. GetSchema ("TABLES ");
Connection. Close ();
// Foreach (DataColumn column in data. Columns)
//{
// Console. WriteLine (column. ColumnName );
//}
Return data;
}
}
}
}
Usage of common classes in System. Data. SQLite Databases
The code below demonstrates the usage of the common database classes just compiled:Copy codeThe Code is as follows: using System;
Using System. Collections. Generic;
Using System. Text;
Using System. Data;
Using System. Data. Common;
Using System. Data. SQLite;
Using SQLiteQueryBrowser;
Namespace SQLiteDemo
{
Class Program
{
Static void Main (string [] args)
{
// CreateTable ();
// InsertData ();
ShowData ();
Console. ReadLine ();
}
Public static void CreateTable ()
{
String dbPath = "D :\\ Demo. db3 ";
// If the database file does not exist, create the database file.
If (! System. IO. File. Exists (dbPath ))
{
SQLiteDBHelper. CreateDB ("D: \ Demo. db3 ");
}
SQLiteDBHelper db = new SQLiteDBHelper ("D :\\ Demo. db3 ");
String SQL = "CREATE TABLE Test3 (id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, Name char (3), TypeName varchar (50), addDate datetime, UpdateTime Date, Time time, comments blob )";
Db. ExecuteNonQuery (SQL, null );
}
Public static void InsertData ()
{
String SQL = "INSERT INTO Test3 (Name, TypeName, addDate, UpdateTime, Time, Comments) values (@ Name, @ TypeName, @ addDate, @ UpdateTime, @ Time, @ Comments) ";
SQLiteDBHelper db = new SQLiteDBHelper ("D :\\ Demo. db3 ");
For (char c = 'a'; c <= 'Z'; c ++)
{
For (int I = 0; I <100; I ++)
{
SQLiteParameter [] parameters = new SQLiteParameter [] {
New SQLiteParameter ("@ Name", c + I. ToString ()),
New SQLiteParameter ("@ TypeName", c. ToString ()),
New SQLiteParameter ("@ addDate", DateTime. Now ),
New SQLiteParameter ("@ UpdateTime", DateTime. Now. Date ),
New SQLiteParameter ("@ Time", DateTime. Now. tow.timestring ()),
New SQLiteParameter ("@ Comments", "Just a Test" + I)
};
Db. ExecuteNonQuery (SQL, parameters );
}
}
}
Public static void ShowData ()
{
// Query 20 records from 50
String SQL = "select * from test3 order by id desc limit 50 offset 20 ";
SQLiteDBHelper db = new SQLiteDBHelper ("D :\\ Demo. db3 ");
Using (SQLiteDataReader reader = db. ExecuteReader (SQL, null ))
{
While (reader. Read ())
{
Console. WriteLine ("ID: {0}, TypeName {1}", reader. GetInt64 (0), reader. GetString (1 ));
}
}
}
}
}
In actual situations, using a general class to insert data in large batches will be somewhat slow, because in the System. data. operations in SQLite are treated as a transaction if they are not specified. If you need to write a large number of records at a time, we recommend that you create a transaction explicitly, it is better to complete all operations in this transaction, which is much more efficient than creating a transaction each time.
Using the functions provided by VS2008, we can see the following data:
It must be noted that in System. data. the data type requirements in SQLite are not strict. From the SQL statement used to create the Test3 table, the addDate, UpdateTime, and Time fields in the table are DateTime, Date, and Time fields, respectively, but in fact we didn't follow this rule when inserting, and the final result is to follow the database Field definition as much as possible.
Summary
System. data. SQLite is indeed a very small and sophisticated database, as an encapsulation of SQLite (SQLite can be accessed by Java on Android and other types of mobile phones), it is still small, high performance, low memory consumption, and only one dll is required for installation (two files are required on the Mobile phone ), the only drawback is that there is no comparative GUI (graphical user interface), but this makes it small.
In actual development, there is no graphical user interface, which may be inconvenient. We can use VS to view and operate data. I also made a small task to facilitate data management and maintenance. The interface is as follows:
If you want to develop applications with less than 0.1 million Data records, I suggest you try System. Data. SQLite. It may be a good choice.
