首页 > Asp.net开发, 数据库开发 > Sqlite在C#中事务支持

Sqlite在C#中事务支持

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

 

[csharp][/csharp] view plaincopy

  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}={1}; ", 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} = '{1}',", 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 {1} 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 {1};", 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}({1}) 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}({1}) 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. }

本文固定链接: http://www.devba.com/index.php/archives/3459.html | 开发吧

报歉!评论已关闭.