C # -- SQLite Quick Start

Why do I needSQLite

* Lightweight, flexible, and based on a single database file, it facilitates creation and movement, and facilitates classified data storage (by business, by function, by date, by customer, etc ), it is very suitable for use with flexible systems.

* Easy deployment: Only one dll file is required for the. NET environment (MDAC may need to be installed for Access ).

* Open Source.

 

SQLitePrepare the development environment and development materials

. Net SQLite database DRIVER: System. Data. SQLite. dll and documentation

SQLite GUI database tool: SQLite Expert PersonalEdition (free for personal use)

. Net SQLite database driver source: sqlite-netFx-source-1.0.76.0

 

C # dynamic creationSQLiteDatabase

Call the SQLiteConnection. CreateFile static method to create

SQLiteConnection. CreateFile (path)

 

Path test path: Data directory exists, Data2 directory does not exist

Var path1 = "dummy1.db ";//Successful

Var path2 = "Data/dummy2.db ";//Successful

Var path3 = "Data \ dummy3.db ";//Successful

Var path4 = "c: \ Data \ dummy4.db ";//Successful

Var path5 = "Data2 // dummy5.db ";//Error. The Data2 directory does not exist.

Var path6 = "C: \ Data2 \ dummy6.db ";//Error. The Data2 directory does not exist.

 

SQLiteConnection. CreateFile internal implementation source code: use File. Create to Create a 0-Byte File

/// <Summary>

/// Creates a database file. This just creates a zero-byte file whichSQLite

/// Will turn into a database when the file is openedproperly.

/// </Summary>

/// <Paramname = "databaseFileName"> Thefile to create </param>

Static public void CreateFile (string databaseFileName)

{

FileStream fs = File. Create (databaseFileName );

Fs. Close ();

}

 

C # connectionSQLiteAnd database security

Common connection string generation

/// <Summary>

/// Generate the SQLite connection string

/// </Summary>

Public static class SQLiteConnectionString

{

Public static string GetConnectionString (stringpath)

{

Return GetConnectionString (path, null );

}

Public static string GetConnectionString (stringpath, string password)

{

If (string. IsNullOrEmpty (password ))

Return "Data Source =" + path;

Return "Data Source =" + path + "; Password =" + password;

}

}

Change Database Password

/// <Summary>

/// Change the password

/// </Summary>

/// <Paramname = "path"> </param>

/// <Param name = "newPassword"> </param>

/// <Param name = "oldPassword"> empty when no password is available </param>

Public static boolChangePassword (stringpath, string newPassword, string oldPassword = null)

{

Try

{

Varcon = new SQLiteConnection (SQLiteConnectionString. GetConnectionString (path, oldPassword ));

Con. Open ();

Con. ChangePassword (newPassword );

Con. Close ();

}

Catch (Exception ex)

{

Return false;

}

Return true;

}

 

Password Change test:

Var path2 = "Data/dummy2.db ";//The initial password of the database is null.

ChangePassword (path2, "123 ");//Succeeded. Changed the Database Password to 123.

ChangePassword (path2, "234 ","");//Error. The current password is 123

ChangePassword (path2, "234", "123 ");//Succeeded. Changed the Database Password to 123.

ChangePassword (path2, "", "234 ");//Succeeded. The database password is changed to null.

 

C # common types andSQLiteType ing

C # type	SQLiteType
Int	INT, INTEGER
String	CHAR, VARCHAR, VARCHAR2, and other character types
Long	INT64
Bool	BOOLEAN
Double	DOUBLE
DateTime	DATETIME
Decmial	DECIMAL
Short	SMALLINT
Float	FLOAT

* The preceding type ing is not unique. write and read tests have been performed.

 

C # pairSQLiteInsert database records and return auto-incrementing IDS

Set auto-incrementing column for SQLite: Create an INTEGER data type column, set this column as the primary key, and then set the auto-incrementing attribute.

After the inserted SQL statement is executed, the last_insert_rowid () function is called to return an auto-incrementing ID.

Object result = null;

IDbConnection conn = null;

IDbCommand cmd = null;

//...

Inti = cmd. ExecuteNonQuery ();

Cmd. Parameters. Clear ();

If (I> 0)

{

Cmd. CommandText = "SELECT last_insert_rowid ()";

Result = cmd. ExecuteScalar ();

}

Else

{

Result =-1;

}

//...

 

C #SQLiteTransaction Control

The transaction has been tested in the TransactionScope pipeline. An internal exception is thrown and the transaction can be terminated.

Try

{

Using (System. Transactions. TransactionScope scope = new System. Transactions. TransactionScope ())

{

//Exception

Scope. Complete ();

}

}

Catch (Exception)

{

 

}

From: hunkcai's column