|
|---|
|
|
|
|
|
|
p****1
发帖数: 275 | 1 Question:
Given the table [tSWIFT_History_Trades] , the C# structure TradeNAK and the
C# method UpdateTradeACKNAKDB as defined below:
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
----------------------------------------------------------------
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 _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;
}
}
First briefly describe this method does?
1. Describe how the method can be changed, improved or written better.
2. Include an explanation for how and why your solution is better.
3. Rewrite UpdateTradeACKNAKDB function | p****1
发帖数: 275 | 2 My answer:
Problems with the above codes:
1. We do not have to have the sql string everywhere, it can be simplified to
be put in just 1 place.
2. This code is not using parameters properly thus allowing for SQL
injection attacks.
My below solution would solve the above 2 problems. In my industry
experience, I did not have experience to put SQL parameters into dba.
ExecuteSQL, so, I convert to SqlCommand.
public int UpdateTradeACKNAKDB(List _lANT, string
strFileNameReceived, string strDirectoryArchivedReceived)
{
try
{
int j = 0;
using (SqlConnection connection = new SqlConnection(/*
connection info*/))
{
for (int i = 0; i < _lANT.Count; i++)
{
string sql = "UPDATE tSWIFT_History_Trades SET MachineIdReceived = '" +
System.Environment.UserName.ToUpper() + "', FileNameReceived = '" +
strFileNameReceived + "', DirectoryArchivedReceived = '" +
strDirectoryArchivedReceived + "', DateReceived = GetDate(), AckNak =@
paramAckNak, '" + (_lANT[i].ErrorText == null ? "' '" : "'[Error] = @
paramText'") + "' WHERE (BatchId = '" + _lANT[i].BatchId + "' AND TradeId =
'" + _lANT[i].TradeId + "')";
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
Var AckNakParam = new SqlParameter(“paramAckNak”, SqlDbType.Char)
AckNakParam.Value = (_lANT[i].AckNakSuccess == true) ? "ACK" : "NAK";
cmd.Parameters.Add(AckNakParam);
If (_lANT[i].ErrorText != null) {
Var TextParam = new SqlParameter(“paramText”, SqlDbType.Text)
TextParam.Value = _lANT[i].ErrorText.Replace("'", "''");
cmd.Parameters.Add(TextParam);
}
j += Convert.ToInt16(cmd.xecuteNonQuery() );
}
}
return j;
}
}
catch (Exception ex)
{
throw ex;
}
} |
|
|
|
|
|
|
|
