C # SQLite for local data storage

 

Even for network applications, local data storage needs to be considered in case of disconnection. Before SQLite appeared, when the data volume was large, we always used ACCESS. If the data volume was small, the file was stored. ACCESS does not support the atomicity of transactions. In the case of power failure (which will always happen), data is hard to be restored.

 

I. Installation

SQLITE is a lightweight database and an ACID-compliant associated database management system. I am using http://sqlite.phxsoftware.com directly/(An open source ADO. NET provider for the SQLite database engine ). The downloaded file is an EXE. The root directory after installation is as follows:

There is a test tool in Bin to view the performance indicators of running SQLITE locally.

 

Ii. Create a database

After the installation is complete, open visual studio and create a data connection. You can see that the data source has an SQLite parameter.

Create a connection, as shown in. The SQLITE database is saved as a file.

 

Iii. Database Maintenance

You can maintain SQLITE data in VS, for example:

You can use functions similar to SQL query analyzer in VS, for example:

 

 

4. hybrid mode

After the installation is complete, you can directly reference the project set.

System. Data. SQLite

System. Data. SQLite. Linq

Two assemblies, because the assemblies are loaded in runtime 4.0. Therefore, you must configure the following parameters in App. config.

 <?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0"/>
</startup>
</configuration>

 

V. sqlitehelper

Finally, a self-written SQLiteHelper is provided:

Code

 Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;
Using System. Data. SQLite;
Using System. Data;
Using System. Data. Common;

Namespace Com. Luminji. DataService. SQLHelpers
{
Public class SQLiteHelper
{
/// <Summary>
/// ConnectionString example: Datasource = Test. db3; Pooling = true; FailIfMissing = false
/// </Summary>
Public static string ConnectionString {get; set ;}

Private static void PrepareCommand (SQLiteCommand cmd, SQLiteConnection conn, string plain text, params object [] p)
{
If (conn. State! = ConnectionState. Open)
Conn. Open ();
Cmd. Parameters. Clear ();
Cmd. Connection = conn;
Cmd. CommandText = plain text;
Cmd. CommandType = CommandType. Text;
Cmd. CommandTimeout = 30;
If (p! = Null)
{
Foreach (object parm in p)
Cmd. Parameters. AddWithValue (string. Empty, parm );
}
}

Public static DataSet ExecuteQuery (string plain text, params object [] p)
{
Using (SQLiteConnection conn = new SQLiteConnection (ConnectionString ))
{
Using (SQLiteCommand command = new SQLiteCommand ())
{
DataSet ds = new DataSet ();
PrepareCommand (command, conn, plain text, p );
SQLiteDataAdapter da = new SQLiteDataAdapter (command );
Da. Fill (ds );
Return ds;
}
}
}

Public static int ExecuteNonQuery (string plain text, params object [] p)
{
Using (SQLiteConnection conn = new SQLiteConnection (ConnectionString ))
{
Using (SQLiteCommand command = new SQLiteCommand ())
{
PrepareCommand (command, conn, plain text, p );
Return command. ExecuteNonQuery ();
}
}
}

Public static SQLiteDataReader ExecuteReader (string plain text, params object [] p)
{
Using (SQLiteConnection conn = new SQLiteConnection (ConnectionString ))
{
Using (SQLiteCommand command = new SQLiteCommand ())
{
PrepareCommand (command, conn, plain text, p );
Return command. ExecuteReader (CommandBehavior. CloseConnection );
}
}
}

Public static object ExecuteScalar (string plain text, params object [] p)
{
Using (sqliteconnection conn = new sqliteconnection (connectionstring ))
{
Using (sqlitecommand command = new sqlitecommand ())
{
Preparecommand (command, Conn, plain text, P );
Return command. executescalar ();
}
}
}

}
}

 

Vi. NotesSQLite Official Website: http://www.sqlite. org/at first glance saw the characteristics of SQLite. 1. ACID transaction 2. zero Configuration-no installation or configuration management required. 3. A complete database stored in a single disk file 4. database files can be freely shared among machines with different bytes. the database size is 2 TB 6. small enough, roughly 30 thousand lines of C code, 250 K 7. it is faster than some popular databases in most common databases. simple and Easy API 9. including TCL binding and supporting binding in other languages through Wrapper 10. source code with good comments, with a test coverage rate of more than 90%. 11. independent: no additional dependencies 12. source completely Open, you can use it for any purpose, including sell it 13. supports multiple development languages, including C, PHP, Perl, Java, and ASP. NET, Python

Source: http://www.cnblogs.com/luminji/archive/2010/12/19/1910396.html