C#中增加SQLite事务操作支持与使用方法
更新时间:2020年6月25日 11:19 点击:2226
本文实例讲述了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#程序设计有所帮助。
上一篇: C#微信接口之推送模板消息功能示例
下一篇: C#实现获取mp3 Tag信息的方法
相关文章
- 我们在使用C#做项目的时候,基本上都需要制作登录界面,那么今天我们就来一步步看看,如果简单的实现登录界面呢,本文给出2个例子,由简入难,希望大家能够喜欢。...2020-06-25
- 这篇文章主要介绍了C# 字段和属性的的相关资料,文中示例代码非常详细,供大家参考和学习,感兴趣的朋友可以了解下...2020-11-03
- 这篇文章主要介绍了C#中截取字符串的的基本方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2020-11-03
- 这篇文章主要介绍了C#实现简单的Http请求的方法,以实例形式较为详细的分析了C#实现Http请求的具体方法,需要的朋友可以参考下...2020-06-25
- 本文给大家分享C#连接SQL数据库和查询数据功能的操作技巧,本文通过图文并茂的形式给大家介绍的非常详细,需要的朋友参考下吧...2021-05-17
- 本文主要介绍了C#中new的几种用法,具有很好的参考价值,下面跟着小编一起来看下吧...2020-06-25
使用Visual Studio2019创建C#项目(窗体应用程序、控制台应用程序、Web应用程序)
这篇文章主要介绍了使用Visual Studio2019创建C#项目(窗体应用程序、控制台应用程序、Web应用程序),小编觉得挺不错的,现在分享给大家,也给大家做个参考。一起跟随小编过来看看吧...2020-06-25- 这篇文章主要介绍了C#开发Windows窗体应用程序的简单操作步骤,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-04-12
- 这篇文章主要介绍了C#从数据库读取图片并保存的方法,帮助大家更好的理解和使用c#,感兴趣的朋友可以了解下...2021-01-16
- 最近做一个小项目不可避免的需要前端脚本与后台进行交互。由于是在asp.net中实现,故问题演化成asp.net中jiavascript与后台c#如何进行交互。...2020-06-25
- 本文通过例子,讲述了C++调用C#的DLL程序的方法,作出了以下总结,下面就让我们一起来学习吧。...2020-06-25
- 轻松学习C#的基础入门,了解C#最基本的知识点,C#是一种简洁的,类型安全的一种完全面向对象的开发语言,是Microsoft专门基于.NET Framework平台开发的而量身定做的高级程序设计语言,需要的朋友可以参考下...2020-06-25
- 本文主要介绍了C#变量命名规则小结,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2021-09-09
- 这篇文章主要介绍了C#绘制曲线图的方法,以完整实例形式较为详细的分析了C#进行曲线绘制的具体步骤与相关技巧,具有一定参考借鉴价值,需要的朋友可以参考下...2020-06-25
- 本文主要介绍了C# 中取绝对值的函数。具有很好的参考价值。下面跟着小编一起来看下吧...2020-06-25
- 这篇文章主要介绍了c#自带缓存使用方法,包括获取数据缓存、设置数据缓存、移除指定数据缓存等方法,需要的朋友可以参考下...2020-06-25
- 这篇文章主要介绍了c#中(&&,||)与(&,|)的区别详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2020-06-25
- 这篇文章主要用实例讲解C#递归算法的概念以及用法,文中代码非常详细,帮助大家更好的参考和学习,感兴趣的朋友可以了解下...2020-06-25
- 下面小编就为大家带来一篇C#学习笔记- 随机函数Random()的用法详解。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧...2020-06-25
- 这篇文章主要介绍了C#中list用法,结合实例形式分析了C#中list排序、运算、转换等常见操作技巧,具有一定参考借鉴价值,需要的朋友可以参考下...2020-06-25