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(); } } }