private void RemoveReceiptTankDetails(OleDbTransaction tran, string receiptNo)
{
OleDbConnection conn;
if (tran != null)
{
conn = tran.Connection;
}
else
{
conn = new OleDbConnection(this.ConnectionString);
conn.Open();
}
try
{
var query = new QueryContext(null, "DELETE * FROM [ReceiptTankDetails] WHERE [ReceiptNo] = ?")
{
Connection = conn, Transaction = tran
};
query.Parameters.AddWithValue(receiptNo);
query.ExecuteNonQuery();
}
finally
{
if (tran == null)
{
conn.Dispose();
}
}
}
public void Add(IEnumerable <OilVolume> oilVolumeItems)
{
using (var conn = new OleDbConnection(this.ConnectionString))
{
var query = new QueryContext(this.ConnectionString, "INSERT INTO OilVolume (TankName, HInclination, VInclination, Height, Volume) VALUES (?, ?, ?, ?, ?)")
{
Connection = conn
};
oilVolumeItems.Each(v =>
{
query.Parameters.Clear();
query.Parameters.AddWithValue(v.TankName);
query.Parameters.AddWithValue(v.HInclination);
query.Parameters.AddWithValue(v.VInclination);
query.Parameters.AddWithValue(v.Height);
query.Parameters.AddWithValue(v.Volume);
query.ExecuteNonQuery();
});
}
if (this.allOilVolumeItems != null)
{
this.allOilVolumeItems.AddRange(oilVolumeItems);
}
}
public void Remove(string receiptNo)
{
using (var conn = new OleDbConnection(this.ConnectionString))
{
conn.Open();
using (var tran = conn.BeginTransaction())
{
try
{
this.RemoveReceiptTankDetails(tran, receiptNo);
var query = new QueryContext(null, "DELETE * FROM [Receipts] WHERE [No] = ?")
{
Connection = conn, Transaction = tran
};
query.Parameters.AddWithValue(receiptNo);
query.ExecuteNonQuery();
tran.Commit();
if (this.all != null)
{
this.all.RemoveAll(r => r.No.Equals(receiptNo));
}
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
}
}
}
private void Add(string username, SecureString password)
{
var query = new QueryContext(this.ConnectionString, "INSERT INTO [Users] ([Username], [Password]) VALUES (?, ?)");
query.Parameters.AddWithValue(username.Trim());
query.Parameters.AddWithValue(CommonHelper.SecureStringToMD5(password));
query.ExecuteNonQuery();
}
private void Update(string username, SecureString password)
{
var query = new QueryContext(this.ConnectionString, "UPDATE [Users] SET [Password] = ? WHERE [Username] = ?");
query.Parameters.AddWithValue(CommonHelper.SecureStringToMD5(password));
query.Parameters.AddWithValue(username.Trim());
query.ExecuteNonQuery();
}
private void Add(OleDbTransaction tran, KindOfGoods kindOfGoods)
{
OleDbConnection conn;
var newTran = (tran == null);
if (newTran)
{
conn = new OleDbConnection(this.ConnectionString);
conn.Open();
tran = conn.BeginTransaction();
}
else
{
conn = tran.Connection;
}
try
{
var query = new QueryContext(null, @"INSERT INTO [KindsOfGoods] ([UId], [Name]) VALUES (?, ?)")
{
Connection = conn, Transaction = tran
};
query.Parameters.AddWithValue(kindOfGoods.UId);
query.Parameters.AddWithValue(kindOfGoods.Name);
query.ExecuteNonQuery();
if (newTran)
{
tran.Commit();
}
if (this.allKindsOfGoods != null)
{
this.allKindsOfGoods.RemoveAll(k => k.UId.Equals(kindOfGoods.UId));
this.allKindsOfGoods.Add(kindOfGoods);
}
}
catch (Exception ex)
{
if (newTran)
{
tran.Rollback();
}
throw ex;
}
finally
{
if (newTran)
{
tran.Dispose();
conn.Dispose();
}
}
}
public void Add(IEnumerable <Tank> tanks)
{
using (var conn = new OleDbConnection(this.ConnectionString))
{
var query = new QueryContext(this.ConnectionString, "INSERT INTO [Tanks] ([Name], [Height]) VALUES (?, ?)")
{
Connection = conn
};
tanks.Each(t =>
{
query.Parameters.Clear();
query.Parameters.AddWithValue(t.Name);
query.Parameters.AddWithValue(t.Height);
query.ExecuteNonQuery();
});
}
if (this.allTanks != null)
{
this.allTanks.AddRange(tanks);
}
}
public void SavePipes(IEnumerable <Pipe> pipes)
{
using (var conn = new OleDbConnection(this.ConnectionString))
{
new QueryContext(null, "DELETE * FROM Pipes")
{
Connection = conn
}.ExecuteNonQuery();
var query = new QueryContext(null, "INSERT INTO Pipes (Name, Volume) VALUES (?, ?)")
{
Connection = conn
};
pipes.Each(p =>
{
query.Parameters.Clear();
query.Parameters.AddWithValue(p.Name);
query.Parameters.AddWithValue(p.Volume);
query.ExecuteNonQuery();
});
}
}
public void RemoveKindOfGoods(string uid)
{
using (var conn = new OleDbConnection(this.ConnectionString))
{
conn.Open();
using (var tran = conn.BeginTransaction())
{
try
{
if (this.IsKindOfGoodsInUsed(tran, uid))
{
throw new InvalidOperationException(String.Format("The kind [{0}] is currently in used, cannot be removed", uid));
}
var query = new QueryContext(null, "DELETE * FROM [KindsOfGoods] WHERE [UId] = ?")
{
Connection = conn, Transaction = tran
};
query.Parameters.AddWithValue(uid);
query.ExecuteNonQuery();
tran.Commit();
if (this.allKindsOfGoods != null)
{
this.allKindsOfGoods.RemoveAll(k => k.UId.Equals(uid));
}
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
}
}
}
private void Add(OleDbTransaction tran, string receiptNo, IEnumerable <ReceiptTankDetail> receiptTankDetails)
{
OleDbConnection conn;
if (tran != null)
{
conn = tran.Connection;
}
else
{
conn = new OleDbConnection(this.ConnectionString);
conn.Open();
}
try
{
var query = new QueryContext(null, @"INSERT INTO ReceiptTankDetails
(
ReceiptNo,
TankName, VolumeByHeight, Height, TemperatureOfTank,
HeightOfWater, TemperatureMeasured, DensityMeasured, DensityOfStandard,
Vcf20, Volume, VolumeOfWater, VolumeOfStandard,
Mass
)
VALUES
(
?,
?, ?, ?, ?,
?, ?, ?, ?,
?, ?, ?, ?,
?
)")
{
Connection = conn, Transaction = tran
};
receiptTankDetails.Each(d =>
{
query.Parameters.Clear();
query.Parameters.AddWithValue(receiptNo);
query.Parameters.AddWithValue(d.TankName);
query.Parameters.AddWithValue(d.VolumeByHeight);
query.Parameters.AddWithValue(d.Height);
query.Parameters.AddWithValue(d.TemperatureOfTank);
query.Parameters.AddWithValue(d.HeightOfWater);
query.Parameters.AddWithValue(d.TemperatureMeasured);
query.Parameters.AddWithValue(d.DensityMeasured);
query.Parameters.AddWithValue(d.DensityOfStandard);
query.Parameters.AddWithValue(d.Vcf20);
query.Parameters.AddWithValue(d.Volume);
query.Parameters.AddWithValue(d.VolumeOfWater);
query.Parameters.AddWithValue(d.VolumeOfStandard);
query.Parameters.AddWithValue(d.Mass);
query.ExecuteNonQuery();
});
}
finally
{
if (tran == null)
{
conn.Dispose();
}
}
}
private void Add(OleDbTransaction tran, Receipt receipt)
{
OleDbConnection conn;
var newTran = (tran == null);
if (newTran)
{
conn = new OleDbConnection(this.ConnectionString);
conn.Open();
tran = conn.BeginTransaction();
}
else
{
conn = tran.Connection;
}
try
{
var query = new QueryContext(null, @"INSERT INTO [Receipts]
(
[No],
[VesselName], [Time], [ReceiptFor], [PortOfShipment],
[PortOfDestination], [KindOfGoodsUId], [VesselStatus], [TotalOfVolumeOfStandard],
[TotalOfVolume], [TotalOfVolumeOfWater], [TotalOfMass], [TotalOfVolumeOfPipes],
[OperaterName], [AgentName], [ShipperName], [ConsignerName],
[ConsigneeName], [TimeSaved], [ReceiptType]
)
VALUES
(
?,
?, ?, ?, ?,
?, ?, ?, ?,
?, ?, ?, ?,
?, ?, ?, ?,
?, ?, ?
)")
{
Connection = conn, Transaction = tran
};
query.Parameters.AddWithValue(receipt.No);
query.Parameters.AddWithValue(receipt.VesselName);
query.Parameters.AddWithValue(receipt.Time).OleDbType = OleDbType.Date;
query.Parameters.AddWithValue(receipt.ReceiptFor);
query.Parameters.AddWithValue(receipt.PortOfShipment);
query.Parameters.AddWithValue(receipt.PortOfDestination);
query.Parameters.AddWithValue(receipt.KindOfGoods != null ? receipt.KindOfGoods.UId : null);
query.Parameters.AddWithValue(receipt.VesselStatus != null ? receipt.VesselStatus.ToString() : VesselStatus.Empty.ToString());
query.Parameters.AddWithValue(receipt.TotalOfVolumeOfStandard);
query.Parameters.AddWithValue(receipt.TotalOfVolume);
query.Parameters.AddWithValue(receipt.TotalOfVolumeOfWater);
query.Parameters.AddWithValue(receipt.TotalOfMass);
query.Parameters.AddWithValue(receipt.TotalOfVolumeOfPipes);
query.Parameters.AddWithValue(receipt.OperaterName);
query.Parameters.AddWithValue(receipt.AgentName);
query.Parameters.AddWithValue(receipt.ShipperName);
query.Parameters.AddWithValue(receipt.ConsignerName);
query.Parameters.AddWithValue(receipt.ConsigneeName);
query.Parameters.AddWithValue(DateTime.Now).OleDbType = OleDbType.Date;
query.Parameters.AddWithValue(receipt.ReceiptType.ToString());
query.ExecuteNonQuery();
this.Add(tran, receipt.No, receipt.ReceiptTankDetails);
if (newTran)
{
tran.Commit();
}
if (this.all != null)
{
this.all.RemoveAll(r => r.No.Equals(receipt.No));
this.all.Add(receipt);
}
}
catch (Exception ex)
{
if (newTran)
{
tran.Rollback();
}
throw ex;
}
finally
{
if (newTran)
{
tran.Dispose();
conn.Dispose();
}
}
}