public void Begin(IsolationLevel level)
{
if (_transaction != null)
{
_transaction.Rollback();
}
_transaction = _connection.BeginTransaction(IsolationLevel.Serializable);
}
/// <summary>
/// 更新数据库
/// </summary>
/// <param name="sql">数据库语句</param>
public static void SQLUpdate(string sql)
{
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
conn.Open(); // 连接数据库
using (System.Data.SQLite.SQLiteTransaction trans = conn.BeginTransaction())
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
//事务处理
cmd.Transaction = trans;
try
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
}
}
}
}
}
public void Rollback(Enlistment enlistment)
{
_transaction.Connection._enlistment = null;
try
{
_transaction.Rollback();
enlistment.Done();
}
finally
{
_transaction = null;
}
}
public override bool RollBackTransaction(string transactionName = null)
{
if (conn == null)
{
return(false);
}
if (transac == null)
{
return(false);
}
transac.Rollback();
transac.Dispose();
transac = null;
return(true);
}
public void Rollback(Enlistment enlistment)
{
SQLiteConnection cnn = _transaction.Connection;
cnn._enlistment = null;
try
{
_transaction.Rollback();
enlistment.Done();
}
finally
{
Cleanup(cnn);
}
}
///////////////////////////////////////////////////////////////////////////
public void Rollback(Enlistment enlistment)
{
CheckDisposed();
SQLiteConnection cnn = null;
try
{
while (true)
{
cnn = _transaction.Connection;
if (cnn == null)
{
break;
}
lock (cnn._enlistmentSyncRoot) /* TRANSACTIONAL */
{
//
// NOTE: This check is necessary to detect the case where
// the SQLiteConnection.Close() method changes the
// connection associated with our transaction (i.e.
// to avoid a race (condition) between grabbing the
// Connection property and locking its enlistment).
//
if (!Object.ReferenceEquals(cnn, _transaction.Connection))
{
continue;
}
cnn._enlistment = null;
_transaction.Rollback();
break;
}
}
enlistment.Done();
}
finally
{
Cleanup(cnn);
}
}
/// <summary>
///Description : This function is used to Handle Transaction Events
///Author : AuthorWho
///Date : 2011-11-01
///Input : Transaction Event Type
///OutPut : NA
///Comments :
/// </summary>
public void TransactionHandler(TransactionType veTransactionType)
{
switch (veTransactionType)
{
case TransactionType.Open: //open a transaction
try
{
oTransaction = oConnection.BeginTransaction();
mblTransaction = true;
}
catch (InvalidOperationException oErr)
{
throw new Exception("@TransactionHandler - " + oErr.Message);
}
break;
case TransactionType.Commit: //commit the transaction
if (null != oTransaction.Connection)
{
try
{
oTransaction.Commit();
mblTransaction = false;
}
catch (InvalidOperationException oErr)
{
throw new Exception("@TransactionHandler - " + oErr.Message);
}
}
break;
case TransactionType.Rollback: //rollback the transaction
try
{
if (mblTransaction)
{
oTransaction.Rollback();
}
mblTransaction = false;
}
catch (InvalidOperationException oErr)
{
throw new Exception("@TransactionHandler - " + oErr.Message);
}
break;
}
}
/* Commits the transaction. If it fails, the transaction is rolled back and an error
* message is opened. Returns true upon success, else false.
*/
public bool CommitTransaction(SQLiteTransaction transaction)
{
try
{
transaction.Commit();
}
catch (Exception)
{
try
{
transaction.Rollback();
MessageBox.Show("CommitTransaction", "Database access failed",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception)
{
MessageBox.Show("CommitTransaction", "Database access and rollback failed",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return false;
}
return true;
}
protected void ExecuteUpdate(String sql, SQLiteTransaction trans)
{
if (!IsOpen)
OpenDB();
SQLiteCommand update = _dbConnection.CreateCommand();
try
{
update.Transaction = trans;
update.CommandText = sql.ToString();
try
{
update.ExecuteNonQuery();
}
catch (Exception)
{
trans.Rollback();
throw;
}
}
catch (Exception ex)
{
TtUtils.WriteError(ex.Message, "DataAccessLayer:ExecuteUpdate");
}
finally
{
update.Dispose();
}
}
public void Rollback()
{
_transaction.Rollback();
Dispose();
}
/// <summary>
/// Executes the command.
/// </summary>
/// <param name="dbCommand">The current sql command.</param>
/// <param name="commandText">The command text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>-1 if command execution failed.</returns>
public Int32 ExecuteCommand(ref DbCommand dbCommand, string commandText,
CommandType commandType, string connectionString, params DbParameter[] values)
{
// Initial connection objects.
dbCommand = null;
Int32 returnValue = -1;
SqliteClient.SQLiteConnection sqlConnection = null;
SqliteClient.SQLiteTransaction sqlTransaction = null;
try
{
// Create a new connection.
using (sqlConnection = new SqliteClient.SQLiteConnection(connectionString))
{
// Open the connection.
sqlConnection.Open();
// Start a new transaction.
sqlTransaction = sqlConnection.BeginTransaction();
// Create the command and assign any parameters.
dbCommand = new SqliteClient.SQLiteCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.SqlDataType, commandText), sqlConnection);
dbCommand.CommandType = commandType;
dbCommand.Transaction = sqlTransaction;
if (values != null)
{
foreach (SqliteClient.SQLiteParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Execute the command.
returnValue = dbCommand.ExecuteNonQuery();
// Commit the transaction.
sqlTransaction.Commit();
// Close the database connection.
sqlConnection.Close();
}
// Return true.
return(returnValue);
}
catch (Exception ex)
{
try
{
// Attempt to roll back the transaction.
if (sqlTransaction != null)
{
sqlTransaction.Rollback();
}
}
catch { }
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (sqlConnection != null)
{
sqlConnection.Close();
}
}
}
public void RollbackTransaction()
{
transaction_.Rollback();
transaction_ = null;
}
public int Command(string cmdstr)
{
try
{
T = conn.BeginTransaction();
cmd.Transaction = T;
cmd.CommandText = cmdstr;
int a = cmd.ExecuteNonQuery();
T.Commit();
return a;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message,"错误提示");
T.Rollback();
return -1;
}
finally
{
T.Dispose();
}
}
public static bool Roolback(SQLiteTransaction aTransaction)
{
try
{
aTransaction.Rollback();
return true;
}
catch (Exception aExc)
{
log.Fatal("Can´t Rollback changes in database!", aExc);
}
return false;
}
protected void RollbackTransaction(SQLiteTransaction transaction)
{
try
{
transaction.Rollback();
}
catch (Exception ex)
{
_log.Error("Unable to rollback transaction.", ex);
throw new StorageException("Unable to rollback transaction.", ex);
}
}
/// <summary>
/// Adds the specified user names to the specified roles for the configured applicationName.
/// </summary>
/// <param name="usernames">A string array of user names to be added to the specified roles.</param>
/// <param name="roleNames">A string array of the role names to add the specified user names to.</param>
public override void AddUsersToRoles(string[] usernames, string[] roleNames)
{
foreach (string roleName in roleNames)
{
if (!RoleExists(roleName))
{
throw new ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
if (username.IndexOf(',') > 0)
{
throw new ArgumentException("User names cannot contain commas.");
}
foreach (string RoleName in roleNames)
{
if (IsUserInRole(username, RoleName))
{
throw new ProviderException("User is already in role.");
}
}
}
SQLiteTransaction tran = null;
SQLiteConnection cn = GetDBConnectionForRole();
try
{
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
if (!IsTransactionInProgress())
{
tran = cn.BeginTransaction();
}
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "INSERT INTO " + USERS_IN_ROLES_TB_NAME
+ " (UserId, RoleId)"
+ " SELECT u.UserId, r.RoleId"
+ " FROM " + USER_TB_NAME + " u, " + ROLE_TB_NAME + " r"
+ " WHERE (u.LoweredUsername = $Username) AND (u.ApplicationId = $ApplicationId)"
+ " AND (r.LoweredRoleName = $RoleName) AND (r.ApplicationId = $ApplicationId)";
SQLiteParameter userParm = cmd.Parameters.Add("$Username", DbType.String, MAX_USERNAME_LENGTH);
SQLiteParameter roleParm = cmd.Parameters.Add("$RoleName", DbType.String, MAX_ROLENAME_LENGTH);
cmd.Parameters.AddWithValue("$ApplicationId", _applicationId);
foreach (string username in usernames)
{
foreach (string roleName in roleNames)
{
userParm.Value = username.ToLowerInvariant();
roleParm.Value = roleName.ToLowerInvariant();
cmd.ExecuteNonQuery();
}
}
// Commit the transaction if it's the one we created in this method.
if (tran != null)
{
tran.Commit();
}
}
}
catch
{
if (tran != null)
{
tran.Rollback();
}
throw;
}
finally
{
if (tran != null)
{
tran.Dispose();
}
if (!IsTransactionInProgress())
{
cn.Dispose();
}
}
}
/// <summary>
/// Insert big Data with transaction
/// </summary>
/// <param name="command">sqlite command seperated with ;</param>
public void insertData(string command)
{
try
{
string[] cmd = command.Split(';');
Connect();
sql_con.Open();
sql_cmd = new SQLiteCommand();
// Start a local transaction
sql_trans = sql_con.BeginTransaction(IsolationLevel.Serializable);
// Assign transaction object for a pending local transaction
sql_cmd.Transaction = sql_trans;
for (int i = 0; i < cmd.Length; i++)
{
if (cmd[i] != "")
{
sql_cmd.CommandText = cmd[i].Trim();
sql_cmd.ExecuteNonQuery();
}
}
sql_trans.Commit();
}
catch (Exception e)
{
sql_trans.Rollback();
message = "Error insertData: Non of the data has been inserted to Database\r\n\r\n" + e.ToString();
success = false;
sql_con.Close();
}
finally
{
sql_con.Close();
message = "Success insertData";
success = true;
}
}
/// <summary>
/// Removes the specified user names from the specified roles for the configured applicationName.
/// </summary>
/// <param name="usernames">A string array of user names to be removed from the specified roles.</param>
/// <param name="roleNames">A string array of role names to remove the specified user names from.</param>
public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
{
foreach (string roleName in roleNames)
{
if (!RoleExists(roleName))
{
throw new ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
foreach (string roleName in roleNames)
{
if (!IsUserInRole(username, roleName))
{
throw new ProviderException("User is not in role.");
}
}
}
SQLiteTransaction tran = null;
SQLiteConnection cn = GetDBConnectionForRole();
try
{
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
if (!IsTransactionInProgress())
{
tran = cn.BeginTransaction();
}
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "DELETE FROM " + USERS_IN_ROLES_TB_NAME
+ " WHERE UserId = (SELECT UserId FROM " + USER_TB_NAME + " WHERE LoweredUsername = $Username AND ApplicationId = $ApplicationId)"
+ " AND RoleId = (SELECT RoleId FROM " + ROLE_TB_NAME + " WHERE LoweredRoleName = $RoleName AND ApplicationId = $ApplicationId)";
SQLiteParameter userParm = cmd.Parameters.Add("$Username", DbType.String, MAX_USERNAME_LENGTH);
SQLiteParameter roleParm = cmd.Parameters.Add("$RoleName", DbType.String, MAX_ROLENAME_LENGTH);
cmd.Parameters.AddWithValue("$ApplicationId", _applicationId);
foreach (string username in usernames)
{
foreach (string roleName in roleNames)
{
userParm.Value = username.ToLowerInvariant();
roleParm.Value = roleName.ToLowerInvariant();
cmd.ExecuteNonQuery();
}
}
// Commit the transaction if it's the one we created in this method.
if (tran != null)
{
tran.Commit();
}
}
}
catch
{
if (tran != null)
{
tran.Rollback();
}
throw;
}
finally
{
if (tran != null)
{
tran.Dispose();
}
if (!IsTransactionInProgress())
{
cn.Dispose();
}
}
}
/// <summary>
/// Removes a role from the data source for the configured applicationName.
/// </summary>
/// <param name="roleName">The name of the role to delete.</param>
/// <param name="throwOnPopulatedRole">If true, throw an exception if <paramref name="roleName"/> has one or more members and do not delete <paramref name="roleName"/>.</param>
/// <returns>
/// true if the role was successfully deleted; otherwise, false.
/// </returns>
public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
{
if (!RoleExists(roleName))
{
throw new ProviderException("Role does not exist.");
}
if (throwOnPopulatedRole && GetUsersInRole(roleName).Length > 0)
{
throw new ProviderException("Cannot delete a populated role.");
}
SQLiteTransaction tran = null;
SQLiteConnection cn = GetDBConnectionForRole();
try
{
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
if (!IsTransactionInProgress())
{
tran = cn.BeginTransaction();
}
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "DELETE FROM " + USERS_IN_ROLES_TB_NAME + " WHERE (RoleId IN"
+ " (SELECT RoleId FROM " + ROLE_TB_NAME + " WHERE LoweredRoleName = $RoleName))";
cmd.Parameters.AddWithValue("$RoleName", roleName.ToLowerInvariant());
cmd.ExecuteNonQuery();
}
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "DELETE FROM " + ROLE_TB_NAME + " WHERE LoweredRoleName = $RoleName AND ApplicationId = $ApplicationId";
cmd.Parameters.AddWithValue("$RoleName", roleName.ToLowerInvariant());
cmd.Parameters.AddWithValue("$ApplicationId", _applicationId);
cmd.ExecuteNonQuery();
}
// Commit the transaction if it's the one we created in this method.
if (tran != null)
{
tran.Commit();
}
}
catch
{
if (tran != null)
{
tran.Rollback();
}
throw;
}
finally
{
if (tran != null)
{
tran.Dispose();
}
if (!IsTransactionInProgress())
{
cn.Dispose();
}
}
return(true);
}
