西夏普的部落格

1.前言
SQLite是一個輕巧的資料庫，透過使用Finisar.SQLite可對資料庫進行操作。
2.說明

取得做操作SQLite的DLL檔，這裡是採用Finisar.SQLite的專案，下載路徑為
http://adodotnetsqlite.sourceforge.net/

將解壓縮的檔案放在自己專案的bin目錄下，此DLL檔只支援32bits，組態管理員請選擇x86

加入參考
SQLite.NET.dll

加入命名空間

using Finisar.SQLite;

資料庫連線程式

public SQLiteConnection OpenConn(string Database)
{
	string cnstr = string.Format("Data Source=" + Database + ";Version=3;New=False;Compress=True;");
	SQLiteConnection icn = new SQLiteConnection();
	icn.ConnectionString = cnstr;
	if (icn.State == ConnectionState.Open) icn.Close();
	icn.Open();
	return icn;
}

建立資料庫程式

public void CreateSQLiteDatabase(string Database)
{
	string cnstr = string.Format("Data Source=" + Database + ";Version=3;New=True;Compress=True;");
	SQLiteConnection icn = new SQLiteConnection();
	icn.ConnectionString = cnstr;
	icn.Open();
	icn.Close();
}

建立資料表程式

public void CreateSQLiteTable(string Database, string CreateTableString)
{
	SQLiteConnection icn = OpenConn(Database);
	SQLiteCommand cmd = new SQLiteCommand(CreateTableString, icn);
	SQLiteTransaction mySqlTransaction = icn.BeginTransaction();
	try
	{
		cmd.Transaction = mySqlTransaction;
		cmd.ExecuteNonQuery();
		mySqlTransaction.Commit();
	}
	catch (Exception ex)
	{
		mySqlTransaction.Rollback();
		throw (ex);
	}
	if (icn.State == ConnectionState.Open) icn.Close();
}

新增資料程式

public void SQLiteInsertUpdateDelete(string Database, string SqlSelectString)
{
	SQLiteConnection icn = OpenConn(Database);
	SQLiteCommand cmd = new SQLiteCommand(SqlSelectString, icn);
	SQLiteTransaction mySqlTransaction = icn.BeginTransaction();
	try
	{
		cmd.Transaction = mySqlTransaction;
		cmd.ExecuteNonQuery();
		mySqlTransaction.Commit();
	}
	catch (Exception ex)
	{
		mySqlTransaction.Rollback();
		throw (ex);
	}
	if (icn.State == ConnectionState.Open) icn.Close();
}

讀取資料程式

public DataTable GetDataTable(string Database, string SQLiteString)
{
	DataTable myDataTable = new DataTable();
	SQLiteConnection icn = OpenConn(Database);
	SQLiteDataAdapter da = new SQLiteDataAdapter(SQLiteString, icn);
	DataSet ds = new DataSet();
	ds.Clear();
	da.Fill(ds);
	myDataTable = ds.Tables[0];
	if (icn.State == ConnectionState.Open) icn.Close();
	return myDataTable;
}

3.應用

//產生SQLite的資料庫文件，副檔名為.db
CreateSQLiteDatabase("data.db");

//建立資料表test
string createtablestring = "create table test (speed double, dist double);";
CreateSQLiteTable("data.db", createtablestring);

//插入資料到test表中
string insertstring = "insert into test (speed,dist) values ('10','100');insert into test (speed,dist) values ('20','200');";
SQLiteInsertUpdateDelete("data.db", insertstring);

//讀取資料
DataTable dt = GetDataTable("data.db", "select * from test");
dataGridView1.DataSource = dt;