C#如何操作SQLite实现数据的增删改查(sqlite,开发技术)

简介

SQLite是一个轻量级、跨平台的关系型数据库，在小型项目中，方便，易用，同时支持多种开发语言，下面是我用C#语言对SQLite 的一个封装。

Winfrom界面如下：

代码还需要改进部分：

下面的代码我不觉得是完美的，读者可以自己去实现这些功能：

1.如果不想用多线程可以去掉UsingLock.cs ，在SQLiteHelper.cs中删除对应的引用即可。

2.创建数据库文件，可以从代码中分离成单独的方法。比如创建有密码的数据库文件。

3.在执行SQL语句时，有些语句执行成功，也不会有影响行数。比如，创建表，删除表，此时执行SQL语句返回的影响行数就为0。

4.只要SQL语句不报错就是执行成功的，在方法的返回值可以改为多个，比如用

Tuple<bool, string, int> 第一个参数 bool 代表执行结果，第二个参数 string 代表错误信息，第三个参数 int 代表影响的行数。

同样，也可以这样：

Tuple<bool, 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>privatestaticreadonlyDictionary<string,ClsLock>RWL=newDictionary<string,ClsLock>();///<summary>///数据库地址///</summary>privatereadonlystringmdataFile;///<summary>///数据库密码///</summary>privatereadonlystringmPassWord;privatereadonlystringLockName=null;///<summary>///数据库连接定义///</summary>privateSQLiteConnectionmConn;#endregion#region构造函数///<summary>///根据数据库地址初始化///</summary>///<paramname="dataFile">数据库地址</param>publicSQLiteHelper(stringdataFile){this.mdataFile=dataFile??thrownewArgumentNullException("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=dataFile??thrownewArgumentNullException("dataFileisnull");this.mPassWord=PassWord??thrownewArgumentNullException("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){try{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(){try{DBtrans.Commit();}catch(Exception){DBtrans.Rollback();}}#endregion#region工具///<summary>///打开一个SQLite数据库文件，如果文件不存在，则创建（无密码）///</summary>///<paramname="dataFile"></param>///<returns>SQLiteConnection类</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>///<returns>SQLiteConnection类</returns>privateSQLiteConnectionOpenConnection(stringdataFile,stringPassword){if(dataFile==null){thrownewArgumentNullException("dataFile=null");}if(!File.Exists(Convert.ToString(dataFile))){SQLiteConnection.CreateFile(dataFile);}try{SQLiteConnectionconn=newSQLiteConnection();SQLiteConnectionStringBuilderconStr=newSQLiteConnectionStringBuilder{DataSource=dataFile,Password=Password};conn.ConnectionString=conStr.ToString();conn.Open();returnconn;}catch(Exception){returnnull;}}///<summary>///读取或设置SQLiteManager使用的数据库连接///</summary>publicSQLiteConnectionConnection{get{returnmConn;}privateset{mConn=value??thrownewArgumentNullException();}}///<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(){try{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);}try{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);}try{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);}try{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()){try{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);}try{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,Dictionary<string,object>entity){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,Dictionary<string,object>entity,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(Dictionary<string,object>entity){List<SQLiteParameter>list=newList<SQLiteParameter>();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,Dictionary<string,object>entity){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,Dictionary<string,object>entity){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){Dictionary<string,object>dic=newDictionary<string,object>();dic.Add("ID",6);dic.Add("name","王二麻子");dic.Add("age",44);dic.Add("address","陕西");intresult=SQLiteHelpers.InsertData("student",dic);Console.WriteLine("插入结果，受影响的行数："+result);}///<summary>///修改数据///</summary>///<paramname="sender"></param>///<paramname="e"></param>privatevoidButton_Modify_Click(objectsender,EventArgse){Dictionary<string,object>dic=newDictionary<string,object>();//将列名name的值改为“猴子”dic.Add("name","猴子");//将列名address的值改为花果山dic.Add("address","花果山");//where条件stringwhere="ID=@IDANDage=@Age";//where条件中对应的参数SQLiteParameter[]parameter=newSQLiteParameter[]{newSQLiteParameter("ID",4),newSQLiteParameter("Age",23)};intresult=SQLiteHelpers.Update("student",dic,where,parameter);Console.WriteLine("修改结果，受影响的行数："+result);}///<summary>///删除数据///</summary>///<paramname="sender"></param>///<paramname="e"></param>privatevoidButton_Delete_Click(objectsender,EventArgse){//where条件stringwhere="ID=@ID";//where条件中对应的参数SQLiteParameter[]parameter=newSQLiteParameter[]{newSQLiteParameter("ID",6),};intresult=SQLiteHelpers.Delete("student",where,parameter);Console.WriteLine("删除结果，受影响的行数："+result);}///<summary>///判断表是否存在///</summary>///<paramname="sender"></param>///<paramname="e"></param>privatevoidButton_TableExists_Click(objectsender,EventArgse){stringtitle="dddd";boolresult=SQLiteHelpers.TableExists(title);Console.WriteLine(string.Format("{0}表是否存在，结果：{1}",title,result));}//输出各表中的数据//publicstaticvoidPrintValues(DataSetds)//{//foreach(DataTabletableinds.Tables)//{//Console.WriteLine("表名称："+table.TableName);//foreach(DataRowrowintable.Rows)//{//foreach(DataColumncolumnintable.Columns)//{//Console.Write(row[column]+"");//}//Console.WriteLine();//}//}//}}}