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;
    /// <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#程序设计有所帮助。