SQLite Introduction
Before introducing System.Data.SQLite, you need to introduce Sqlite,sqlite is a stand-alone database management system similar to access, which keeps the definition of all databases (including definitions, tables, indexes, and data itself) in a single file. And, SQLite is a C implementation of the class library, it in memory consumption, file volume, simplicity has a good performance, if the data below 10W, query speed is quite fast.
SQLite has the following characteristics:
Implements most SQL92 standards, including transactions (atomicity, consistency, isolation, and persistence), triggers, and most complex queries.
Do not type-check the inserted or updated data, you can insert the string into the whole series (this may not be appropriate for some users).
Supports mainstream systems such as Windows/linux/unix, and also supports embedded systems such as Android or Windows Mobile.
System.Data.SQLite
System.Data.SQLite is a sqlite version that can be used without the. NET framework support, because it contains a Ado.net 2.0 engine internally, so. NET developers can use System.Data.SQLite to easily develop. NET programs.
System.Data.SQLite and SQLite also have some limitations, such as not supporting row-level and table-level locks, when a connection locks a database for writing data, other database connections can only wait for that connection operation to read and write, and sqlite.net try multiple times during the timeout period.
In fact, for large applications we will choose some large professional database, System.Data.SQLite and SQLite suitable for some limited occasions, such as mobile phones. Here I tell a real story, I once did a small system, to analyze three Excel files, two of which records about 400, and the other is about 10,000, for such a system if you use the database, even if the stand-alone version of Access, After importing, it is relatively easy to analyze with the characteristics of the database, because we can use the connection query in the database, and we can use the database function on the record, but the information provided by the other party is that the machine is installed with Office, but only word, Excel and Outlook, without access, do not want to install other software, Since I am not sure if I have access to the. mdb file through OLE DB on a machine that does not have access installed, there is no way to have the form of a memory table, read the data in Excel to the DataTable, and then analyze three of the DataTable, but the efficiency is still not too Ideal. For this situation, If I had known System.Data.SQLite, it would have been much easier to import data from three Excel into System.Data.SQLite, and then take advantage of the functions provided by System.Data.SQLite. For System.Data.SQLite, deployment does not need to install, only need one System.Data.SQLite.dll is enough, this DLL only 866k! And it does not need to be registered as you would with COM components.
Manipulating System.Data.SQLite in VS2008
For the convenience of developers, System.Data.SQLite provides support for VS2005 and VS2008, and even supports the SP1 Framework in the. NET 3.5 entity. The following is a case of using the System.Data.SQLite designer in VS2008:
First, open the Server Explorer in VS2008, as shown in the following figure:
Then click the right mouse button on the data connection, as shown in the following image:
Then select Add Connection, as shown in the following illustration:
At this time select System.Data.SQLite use of the database file, file suffix default is. DB3, you can also click the "Test Connection" button below, if there is no problem will pop up the correct dialog box, click the "OK" button in the Server Explorer will appear as follows:
This allows us to manipulate the tables in System.Data.SQLite as we do with libraries in SQL Server.
System.Data.SQLite Database Universal class
For the operation of the database, divided into the following situations:
Create a database file;
Returns a DataTable;
Return to DataReader;
Perform additions and deletions to return the number of rows affected;
Executes a query, returning the first column of the first row (usually for queries with row functions, such as sum/avg/count);
Returns all tables in the library;
Because there is no stored procedure in the System.Data.SQLite, all operations are text-based SQL statements and, in order to avoid SQL injection, a parameterized SQL statement is used, which is the common class for the database as follows:
Copy Code code as follows:
Using System;
Using System.Data;
Using System.Data.Common;
Using System.Data.SQLite;
Namespace Sqlitequerybrowser
{
<summary>
Description: This is a generic class for System.Data.SQLite database General operations encapsulation.
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>
Constructors
</summary>
<param name= "dbpath" >sqlite database file path </param>
Public Sqlitedbhelper (String dbpath)
{
this.connectionstring = "Data source=" + dbpath;
}
<summary>
Creating SQLite database files
</summary>
<param name= "DBPath" > SQLite database file path to create </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>
Performs additions and deletions to the SQLite database, returning the number of rows affected.
</summary>
<param name= "SQL" > the SQL statement to be executed </param>
<param name= "Parameters" > Perform additions and deletions to the parameters required by the statement, the parameters must be in the order in which they are in the SQL statement </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>
Executes a query statement that returns an associated Sqlitedatareader instance
</summary>
<param name= "SQL" > Query statements to execute </param>
<param name= "Parameters" > The parameters required to execute the SQL query statement must be in the order in which they are 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>
Executes a query statement that returns a DataTable containing the results of the query
</summary>
<param name= "SQL" > Query statements to execute </param>
<param name= "Parameters" > The parameters required to execute the SQL query statement must be in the order in which they are 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>
Executes a query statement that returns the first row of the query results first column
</summary>
<param name= "SQL" > Query statements to execute </param>
<param name= "Parameters" > The parameters required to execute the SQL query statement must be in the order in which they are 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>
Querying all data type information in a 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;
}
}
}
}
System.Data.SQLite the use of common classes in a database
The following is a demonstration of the usage of the common class of the database just written, as follows:
Copy Code code 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 is not present, the database file is created
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 (), AD Ddate 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 < 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.ToShortTimeString ()),
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 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 practice, it is slow to insert data in large quantities in general class, This is because operations in System.Data.SQLite are treated as a thing if you do not specify an action, and if you need to write a large number of records at once, it is recommended that you explicitly create an object that completes all the operations in this transaction, which is much more efficient than creating one thing per operation.
Finally, using the functionality provided by VS2008, you can see the following data:
What needs to be explained is that the data type specification in System.Data.SQLite is not very strict, and from the SQL statement that creates the TEST3 table, the table adddate, UpdateTime, Time are datetime, Date, Time Type fields, But in fact we didn't follow this rule when we inserted it, and the final result is to follow the definition of database field as much as possible.
Summary
System.Data.SQLite is really a very compact database, as a package for SQLite (SQLite can use Java access on types of Android phones), it is still small, high performance, low memory consumption, The advantage of running without having to install just one DLL (if you need two files on a mobile phone) is that there is no GUI for comparison (a graphical user interface), but that is why it is small in size.
In the actual development of no graphical user interface may be inconvenient, we can use vs to view and manipulate the data, I also made a small, easy to manage and maintain data, the interface is as follows:
If you want to develop a data volume below 100,000 application, I suggest you try to use System.Data.SQLite, it may be a good choice.
