SqlLiteHelper. cs class for sqlite Database

 

Using System;

Using System. Collections. Generic;

Using System. Linq;

Using System. Text;

Www.2cto.com

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;

}

 

// Retrieve dataset

Public static DataSet GetDataSet (out string sError, string sSQL)

{

DataSet ds = 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 );

Ds = new DataSet ();

Dao. Fill (ds );

}

Catch (Exception ex)

{

SError = ex. Message;

}

 

Return ds;

}

 

// 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) as 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;

}

 

}

}

 

 

 

 

For details about how to call SqlLiteHelper, refer to my blog:

 

C # Use sqlite lightweight Database

 

Web: http://www.bkjia.com/kf/201111/111179.html