本文实例讲述了C#中增加SQLite事务操作支持与使用方法。分享给大家供大家参考，具体如下：

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

using System;using System.Collections.Generic;using System.Data;using System.Data.SQLite;using System.Globalization;using System.Linq;using System.Windows.Forms;namespace Simple_Disk_Catalog{ public class SQLiteDatabase { String DBConnection; private readonly SQLiteTransaction _sqLiteTransaction; private readonly SQLiteConnection _sqLiteConnection; private readonly bool _transaction; /// <summary> /// Default Constructor for SQLiteDatabase Class. /// </summary> /// <param name="transaction">Allow programmers to insert, update and delete values in one transaction</param> public SQLiteDatabase(bool transaction = false) { _transaction = transaction; DBConnection = "Data Source=recipes.s3db"; if (transaction) { _sqLiteConnection = new SQLiteConnection(DBConnection); _sqLiteConnection.Open(); _sqLiteTransaction = _sqLiteConnection.BeginTransaction(); } } /// <summary> /// Single Param Constructor for specifying the DB file. /// </summary> /// <param name="inputFile">The File containing the DB</param> public SQLiteDatabase(String inputFile) { DBConnection = String.Format("Data Source={0}", inputFile); } /// <summary> /// Commit transaction to the database. /// </summary> public void CommitTransaction() { _sqLiteTransaction.Commit(); _sqLiteTransaction.Dispose(); _sqLiteConnection.Close(); _sqLiteConnection.Dispose(); } /// <summary> /// Single Param Constructor for specifying advanced connection options. /// </summary> /// <param name="connectionOpts">A dictionary containing all desired options and their values</param> public SQLiteDatabase(Dictionary<String, String> connectionOpts) { String str = connectionOpts.Aggregate("", (current, row) => current + String.Format("{0}={1}; ", row.Key, row.Value)); str = str.Trim().Substring(0, str.Length - 1); DBConnection = str; } /// <summary> /// Allows the programmer to create new database file. /// </summary> /// <param name="filePath">Full path of a new database file.</param> /// <returns>true or false to represent success or failure.</returns> public static bool CreateDB(string filePath) { try { SQLiteConnection.CreateFile(filePath); return true; } catch (Exception e) { MessageBox.Show(e.Message, e.GetType().ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error); return false; } } /// <summary> /// Allows the programmer to run a query against the Database. /// </summary> /// <param name="sql">The SQL to run</param> /// <param name="allowDBNullColumns">Allow null value for columns in this collection.</param> /// <returns>A DataTable containing the result set.</returns> public DataTable GetDataTable(string sql, IEnumerable<string> allowDBNullColumns = null) { var dt = new DataTable(); if (allowDBNullColumns != null) foreach (var s in allowDBNullColumns) { dt.Columns.Add(s); dt.Columns[s].AllowDBNull = true; } try { var cnn = new SQLiteConnection(DBConnection); cnn.Open(); var mycommand = new SQLiteCommand(cnn) {CommandText = sql}; var reader = mycommand.ExecuteReader(); dt.Load(reader); reader.Close(); cnn.Close(); } catch (Exception e) { throw new Exception(e.Message); } return dt; } public string RetrieveOriginal(string value) { return value.Replace("&", "&").Replace("<", "<").Replace(">", "<").Replace(""", "\"").Replace( "'", "'"); } /// <summary> /// Allows the programmer to interact with the database for purposes other than a query. /// </summary> /// <param name="sql">The SQL to be run.</param> /// <returns>An Integer containing the number of rows updated.</returns> public int ExecuteNonQuery(string sql) { if (!_transaction) { var cnn = new SQLiteConnection(DBConnection); cnn.Open(); var mycommand = new SQLiteCommand(cnn) {CommandText = sql}; var rowsUpdated = mycommand.ExecuteNonQuery(); cnn.Close(); return rowsUpdated; } else { var mycommand = new SQLiteCommand(_sqLiteConnection) { CommandText = sql }; return mycommand.ExecuteNonQuery(); } } /// <summary> /// Allows the programmer to retrieve single items from the DB. /// </summary> /// <param name="sql">The query to run.</param> /// <returns>A string.</returns> public string ExecuteScalar(string sql) { if (!_transaction) { var cnn = new SQLiteConnection(DBConnection); cnn.Open(); var mycommand = new SQLiteCommand(cnn) {CommandText = sql}; var value = mycommand.ExecuteScalar(); cnn.Close(); return value != null ? value.ToString() : ""; } else { var sqLiteCommand = new SQLiteCommand(_sqLiteConnection) { CommandText = sql }; var value = sqLiteCommand.ExecuteScalar(); return value != null ? value.ToString() : ""; } } /// <summary> /// Allows the programmer to easily update rows in the DB. /// </summary> /// <param name="tableName">The table to update.</param> /// <param name="data">A dictionary containing Column names and their new values.</param> /// <param name="where">The where clause for the update statement.</param> /// <returns>A boolean true or false to signify success or failure.</returns> public bool Update(String tableName, Dictionary<String, String> data, String where) { String vals = ""; Boolean returnCode = 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; } return returnCode; } /// <summary> /// Allows the programmer to easily delete rows from the DB. /// </summary> /// <param name="tableName">The table from which to delete.</param> /// <param name="where">The where clause for the delete.</param> /// <returns>A boolean true or false to signify success or failure.</returns> public bool Delete(String tableName, String where) { Boolean returnCode = true; try { ExecuteNonQuery(String.Format("delete from {0} where {1};", tableName, where)); } catch (Exception fail) { MessageBox.Show(fail.Message, fail.GetType().ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error); returnCode = false; } return returnCode; } /// <summary> /// Allows the programmer to easily insert into the DB /// </summary> /// <param name="tableName">The table into which we insert the data.</param> /// <param name="data">A dictionary containing the column names and data for the insert.</param> /// <returns>returns last inserted row id if it's value is zero than it means failure.</returns> public long Insert(String tableName, Dictionary<String, String> data) { String columns = ""; String values = ""; String value; foreach (KeyValuePair<String, String> val in data) { 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) { var cnn = new SQLiteConnection(DBConnection); cnn.Open(); var sqLiteCommand = new SQLiteCommand(cnn) { CommandText = String.Format("insert into {0}({1}) values({2});", tableName, columns, values) }; sqLiteCommand.ExecuteNonQuery(); sqLiteCommand = new SQLiteCommand(cnn) { CommandText = "SELECT last_insert_rowid()" }; value = sqLiteCommand.ExecuteScalar().ToString(); } else { ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values)); value = ExecuteScalar("SELECT last_insert_rowid()"); } } catch (Exception fail) { MessageBox.Show(fail.Message, fail.GetType().ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error); return 0; } return long.Parse(value); } /// <summary> /// Allows the programmer to easily delete all data from the DB. /// </summary> /// <returns>A boolean true or false to signify success or failure.</returns> public bool ClearDB() { try { var tables = GetDataTable("select NAME from SQLITE_MASTER where type='table' order by NAME;"); foreach (DataRow table in tables.Rows) { ClearTable(table["NAME"].ToString()); } return true; } catch { return false; } } /// <summary> /// Allows the user to easily clear all data from a specific table. /// </summary> /// <param name="table">The name of the table to clear.</param> /// <returns>A boolean true or false to signify success or failure.</returns> public bool ClearTable(String table) { try { ExecuteNonQuery(String.Format("delete from {0};", table)); return true; } catch { return false; } } /// <summary> /// Allows the user to easily reduce size of database. /// </summary> /// <returns>A boolean true or false to signify success or failure.</returns> public bool CompactDB() { try { ExecuteNonQuery("Vacuum;"); return true; } catch (Exception) { return false; } } }}

更多关于C#相关内容感兴趣的读者可查看本站专题：《C#常见数据库操作技巧汇总》、《C#常见控件用法教程》、《C#窗体操作技巧汇总》、《C#数据结构与算法教程》、《C#面向对象程序设计入门教程》及《C#程序设计之线程使用技巧总结》

希望本文所述对大家C#程序设计有所帮助。