public void Dispose()
{
_dbConnection?.Dispose();
_dbTransaction?.Dispose();
GC.SuppressFinalize(this);
}
public IDisposable?TryAcquire <TLockCookie>(int timeoutMillis, ISqlSynchronizationStrategy <TLockCookie> strategy, IDisposable?contextHandle)
where TLockCookie : class
{
if (contextHandle != null)
{
return(this.CreateContextLock(contextHandle).TryAcquire(timeoutMillis, strategy, contextHandle: null));
}
IDisposable? result = null;
var connection = SqlHelpers.CreateConnection(this.connectionString);
DbTransaction?transaction = null;
try
{
connection.Open();
// when creating a transaction, the isolation level doesn't matter, since we're using sp_getapplock
transaction = connection.BeginTransaction();
var lockCookie = strategy.TryAcquire(transaction, this.lockName, timeoutMillis);
if (lockCookie != null)
{
result = new LockScope(transaction);
}
}
finally
{
// if we fail to acquire or throw, make sure to clean up
if (result == null)
{
transaction?.Dispose();
connection.Dispose();
}
}
return(result);
}
/// <summary>
/// Executes pre-migration scripts.
/// </summary>
/// <returns>True if pre-migrations were executed, otherwise - false.</returns>
/// <exception cref="MigrationException"></exception>
private async Task <bool> ExecutePreMigrationScriptsAsync(CancellationToken token = default)
{
var desiredMigrations = _preMigrations
.OrderBy(x => x.Version)
.ToArray();
if (desiredMigrations.Length == 0)
{
return(false);
}
foreach (var migration in desiredMigrations)
{
token.ThrowIfCancellationRequested();
DbTransaction?transaction = null;
await _dbProvider.CloseConnectionAsync();
await _dbProvider.OpenConnectionAsync(token);
try
{
_logger?.LogInformation(
$"Executing pre-migration script {migration.Version} (\"{migration.Comment}\") for database \"{_dbProvider.DbName}\"...");
if (migration.IsTransactionRequired)
{
transaction = _dbProvider.BeginTransaction();
}
else
{
_logger?.LogWarning($"Transaction is disabled for pre-migration {migration.Version}");
}
await migration.UpgradeAsync(transaction, token);
transaction?.Commit();
_logger?.LogInformation(
$"Executing pre-migration script {migration.Version} for database \"{_dbProvider.DbName}\" completed.");
}
catch (Exception e)
{
throw new MigrationException(
MigrationError.MigratingError,
$"Error while executing pre-migration to {migration.Version} for database \"{_dbProvider.DbName}\": {e.Message}",
e);
}
finally
{
transaction?.Dispose();
}
}
return(true);
}
public void Dispose()
{
try
{
connection?.Dispose();
}
catch
{
// ignored
}
try
{
transaction?.Dispose();
}
catch
{
// ignored
}
}
private void DisposeofTransaction()
{
TransactionInstance?.Dispose();
TransactionInstance = null;
}
public void Dispose()
{
_connection.Dispose();
_transaction?.Dispose();
}
//更新BLOB字段值
public bool UpdateBLOBFieldValue(string UpdateTableName, string UpdateBLOBFieldName, object UpdateBLOBFdValue, string WhereClause)
{
bool rbc = false;
DbConnection dbconn = null;
DbTransaction dbtrans = null;
DbCommand dbCmd1 = null;
try
{
dbconn = this.GetDbConnection();//
dbconn.ConnectionString = this.GetConnectionString;
if (dbconn.State != ConnectionState.Open)
{
dbconn.Open();
}
dbtrans = dbconn.BeginTransaction();
string PrixChar = this.ParameterChar.ToString();// PrixChar;
string x = "update " + UpdateTableName + " set " + UpdateBLOBFieldName + "=" + PrixChar + "Img where " + WhereClause;
byte[] byteArray = UpdateBLOBFdValue as byte[];
//
dbCmd1 = dbconn.CreateCommand();
dbCmd1.CommandText = x;
dbCmd1.CommandType = CommandType.Text;
dbCmd1.Connection = dbconn;
dbCmd1.Transaction = dbtrans;
//
DbParameter dbparam = this.CreateParameter("" + PrixChar + "Img", byteArray);
dbparam.Direction = ParameterDirection.Input;
dbparam.DbType = System.Data.DbType.Binary;
dbparam.Value = byteArray;
dbparam.SourceColumn = UpdateBLOBFieldName;
dbCmd1.Parameters.Add(dbparam);
if (dbCmd1.ExecuteNonQuery() > 0)
{
rbc = true;
dbtrans.Commit();
}
}
catch (Exception ee)
{
if (dbtrans != null)
{
dbtrans.Rollback();
}
rbc = false;
}
finally
{
if (dbCmd1 != null)
{
dbCmd1.Dispose();
dbCmd1 = null;
}
//----
if (dbtrans != null)
{
dbtrans.Dispose();
dbtrans = null;
}
if (dbconn != null)
{
dbconn.Dispose();
dbconn = null;
}
}
return(rbc);
}
/// <summary>
/// Disposes the.
/// </summary>
public void Dispose()
{
dbTransaction?.Dispose();
}
public void Dispose()
{
DbTransaction?.Dispose();
_dbConnection?.Close();
}
public void Dispose()
{
_tx?.Dispose();
}
private static long StoreNewEntries(SectorCache cache, string tablepostfix = "", // set to add on text to table names to redirect to another table
StreamWriter sw = null
)
{
////////////////////////////////////////////////////////////// push all new data to the db without any selects
return(SystemsDatabase.Instance.ExecuteWithDatabase(func: db =>
{
long updates = 0;
DbTransaction txn = null;
DbCommand replaceSectorCmd = null;
DbCommand replaceSysCmd = null;
DbCommand replaceNameCmd = null;
try
{
var cn = db.Connection;
txn = cn.BeginTransaction();
replaceSectorCmd = cn.CreateReplace("Sectors" + tablepostfix, new string[] { "name", "gridid", "id" }, new DbType[] { DbType.String, DbType.Int32, DbType.Int64 }, txn);
replaceSysCmd = cn.CreateReplace("Systems" + tablepostfix, new string[] { "sectorid", "nameid", "x", "y", "z", "edsmid" },
new DbType[] { DbType.Int64, DbType.Int64, DbType.Int32, DbType.Int32, DbType.Int32, DbType.Int64 }, txn);
replaceNameCmd = cn.CreateReplace("Names" + tablepostfix, new string[] { "name", "id" }, new DbType[] { DbType.String, DbType.Int64 }, txn);
foreach (var kvp in cache.SectorIDCache) // all sectors cached, id is unique so its got all sectors
{
Sector t = kvp.Value;
if (t.insertsec) // if we have been told to insert the sector, do it
{
replaceSectorCmd.Parameters[0].Value = t.Name; // make a new one so we can get the ID
replaceSectorCmd.Parameters[1].Value = t.GId;
replaceSectorCmd.Parameters[2].Value = t.Id; // and we insert with ID, managed by us, and replace in case there are any repeat problems (which there should not be)
replaceSectorCmd.ExecuteNonQuery();
//System.Diagnostics.Debug.WriteLine("Written sector " + t.GId + " " +t.Name);
t.insertsec = false;
}
if (t.edsmdatalist != null) // if updated..
{
#if DEBUG
t.edsmdatalist.Sort(delegate(TableWriteData left, TableWriteData right) { return left.edsm.id.CompareTo(right.edsm.id); });
#endif
foreach (var data in t.edsmdatalist) // now write the star list in this sector
{
try
{
if (data.classifier.IsNamed) // if its a named entry, we need a name
{
data.classifier.NameIdNumeric = data.edsm.id; // name is the edsm id
replaceNameCmd.Parameters[0].Value = data.classifier.StarName; // insert a new name
replaceNameCmd.Parameters[1].Value = data.edsm.id; // we use edsmid as the nameid, and use replace to ensure that if a prev one is there, its replaced
replaceNameCmd.ExecuteNonQuery();
// System.Diagnostics.Debug.WriteLine("Make name " + data.classifier.NameIdNumeric);
}
replaceSysCmd.Parameters[0].Value = t.Id;
replaceSysCmd.Parameters[1].Value = data.classifier.ID;
replaceSysCmd.Parameters[2].Value = data.edsm.x;
replaceSysCmd.Parameters[3].Value = data.edsm.y;
replaceSysCmd.Parameters[4].Value = data.edsm.z;
replaceSysCmd.Parameters[5].Value = data.edsm.id; // in the event a new entry has the same edsmid, the system table edsmid is replace with new data
replaceSysCmd.ExecuteNonQuery();
if (sw != null)
{
sw.WriteLine(data.edsm.name + " " + data.edsm.x + "," + data.edsm.y + "," + data.edsm.z + ", EDSM:" + data.edsm.id + " Grid:" + data.gridid);
}
updates++;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("general exception during insert - ignoring " + ex.ToString());
}
}
}
t.edsmdatalist = null; // and delete back
}
txn.Commit();
return updates;
}
finally
{
replaceSectorCmd?.Dispose();
replaceSysCmd?.Dispose();
replaceNameCmd?.Dispose();
txn?.Dispose();
}
}, warnthreshold: 5000));
}
private Int64 UpdateTran(DMSFileTrackingEntity dMSFileTrackingEntity, String filterExpression, DatabaseOperationType option)
{
long returnCode = -99;
const string SP = "dbo.DMSFileTracking_SET";
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand cmd = db.GetStoredProcCommand(SP))
{
AddOptionParameter(cmd, option, db);
AddOutputParameter(cmd, db);
AddFilterExpressionParameter(cmd, filterExpression, db);
db.AddInParameter(cmd, "@FileTrackingID", DbType.Int64, dMSFileTrackingEntity.FileTrackingID);
db.AddInParameter(cmd, "@FileNo", DbType.String, dMSFileTrackingEntity.FileNo);
db.AddInParameter(cmd, "@FileName", DbType.String, dMSFileTrackingEntity.FileName);
db.AddInParameter(cmd, "@DepartmentID", DbType.Int64, dMSFileTrackingEntity.DepartmentID);
db.AddInParameter(cmd, "@CreatedByEmployeeID", DbType.Int64, dMSFileTrackingEntity.CreatedByEmployeeID);
db.AddInParameter(cmd, "@CreatedDateTime", DbType.DateTime, dMSFileTrackingEntity.CreatedDateTime);
db.AddInParameter(cmd, "@FileParmanentPlace", DbType.String, dMSFileTrackingEntity.FileParmanentPlace);
db.AddInParameter(cmd, "@FileCurrentPlace", DbType.String, dMSFileTrackingEntity.FileCurrentPlace);
db.AddInParameter(cmd, "@FileType", DbType.String, dMSFileTrackingEntity.FileType);
db.AddInParameter(cmd, "@SoftFileName", DbType.String, dMSFileTrackingEntity.SoftFileName);
db.AddInParameter(cmd, "@Extension", DbType.String, dMSFileTrackingEntity.Extension);
db.AddInParameter(cmd, "@Path", DbType.String, dMSFileTrackingEntity.Path);
db.AddInParameter(cmd, "@OriginalFileName", DbType.String, dMSFileTrackingEntity.OriginalFileName);
db.AddInParameter(cmd, "@CurrentFileName", DbType.String, dMSFileTrackingEntity.CurrentFileName);
DbConnection connection = db.CreateConnection();
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
using (IDataReader reader = db.ExecuteReader(cmd, transaction))
{
returnCode = GetReturnCodeFromParameter(cmd);
}
if (returnCode > 0)
{
transaction.Commit();
}
else
{
throw new ArgumentException("Error Code." + returnCode.ToString());
}
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
transaction.Dispose();
connection.Close();
connection = null;
}
}
return(returnCode);
}
protected override void DoCommit()
{
_transaction.Commit();
_transaction.Dispose();
_dbConnection.Close();
}
private Int64 UpdateTran(HREmployeeDrivingLicenseInformationEntity hREmployeeDrivingLicenseInformationEntity, String filterExpression, DatabaseOperationType option)
{
long returnCode = -99;
const string SP = "dbo.HREmployeeDrivingLicenseInformation_SET";
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand cmd = db.GetStoredProcCommand(SP))
{
AddOptionParameter(cmd, option, db);
AddOutputParameter(cmd, db);
AddFilterExpressionParameter(cmd, filterExpression, db);
db.AddInParameter(cmd, "@EmployeeDrivingLicenseInformationID", DbType.Int64, hREmployeeDrivingLicenseInformationEntity.EmployeeDrivingLicenseInformationID);
db.AddInParameter(cmd, "@EmployeeID", DbType.Int64, hREmployeeDrivingLicenseInformationEntity.EmployeeID);
db.AddInParameter(cmd, "@DrivingLicenseCategoryID", DbType.Int64, hREmployeeDrivingLicenseInformationEntity.DrivingLicenseCategoryID);
db.AddInParameter(cmd, "@LicenseNumber", DbType.String, hREmployeeDrivingLicenseInformationEntity.LicenseNumber);
db.AddInParameter(cmd, "@CountryID", DbType.Int64, hREmployeeDrivingLicenseInformationEntity.CountryID);
db.AddInParameter(cmd, "@CityName", DbType.String, hREmployeeDrivingLicenseInformationEntity.CityName);
db.AddInParameter(cmd, "@IssuedDate", DbType.DateTime, hREmployeeDrivingLicenseInformationEntity.IssuedDate);
db.AddInParameter(cmd, "@ExpiryDate", DbType.DateTime, hREmployeeDrivingLicenseInformationEntity.ExpiryDate);
db.AddInParameter(cmd, "@RenewDate", DbType.DateTime, hREmployeeDrivingLicenseInformationEntity.RenewDate);
db.AddInParameter(cmd, "@IsDefaultLicense", DbType.Boolean, hREmployeeDrivingLicenseInformationEntity.IsDefaultLicense);
db.AddInParameter(cmd, "@IssueAuthority", DbType.String, hREmployeeDrivingLicenseInformationEntity.IssueAuthority);
db.AddInParameter(cmd, "@Remarks", DbType.String, hREmployeeDrivingLicenseInformationEntity.Remarks);
DbConnection connection = db.CreateConnection();
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
using (IDataReader reader = db.ExecuteReader(cmd, transaction))
{
returnCode = GetReturnCodeFromParameter(cmd);
}
if (returnCode > 0)
{
transaction.Commit();
}
else
{
throw new ArgumentException("Error Code." + returnCode.ToString());
}
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
transaction.Dispose();
connection.Close();
connection = null;
}
}
return(returnCode);
}
long IALRD_DistrictInfoDao.SaveList(IList <ALRD_DistrictInfoEntity> list)
{
long returnCode = -99;
const string SPInsert = "alrd_districtinfo_Insert";
const string SPUpdate = "alrd_districtinfo_Update";
const string SPDelete = "alrd_districtinfo_Delete";
Database db = DatabaseFactory.CreateDatabase();
DbConnection connection = db.CreateConnection();
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
IList <ALRD_DistrictInfoEntity> listAdded = new List <ALRD_DistrictInfoEntity>();
IList <ALRD_DistrictInfoEntity> listUpdated = new List <ALRD_DistrictInfoEntity>();
IList <ALRD_DistrictInfoEntity> listDeleted = new List <ALRD_DistrictInfoEntity>();
foreach (ALRD_DistrictInfoEntity item in list)
{
if (item.CurrentState == SH.Entity.BaseEntity.EntityState.Added)
{
listAdded.Add(item);
}
}
foreach (ALRD_DistrictInfoEntity item in list)
{
if (item.CurrentState == SH.Entity.BaseEntity.EntityState.Changed)
{
listUpdated.Add(item);
}
}
foreach (ALRD_DistrictInfoEntity item in list)
{
if (item.CurrentState == SH.Entity.BaseEntity.EntityState.Deleted)
{
listDeleted.Add(item);
}
}
if (listDeleted.Count > 0)
{
foreach (ALRD_DistrictInfoEntity entity in listDeleted)
{
using (DbCommand cmd = db.GetStoredProcCommand(SPDelete))
{
FillParameters(entity, cmd, db, true);
AddOutputParameter(cmd, db);
returnCode = db.ExecuteNonQuery(cmd, transaction);
if (returnCode < 0)
{
throw new ArgumentException("Error in transaction.");
}
}
}
}
if (listUpdated.Count > 0)
{
foreach (ALRD_DistrictInfoEntity entity in listUpdated)
{
using (DbCommand cmd = db.GetStoredProcCommand(SPUpdate))
{
FillParameters(entity, cmd, db);
AddOutputParameter(cmd, db);
returnCode = db.ExecuteNonQuery(cmd, transaction);
if (returnCode < 0)
{
throw new ArgumentException("Error in transaction.");
}
}
}
}
if (listAdded.Count > 0)
{
foreach (ALRD_DistrictInfoEntity entity in listAdded)
{
using (DbCommand cmd = db.GetStoredProcCommand(SPInsert))
{
FillParameters(entity, cmd, db);
AddOutputParameter(cmd, db);
returnCode = db.ExecuteNonQuery(cmd, transaction);
if (returnCode < 0)
{
throw new ArgumentException("Error in transaction.");
}
}
}
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw GetDataAccessException(ex, SourceOfException("SaveList"));
}
finally
{
transaction.Dispose();
connection.Close();
connection = null;
}
return(returnCode);
}
private Int64 UpdateTran(CMNDashboardItemEntity cMNDashboardItemEntity, String filterExpression, DatabaseOperationType option)
{
long returnCode = -99;
const string SP = "dbo.CMNDashboardItem_SET";
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand cmd = db.GetStoredProcCommand(SP))
{
AddOptionParameter(cmd, option, db);
AddOutputParameter(cmd, db);
AddFilterExpressionParameter(cmd, filterExpression, db);
db.AddInParameter(cmd, "@DashboardItemID", DbType.Int64, cMNDashboardItemEntity.DashboardItemID);
db.AddInParameter(cmd, "@ParentDashboardItemID", DbType.Int64, cMNDashboardItemEntity.ParentDashboardItemID);
db.AddInParameter(cmd, "@Title", DbType.String, cMNDashboardItemEntity.Title);
db.AddInParameter(cmd, "@TittleColor", DbType.String, cMNDashboardItemEntity.TittleColor);
db.AddInParameter(cmd, "@Description", DbType.String, cMNDashboardItemEntity.Description);
db.AddInParameter(cmd, "@DescriptionColor", DbType.String, cMNDashboardItemEntity.DescriptionColor);
db.AddInParameter(cmd, "@ImageUrl", DbType.String, cMNDashboardItemEntity.ImageUrl);
db.AddInParameter(cmd, "@HoverImageUrl", DbType.String, cMNDashboardItemEntity.HoverImageUrl);
db.AddInParameter(cmd, "@NavigationUrl", DbType.String, cMNDashboardItemEntity.NavigationUrl);
db.AddInParameter(cmd, "@IsPopup", DbType.Boolean, cMNDashboardItemEntity.IsPopup);
db.AddInParameter(cmd, "@HoverMessage", DbType.String, cMNDashboardItemEntity.HoverMessage);
db.AddInParameter(cmd, "@SequenceID", DbType.Int32, cMNDashboardItemEntity.SequenceID);
db.AddInParameter(cmd, "@ImageWidth", DbType.Int32, cMNDashboardItemEntity.ImageWidth);
db.AddInParameter(cmd, "@ImageHeight", DbType.Int32, cMNDashboardItemEntity.ImageHeight);
db.AddInParameter(cmd, "@DashboardItemWidth", DbType.Int32, cMNDashboardItemEntity.DashboardItemWidth);
db.AddInParameter(cmd, "@DashboardItemHeight", DbType.Int32, cMNDashboardItemEntity.DashboardItemHeight);
db.AddInParameter(cmd, "@IsRemoved", DbType.Boolean, cMNDashboardItemEntity.IsRemoved);
DbConnection connection = db.CreateConnection();
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
using (IDataReader reader = db.ExecuteReader(cmd, transaction))
{
returnCode = GetReturnCodeFromParameter(cmd);
}
if (returnCode > 0)
{
transaction.Commit();
}
else
{
throw new ArgumentException("Error Code." + returnCode.ToString());
}
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
transaction.Dispose();
connection.Close();
connection = null;
}
}
return(returnCode);
}
private Int64 UpdateTran(CRMLandBasicInfoEntity cRMLandBasicInfoEntity, String filterExpression, DatabaseOperationType option)
{
long returnCode = -99;
const string SP = "dbo.CRMLandBasicInfo_SET";
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand cmd = db.GetStoredProcCommand(SP))
{
AddOptionParameter(cmd, option, db);
AddOutputParameter(cmd, db);
AddFilterExpressionParameter(cmd, filterExpression, db);
db.AddInParameter(cmd, "@LandBasicInfoID", DbType.Int64, cRMLandBasicInfoEntity.LandBasicInfoID);
db.AddInParameter(cmd, "@LandOwnerTypeID", DbType.Int64, cRMLandBasicInfoEntity.LandOwnerTypeID);
db.AddInParameter(cmd, "@ContactFullName", DbType.String, cRMLandBasicInfoEntity.ContactFullName);
db.AddInParameter(cmd, "@MobileNo", DbType.String, cRMLandBasicInfoEntity.MobileNo);
db.AddInParameter(cmd, "@LandPhone", DbType.String, cRMLandBasicInfoEntity.LandPhone);
db.AddInParameter(cmd, "@PresentAddress", DbType.String, cRMLandBasicInfoEntity.PresentAddress);
db.AddInParameter(cmd, "@Email", DbType.String, cRMLandBasicInfoEntity.Email);
db.AddInParameter(cmd, "@InterestedLandLocationID", DbType.Int64, cRMLandBasicInfoEntity.InterestedLandLocationID);
db.AddInParameter(cmd, "@CityID", DbType.Int64, cRMLandBasicInfoEntity.CityID);
db.AddInParameter(cmd, "@LandArea1", DbType.String, cRMLandBasicInfoEntity.LandArea1);
db.AddInParameter(cmd, "@AreaUnitID1", DbType.Int64, cRMLandBasicInfoEntity.AreaUnitID1);
db.AddInParameter(cmd, "@LandArea2", DbType.String, cRMLandBasicInfoEntity.LandArea2);
db.AddInParameter(cmd, "@AreaUnitID2", DbType.Int64, cRMLandBasicInfoEntity.AreaUnitID2);
db.AddInParameter(cmd, "@PlotNumber", DbType.String, cRMLandBasicInfoEntity.PlotNumber);
db.AddInParameter(cmd, "@PlotAddress", DbType.String, cRMLandBasicInfoEntity.PlotAddress);
db.AddInParameter(cmd, "@PlotMapUrl", DbType.String, cRMLandBasicInfoEntity.PlotMapUrl);
db.AddInParameter(cmd, "@Area", DbType.String, cRMLandBasicInfoEntity.Area);
db.AddInParameter(cmd, "@Dimension", DbType.String, cRMLandBasicInfoEntity.Dimension);
db.AddInParameter(cmd, "@SpecialFeatures", DbType.String, cRMLandBasicInfoEntity.SpecialFeatures);
db.AddInParameter(cmd, "@Structure", DbType.String, cRMLandBasicInfoEntity.Structure);
db.AddInParameter(cmd, "@RAJUKDetails", DbType.String, cRMLandBasicInfoEntity.RAJUKDetails);
db.AddInParameter(cmd, "@LeadGeneratedByEmployeeID", DbType.Int64, cRMLandBasicInfoEntity.LeadGeneratedByEmployeeID);
db.AddInParameter(cmd, "@LeadManagedByOneEmployeeID", DbType.Int64, cRMLandBasicInfoEntity.LeadManagedByOneEmployeeID);
db.AddInParameter(cmd, "@LeadManagedByTwoEmployeeID", DbType.Int64, cRMLandBasicInfoEntity.LeadManagedByTwoEmployeeID);
db.AddInParameter(cmd, "@LeadConversionDate", DbType.DateTime, cRMLandBasicInfoEntity.LeadConversionDate);
db.AddInParameter(cmd, "@ReferredByEmployeeID", DbType.Int64, cRMLandBasicInfoEntity.ReferredByEmployeeID);
db.AddInParameter(cmd, "@LandOwnerOfProject", DbType.Int64, cRMLandBasicInfoEntity.LandOwnerOfProject);
db.AddInParameter(cmd, "@CreatedByEmployeeID", DbType.Int64, cRMLandBasicInfoEntity.CreatedByEmployeeID);
db.AddInParameter(cmd, "@IP", DbType.String, cRMLandBasicInfoEntity.IP);
db.AddInParameter(cmd, "@CreateDate", DbType.DateTime, cRMLandBasicInfoEntity.CreateDate);
DbConnection connection = db.CreateConnection();
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
using (IDataReader reader = db.ExecuteReader(cmd, transaction))
{
returnCode = GetReturnCodeFromParameter(cmd);
}
if (returnCode > 0)
{
transaction.Commit();
}
else
{
throw new ArgumentException("Error Code." + returnCode.ToString());
}
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
transaction.Dispose();
connection.Close();
connection = null;
}
}
return(returnCode);
}
private Int64 UpdateTran(BDOperatorEntity bDOperatorEntity, String filterExpression, DatabaseOperationType option)
{
long returnCode = -99;
const string SP = "dbo.BDOperator_SET";
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand cmd = db.GetStoredProcCommand(SP))
{
AddOptionParameter(cmd, option, db);
AddOutputParameter(cmd, db);
AddFilterExpressionParameter(cmd, filterExpression, db);
db.AddInParameter(cmd, "@OperatorID", DbType.Int64, bDOperatorEntity.OperatorID);
db.AddInParameter(cmd, "@OperatorCode", DbType.String, bDOperatorEntity.OperatorCode);
db.AddInParameter(cmd, "@OperatorName", DbType.String, bDOperatorEntity.OperatorName);
db.AddInParameter(cmd, "@Relationship", DbType.String, bDOperatorEntity.Relationship);
db.AddInParameter(cmd, "@Phone", DbType.String, bDOperatorEntity.Phone);
db.AddInParameter(cmd, "@MobileNo", DbType.String, bDOperatorEntity.MobileNo);
db.AddInParameter(cmd, "@Fax", DbType.String, bDOperatorEntity.Fax);
db.AddInParameter(cmd, "@Email", DbType.String, bDOperatorEntity.Email);
db.AddInParameter(cmd, "@WebLink", DbType.String, bDOperatorEntity.WebLink);
db.AddInParameter(cmd, "@CreateDate", DbType.DateTime, bDOperatorEntity.CreateDate);
db.AddInParameter(cmd, "@Remarks", DbType.String, bDOperatorEntity.Remarks);
db.AddInParameter(cmd, "@Reason", DbType.String, bDOperatorEntity.Reason);
db.AddInParameter(cmd, "@OperatorStatusID", DbType.Int64, bDOperatorEntity.OperatorStatusID);
db.AddInParameter(cmd, "@IsRemoved", DbType.Boolean, bDOperatorEntity.IsRemoved);
DbConnection connection = db.CreateConnection();
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
using (IDataReader reader = db.ExecuteReader(cmd, transaction))
{
returnCode = GetReturnCodeFromParameter(cmd);
}
if (returnCode > 0)
{
transaction.Commit();
}
else
{
throw new ArgumentException("Error Code." + returnCode.ToString());
}
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
transaction.Dispose();
connection.Close();
connection = null;
}
}
return(returnCode);
}
public void Dispose()
{
transaction?.Dispose();
connection?.Dispose();
}
/// <summary>
/// Commit the changes during the transaction to the database
/// </summary>
public void CommitTransaction()
{
DbTransaction.Commit();
DbTransaction.Dispose();
}
private SqlServerTransactionJob DequeueUsingTransaction(string[] queues, CancellationToken cancellationToken)
{
FetchedJob fetchedJob = null;
DbTransaction transaction = null;
string fetchJobSqlTemplate =
$@"delete top (1) JQ
output DELETED.Id, DELETED.JobId, DELETED.Queue
from [{_storage.SchemaName}].JobQueue JQ with (readpast, updlock, rowlock, forceseek)
where Queue in @queues and (FetchedAt is null or FetchedAt < DATEADD(second, @timeout, GETUTCDATE()))";
var pollInterval = _options.QueuePollInterval > TimeSpan.Zero
? _options.QueuePollInterval
: TimeSpan.FromSeconds(1);
using (var cancellationEvent = cancellationToken.GetCancellationEvent())
{
do
{
cancellationToken.ThrowIfCancellationRequested();
var connection = _storage.CreateAndOpenConnection();
try
{
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
fetchedJob = connection.Query <FetchedJob>(
fetchJobSqlTemplate,
#pragma warning disable 618
new { queues = queues, timeout = _options.InvisibilityTimeout.Negate().TotalSeconds },
#pragma warning restore 618
transaction,
commandTimeout: _storage.CommandTimeout).SingleOrDefault();
if (fetchedJob != null)
{
return(new SqlServerTransactionJob(
_storage,
connection,
transaction,
fetchedJob.JobId.ToString(CultureInfo.InvariantCulture),
fetchedJob.Queue));
}
else
{
// Nothing updated, just commit the empty transaction.
transaction.Commit();
}
}
catch
{
// Check connection isn't broken first, and that transaction
// can be rolled back without throwing InvalidOperationException
// on older System.Data.SqlClient in .NET Core.
// https://github.com/HangfireIO/Hangfire/issues/1494
// https://github.com/dotnet/efcore/issues/12864
if (transaction?.Connection != null)
{
transaction.Rollback();
}
throw;
}
finally
{
if (fetchedJob == null)
{
transaction?.Dispose();
transaction = null;
_storage.ReleaseConnection(connection);
}
}
WaitHandle.WaitAny(new WaitHandle[] { cancellationEvent.WaitHandle, NewItemInQueueEvent }, pollInterval);
cancellationToken.ThrowIfCancellationRequested();
} while (true);
}
}
/// <summary>
/// Rolls back the changes to the database that were made during the transaction
/// </summary>
public void RollbackTransaction()
{
DbTransaction.Rollback();
DbTransaction.Dispose();
}
public virtual void Commit()
{
_transaction?.Commit();
_transaction?.Dispose();
_transaction = null;
}
public void Add(Order order)
{
using (var connection = _providerFactory.CreateConnection())
using (var command = connection.CreateCommand())
{
DbTransaction transaction = null;
try
{
connection.ConnectionString = _connectionString;
connection.Open();
transaction = connection.BeginTransaction();
command.Transaction = transaction;
command.CommandText = $"insert into [Order] values(@userId, @date, @totalSum, @status)";
DbParameter userIdParametr = command.CreateParameter();
userIdParametr.ParameterName = "@userId";
userIdParametr.DbType = System.Data.DbType.Int32;
userIdParametr.Value = order.UserId;
DbParameter dateParametr = command.CreateParameter();
dateParametr.ParameterName = "@date";
dateParametr.DbType = System.Data.DbType.DateTime;
dateParametr.Value = order.DateTime;
DbParameter totalSumParametr = command.CreateParameter();
totalSumParametr.ParameterName = "@totalSum";
totalSumParametr.DbType = System.Data.DbType.Int32;
totalSumParametr.Value = order.Sum;
DbParameter statusParametr = command.CreateParameter();
statusParametr.ParameterName = "@status";
statusParametr.DbType = System.Data.DbType.Boolean;
statusParametr.Value = order.State;
command.Parameters.AddRange(new DbParameter[] { userIdParametr, totalSumParametr, statusParametr, dateParametr });
var affectedRows = command.ExecuteNonQuery();
if (affectedRows < 1)
{
throw new Exception("Вставка не удалась.");
}
transaction.Commit();
}
catch (DbException exception)
{
Console.WriteLine($"Ошибка: {exception.Message}.");
transaction?.Rollback();
throw;
}
catch (Exception exception)
{
Console.WriteLine($"Ошибка: {exception.Message}.");
throw;
}
finally
{
transaction?.Dispose();
}
}
}
/// <inheritdoc />
public virtual void ExecuteWorkInIsolation(ISessionImplementor session, IIsolatedWork work, bool transacted)
{
if (session == null)
{
throw new ArgumentNullException(nameof(session));
}
if (work == null)
{
throw new ArgumentNullException(nameof(work));
}
DbConnection connection = null;
DbTransaction trans = null;
// bool wasAutoCommit = false;
try
{
// We make an exception for SQLite and use the session's connection,
// since SQLite only allows one connection to the database.
if (session.Factory.Dialect is SQLiteDialect)
{
connection = session.Connection;
}
else
{
connection = session.Factory.ConnectionProvider.GetConnection();
}
if (transacted)
{
trans = connection.BeginTransaction();
// TODO NH: a way to read the autocommit state is needed
//if (TransactionManager.GetAutoCommit(connection))
//{
// wasAutoCommit = true;
// TransactionManager.SetAutoCommit(connection, false);
//}
}
work.DoWork(connection, trans);
if (transacted)
{
trans.Commit();
//TransactionManager.Commit(connection);
}
}
catch (Exception t)
{
using (new SessionIdLoggingContext(session.SessionId))
{
try
{
if (trans != null && connection.State != ConnectionState.Closed)
{
trans.Rollback();
}
}
catch (Exception ignore)
{
isolaterLog.Debug("Unable to rollback transaction", ignore);
}
if (t is HibernateException)
{
throw;
}
else if (t is DbException)
{
throw ADOExceptionHelper.Convert(session.Factory.SQLExceptionConverter, t,
"error performing isolated work");
}
else
{
throw new HibernateException("error performing isolated work", t);
}
}
}
finally
{
//if (transacted && wasAutoCommit)
//{
// try
// {
// // TODO NH: reset autocommit
// // TransactionManager.SetAutoCommit(connection, true);
// }
// catch (Exception)
// {
// log.Debug("was unable to reset connection back to auto-commit");
// }
//}
try
{
trans?.Dispose();
}
catch (Exception ignore)
{
isolaterLog.Warn("Unable to dispose transaction", ignore);
}
if (session.Factory.Dialect is SQLiteDialect == false)
{
session.Factory.ConnectionProvider.CloseConnection(connection);
}
}
}
private int ModifyLastPD(DbConnection tmp_con, string str_bid, string str_sid, int i_flag, double d_pd, DateTime dt_time, string str_mode)
{
DbCommand dbCommand = null;
DbTransaction dbTransaction = null;
try
{
dbCommand = tmp_con.CreateCommand();
dbTransaction = tmp_con.BeginTransaction();
dbCommand.Transaction = dbTransaction;
string text;
if (str_mode.Equals("UPDATE"))
{
text = string.Concat(new object[]
{
"update gatewaylastpd set eleflag=",
i_flag,
",pd=",
MyConvert.ToString(d_pd),
",timemark=#",
dt_time.ToString("yyyy-MM-dd HH:mm:ss"),
"# where bid='",
str_bid,
"' and sid='",
str_sid,
"' "
});
}
else
{
text = string.Concat(new object[]
{
"insert into gatewaylastpd (bid,sid,eleflag,pd,timemark) values ('",
str_bid,
"','",
str_sid,
"',",
i_flag,
",",
MyConvert.ToString(d_pd),
",#",
dt_time.ToString("yyyy-MM-dd HH:mm:ss"),
"# ) "
});
}
if (DBUrl.SERVERMODE)
{
text = text.Replace("#", "'");
}
dbCommand.CommandText = text;
dbCommand.ExecuteNonQuery();
dbCommand.Transaction.Commit();
dbTransaction.Dispose();
dbCommand.Dispose();
return(1);
}
catch (Exception ex)
{
DebugCenter.GetInstance().appendToFile("DBERROR~~~~~~~~~~~DBERROR : " + ex.Message + "\n" + ex.StackTrace);
if (dbTransaction != null)
{
try
{
dbTransaction.Dispose();
}
catch (Exception)
{
}
}
if (dbCommand != null)
{
try
{
dbCommand.Dispose();
}
catch (Exception)
{
}
}
}
return(-1);
}
public void Dispose()
{
DbTransaction?.Dispose();
DbConnection?.Dispose();
}
protected override void DbCreateDatabase(DbConnection connection, int?timeOut, StoreItemCollection storeItemCollection)
{
Check.NotNull(connection, "connection");
Check.NotNull(storeItemCollection, "storeItemCollection");
// Validate that connection is a SqlCeConnection.
ValidateConnection(connection);
// We don't support create/delete database operations inside a transaction as they can't be rolled back.
if (InTransactionScope())
{
throw ADP1.CreateDatabaseNotAllowedWithinTransaction();
}
if (_isLocalProvider)
{
var engine = new SqlCeEngine(connection.ConnectionString);
engine.CreateDatabase();
engine.Dispose();
}
else
{
try
{
Type rdpType;
// If we are working with RDP, then we will need to invoke the APIs through reflection.
var engine = RemoteProviderHelper.GetRemoteSqlCeEngine(connection.ConnectionString, out rdpType);
Debug.Assert(engine != null);
// Invoke the required method on SqlCeEngine.
var mi = rdpType.GetMethod("CreateDatabase", new[] { typeof(int?) });
Debug.Assert(mi != null);
// We will pass 'timeout' to RDP, this will be used as timeout period for connecting and executing on TDSServer.
mi.Invoke(engine, new object[] { timeOut });
}
catch (Exception e)
{
throw e.GetBaseException();
}
}
// Create the command object depending on provider.
var command = connection.CreateCommand();
// Create the command texts from StoreItemCollection.
var commandTextCollection = SqlDdlBuilder.CreateObjectsScript(storeItemCollection, false);
DbTransaction transaction = null;
try
{
// Open the connection.
connection.Open();
// Open a transaction and attach to the command.
transaction = connection.BeginTransaction();
command.Transaction = transaction;
// Execute each statement.
foreach (var text in commandTextCollection)
{
command.CommandText = text;
Interception.Dispatch.Command.NonQuery(command, new DbCommandBaseInterceptionContext());
}
// Commit the transaction.
transaction.Commit();
}
catch (Exception e)
{
if (transaction != null)
{
// Rollback the transaction.
transaction.Rollback();
}
// Throw IOE with SqlCeException embedded as inner exception.
throw new InvalidOperationException(EntityRes.GetString(EntityRes.IncompleteDatabaseCreation), e);
}
finally
{
// Close connection and cleanup objects.
if (command != null)
{
command.Dispose();
}
if (transaction != null)
{
transaction.Dispose();
}
if (connection != null)
{
connection.Close();
}
}
}
/// <summary>
/// 回收
/// </summary>
public void Dispose()
{
_innerTransaction?.Dispose();
_innerTransaction = null;
}
private async Task MigrateAsync(IReadOnlyCollection <IMigration> orderedMigrations, bool isUpgrade, MigrationPolicy policy, CancellationToken token = default)
{
if (policy == MigrationPolicy.Forbidden)
{
throw new MigrationException(MigrationError.PolicyError, $"{(isUpgrade ? "Upgrading": "Downgrading")} is forbidden due to migration policy");
}
if (orderedMigrations.Count == 0)
{
return;
}
var operationName = isUpgrade
? "Upgrade"
: "Downgrade";
foreach (var migration in orderedMigrations)
{
token.ThrowIfCancellationRequested();
DbTransaction?transaction = null;
try
{
// sometimes transactions fails without reopening connection
//todo #19: fix it later
await _dbProvider.CloseConnectionAsync();
await _dbProvider.OpenConnectionAsync(token);
if (migration.IsTransactionRequired)
{
transaction = _dbProvider.BeginTransaction();
}
else
{
_logger?.LogWarning($"Transaction is disabled for migration {migration.Version}");
}
_logger?.LogInformation($"{operationName} to {migration.Version} ({migration.Comment} for database \"{_dbProvider.DbName}\")...");
if (isUpgrade)
{
await migration.UpgradeAsync(transaction, token);
await _dbProvider.SaveAppliedMigrationVersionAsync(migration.Comment, migration.Version, token);
}
else
{
if (!(migration is IDowngradeMigration downgradableMigration))
{
throw new MigrationException(MigrationError.MigrationNotFound, $"Migration with version {migration.Version} doesn't support downgrade");
}
await downgradableMigration.DowngradeAsync(transaction, token);
await _dbProvider.DeleteAppliedMigrationVersionAsync(migration.Version, token);
}
// Commit transaction if all commands succeed, transaction will auto-rollback
// when disposed if either commands fails
transaction?.Commit();
_logger?.LogInformation($"{operationName} to {migration.Version} (database \"{_dbProvider.DbName}\") completed.");
}
catch (Exception e)
{
throw new MigrationException(
MigrationError.MigratingError,
$"Error while executing {operationName.ToLower()} migration to {migration.Version} for database \"{_dbProvider.DbName}\": {e.Message}",
e);
}
finally
{
transaction?.Dispose();
}
}
}