查看: 3647|回复: 0

[ASP.NET教程] C#中增加SQLite事务操作支持与使用方法

发表于 2018-2-10 08:00:07

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

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

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.Data.SQLite;
  5. using System.Globalization;
  6. using System.Linq;
  7. using System.Windows.Forms;
  8. namespace Simple_Disk_Catalog
  9. {
  10. public class SQLiteDatabase
  11. {
  12. String DBConnection;
  13. private readonly SQLiteTransaction _sqLiteTransaction;
  14. private readonly SQLiteConnection _sqLiteConnection;
  15. private readonly bool _transaction;
  16. /// <summary>
  17. /// Default Constructor for SQLiteDatabase Class.
  18. /// </summary>
  19. /// <param name="transaction">Allow programmers to insert, update and delete values in one transaction</param>
  20. public SQLiteDatabase(bool transaction = false)
  21. {
  22. _transaction = transaction;
  23. DBConnection = "Data Source=recipes.s3db";
  24. if (transaction)
  25. {
  26. _sqLiteConnection = new SQLiteConnection(DBConnection);
  27. _sqLiteConnection.Open();
  28. _sqLiteTransaction = _sqLiteConnection.BeginTransaction();
  29. }
  30. }
  31. /// <summary>
  32. /// Single Param Constructor for specifying the DB file.
  33. /// </summary>
  34. /// <param name="inputFile">The File containing the DB</param>
  35. public SQLiteDatabase(String inputFile)
  36. {
  37. DBConnection = String.Format("Data Source={0}", inputFile);
  38. }
  39. /// <summary>
  40. /// Commit transaction to the database.
  41. /// </summary>
  42. public void CommitTransaction()
  43. {
  44. _sqLiteTransaction.Commit();
  45. _sqLiteTransaction.Dispose();
  46. _sqLiteConnection.Close();
  47. _sqLiteConnection.Dispose();
  48. }
  49. /// <summary>
  50. /// Single Param Constructor for specifying advanced connection options.
  51. /// </summary>
  52. /// <param name="connectionOpts">A dictionary containing all desired options and their values</param>
  53. public SQLiteDatabase(Dictionary<String, String> connectionOpts)
  54. {
  55. String str = connectionOpts.Aggregate("", (current, row) => current + String.Format("{0}=小贝; ", row.Key, row.Value));
  56. str = str.Trim().Substring(0, str.Length - 1);
  57. DBConnection = str;
  58. }
  59. /// <summary>
  60. /// Allows the programmer to create new database file.
  61. /// </summary>
  62. /// <param name="filePath">Full path of a new database file.</param>
  63. /// <returns>true or false to represent success or failure.</returns>
  64. public static bool CreateDB(string filePath)
  65. {
  66. try
  67. {
  68. SQLiteConnection.CreateFile(filePath);
  69. return true;
  70. }
  71. catch (Exception e)
  72. {
  73. MessageBox.Show(e.Message, e.GetType().ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
  74. return false;
  75. }
  76. }
  77. /// <summary>
  78. /// Allows the programmer to run a query against the Database.
  79. /// </summary>
  80. /// <param name="sql">The SQL to run</param>
  81. /// <param name="allowDBNullColumns">Allow null value for columns in this collection.</param>
  82. /// <returns>A DataTable containing the result set.</returns>
  83. public DataTable GetDataTable(string sql, IEnumerable<string> allowDBNullColumns = null)
  84. {
  85. var dt = new DataTable();
  86. if (allowDBNullColumns != null)
  87. foreach (var s in allowDBNullColumns)
  88. {
  89. dt.Columns.Add(s);
  90. dt.Columns[s].AllowDBNull = true;
  91. }
  92. try
  93. {
  94. var cnn = new SQLiteConnection(DBConnection);
  95. cnn.Open();
  96. var mycommand = new SQLiteCommand(cnn) {CommandText = sql};
  97. var reader = mycommand.ExecuteReader();
  98. dt.Load(reader);
  99. reader.Close();
  100. cnn.Close();
  101. }
  102. catch (Exception e)
  103. {
  104. throw new Exception(e.Message);
  105. }
  106. return dt;
  107. }
  108. public string RetrieveOriginal(string value)
  109. {
  110. return
  111. value.Replace("&", "&").Replace("<", "<").Replace(">", "<").Replace(""", "\"").Replace(
  112. "'", "'");
  113. }
  114. /// <summary>
  115. /// Allows the programmer to interact with the database for purposes other than a query.
  116. /// </summary>
  117. /// <param name="sql">The SQL to be run.</param>
  118. /// <returns>An Integer containing the number of rows updated.</returns>
  119. public int ExecuteNonQuery(string sql)
  120. {
  121. if (!_transaction)
  122. {
  123. var cnn = new SQLiteConnection(DBConnection);
  124. cnn.Open();
  125. var mycommand = new SQLiteCommand(cnn) {CommandText = sql};
  126. var rowsUpdated = mycommand.ExecuteNonQuery();
  127. cnn.Close();
  128. return rowsUpdated;
  129. }
  130. else
  131. {
  132. var mycommand = new SQLiteCommand(_sqLiteConnection) { CommandText = sql };
  133. return mycommand.ExecuteNonQuery();
  134. }
  135. }
  136. /// <summary>
  137. /// Allows the programmer to retrieve single items from the DB.
  138. /// </summary>
  139. /// <param name="sql">The query to run.</param>
  140. /// <returns>A string.</returns>
  141. public string ExecuteScalar(string sql)
  142. {
  143. if (!_transaction)
  144. {
  145. var cnn = new SQLiteConnection(DBConnection);
  146. cnn.Open();
  147. var mycommand = new SQLiteCommand(cnn) {CommandText = sql};
  148. var value = mycommand.ExecuteScalar();
  149. cnn.Close();
  150. return value != null ? value.ToString() : "";
  151. }
  152. else
  153. {
  154. var sqLiteCommand = new SQLiteCommand(_sqLiteConnection) { CommandText = sql };
  155. var value = sqLiteCommand.ExecuteScalar();
  156. return value != null ? value.ToString() : "";
  157. }
  158. }
  159. /// <summary>
  160. /// Allows the programmer to easily update rows in the DB.
  161. /// </summary>
  162. /// <param name="tableName">The table to update.</param>
  163. /// <param name="data">A dictionary containing Column names and their new values.</param>
  164. /// <param name="where">The where clause for the update statement.</param>
  165. /// <returns>A boolean true or false to signify success or failure.</returns>
  166. public bool Update(String tableName, Dictionary<String, String> data, String where)
  167. {
  168. String vals = "";
  169. Boolean returnCode = true;
  170. if (data.Count >= 1)
  171. {
  172. vals = data.Aggregate(vals, (current, val) => current + String.Format(" {0} = '小贝',", val.Key.ToString(CultureInfo.InvariantCulture), val.Value.ToString(CultureInfo.InvariantCulture)));
  173. vals = vals.Substring(0, vals.Length - 1);
  174. }
  175. try
  176. {
  177. ExecuteNonQuery(String.Format("update {0} set 小贝 where {2};", tableName, vals, where));
  178. }
  179. catch
  180. {
  181. returnCode = false;
  182. }
  183. return returnCode;
  184. }
  185. /// <summary>
  186. /// Allows the programmer to easily delete rows from the DB.
  187. /// </summary>
  188. /// <param name="tableName">The table from which to delete.</param>
  189. /// <param name="where">The where clause for the delete.</param>
  190. /// <returns>A boolean true or false to signify success or failure.</returns>
  191. public bool Delete(String tableName, String where)
  192. {
  193. Boolean returnCode = true;
  194. try
  195. {
  196. ExecuteNonQuery(String.Format("delete from {0} where 小贝;", tableName, where));
  197. }
  198. catch (Exception fail)
  199. {
  200. MessageBox.Show(fail.Message, fail.GetType().ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
  201. returnCode = false;
  202. }
  203. return returnCode;
  204. }
  205. /// <summary>
  206. /// Allows the programmer to easily insert into the DB
  207. /// </summary>
  208. /// <param name="tableName">The table into which we insert the data.</param>
  209. /// <param name="data">A dictionary containing the column names and data for the insert.</param>
  210. /// <returns>returns last inserted row id if it's value is zero than it means failure.</returns>
  211. public long Insert(String tableName, Dictionary<String, String> data)
  212. {
  213. String columns = "";
  214. String values = "";
  215. String value;
  216. foreach (KeyValuePair<String, String> val in data)
  217. {
  218. columns += String.Format(" {0},", val.Key.ToString(CultureInfo.InvariantCulture));
  219. values += String.Format(" '{0}',", val.Value);
  220. }
  221. columns = columns.Substring(0, columns.Length - 1);
  222. values = values.Substring(0, values.Length - 1);
  223. try
  224. {
  225. if (!_transaction)
  226. {
  227. var cnn = new SQLiteConnection(DBConnection);
  228. cnn.Open();
  229. var sqLiteCommand = new SQLiteCommand(cnn)
  230. {
  231. CommandText =
  232. String.Format("insert into {0}(小贝) values({2});", tableName, columns,
  233. values)
  234. };
  235. sqLiteCommand.ExecuteNonQuery();
  236. sqLiteCommand = new SQLiteCommand(cnn) { CommandText = "SELECT last_insert_rowid()" };
  237. value = sqLiteCommand.ExecuteScalar().ToString();
  238. }
  239. else
  240. {
  241. ExecuteNonQuery(String.Format("insert into {0}(小贝) values({2});", tableName, columns, values));
  242. value = ExecuteScalar("SELECT last_insert_rowid()");
  243. }
  244. }
  245. catch (Exception fail)
  246. {
  247. MessageBox.Show(fail.Message, fail.GetType().ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
  248. return 0;
  249. }
  250. return long.Parse(value);
  251. }
  252. /// <summary>
  253. /// Allows the programmer to easily delete all data from the DB.
  254. /// </summary>
  255. /// <returns>A boolean true or false to signify success or failure.</returns>
  256. public bool ClearDB()
  257. {
  258. try
  259. {
  260. var tables = GetDataTable("select NAME from SQLITE_MASTER where type='table' order by NAME;");
  261. foreach (DataRow table in tables.Rows)
  262. {
  263. ClearTable(table["NAME"].ToString());
  264. }
  265. return true;
  266. }
  267. catch
  268. {
  269. return false;
  270. }
  271. }
  272. /// <summary>
  273. /// Allows the user to easily clear all data from a specific table.
  274. /// </summary>
  275. /// <param name="table">The name of the table to clear.</param>
  276. /// <returns>A boolean true or false to signify success or failure.</returns>
  277. public bool ClearTable(String table)
  278. {
  279. try
  280. {
  281. ExecuteNonQuery(String.Format("delete from {0};", table));
  282. return true;
  283. }
  284. catch
  285. {
  286. return false;
  287. }
  288. }
  289. /// <summary>
  290. /// Allows the user to easily reduce size of database.
  291. /// </summary>
  292. /// <returns>A boolean true or false to signify success or failure.</returns>
  293. public bool CompactDB()
  294. {
  295. try
  296. {
  297. ExecuteNonQuery("Vacuum;");
  298. return true;
  299. }
  300. catch (Exception)
  301. {
  302. return false;
  303. }
  304. }
  305. }
  306. }
复制代码

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

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

您可能感兴趣的文章:

  • C#操作SQLite方法实例详解
  • C#/.Net 中快速批量给SQLite数据库插入测试数据
  • C#基于SQLiteHelper类似SqlHelper类实现存取Sqlite数据库的方法
  • C#简单访问SQLite数据库的方法(安装,连接,查询等)
  • C#简单查询SQLite数据库是否存在数据的方法
  • C#操作SQLite数据库方法小结(创建,连接,插入,查询,删除等)
  • C#操作SQLite数据库之读写数据库的方法
  • C#解决SQlite并发异常问题的方法(使用读写锁)
  • c#几种数据库的大数据批量插入(SqlServer、Oracle、SQLite和MySql)
  • C#操作SQLite数据库帮助类详解
  • C# SQLite事务操作方法分析
  • C# SQLite序列操作实现方法详解


回复

使用道具 举报