C#, recruiter发过来的面试题, 帮我看看解答 - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>JobHunting - 待字闺中

C#, recruiter发过来的面试题, 帮我看看解答

C#, recruiter发过来的面试题, 帮我看看解答# JobHunting - 待字闺中

p*12016-09-09 07:09

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*12016-09-09 07:09

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 [email protected]
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;
}
}