第C#操作SQLite實現數據的增刪改查目錄簡介主要代碼SQLiteHelper.csUsingLock.csForm1.cs

簡介

SQLite是一個輕量級、跨平臺的關系型數據庫，在小型項目中，方便，易用，同時支持多種開發語言，下面是我用C#語言對SQLite的一個封裝。

Winfrom界面如下：

代碼還需要改進部分：

下面的代碼我不覺得是完美的，讀者可以自己去實現這些功能：

1.如果不想用多線程可以去掉UsingLock.cs，在SQLiteHelper.cs中刪除對應的引用即可。

2.創建數據庫文件，可以從代碼中分離成單獨的方法。比如創建有密碼的數據庫文件。

3.在執行SQL語句時，有些語句執行成功，也不會有影響行數。比如，創建表，刪除表，此時執行SQL語句返回的影響行數就為0。

4.只要SQL語句不報錯就是執行成功的，在方法的返回值可以改為多個，比如用

Tuplebool,string,int第一個參數bool代表執行結果，第二個參數string代表錯誤信息，第三個參數int代表影響的行數。

同樣，也可以這樣：

Tuplebool,string,DataSet第一個參數bool代表執行結果，第二個參數string代表錯誤信息，第三個參數DataSet代表返回的表單數據。

主要代碼

SQLiteHelper.cs

usingSystem;

usingSystem.Collections.Generic;

usingSystem.Data;

usingSystem.Data.Common;

usingSystem.Data.SQLite;

usingSystem.IO;

usingSystem.Text;

namespaceMySQLiteHelper

publicclassSQLiteHelper

#region字段

///summary

///事務的基類

////summary

privateDbTransactionDBtrans;

///summary

///使用靜態變量字典解決多線程實例本類，實現一個數據庫對應一個clslock

////summary

privatestaticreadonlyDictionarystring,ClsLockRWL=newDictionarystring,ClsLock

///summary

///數據庫地址

////summary

privatereadonlystringmdataFile;

///summary

///數據庫密碼

////summary

privatereadonlystringmPassWord;

privatereadonlystringLockName=null;

///summary

///數據庫連接定義

////summary

privateSQLiteConnectionmConn;

#endregion

#region構造函數

///summary

///根據數據庫地址初始化

////summary

///paramname="dataFile"數據庫地址/param

publicSQLiteHelper(stringdataFile)

this.mdataFile=dataFilethrownewArgumentNullException("dataFile=null");

//this.mdataFile=AppDomain.CurrentDomain.BaseDirectory+dataFile;

this.mdataFile=dataFile;

if(!RWL.ContainsKey(dataFile))

LockName=dataFile;

RWL.Add(dataFile,newClsLock());

///summary

///使用密碼打開數據庫

////summary

///paramname="dataFile"數據庫地址/param

///paramname="PassWord"數據庫密碼/param

publicSQLiteHelper(stringdataFile,stringPassWord)

this.mdataFile=dataFilethrownewArgumentNullException("dataFileisnull");

this.mPassWord=PassWordthrownewArgumentNullException("PassWordisnull");

//this.mdataFile=AppDomain.CurrentDomain.BaseDirectory+dataFile;

this.mdataFile=dataFile;

if(!RWL.ContainsKey(dataFile))

LockName=dataFile;

RWL.Add(dataFile,newClsLock());

#endregion

#region打開/關閉數據庫

///summary

///打開SQLiteManager使用的數據庫連接

////summary

publicvoidOpen()

if(string.IsNullOrWhiteSpace(mPassWord))

mConn=OpenConnection(this.mdataFile);

else

mConn=OpenConnection(this.mdataFile,mPassWord);

Console.WriteLine("打開數據庫成功");

///summary

///關閉連接

////summary

publicvoidClose()

if(this.mConn!=null)

this.mConn.Close();

if(RWL.ContainsKey(LockName))

RWL.Remove(LockName);

catch

Console.WriteLine("關閉失敗");

Console.WriteLine("關閉數據庫成功");

#endregion

#region事務

///summary

///開始事務

////summary

publicvoidBeginTrain()

EnsureConnection();

DBtrans=mConn.BeginTransaction();

///summary

///提交事務

////summary

publicvoidDBCommit()

DBtrans.Commit();

catch(Exception)

DBtrans.Rollback();

#endregion

#region工具

///summary

///打開一個SQLite數據庫文件，如果文件不存在，則創建（無密碼）

////summary

///paramname="dataFile"/param

///returnsSQLiteConnection類/returns

privateSQLiteConnectionOpenConnection(stringdataFile)

if(dataFile==null)

thrownewArgumentNullException("dataFiledataFile=null");

if(!File.Exists(dataFile))

SQLiteConnection.CreateFile(dataFile);

SQLiteConnectionconn=newSQLiteConnection();

SQLiteConnectionStringBuilderconStr=newSQLiteConnectionStringBuilder

DataSource=dataFile

conn.ConnectionString=conStr.ToString();

conn.Open();

returnconn;

///summary

///打開一個SQLite數據庫文件，如果文件不存在，則創建（有密碼）

////summary

///paramname="dataFile"/param

///paramname="Password"/param

///returnsSQLiteConnection類/returns

privateSQLiteConnectionOpenConnection(stringdataFile,stringPassword)

if(dataFile==null)

thrownewArgumentNullException("dataFile=null");

if(!File.Exists(Convert.ToString(dataFile)))

SQLiteConnection.CreateFile(dataFile);

SQLiteConnectionconn=newSQLiteConnection();

SQLiteConnectionStringBuilderconStr=newSQLiteConnectionStringBuilder

DataSource=dataFile,

Password=Password

conn.ConnectionString=conStr.ToString();

conn.Open();

returnconn;

catch(Exception)

returnnull;

///summary

///讀取或設置SQLiteManager使用的數據庫連接

////summary

publicSQLiteConnectionConnection

returnmConn;

privateset

mConn=valuethrownewArgumentNullException();

///summary

///確保數據庫是連接狀態

////summary

///exceptioncref="Exception"/exception

protectedvoidEnsureConnection()

if(this.mConn==null)

thrownewException("SQLiteManager.Connection=null");

if(mConn.State!=ConnectionState.Open)

mConn.Open();

///summary

///獲取數據庫文件的路徑

////summary

///returns/returns

publicstringGetDataFile()

returnthis.mdataFile;

///summary

///判斷表table是否存在

////summary

///paramname="table"/param

///returns存在返回true，否則返回false/returns

publicboolTableExists(stringtable)

if(table==null)

thrownewArgumentNullException("table=null");

EnsureConnection();

SQLiteDataReaderreader=ExecuteReader("SELECTcount(*)ascFROMsqlite_masterWHEREtype='table'ANDname=@tableName",newSQLiteParameter[]{newSQLiteParameter("tableName",table)});

if(reader==null)

returnfalse;

reader.Read();

intc=reader.GetInt32(0);

reader.Close();

reader.Dispose();

//returnfalse;

returnc==1;

///summary

///VACUUM命令（通過復制主數據庫中的內容到一個臨時數據庫文件，然后清空主數據庫，并從副本中重新載入原始的數據庫文件）

////summary

///returns/returns

publicboolVacuum()

using(SQLiteCommandCommand=newSQLiteCommand("VACUUM",Connection))

Command.ExecuteNonQuery();

returntrue;

catch(System.Data.SQLite.SQLiteException)

returnfalse;

#endregion

#region執行SQL

///summary

///執行SQL,并返回SQLiteDataReader對象結果

////summary

///paramname="sql"/param

///paramname="paramArr"null表示無參數/param

///returns/returns

publicSQLiteDataReaderExecuteReader(stringsql,SQLiteParameter[]paramArr)

if(sql==null)

thrownewArgumentNullException("sql=null");

EnsureConnection();

using(RWL[LockName].Read())

using(SQLiteCommandcmd=newSQLiteCommand(sql,Connection))

if(paramArr!=null)

cmd.Parameters.AddRange(paramArr);

SQLiteDataReaderreader=cmd.ExecuteReader();

cmd.Parameters.Clear();

returnreader;

catch(Exception)

returnnull;

///summary

///執行查詢，并返回dataset對象

////summary

///paramname="sql"SQL查詢語句/param

///paramname="paramArr"參數數組/param

///returns/returns

publicDataSetExecuteDataSet(stringsql,SQLiteParameter[]paramArr)

if(sql==null)

thrownewArgumentNullException("sql=null");

this.EnsureConnection();

using(RWL[LockName].Read())

using(SQLiteCommandcmd=newSQLiteCommand(sql,this.Connection))

if(paramArr!=null)

cmd.Parameters.AddRange(paramArr);

SQLiteDataAdapterda=newSQLiteDataAdapter();

DataSetds=newDataSet();

da.SelectCommand=cmd;

da.Fill(ds);

cmd.Parameters.Clear();

da.Dispose();

returnds;

catch(Exception)

returnnull;

///summary

///執行SQL查詢，并返回dataset對象。

////summary

///paramname="strTable"映射源表的名稱/param

///paramname="sql"SQL語句/param

///paramname="paramArr"SQL參數數組/param

///returns/returns

publicDataSetExecuteDataSet(stringstrTable,stringsql,SQLiteParameter[]paramArr)

if(sql==null)

thrownewArgumentNullException("sql=null");

this.EnsureConnection();

using(RWL[LockName].Read())

using(SQLiteCommandcmd=newSQLiteCommand(sql,this.Connection))

if(paramArr!=null)

cmd.Parameters.AddRange(paramArr);

SQLiteDataAdapterda=newSQLiteDataAdapter();

DataSetds=newDataSet();

da.SelectCommand=cmd;

da.Fill(ds,strTable);

cmd.Parameters.Clear();

da.Dispose();

returnds;

catch(Exception)

returnnull;

///summary

///執行SQL，返回受影響的行數，可用于執行表創建語句，paramArr==null表示無參數

////summary

///paramname="sql"/param

///returns/returns

publicintExecuteNonQuery(stringsql,SQLiteParameter[]paramArr)

if(sql==null)

thrownewArgumentNullException("sql=null");

this.EnsureConnection();

using(RWL[LockName].Read())

using(SQLiteCommandcmd=newSQLiteCommand(sql,Connection))

if(paramArr!=null)

foreach(SQLiteParameterpinparamArr)

cmd.Parameters.Add(p);

intc=cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

returnc;

catch(SQLiteException)

return0;

///summary

///執行SQL，返回結果集第一行，如果結果集為空，那么返回空List(List.Count=0)，

///rowWrapper=null時，使用WrapRowToDictionary

////summary

///paramname="sql"/param

///paramname="paramArr"/param

///returns/returns

publicobjectExecuteScalar(stringsql,SQLiteParameter[]paramArr)

if(sql==null)

thrownewArgumentNullException("sql=null");

this.EnsureConnection();

using(RWL[LockName].Read())

using(SQLiteCommandcmd=newSQLiteCommand(sql,Connection))

if(paramArr!=null)

cmd.Parameters.AddRange(paramArr);

objectreader=cmd.ExecuteScalar();

cmd.Parameters.Clear();

cmd.Dispose();

returnreader;

catch(Exception)

returnnull;

///summary

///查詢一行記錄，無結果時返回null，conditionCol=null時將忽略條件，直接執行select*fromtable

////summary

///paramname="table"表名/param

///paramname="conditionCol"/param

///paramname="conditionVal"/param

///returns/returns

publicobjectQueryOne(stringtable,stringconditionCol,objectconditionVal)

if(table==null)

thrownewArgumentNullException("table=null");

this.EnsureConnection();

stringsql="select*from"+table;

if(conditionCol!=null)

sql+="where"+conditionCol+"=@"+conditionCol;

objectresult=ExecuteScalar(sql,newSQLiteParameter[]{newSQLiteParameter(conditionCol,conditionVal)});

returnresult;

#endregion

#region增刪改

///summary

///執行insertinto語句

////summary

///paramname="table"/param

///paramname="entity"/param

///returns/returns

publicintInsertData(stringtable,Dictionarystring,objectentity)

if(table==null)

thrownewArgumentNullException("table=null");

this.EnsureConnection();

stringsql=BuildInsert(table,entity);

returnthis.ExecuteNonQuery(sql,BuildParamArray(entity));

///summary

///執行update語句，注意：如果where=null，那么whereParams也為null，

////summary

///paramname="table"表名/param

///paramname="entity"要修改的列名和列名的值/param

///paramname="where"查找符合條件的列/param

///paramname="whereParams"where條件中參數的值/param

///returns/returns

publicintUpdate(stringtable,Dictionarystring,objectentity,stringwhere,SQLiteParameter[]whereParams)

if(table==null)

thrownewArgumentNullException("table=null");

this.EnsureConnection();

stringsql=BuildUpdate(table,entity);

SQLiteParameter[]parameter=BuildParamArray(entity);

if(where!=null)

sql+="where"+where;

if(whereParams!=null)

SQLiteParameter[]newArr=newSQLiteParameter[(parameter.Length+whereParams.Length)];

Array.Copy(parameter,newArr,parameter.Length);

Array.Copy(whereParams,0,newArr,parameter.Length,whereParams.Length);

parameter=newArr;

returnthis.ExecuteNonQuery(sql,parameter);

///summary

///執行deletefromtable語句，where不必包含'where'關鍵字，where=null時將忽略whereParams

////summary

///paramname="table"/param

///paramname="where"/param

///paramname="whereParams"/param

///returns/returns

publicintDelete(stringtable,stringwhere,SQLiteParameter[]whereParams)

if(table==null)

thrownewArgumentNullException("table=null");

this.EnsureConnection();

stringsql="deletefrom"+table+"";

if(where!=null)

sql+="where"+where;

returnExecuteNonQuery(sql,whereParams);

///summary

///將Dictionary類型數據轉換為SQLiteParameter[]類型

////summary

///paramname="entity"/param

///returns/returns

privateSQLiteParameter[]BuildParamArray(Dictionarystring,objectentity)

ListSQLiteParameterlist=newListSQLiteParameter

foreach(stringkeyinentity.Keys)

list.Add(newSQLiteParameter(key,entity[key]));

if(list.Count==0)

returnnull;

returnlist.ToArray();

///summary

///將Dictionary類型數據轉換為插入數據的SQL語句

////summary

///paramname="table"表名/param

///paramname="entity"字典/param

///returns/returns

privatestringBuildInsert(stringtable,Dictionarystring,objectentity)

StringBuilderbuf=newStringBuilder();

buf.Append("insertinto").Append(table);

buf.Append("(");

foreach(stringkeyinentity.Keys)

buf.Append(key).Append(",");

buf.Remove(buf.Length-1,1);//移除最后一個,

buf.Append(")");

buf.Append("values(");

foreach(stringkeyinentity.Keys)

buf.Append("@").Append(key).Append(",");//創建一個參數

buf.Remove(buf.Length-1,1);

buf.Append(")");

returnbuf.ToString();

///summary

///將Dictionary類型數據轉換為修改數據的SQL語句

////summary

///paramname="table"表名/param

///paramname="entity"字典/param

///returns/returns

privatestringBuildUpdate(stringtable,Dictionarystring,objectentity)

StringBuilderbuf=newStringBuilder();

buf.Append("update").Append(table).Append("set");

foreach(stringkeyinentity.Keys)

buf.Append(key).Append("=").Append("@").Append(key).Append(",");

buf.Remove(buf.Length-1,1);

buf.Append("");

returnbuf.ToString();

#endregion

}

UsingLock.cs

usingSystem;

usingSystem.Collections.Generic;

usingSystem.Linq;

usingSystem.Text;

usingSystem.Threading.Tasks;

usingSystem.Threading;

namespaceMySQLiteHelper

///summary

///使用using代替lock操作的對象，可指定寫入和讀取鎖定模式

////summary

publicsealedclassClsLock

#region內部類

///summary

///利用IDisposable的using語法糖方便的釋放鎖定操作內部類

////summary

privatestructLock:IDisposable

///summary

///讀寫鎖對象

////summary

privatereadonlyReaderWriterLockSlim_Lock;

///summary

///是否為寫入模式

////summary

privatebool_IsWrite;

///summary

///利用IDisposable的using語法糖方便的釋放鎖定操作構造函數

////summary

///paramname="rwl"讀寫鎖/param

///paramname="isWrite"寫入模式為true，讀取模式為false/param

publicLock(ReaderWriterLockSlimrwl,boolisWrite)

_Lock=rwl;

_IsWrite=isWrite;

///summary

///釋放對象時退出指定鎖定模式

////summary

publicvoidDispose()

if(_IsWrite)

if(_Lock.IsWriteLockHeld)

_Lock.ExitWriteLock();

else

if(_Lock.IsReadLockHeld)

_Lock.ExitReadLock();

///summary

///空的可釋放對象，免去了調用時需要判斷是否為null的問題內部類

////summary

privateclassDisposable:IDisposable

///summary

///空的可釋放對象

////summary

publicstaticreadonlyDisposableEmpty=newDisposable();

///summary

///空的釋放方法

////summary

publicvoidDispose(){}

#endregion

///summary

///讀寫鎖

////summary

privatereadonlyReaderWriterLockSlim_LockSlim=newReaderWriterLockSlim();

///summary

///使用using代替lock操作的對象，可指定寫入和讀取鎖定模式構造函數

////summary

publicClsLock()

Enabled=true;

///summary

///是否啟用，當該值為false時，Read()和Write()方法將返回Disposable.Empty

////summary

publicboolEnabled{get;set;}

///summary

///進入讀取鎖定模式，該模式下允許多個讀操作同時進行，

///退出讀鎖請將返回對象釋放，建議使用using語塊,

///Enabled為false時，返回Disposable.Empty，

///在讀取或寫入鎖定模式下重復執行，返回Disposable.Empty;

////summary

publicIDisposableRead()

if(Enabled==false||_LockSlim.IsReadLockHeld||_LockSlim.IsWriteLockHeld)

returnDisposable.Empty;

else

_LockSlim.EnterReadLock();

returnnewLock(_LockSlim,false);

///summary

///進入寫入鎖定模式,該模式下只允許同時執行一個讀操作，

///退出讀鎖請將返回對象釋放，建議使用using語塊，

///Enabled為false時，返回Disposable.Empty，

///在寫入鎖定模式下重復執行，返回Disposable.Empty

////summary

///exceptioncref="NotImplementedException"讀取模式下不能進入寫入鎖定狀態/exception

publicIDisposableWrite()

if(Enabled==false||_LockSlim.IsWriteLockHeld)

returnDisposable.Empty;

elseif(_LockSlim.IsReadLockHeld)

thrownewNotImplementedException("讀取模式下不能進入寫入鎖定狀態");

else

_LockSlim.EnterWriteLock();

returnnewLock(_LockSlim,true);

}

Form1.cs

usingMySQLiteHelper;

usingSystem;

usingSystem.Collections.Generic;

usingSystem.Data;

usingSystem.Data.SQLite;

usingSystem.Windows.Forms;

namespaceSQLiteDemo

publicpartialclassForm1:Form

publicForm1()

InitializeComponent();

privateSQLiteHelperSQLiteHelpers=null;

privateconststringDBAddress="D:\\SQLiteData\\test_record.db3";

privatevoidForm1_Load(objectsender,EventArgse)

SQLiteHelpers=newSQLiteHelper(DBAddress,"123456");

///summary

///打開數據庫

////summary

///paramname="sender"/param

///paramname="e"/param

privatevoidButton_OpenDB_Click(objectsender,EventArgse)

SQLiteHelpers.Open();

Label_DBOpenState.Text="打開";

///summary

///關閉數據庫

////summary

///paramname="sender"/param

///paramname="e"/param

privatevoidButton_CloseDB_Click(objectsender,EventArgse)

SQLiteHelpers.Close();

Label_DBOpenState.Text="關閉";

///summary

///查詢

////summary

///paramname="sender"/param

///paramname="e"/param

privatevoidButton_Query_Click(objectsender,EventArgse)

SQLiteParameter[]parameter=newSQLiteParameter[]

newSQLiteParameter("address","濟南")

stringsql="SELECT*FROMstudentWHEREaddress=@address";

DataSetdataSet=SQLiteHelpers.ExecuteDataSet(sql,parameter);

if(dataSet!=null)

dataGridView1.DataSource=dataSet.Tables[0];

///summary

///插入數據

////summary

///paramname="sender"/param

///paramname="e"/param

privatevoidButton_Add_Click(objectsender,EventArgse)

Dictionarystring,objectdic=newDictionarystring,object

dic.Add("ID