A database OOP Questions from Amitav vai

UpdateTradeACKNAKDB.cs

/*
 CREATE TABLE [tSWIFT_History_Trades](
	[Id] 		[bigint]	 IDENTITY(1,1) NOT NULL,
	[BatchId] 	[varchar](25) NOT NULL,
	[TradeId] 	[varchar](25) NOT NULL,
	[Account] 	[varchar](25) NULL,
	[TranType] 	[varchar](50) NULL,
	[TradeDate] 	[int] NULL,
	[SettleDate] 	[int] NULL,
	[Exch] 		[varchar](50) NULL,
	[DateSent] 	[datetime] NOT NULL 
CONSTRAINT [DF_tSWIFT_History_Trades_Sent_DateSent]  DEFAULT (getdate()),
	[Status] 	[varchar](20) NOT NULL,
	[AckNak] 	[varchar](7) NULL 
CONSTRAINT [DF_tSWIFT_History_Trades_AckNak]  DEFAULT ('Pending'),
	[MachineIdReceived] [varchar](50) NULL,
	[FileNameReceived] [varchar](100) NULL,
	[DateReceived] [datetime] NULL,
	[DirectoryArchivedReceived] [varchar](300) NULL,
	[Error] 		[varchar](500) NULL,
	[Price] 		[float] NULL CONSTRAINT [DF_tSWIFT_History_Trades_Price]  DEFAULT (0),
 CONSTRAINT [PK_tSWIFT_History_Trades] PRIMARY KEY NONCLUSTERED   ([Id] ASC )
GO

select count (*) from dbo.tSWIFT_History_Trades
--returns 568655
 */

class DatbaseDemo {
  public struct TradeNAK {
    public string BatchId;
    public string TradeId;
    public bool AckNakSuccess;
    public string ErrorText;
  }

  /* The number of trade acknowledgements (TradeNAKs) per batch (i.e. _lANT) can reach 2000. */

  public int UpdateTradeACKNAKDB(List<TradeNAK> _lANT, string strFileNameReceived, string strDirectoryArchivedReceived) {
    try {
      int j = 0;
      for (int i = 0; i < _lANT.Count; i++) {
        if (_lANT[i].AckNakSuccess == true) {
          if (_lANT[i].ErrorText == null)
            j += Convert.ToInt16(dba.ExecuteSQL("UPDATE tSWIFT_History_Trades " +
              "SET MachineIdReceived = '" + System.Environment.UserName.
              ToUpper() + "', FileNameReceived = '" + strFileNameReceived + "'," +
              "DirectoryArchivedReceived = '" + strDirectoryArchivedReceived +
              "', DateReceived = GetDate(), AckNak = 'ACK' WHERE (BatchId = '" + 
              _lANT[i].BatchId + "' AND TradeId = '" + _lANT[i].TradeId + "')"));
          else
            j += Convert.ToInt16(dba.ExecuteSQL("UPDATE tSWIFT_History_Trades" +
              "SET MachineIdReceived = '" + System.Environment.UserName.
              ToUpper() + "', FileNameReceived = '" + strFileNameReceived + "'," +
              " DirectoryArchivedReceived = '" + strDirectoryArchivedReceived +
              "', DateReceived = GetDate(), AckNak = 'ACK', [Error] = '" + 
              _lANT[i].ErrorText.Replace("'", "''") + "' WHERE (BatchId = '" + 
              _lANT[i].BatchId + "' AND TradeId = '" + _lANT[i].TradeId + "')"));
        }
        else {
          if (_lANT[i].ErrorText == null)
            j += Convert.ToInt16(dba.ExecuteSQL("UPDATE tSWIFT_History_Trades SET MachineIdReceived = '" + System.Environment.UserName.ToUpper() + "', FileNameReceived = '" + strFileNameReceived + "', DirectoryArchivedReceived = '" + strDirectoryArchivedReceived + "', DateReceived = GetDate(), AckNak = 'NAK' WHERE (BatchId = '" + _lANT[i].BatchId + "' AND TradeId = '" + _lANT[i].TradeId + "')"));
          else
            j += Convert.ToInt16(dba.ExecuteSQL("UPDATE tSWIFT_History_Trades SET MachineIdReceived = '" + System.Environment.UserName.ToUpper() + "', FileNameReceived = '" + strFileNameReceived + "', DirectoryArchivedReceived = '" + strDirectoryArchivedReceived + "', DateReceived = GetDate(), AckNak = 'NAK', [Error] = '" + _lANT[i].ErrorText.Replace("'", "''") + "' WHERE (BatchId = '" + _lANT[i].BatchId + "' AND TradeId = '" + _lANT[i].TradeId + "')"));
        }
      }
      return j;
    }
    catch (Exception ex) {
      throw ex;
    }
  }
}