C#中增加SQLite事务操作支持与使用方法
本文实例讲述了C#中增加SQLite事务操作支持与使用方法。分享给大家供大家参考,具体如下:
在C#中使用Sqlite增加对transaction支持
usingSystem; usingSystem.Collections.Generic; usingSystem.Data; usingSystem.Data.SQLite; usingSystem.Globalization; usingSystem.Linq; usingSystem.Windows.Forms; namespaceSimple_Disk_Catalog { publicclassSQLiteDatabase { StringDBConnection; privatereadonlySQLiteTransaction_sqLiteTransaction; privatereadonlySQLiteConnection_sqLiteConnection; privatereadonlybool_transaction; //////DefaultConstructorforSQLiteDatabaseClass. /// ///Allowprogrammerstoinsert,updateanddeletevaluesinonetransaction publicSQLiteDatabase(booltransaction=false) { _transaction=transaction; DBConnection="DataSource=recipes.s3db"; if(transaction) { _sqLiteConnection=newSQLiteConnection(DBConnection); _sqLiteConnection.Open(); _sqLiteTransaction=_sqLiteConnection.BeginTransaction(); } } /// ///SingleParamConstructorforspecifyingtheDBfile. /// ///TheFilecontainingtheDB publicSQLiteDatabase(StringinputFile) { DBConnection=String.Format("DataSource={0}",inputFile); } /// ///Committransactiontothedatabase. /// publicvoidCommitTransaction() { _sqLiteTransaction.Commit(); _sqLiteTransaction.Dispose(); _sqLiteConnection.Close(); _sqLiteConnection.Dispose(); } //////SingleParamConstructorforspecifyingadvancedconnectionoptions. /// ///Adictionarycontainingalldesiredoptionsandtheirvalues publicSQLiteDatabase(Dictionary connectionOpts) { Stringstr=connectionOpts.Aggregate("",(current,row)=>current+String.Format("{0}={1};",row.Key,row.Value)); str=str.Trim().Substring(0,str.Length-1); DBConnection=str; } /// ///Allowstheprogrammertocreatenewdatabasefile. /// ///Fullpathofanewdatabasefile. /// trueorfalsetorepresentsuccessorfailure. publicstaticboolCreateDB(stringfilePath) { try { SQLiteConnection.CreateFile(filePath); returntrue; } catch(Exceptione) { MessageBox.Show(e.Message,e.GetType().ToString(),MessageBoxButtons.OK,MessageBoxIcon.Error); returnfalse; } } //////AllowstheprogrammertorunaqueryagainsttheDatabase. /// ///TheSQLtorun /// Allownullvalueforcolumnsinthiscollection. /// ADataTablecontainingtheresultset. publicDataTableGetDataTable(stringsql,IEnumerableallowDBNullColumns=null) { vardt=newDataTable(); if(allowDBNullColumns!=null) foreach(varsinallowDBNullColumns) { dt.Columns.Add(s); dt.Columns[s].AllowDBNull=true; } try { varcnn=newSQLiteConnection(DBConnection); cnn.Open(); varmycommand=newSQLiteCommand(cnn){CommandText=sql}; varreader=mycommand.ExecuteReader(); dt.Load(reader); reader.Close(); cnn.Close(); } catch(Exceptione) { thrownewException(e.Message); } returndt; } publicstringRetrieveOriginal(stringvalue) { return value.Replace("&","&").Replace("<","<").Replace(">","<").Replace(""","\"").Replace( "'","'"); } /// ///Allowstheprogrammertointeractwiththedatabaseforpurposesotherthanaquery. /// ///TheSQLtoberun. /// AnIntegercontainingthenumberofrowsupdated. publicintExecuteNonQuery(stringsql) { if(!_transaction) { varcnn=newSQLiteConnection(DBConnection); cnn.Open(); varmycommand=newSQLiteCommand(cnn){CommandText=sql}; varrowsUpdated=mycommand.ExecuteNonQuery(); cnn.Close(); returnrowsUpdated; } else { varmycommand=newSQLiteCommand(_sqLiteConnection){CommandText=sql}; returnmycommand.ExecuteNonQuery(); } } //////AllowstheprogrammertoretrievesingleitemsfromtheDB. /// ///Thequerytorun. /// Astring. publicstringExecuteScalar(stringsql) { if(!_transaction) { varcnn=newSQLiteConnection(DBConnection); cnn.Open(); varmycommand=newSQLiteCommand(cnn){CommandText=sql}; varvalue=mycommand.ExecuteScalar(); cnn.Close(); returnvalue!=null?value.ToString():""; } else { varsqLiteCommand=newSQLiteCommand(_sqLiteConnection){CommandText=sql}; varvalue=sqLiteCommand.ExecuteScalar(); returnvalue!=null?value.ToString():""; } } //////AllowstheprogrammertoeasilyupdaterowsintheDB. /// ///Thetabletoupdate. /// AdictionarycontainingColumnnamesandtheirnewvalues. /// Thewhereclausefortheupdatestatement. /// Abooleantrueorfalsetosignifysuccessorfailure. publicboolUpdate(StringtableName,Dictionarydata,Stringwhere) { Stringvals=""; BooleanreturnCode=true; if(data.Count>=1) { vals=data.Aggregate(vals,(current,val)=>current+String.Format("{0}='{1}',",val.Key.ToString(CultureInfo.InvariantCulture),val.Value.ToString(CultureInfo.InvariantCulture))); vals=vals.Substring(0,vals.Length-1); } try { ExecuteNonQuery(String.Format("update{0}set{1}where{2};",tableName,vals,where)); } catch { returnCode=false; } returnreturnCode; } /// ///AllowstheprogrammertoeasilydeleterowsfromtheDB. /// ///Thetablefromwhichtodelete. /// Thewhereclauseforthedelete. /// Abooleantrueorfalsetosignifysuccessorfailure. publicboolDelete(StringtableName,Stringwhere) { BooleanreturnCode=true; try { ExecuteNonQuery(String.Format("deletefrom{0}where{1};",tableName,where)); } catch(Exceptionfail) { MessageBox.Show(fail.Message,fail.GetType().ToString(),MessageBoxButtons.OK,MessageBoxIcon.Error); returnCode=false; } returnreturnCode; } //////AllowstheprogrammertoeasilyinsertintotheDB /// ///Thetableintowhichweinsertthedata. /// Adictionarycontainingthecolumnnamesanddatafortheinsert. /// returnslastinsertedrowidifit'svalueiszerothanitmeansfailure. publiclongInsert(StringtableName,Dictionarydata) { Stringcolumns=""; Stringvalues=""; Stringvalue; foreach(KeyValuePair valindata) { columns+=String.Format("{0},",val.Key.ToString(CultureInfo.InvariantCulture)); values+=String.Format("'{0}',",val.Value); } columns=columns.Substring(0,columns.Length-1); values=values.Substring(0,values.Length-1); try { if(!_transaction) { varcnn=newSQLiteConnection(DBConnection); cnn.Open(); varsqLiteCommand=newSQLiteCommand(cnn) { CommandText= String.Format("insertinto{0}({1})values({2});",tableName,columns, values) }; sqLiteCommand.ExecuteNonQuery(); sqLiteCommand=newSQLiteCommand(cnn){CommandText="SELECTlast_insert_rowid()"}; value=sqLiteCommand.ExecuteScalar().ToString(); } else { ExecuteNonQuery(String.Format("insertinto{0}({1})values({2});",tableName,columns,values)); value=ExecuteScalar("SELECTlast_insert_rowid()"); } } catch(Exceptionfail) { MessageBox.Show(fail.Message,fail.GetType().ToString(),MessageBoxButtons.OK,MessageBoxIcon.Error); return0; } returnlong.Parse(value); } /// ///AllowstheprogrammertoeasilydeletealldatafromtheDB. /// ///Abooleantrueorfalsetosignifysuccessorfailure. publicboolClearDB() { try { vartables=GetDataTable("selectNAMEfromSQLITE_MASTERwheretype='table'orderbyNAME;"); foreach(DataRowtableintables.Rows) { ClearTable(table["NAME"].ToString()); } returntrue; } catch { returnfalse; } } //////Allowstheusertoeasilyclearalldatafromaspecifictable. /// ///Thenameofthetabletoclear. /// Abooleantrueorfalsetosignifysuccessorfailure. publicboolClearTable(Stringtable) { try { ExecuteNonQuery(String.Format("deletefrom{0};",table)); returntrue; } catch { returnfalse; } } //////Allowstheusertoeasilyreducesizeofdatabase. /// ///Abooleantrueorfalsetosignifysuccessorfailure. publicboolCompactDB() { try { ExecuteNonQuery("Vacuum;"); returntrue; } catch(Exception) { returnfalse; } } } }
更多关于C#相关内容感兴趣的读者可查看本站专题:《C#常见数据库操作技巧汇总》、《C#常见控件用法教程》、《C#窗体操作技巧汇总》、《C#数据结构与算法教程》、《C#面向对象程序设计入门教程》及《C#程序设计之线程使用技巧总结》
希望本文所述对大家C#程序设计有所帮助。