这篇文章给大家分享的是有关C#中增加SQLite事务操作支持与使用方法的示例分析的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

具体如下：

在C#中使用Sqpte增加对transaction支持

usingSystem;
usingSystem.Collections.Generic;
usingSystem.Data;
usingSystem.Data.SQLite;
usingSystem.Globapzation;
usingSystem.Linq;
usingSystem.Windows.Forms;
namespaceSimple_Disk_Catalog
{
pubpcclassSQLiteDatabase
{
StringDBConnection;
privatereadonlySQLiteTransaction_sqLiteTransaction;
privatereadonlySQLiteConnection_sqLiteConnection;
privatereadonlybool_transaction;
///<summary>
///DefaultConstructorforSQLiteDatabaseClass.
///</summary>
///<paramname="transaction">Allowprogrammerstoinsert,updateanddeletevaluesinonetransaction</param>
pubpcSQLiteDatabase(booltransaction=false)
{
_transaction=transaction;
DBConnection="DataSource=recipes.s3db";
if(transaction)
{
_sqLiteConnection=newSQLiteConnection(DBConnection);
_sqLiteConnection.Open();
_sqLiteTransaction=_sqLiteConnection.BeginTransaction();
}
}
///<summary>
///SingleParamConstructorforspecifyingtheDBfile.
///</summary>
///<paramname="inputFile">TheFilecontainingtheDB</param>
pubpcSQLiteDatabase(StringinputFile)
{
DBConnection=String.Format("DataSource={0}",inputFile);
}
///<summary>
///Committransactiontothedatabase.
///</summary>
pubpcvoidCommitTransaction()
{
_sqLiteTransaction.Commit();
_sqLiteTransaction.Dispose();
_sqLiteConnection.Close();
_sqLiteConnection.Dispose();
}
///<summary>
///SingleParamConstructorforspecifyingadvancedconnectionoptions.
///</summary>
///<paramname="connectionOpts">Adictionarycontainingalldesiredoptionsandtheirvalues</param>
pubpcSQLiteDatabase(Dictionary<String,String>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;
}
///<summary>
///Allowstheprogrammertocreatenewdatabasefile.
///</summary>
///<paramname="filePath">Fullpathofanewdatabasefile.</param>
///<returns>trueorfalsetorepresentsuccessorfailure.</returns>
pubpcstaticboolCreateDB(stringfilePath)
{
try
{
SQLiteConnection.CreateFile(filePath);
returntrue;
}
catch(Exceptione)
{
MessageBox.Show(e.Message,e.GetType().ToString(),MessageBoxButtons.OK,MessageBoxIcon.Error);
returnfalse;
}
}
///<summary>
///AllowstheprogrammertorunaqueryagainsttheDatabase.
///</summary>
///<paramname="sql">TheSQLtorun</param>
///<paramname="allowDBNullColumns">Allownullvalueforcolumnsinthiscollection.</param>
///<returns>ADataTablecontainingtheresultset.</returns>
pubpcDataTableGetDataTable(stringsql,IEnumerable<string>allowDBNullColumns=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;
}
pubpcstringRetrieveOriginal(stringvalue)
{
return
value.Replace("&","&").Replace("<","<").Replace(">","<").Replace(""","\"").Replace(
"'","'");
}
///<summary>
///Allowstheprogrammertointeractwiththedatabaseforpurposesotherthanaquery.
///</summary>
///<paramname="sql">TheSQLtoberun.</param>
///<returns>AnIntegercontainingthenumberofrowsupdated.</returns>
pubpcintExecuteNonQuery(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();
}
}
///<summary>
///AllowstheprogrammertoretrievesingleitemsfromtheDB.
///</summary>
///<paramname="sql">Thequerytorun.</param>
///<returns>Astring.</returns>
pubpcstringExecuteScalar(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():"";
}
}
///<summary>
///AllowstheprogrammertoeasilyupdaterowsintheDB.
///</summary>
///<paramname="tableName">Thetabletoupdate.</param>
///<paramname="data">AdictionarycontainingColumnnamesandtheirnewvalues.</param>
///<paramname="where">Thewhereclausefortheupdatestatement.</param>
///<returns>Abooleantrueorfalsetosignifysuccessorfailure.</returns>
pubpcboolUpdate(StringtableName,Dictionary<String,String>data,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;
}
///<summary>
///AllowstheprogrammertoeasilydeleterowsfromtheDB.
///</summary>
///<paramname="tableName">Thetablefromwhichtodelete.</param>
///<paramname="where">Thewhereclauseforthedelete.</param>
///<returns>Abooleantrueorfalsetosignifysuccessorfailure.</returns>
pubpcboolDelete(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;
}
///<summary>
///AllowstheprogrammertoeasilyinsertintotheDB
///</summary>
///<paramname="tableName">Thetableintowhichweinsertthedata.</param>
///<paramname="data">Adictionarycontainingthecolumnnamesanddatafortheinsert.</param>
///<returns>returnslastinsertedrowidifit'svalueiszerothanitmeansfailure.</returns>
pubpclongInsert(StringtableName,Dictionary<String,String>data)
{
Stringcolumns="";
Stringvalues="";
Stringvalue;
foreach(KeyValuePair<String,String>vapndata)
{
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);
}
///<summary>
///AllowstheprogrammertoeasilydeletealldatafromtheDB.
///</summary>
///<returns>Abooleantrueorfalsetosignifysuccessorfailure.</returns>
pubpcboolClearDB()
{
try
{
vartables=GetDataTable("selectNAMEfromSQLITE_MASTERwheretype='table'orderbyNAME;");
foreach(DataRowtableintables.Rows)
{
ClearTable(table["NAME"].ToString());
}
returntrue;
}
catch
{
returnfalse;
}
}
///<summary>
///Allowstheusertoeasilyclearalldatafromaspecifictable.
///</summary>
///<paramname="table">Thenameofthetabletoclear.</param>
///<returns>Abooleantrueorfalsetosignifysuccessorfailure.</returns>
pubpcboolClearTable(Stringtable)
{
try
{
ExecuteNonQuery(String.Format("deletefrom{0};",table));
returntrue;
}
catch
{
returnfalse;
}
}
///<summary>
///Allowstheusertoeasilyreducesizeofdatabase.
///</summary>
///<returns>Abooleantrueorfalsetosignifysuccessorfailure.</returns>
pubpcboolCompactDB()
{
try
{
ExecuteNonQuery("Vacuum;");
returntrue;
}
catch(Exception)
{
returnfalse;
}
}
}
}

感谢各位的阅读！关于“C#中增加SQLite事务操作支持与使用方法的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！

关于C#中增加SQLite事务操作支持与使用方法的示例分析的内容到此结束，希望对大家有所帮助。屹东网往后会继续推荐C#中增加SQLite事务操作支持与使用方法的示例分析相关内容。