Using SQLite ADO. Net in. Net to use SQLite Database

 

1. About SQLite ADO. net

System. Data. SQLite

Is an enhanced version of the original SQLite. it will be a complete replacement of the original sqlite3.dll (you can even rename it as sqlite3.dll ). it does not need to be linked.. net independent release, but it is embedded with a complete ADO. NET 2.0 engine, providing complete support for development.

The following is a brief introduction to its features:

Complete ADO. NET 2.0 implementation

The entire project is based entirely on vs2005 and ADO. NET 2.0 is newly built and uses all ADO. net Framework. including full support for dbproviderfactory, Automatic Distributed Transaction calling, and extensive mode support. In addition, all classes are from ADO.. NET 2.0 base class inherited.

Supports the complete and streamlined. NET Framework and C/C ++

This library is 100% compatible with the original sqlite3.dll and can be developed using an unmanaged C/C ++ when it does not need to be linked to the. NET runtime.

Portable database files

Unencrypted SQLite database files can be used freely across platforms and processors, including non-Windows platforms. Encrypted databases can be used on all Windows platforms.

Reliable speed, faster than most other embedded databases, including SQL Server mobile

The space occupied by SQLite installation is negligible compared with SQL mobile. It consumes less memory during running and generates a smaller database.

Database Encryption

The entire database file can be encrypted. Binary and plaintext passwords are supported.

Supports Visual Studio 2005 Design

You can add an SQLite connection to Server Explorer, use the query designer to create a query statement, and drag a table to a dataset! SQLite developers can work in a variety of Visual Studio 2005, including the experience version.

The size of a single file re-deployment package is less than KB

The SQLite and ADO. net packages are bundled and compiled together. Pre-compiled binary files provide x86, IA64, x64, and arm versions.

Extensive SQL language support

SQLite supports most sql92 standards (see below). Supports named and unnamed parameters to pass in the SQLite kernel through an optimized pipeline in UTF-8 and UTF-16 encoding.

User-Defined Functions and sorting

Fully supports user-defined functions and sorting methods, which means you can use your favorite. NET language to implement features not provided by SQLite, which will be very simple.

All source code. 100% is provided for free.

The source code of all the encapsulated libraries is public. No protocol constraints are imposed on individual or commercial applications.

Home: http://sqlite.phxsoftware.com/

(Latest Version 1.0.66.0): http://sourceforge.net/projects/sqlite-dotnet2/files/SQLite%20for%20ADO.NET%202.0/1.0.66.0/SQLite-1.0.66.0-setup.exe/download

2. Use SQLite in C #

1. Use Add references to reference system. Data. SQLite. dll in the bin directory of the SQLite ADO. net installation directory.

2. Create a database file: because it is always a 0-byte file, I/O can also be used (?!).

System. Data. SQLite. sqliteconnection. createfile (datasource );

3. Connect to the database

System. Data. SQLite. sqliteconnection conn = new system. Data. SQLite. sqliteconnection (connectionstring );

Connectionstring contains some database configuration information, such as database files and password opened by the database. You can use system. Data. SQLite. sqliteconnectionstringbuilder to create connectionstring.

4. Creating tables and reading data are not much different from access or ms SQL.

// Create a database file

String datasource = "H:/test. DB ";

System. Data. SQLite. sqliteconnection. createfile (datasource );

// Connect to the database

System. Data. SQLite. sqliteconnection conn = new system. Data. SQLite. sqliteconnection ();

System. Data. SQLite. sqliteconnectionstringbuilder connstr = new system. Data. SQLite. sqliteconnectionstringbuilder ();

Connstr. datasource = datasource;

Connstr. Password = "admin"; // set the password. SQLite ADO. Net implements database password protection.

Conn. connectionstring = connstr. tostring ();

Conn. open ();

// Create a table

System. Data. SQLite. sqlitecommand cmd = new system. Data. SQLite. sqlitecommand ();

String SQL = "CREATE TABLE test (username varchar (20), password varchar (20 ))";

Cmd. commandtext = SQL;

Cmd. Connection = conn;

Cmd. executenonquery ();

// Insert data

SQL = "insert into test values ('ekinglong', 'mypassword ')";

Cmd. commandtext = SQL;

Cmd. executenonquery ();

// Retrieve data

SQL = "select * from test ";

Cmd. commandtext = SQL;

System. Data. SQLite. sqlitedatareader reader = cmd. executereader ();

Stringbuilder sb = new stringbuilder ();

While (reader. Read ())

{

SB. append ("username:"). append (reader. getstring (0). append ("\ n ")

. Append ("Password:"). append (reader. getstring (1 ));

}

MessageBox. Show (sb. tostring ());