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(DictionaryconnectionOpts)
{
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(KeyValuePairvalindata)
{
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#程序设计有所帮助。