c# mysql加事务_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("", "

"'", "'");

}

///

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