protected override void Dispose(bool disposing)
{
if (!disposed)
{
if (disposing)
{
Transaction.Dispose();
}
disposed = true;
}
}
/// <summary>
/// 关闭事务
/// </summary>
/// <param name="tran">需要关闭的事务对象</param>
public static void CloseTransaction(DbTransaction tran)
{
if (tran == null)
{
return;
}
CloseConnection(tran.Connection);
tran.Dispose();
tran = null;
}
/// <summary>
///
/// </summary>
/// <param name="DbFilePath">数据库相对URL</param>
/// <param name="ID"></param>
/// <returns></returns>
public static DataTable GetLogData(string DbFilePath, string ID)
{
string DbPath = LogDbPathRoot + "\\" + DbFilePath;
SQLiteConnection conn = new SQLiteConnection("Data Source=" + DbPath + ";Pooling=true;Max Pool Size=100;");
conn.Open();
string sql = @"SELECT ID,Time,Level,Type,UserID,UserName,UserIP,Message,Data FROM Logs where ID=@ID";
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
System.Data.Common.DbTransaction transaction = cmd.Connection.BeginTransaction();
try
{
cmd.Parameters.Add(new SQLiteParameter("@ID", ID));
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
DataTable data = new DataTable();
adapter.Fill(data);
transaction.Commit();
adapter.Dispose();
return(data);
}
catch (Exception exp)
{
transaction.Rollback();
throw exp;
}
finally
{
transaction.Dispose();
cmd.Connection.Close();
}
}
}
public bool InsertEmployee(FEPV_Employees_Data emp)
{
System.Data.Common.DbTransaction tran = gateFEPVOA.BeginTransaction();
try
{
string[] Columns = new string[] { "[EmployeeID]", "[EmployeeName]", "[EmployeeName_Eng]", "[CostCenter]", "[CardNo]", "[State]", "[DepartmentName_EN]"
, "[DepartmentName_CN]", "[Email]", "[PositionName]", "[JoinDate]", "[Gender]", "[CompanyShortName]", "[CompanyFullName]", "[CompanyCode]"
, "[LastUpdateDate]", "[Workplace]", "[NumberOfUpdate]", "[Birthday]" };
object[] Values = new object[] { emp.EmployeeID, emp.EmployeeName, emp.EmployeeName_Eng, emp.CostCenter, emp.CardNo, emp.State, emp.DepartmentName_EN
, emp.DepartmentName_CN, emp.Email, emp.PositionName, emp.JoinDate, emp.Gender, emp.CompanyShortName, emp.CompanyFullName, emp.CompanyCode
, emp.LastUpdateDate, emp.Workplace, emp.NumberOfUpdate, emp.Birthday };
gateFEPVOA.DbHelper.Insert("FEPV_Employees_Data", Columns, Values, tran, "EmployeeID");
tran.Commit();
tran.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
tran.Rollback();
throw new Exception(ex.Message);
}
return(true);
}
/// <summary>
/// Closes the connection.
/// </summary>
/// <param name="tran">The tran.</param>
public void CloseConnection(DbTransaction tran)
{
if (tran.Connection != null)
{
CloseConnection(tran.Connection);
tran.Dispose();
}
}
/// <summary>
///
/// </summary>
/// <param name="DbFilePath">数据库相对URL</param>
/// <returns></returns>
public static DataTable GetLogs(string DbFilePath
, string bTime = "", string eTime = "", string Type = "", string UserID = ""
, string UserName = "", string UserIP = "", string Message = "", string Data = ""
)
{
string DbPath = LogDbPathRoot + "\\" + DbFilePath;
SQLiteConnection conn = new SQLiteConnection("Data Source=" + DbPath + ";Pooling=true;Max Pool Size=100;");
conn.Open();
string sql = @"SELECT ID,Time,Level,Type,UserID,UserName,UserIP,Message FROM Logs";
List <string> whereList = new List <string>();
List <SQLiteParameter> parList = new List <SQLiteParameter>();
#region where par
if (!string.IsNullOrEmpty(bTime))
{
whereList.Add(@" Time >= @bTime ");
parList.Add(new SQLiteParameter("@bTime", bTime));
}
if (!string.IsNullOrEmpty(eTime))
{
whereList.Add(@" Time <= @eTime ");
parList.Add(new SQLiteParameter("@eTime", eTime));
}
if (!string.IsNullOrEmpty(Type))
{
whereList.Add(@" Type like '%'||@Type||'%' ");
parList.Add(new SQLiteParameter("@Type", Type));
}
if (!string.IsNullOrEmpty(UserID))
{
whereList.Add(@" UserID=@UserID ");
parList.Add(new SQLiteParameter("@UserID", UserID));
}
if (!string.IsNullOrEmpty(UserName))
{
whereList.Add(@" UserName like '%'||@UserName||'%' ");
parList.Add(new SQLiteParameter("@UserName", UserName));
}
if (!string.IsNullOrEmpty(UserIP))
{
whereList.Add(@" UserIP like '%'||@UserIP||'%' ");
parList.Add(new SQLiteParameter("@UserIP", UserIP));
}
if (!string.IsNullOrEmpty(Message))
{
whereList.Add(@" Message like '%'||@Message||'%' ");
parList.Add(new SQLiteParameter("@Message", Message));
}
if (!string.IsNullOrEmpty(Data))
{
whereList.Add(@" Data like '%'||@Data||'%' ");
parList.Add(new SQLiteParameter("@Data", Data));
}
#endregion
if (whereList.Count > 0)
{
sql += @" where " + string.Join(" and ", whereList);
}
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
System.Data.Common.DbTransaction transaction = cmd.Connection.BeginTransaction();
try
{
if (parList.Count > 0)
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(parList.ToArray());
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
DataTable data = new DataTable();
adapter.Fill(data);
transaction.Commit();
adapter.Dispose();
return(data);
}
catch (Exception exp)
{
transaction.Rollback();
throw exp;
}
finally
{
transaction.Dispose();
cmd.Connection.Close();
}
}
}
/// <summary>
/// 执行并加入事务
/// </summary>
///
public void Commit()
{
if (_multiSqls.Count > 0)
{
using (DbConnection currentConnet = DbHelper.CreateConnection())
{
DbHelper.OpenCon(currentConnet);
DbCommand command = DbHelper.CreateCommand(currentConnet);
_currentTransaction = command.Connection.BeginTransaction(IsolationLevel.Serializable);
command.Transaction = _currentTransaction;
foreach (string item in _multiSqls)
{
command.Parameters.Clear();
command.CommandText = item ;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
if (_currentTransaction != null)
{
_currentTransaction.Commit();
_currentTransaction.Dispose();
_currentTransaction = null;
}
DbHelper.CloseCon(currentConnet);
}
}
else
{
using (DbConnection currentConnet = DbHelper.CreateConnection())
{
DbHelper.OpenCon(currentConnet);
DbCommand command = DbHelper.CreateCommand(currentConnet);
_currentTransaction = command.Connection.BeginTransaction();
command.Transaction = _currentTransaction;
foreach (DataBaseAction item in _multiActions)
{
command.Parameters.Clear();
item.DbHelper.MyDbCommand = command;
command.CommandText = item.CreateSql(item.CurrentOperate);
foreach (DataParameter itemparmeters in item.Parameters)
{
DbParameter newParameter = command.CreateParameter();
newParameter.ParameterName = itemparmeters.ParameterName;
newParameter.Value = itemparmeters.Value;
newParameter.DbType = itemparmeters.DbType;
if (itemparmeters.Size > -1)
{
newParameter.Size = itemparmeters.Size;
}
newParameter.Direction = itemparmeters.Direction;
command.Parameters.Add(newParameter);
}
command.ExecuteNonQuery();
}
if (_currentTransaction != null)
{
_currentTransaction.Commit();
_currentTransaction.Dispose();
_currentTransaction = null;
}
DbHelper.CloseCon(currentConnet);
}
}
}
public void CloseConnection(DbTransaction tran)
{
if (tran != null &
tran.Connection != null &&
tran.Connection.State != ConnectionState.Closed)
{
CloseConnection(tran.Connection);
tran.Dispose();
}
}
/// <summary>
/// Creates a new user account in both MembershipReboot database and in the MapHive meta database;
/// sends out a confirmation email if email account and template are provided
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="TAccount"></typeparam>
/// <param name="userAccountService"></param>
/// <param name="dbCtx"></param>
/// <param name="emailAccount"></param>
/// <param name="emailTemplate"></param>
/// <returns></returns>
protected internal virtual async Task <T> CreateAsync <T, TAccount>(DbContext dbCtx, UserAccountService <TAccount> userAccountService, IEmailAccount emailAccount = null, IEmailTemplate emailTemplate = null)
where T : MapHiveUserBase
where TAccount : RelationalUserAccount
{
T output;
//need to validate the model first
await ValidateAsync(dbCtx);
//make sure the email is ALWAYS lower case
Email = Email.ToLower();
//check if the email is already used or not; throw validation feedback exception if so
//Note - could do it in the mh meta, but both dbs must be in sync anyway
var emailInUse = userAccountService.GetByEmail(Email) != null;
if (emailInUse)
{
throw Validation.Utils.GenerateValidationFailedException(nameof(Email), ValidationErrors.EmailInUse);
}
//user account exists in two places - mbr and mh databases. Therefore need to handle them both properly wrapped into transactions
DbContext mbrDbCtx = GetMembershipRebootDbCtx(userAccountService);
System.Data.Common.DbTransaction mbrTrans = null;
System.Data.Common.DbTransaction mhTrans = null;
//since this method wraps the op on 2 dbs into transactions, it must handle connections manually and take care of closing it aftwerwards
//it is therefore required to clone contexts with independent conns so the base contexts can be reused
var clonedMhDbCtx = dbCtx.Clone(contextOwnsConnection: false);
var clonedMbrDbCtx = mbrDbCtx.Clone(false);
try
{
//open the connections as otherwise will not be able to begin transaction
await clonedMbrDbCtx.Database.Connection.OpenAsync();
await clonedMhDbCtx.Database.Connection.OpenAsync();
//begin the transaction and set the transaction object back on the db context so it uses it
mbrTrans = clonedMbrDbCtx.Database.Connection.BeginTransaction();
clonedMbrDbCtx.Database.UseTransaction(mbrTrans);
mhTrans = clonedMhDbCtx.Database.Connection.BeginTransaction();
clonedMhDbCtx.Database.UseTransaction(mhTrans);
//first create a membership reboot account
//wire up evt too, to intercept what mbr is trying to say...
AccountCreatedEvent <TAccount> e = null;
userAccountService.Configuration.AddEventHandler(new MembershipRebootEventHandlers.AccountCreatedEventHandler <TAccount>(
(evt) =>
{
e = evt;
}));
var newMbrAccount = userAccountService.CreateAccount(this.Email, Cartomatic.Utils.Crypto.Generator.GenerateRandomString(10), this.Email);
//so can next pass some data to the mh meta user object
this.Uuid = newMbrAccount.ID;
//mbr work done, so can create the user within the mh metadata db
output = await base.CreateAsync <T>(clonedMhDbCtx);
//looks like we're good to go, so can commit
mbrTrans.Commit();
mhTrans.Commit();
var opFeedback = new Dictionary <string, object>
{
{ nameof(e.InitialPassword), e.InitialPassword },
{ nameof(e.VerificationKey), e.VerificationKey }
};
//if email related objects have been provided, send the account created email
if (emailAccount != null && emailTemplate != null)
{
EmailSender.Send(
emailAccount, emailTemplate.Prepare(opFeedback), Email
);
}
//finally the user created event
UserCreated?.Invoke(
this,
new Events.OpFeedbackEventArgs
{
OperationFeedback = opFeedback
}
);
}
catch (Exception ex)
{
mbrTrans?.Rollback();
mhTrans?.Rollback();
throw Validation.Utils.GenerateValidationFailedException(ex);
}
finally
{
//try to close the connections as they were opened manually and therefore may not have been closed!
clonedMhDbCtx.Database.Connection.CloseConnection(dispose: true);
clonedMbrDbCtx.Database.Connection.CloseConnection(dispose: true);
mbrTrans?.Dispose();
mhTrans?.Dispose();
}
return(output);
}
public bool ExecuteScripts(string connectionString, string providerType, string filePath, ref string expmsg,ref string experror, bool isCommit)
{
bool result = true;
DbProviderFactory provider = GetProviderFactory(providerType);
var connection = provider.CreateConnection();
string statusMsg = "";
try
{
olderBatch = ScriptUtility.GetBatchNumber(connectionString, out olderBatchExecutedOn, false, 0,
providerType);
connection.ConnectionString = connectionString;
connection.Open();
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
result = ExecuteScript(filePath, ref statusMsg);
DateTime now = DateTime.MinValue;
fromBatch = ScriptUtility.GetBatchNumber(connectionString, out now, true, olderBatch, providerType);
lastBatch = ScriptUtility.GetBatchNumber(connectionString, out now, false, olderBatch, providerType);
}
catch (Exception ex)
{
msg = ex.Message;
errormsg=ex.InnerException.Message;
result = false;
}
finally
{
if (transaction != null)
{
if (isCommit)
{
transaction.Commit();
}
else
transaction.Rollback();
}
connection.Close();
transaction.Dispose();
}
expmsg = msg;
experror = string.Format("{0}{1}", errormsg, statusMsg);
return result;
}
protected void DisposeTransaction(DbTransaction transaction)
{
if (transaction != null)
transaction.Dispose();
}
