C # Use sqlite lightweight Database

 

1. Preparations

 

Sqlite3.exe: http://www.sqlite.org/download.html downloading "sqlite-shell-win32-x86-

 

3070800. zip "is OK For Precompiled Binaries For Windows

 

Sqlite-shell-win32-x86-3070800.zip

(248.28 KiB)

 

 

 

 

System. data. sqlite. dll: http://www.dllzj.com/Down_System.Data.SQLite.DLL.html this dll

 

Used for reference in Visual Studio Projects

 

 

 

Sqlite3.exe

 

 

 

Decompress sqlite-shell-win32-x86-3070800.zip to the F: \ jonse \ DownLoads \ sqlLite directory

 

Start --> Run --> cmd

 

> F:

 

> Cd F: \ jonse \ DownLoads \ sqlLite

 

> Sqlite3 myDB. db (if myDB. db does not exist, it is created; If yes, it is enabled)

 

> Create table test (id int, name varchar (20), remark varchar (200); (create a test table with three columns:

 

Id, name, remark)

 

> Insert into test select 1, 'name1', 'remark1 'Union select 2, 'name2', 'remark2'; (two rows of data are inserted temporarily)

 

>. Mode column (show column mode)

 

>. Headers on (show column header information)

 

> Select * from test; (query all data)

 

> Select ifnull (max (id), 0) as MaxID from test; (query the maximum ID value of the test table)

 

 

 

 

 

III. C # Use sqlite

 

 

 

Add System. Data. SQLite. dll to the reference of VS2010 Project (this has been downloaded in preparation)

 

 

 

(1) Create a New SqlLiteHelper. cs class

 

 

 

Using System;

Using System. Collections. Generic;

Using System. Linq;

Using System. Text;

 

Using System. Data;

Using System. Data. SQLite;

Using System. Data. Common;

 

Namespace JonseTest

{

Public abstract class SqlLiteHelper

{

Public static string ConnSqlLiteDbPath = string. Empty;

Public static string ConnString

{

Get

{

Return string. Format (@ "Data Source = {0}", ConnSqlLiteDbPath );

}

}

 

// Retrieve the datatable

Public static DataTable GetDataTable (out string sError, string sSQL)

{

DataTable dt = null;

SError = string. Empty;

 

Try

{

SQLiteConnection conn = new SQLiteConnection (ConnString );

Conn. Open ();

SQLiteCommand cmd = new SQLiteCommand ();

Cmd. CommandText = sSQL;

Cmd. Connection = conn;

SQLiteDataAdapter dao = new SQLiteDataAdapter (cmd );

Dt = new DataTable ();

Dao. Fill (dt );

}

Catch (Exception ex)

{

SError = ex. Message;

}

 

Return dt;

}

 

// Obtain a single element

Public static object GetSingle (out string sError, string sSQL)

{

DataTable dt = GetDataTable (out sError, sSQL );

If (dt! = Null & dt. Rows. Count> 0)

{

Return dt. Rows [0] [0];

}

 

Return null;

}

 

// Obtain the largest ID

Public static Int32 GetMaxID (out string sError, string sKeyField, string sTableName)

{

DataTable dt = GetDataTable (out sError, "select ifnull (max ([" + sKeyField + "]), 0)

 

MaxID from ["+ sTableName +"] ");

If (dt! = Null & dt. Rows. Count> 0)

{

Return Convert. ToInt32 (dt. Rows [0] [0]. ToString ());

}

 

Return 0;

}

 

// Execute insert, update, delete actions, or use transactions

Public static bool UpdateData (out string sError, string sSQL, bool bUseTransaction = false)

{

Int iResult = 0;

SError = string. Empty;

 

If (! BUseTransaction)

{

Try

{

SQLiteConnection conn = new SQLiteConnection (ConnString );

Conn. Open ();

SQLiteCommand comm = new SQLiteCommand (conn );

Comm. CommandText = sSQL;

IResult = comm. ExecuteNonQuery ();

}

Catch (Exception ex)

{

SError = ex. Message;

IResult =-1;

}

}

Else // use transactions

{

DbTransaction trans = null;

Try

{

SQLiteConnection conn = new SQLiteConnection (ConnString );

Conn. Open ();

Trans = conn. BeginTransaction ();

SQLiteCommand comm = new SQLiteCommand (conn );

Comm. CommandText = sSQL;

IResult = comm. ExecuteNonQuery ();

Trans. Commit ();

}

Catch (Exception ex)

{

SError = ex. Message;

IResult =-1;

Trans. Rollback ();

}

}

 

Return iResult> 0;

}

 

}

}

 

 

 

(2) create a new form for frmSqlLite

 

 

 

Public partial class frmSqlLite: Form

{

String sError = string. Empty;

Public frmSqlLite ()

{

InitializeComponent ();

}

 

Private void InitGrid ()

{

SqlLiteHelper. ConnSqlLiteDbPath = @ "F: \ jonse \ DownLoads \ sqlLite \ myDB. db ";

 

SError = string. Empty;

String sSql = "select * from test ";

DataTable dt = SqlLiteHelper. GetDataTable (out sError, sSql );

If (! String. IsNullOrEmpty (sError ))

Common. DisplayMsg (this. Text, sError );

 

DataGridView1.DataSource = dt;

}

 

Private void frmSqlLite_Load (object sender, EventArgs e)

{

InitGrid ();

}

 

Private void button#click (object sender, EventArgs e)

{

SError = string. Empty;

Int iMaxID = SqlLiteHelper. GetMaxID (out sError, "id", "test") + 1;

String sSql = "insert into test select" + iMaxID + ", 'name" + iMaxID + "', 'remark" +

 

IMaxID + "'";

SError = string. Empty;

Bool bResult = SqlLiteHelper. UpdateData (out sError, sSql, true );

If (bResult)

Common. DisplayMsg (this. Text, "inserted successfully ");

 

InitGrid ();

}

 

Private void button2_Click (object sender, EventArgs e)

{

SError = string. Empty;

Int iMaxID = SqlLiteHelper. GetMaxID (out sError, "id", "test ");

String sSql = "update test set name = 'name _ jonse ', remark = 'remark _ jonse' where id =" +

 

IMaxID;

SError = string. Empty;

Bool bResult = SqlLiteHelper. UpdateData (out sError, sSql, true );

If (bResult)

Common. DisplayMsg (this. Text, "modified successfully ");

 

InitGrid ();

}

 

Private void button3_Click (object sender, EventArgs e)

{

SError = string. Empty;

Int iMaxID = SqlLiteHelper. GetMaxID (out sError, "id", "test ");

String sSql = "delete from test where id =" + iMaxID;

SError = string. Empty;

Bool bResult = SqlLiteHelper. UpdateData (out sError, sSql, true );

If (bResult)

Common. DisplayMsg (this. Text, "deleted successfully ");

 

InitGrid ();

}

}

 

 

 

(3). Public class

 

 

 

Public abstract class Common

{

Public static void DisplayMsg (string sCaption, string sMsg)

{

SMsg = sMsg. TrimEnd ('! '). TrimEnd ('! ') + "! ";

MessageBox. Show (sMsg, sCaption );

}

 

Public static bool IsNullOrEmptyObject (object oSource)

{

If (oSource! = Null)

{

Return string. IsNullOrEmpty (oSource. ToString ());

}

 

Return true;

}

 

}

 

From the column keenweiwei