C#操作SQLite實(shí)現(xiàn)數(shù)據(jù)的增刪改查

第C#操作SQLite實(shí)現(xiàn)數(shù)據(jù)的增刪改查目錄簡介主要代碼SQLiteHelper.csUsingLock.csForm1.cs

簡介

SQLite是一個輕量級、跨平臺的關(guān)系型數(shù)據(jù)庫，在小型項(xiàng)目中，方便，易用，同時支持多種開發(fā)語言，下面是我用C#語言對SQLite的一個封裝。

Winfrom界面如下：

代碼還需要改進(jìn)部分：

下面的代碼我不覺得是完美的，讀者可以自己去實(shí)現(xiàn)這些功能：

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

2.創(chuàng)建數(shù)據(jù)庫文件，可以從代碼中分離成單獨(dú)的方法。比如創(chuàng)建有密碼的數(shù)據(jù)庫文件。

3.在執(zhí)行SQL語句時，有些語句執(zhí)行成功，也不會有影響行數(shù)。比如，創(chuàng)建表，刪除表，此時執(zhí)行SQL語句返回的影響行數(shù)就為0。

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

Tuplebool,string,int第一個參數(shù)bool代表執(zhí)行結(jié)果，第二個參數(shù)string代表錯誤信息，第三個參數(shù)int代表影響的行數(shù)。

同樣，也可以這樣：

Tuplebool,string,DataSet第一個參數(shù)bool代表執(zhí)行結(jié)果，第二個參數(shù)string代表錯誤信息，第三個參數(shù)DataSet代表返回的表單數(shù)據(jù)。

主要代碼

SQLiteHelper.cs

usingSystem;

usingSystem.Collections.Generic;

usingSystem.Data;

usingSystem.Data.Common;

usingSystem.Data.SQLite;

usingSystem.IO;

usingSystem.Text;

namespaceMySQLiteHelper

publicclassSQLiteHelper

#region字段

///summary

///事務(wù)的基類

////summary

privateDbTransactionDBtrans;

///summary

///使用靜態(tài)變量字典解決多線程實(shí)例本類，實(shí)現(xiàn)一個數(shù)據(jù)庫對應(yīng)一個clslock

////summary

privatestaticreadonlyDictionarystring,ClsLockRWL=newDictionarystring,ClsLock

///summary

///數(shù)據(jù)庫地址

////summary

privatereadonlystringmdataFile;

///summary

///數(shù)據(jù)庫密碼

////summary

privatereadonlystringmPassWord;

privatereadonlystringLockName=null;

///summary

///數(shù)據(jù)庫連接定義

////summary

privateSQLiteConnectionmConn;

#endregion

#region構(gòu)造函數(shù)

///summary

///根據(jù)數(shù)據(jù)庫地址初始化

////summary

///paramname="dataFile"數(shù)據(jù)庫地址/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

///使用密碼打開數(shù)據(jù)庫

////summary

///paramname="dataFile"數(shù)據(jù)庫地址/param

///paramname="PassWord"數(shù)據(jù)庫密碼/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打開/關(guān)閉數(shù)據(jù)庫

///summary

///打開SQLiteManager使用的數(shù)據(jù)庫連接

////summary

publicvoidOpen()

if(string.IsNullOrWhiteSpace(mPassWord))

mConn=OpenConnection(this.mdataFile);

else

mConn=OpenConnection(this.mdataFile,mPassWord);

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

///summary

///關(guān)閉連接

////summary

publicvoidClose()

if(this.mConn!=null)

this.mConn.Close();

if(RWL.ContainsKey(LockName))

RWL.Remove(LockName);

catch

Console.WriteLine("關(guān)閉失敗");

Console.WriteLine("關(guān)閉數(shù)據(jù)庫成功");

#endregion

#region事務(wù)

///summary

///開始事務(wù)

////summary

publicvoidBeginTrain()

EnsureConnection();

DBtrans=mConn.BeginTransaction();

///summary

///提交事務(wù)

////summary

publicvoidDBCommit()

DBtrans.Commit();

catch(Exception)

DBtrans.Rollback();

#endregion

#region工具

///summary

///打開一個SQLite數(shù)據(jù)庫文件，如果文件不存在，則創(chuàng)建（無密碼）

////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數(shù)據(jù)庫文件，如果文件不存在，則創(chuàng)建（有密碼）

////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

///讀取或設(shè)置SQLiteManager使用的數(shù)據(jù)庫連接

////summary

publicSQLiteConnectionConnection

returnmConn;

privateset

mConn=valuethrownewArgumentNullException();

///summary

///確保數(shù)據(jù)庫是連接狀態(tài)

////summary

///exceptioncref="Exception"/exception

protectedvoidEnsureConnection()

if(this.mConn==null)

thrownewException("SQLiteManager.Connection=null");

if(mConn.State!=ConnectionState.Open)

mConn.Open();

///summary

///獲取數(shù)據(jù)庫文件的路徑

////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命令（通過復(fù)制主數(shù)據(jù)庫中的內(nèi)容到一個臨時數(shù)據(jù)庫文件，然后清空主數(shù)據(jù)庫，并從副本中重新載入原始的數(shù)據(jù)庫文件）

////summary

///returns/returns

publicboolVacuum()

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

Command.ExecuteNonQuery();

returntrue;

catch(System.Data.SQLite.SQLiteException)

returnfalse;

#endregion

#region執(zhí)行SQL

///summary

///執(zhí)行SQL,并返回SQLiteDataReader對象結(jié)果

////summary

///paramname="sql"/param

///paramname="paramArr"null表示無參數(shù)/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

///執(zhí)行查詢，并返回dataset對象

////summary

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

///paramname="paramArr"參數(shù)數(shù)組/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

///執(zhí)行SQL查詢，并返回dataset對象。

////summary

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

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

///paramname="paramArr"SQL參數(shù)數(shù)組/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

///執(zhí)行SQL，返回受影響的行數(shù)，可用于執(zhí)行表創(chuàng)建語句，paramArr==null表示無參數(shù)

////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

///執(zhí)行SQL，返回結(jié)果集第一行，如果結(jié)果集為空，那么返回空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

///查詢一行記錄，無結(jié)果時返回null，conditionCol=null時將忽略條件，直接執(zhí)行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

///執(zhí)行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

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

////summary

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

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

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

///paramname="whereParams"where條件中參數(shù)的值/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

///執(zhí)行deletefromtable語句，where不必包含'where'關(guān)鍵字，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類型數(shù)據(jù)轉(zhuǎn)換為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類型數(shù)據(jù)轉(zhuǎn)換為插入數(shù)據(jù)的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(",");//創(chuàng)建一個參數(shù)

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

buf.Append(")");

returnbuf.ToString();

///summary

///將Dictionary類型數(shù)據(jù)轉(zhuǎn)換為修改數(shù)據(jù)的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內(nèi)部類

///summary

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

////summary

privatestructLock:IDisposable

///summary

///讀寫鎖對象

////summary

privatereadonlyReaderWriterLockSlim_Lock;

///summary

///是否為寫入模式

////summary

privatebool_IsWrite;

///summary

///利用IDisposable的using語法糖方便的釋放鎖定操作構(gòu)造函數(shù)

////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

///空的可釋放對象，免去了調(diào)用時需要判斷是否為null的問題內(nèi)部類

////summary

privateclassDisposable:IDisposable

///summary

///空的可釋放對象

////summary

publicstaticreadonlyDisposableEmpty=newDisposable();

///summary

///空的釋放方法

////summary

publicvoidDispose(){}

#endregion

///summary

///讀寫鎖

////summary

privatereadonlyReaderWriterLockSlim_LockSlim=newReaderWriterLockSlim();

///summary

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

////summary

publicClsLock()

Enabled=true;

///summary

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

////summary

publicboolEnabled{get;set;}

///summary

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

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

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

///在讀取或?qū)懭腈i定模式下重復(fù)執(zhí)行，返回Disposable.Empty;

////summary

publicIDisposableRead()

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

returnDisposable.Empty;

else

_LockSlim.EnterReadLock();

returnnewLock(_LockSlim,false);

///summary

///進(jìn)入寫入鎖定模式,該模式下只允許同時執(zhí)行一個讀操作，

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

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

///在寫入鎖定模式下重復(fù)執(zhí)行，返回Disposable.Empty

////summary

///exceptioncref="NotImplementedException"讀取模式下不能進(jìn)入寫入鎖定狀態(tài)/exception

publicIDisposableWrite()

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

returnDisposable.Empty;

elseif(_LockSlim.IsReadLockHeld)

thrownewNotImplementedException("讀取模式下不能進(jìn)入寫入鎖定狀態(tài)");

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

///打開數(shù)據(jù)庫

////summary

///paramname="sender"/param

///paramname="e"/param

privatevoidButton_OpenDB_Click(objectsender,EventArgse)

SQLiteHelpers.Open();

Label_DBOpenState.Text="打開";

///summary

///關(guān)閉數(shù)據(jù)庫

////summary

///paramname="sender"/param

///paramname="e"/param

privatevoidButton_CloseDB_Click(objectsender,EventArgse)

SQLiteHelpers.Close();

Label_DBOpenState.Text="關(guān)閉";

///summary

///查詢

////summary

///paramname="sender"/param

///paramname="e"/param

privatevoidButton_Query_Click(objectsender,EventArgse)

SQLiteParameter[]parameter=newSQLiteParameter[]

newSQLiteParameter("address","濟(jì)南")

stringsql="SELECT*FROMstudentWHEREaddress=@address";

DataSetdataSet=SQLiteHelpers.ExecuteDataSet(sql,parameter);

if(dataSet!=null)

dataGridView1.DataSource=dataSet.Tables[0];

///summary

///插入數(shù)據(jù)

////summary

///paramname="sender"/param

///paramname="e"/param

privatevoidButton_Add_Click(objectsender,EventArgse)

Dictionarystring,objectdic=newDictionarystring,object

dic.Add("ID