C#操作SQLite实现数据的增删改查

第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