C#中增加中增加SQLite事务操作支持与使用方法 事务操作支持与使用方法 主要介绍了C#中增加SQLite事务操作支持与使用方法,结合实例形式分析了C#中针对SQLite事务操作的添加及使用技巧,需要的朋友 可以参考下 本文实例讲述了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; ///

/// Default Constructor for SQLiteDatabase Class. ///

/// Allow programmers to insert, update and delete values in one transaction public SQLiteDatabase(bool transaction = false) { _transaction = transaction; DBConnection = "Data Source=recipes.s3db"; if (transaction) { _sqLiteConnection = new SQLiteConnection(DBConnection); _sqLiteConnection.Open(); _sqLiteTransaction = _sqLiteConnection.BeginTransaction(); } } ///

/// Single Param Constructor for specifying the DB file. ///

/// The File containing the DB public SQLiteDatabase(String inputFile) { DBConnection = String.Format("Data Source={0}", inputFile); } ///

/// Commit transaction to the database. ///

public void CommitTransaction() { _sqLiteTransaction.Commit(); _sqLiteTransaction.Dispose(); _sqLiteConnection.Close(); _sqLiteConnection.Dispose(); } ///

/// Single Param Constructor for specifying advanced connection options. ///

/// A dictionary containing all desired options and their values public SQLiteDatabase(Dictionary 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; } ///

/// Allows the programmer to create new database file. ///

/// Full path of a new database file. /// true or false to represent success or failure. 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; } } ///

/// Allows the programmer to run a query against the Database. ///

/// The SQL to run /// Allow null value for columns in this collection. /// A DataTable containing the result set. public DataTable GetDataTable(string sql, IEnumerable 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( "'", "'"); } ///

/// Allows the programmer to interact with the database for purposes other than a query. ///

/// The SQL to be run. /// An Integer containing the number of rows updated. 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(); } } ///

/// Allows the programmer to retrieve single items from the DB. ///

/// The query to run. /// A string. 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() : ""; } } ///

/// Allows the programmer to easily update rows in the DB. ///

/// The table to update. /// A dictionary containing Column names and their new values. /// The where clause for the update statement. /// A boolean true or false to signify success or failure. public bool Update(String tableName, Dictionary 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; } ///

/// Allows the programmer to easily delete rows from the DB. ///

/// The table from which to delete. /// The where clause for the delete. /// A boolean true or false to signify success or failure. 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; } ///

/// Allows the programmer to easily insert into the DB ///

/// The table into which we insert the data. /// A dictionary containing the column names and data for the insert. /// returns last inserted row id if it's value is zero than it means failure. public long Insert(String tableName, Dictionary data) { String columns = ""; String values = ""; String value; foreach (KeyValuePair 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); } ///

/// Allows the programmer to easily delete all data from the DB. ///

/// A boolean true or false to signify success or failure. 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; } } ///

/// Allows the user to easily clear all data from a specific table. ///

/// The name of the table to clear. /// A boolean true or false to signify success or failure. public bool ClearTable(String table) { 

try { ExecuteNonQuery(String.Format("delete from {0};", table)); return true; } catch { return false; } } ///

/// Allows the user to easily reduce size of database. ///

/// A boolean true or false to signify success or failure. public bool CompactDB() { try { ExecuteNonQuery("Vacuum;"); return true; } catch (Exception) { return false; } } } } 更多关于C#相关内容感兴趣的读者可查看本站专题：《C#常见数据库操作技巧汇总》、《C#常见控件用法教程》、《C#窗体操作技巧汇总》、《C# 数据结构与算法教程》、《C#面向对象程序设计入门教程》及《C#程序设计之线程使用技巧总结》 希望本文所述对大家C#程序设计有所帮助。