public override bool CommitTransaction(string transactionName = null)
{
if (conn == null)
{
return(false);
}
if (transac == null)
{
return(false);
}
transac.Commit();
transac.Dispose();
transac = null;
return(true);
}
///////////////////////////////////////////////////////////////////////////
private /* protected virtual */ void Dispose(bool disposing)
{
if (!disposed)
{
if (disposing)
{
////////////////////////////////////
// dispose managed resources here...
////////////////////////////////////
if (_transaction != null)
{
_transaction.Dispose();
_transaction = null;
}
if (_scope != null)
{
// _scope.Dispose(); // NOTE: Not "owned" by us.
_scope = null;
}
}
//////////////////////////////////////
// release unmanaged resources here...
//////////////////////////////////////
disposed = true;
}
}
public void Dispose()
{
// Dispose the System.Data.SQLite.SQLiteTransaction. If neither Commit nor Rollback was
// called before, the standard behaviour of System.Data.SQLite.SQLiteTransaction is to
// issue a Rollback during disposing.
if (_transaction != null)
{
_transaction.Dispose();
_transaction = null;
}
// Return the underlying connection to the connection pool without closing it
if (_connection != null)
{
_database.ConnectionPool.PutConnection(_connection);
_connection = 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 void CommitToDatabase()
{
NhlStatsSet changes = (NhlStatsSet)nhlStatsDatabase.GetChanges();
System.Console.Write("Commiting to SQLite database...");
transaction = connection.BeginTransaction();
playerStatsAdapter.Update(changes.playerstats);
goalieStatsAdapter.Update(changes.goaliestats);
teamStatsAdapter.Update(changes.teamstats);
scheduleAdapter.Update(changes.schedule);
teamsAdapter.Update(changes.teams);
transaction.Commit();
transaction.Dispose();
nhlStatsDatabase.playerstats.AcceptChanges();
nhlStatsDatabase.goaliestats.AcceptChanges();
nhlStatsDatabase.teamstats.AcceptChanges();
nhlStatsDatabase.schedule.AcceptChanges();
nhlStatsDatabase.teams.AcceptChanges();
System.Console.WriteLine("done!");
}
/// <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);
}
/// <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();
}
}
}
