I have a list of about 7030 items. I save the items from the list to a table in SQL server. I thought, I could use multithreading to speed up the process, which it did, however, there emerged an issue.
The number of items being uploaded to the database does not change, but when I query the number of records in the table after running my code, its always different, say one time it will have uploaded 6925, next time 6831 etc. I cannot see why this is happening.
In the class where I get the data
void DatabaseUploadMultiThreading()
{
DateTime dtUpload = Program.UploadDate();
int numThread = 8;
int splitNum = _holdingList.Count / numThread;
int leftOver = _holdingList.Count - (splitNum * (numThread - 1));
DatabaseWriter[] dbArray = new DatabaseWriter[numThread];
List<Holding>[] holdingArray = new List<Holding>[numThread];
Task[] taskDB = new Task[numThread];
for (int i = 0; i < holdingArray.Length; i++)
{
dbArray[i] = new DatabaseWriter(i + 1, dtUpload);
if (i == (numThread - 1))
holdingArray[i] = _holdingList.GetRange(i * splitNum, leftOver);
else
holdingArray[i] = _holdingList.GetRange(i * splitNum, splitNum);
}
for (int i = 0; i < taskDB.Length; i++)
taskDB[i] = Task.Factory.StartNew(dbArray[i].UploadHoldings, holdingArray[i]);
try
{
Task.WaitAll(taskDB); // wait for all the threads to complete
}
catch (AggregateException ex)
{
ExceptionDispatchInfo.Capture(ex.InnerException).Throw();
}
}
The DatabaseWriter class snipet
class DatabaseWriter : IDisposable
{
#region variable declaration
private SqlConnection _connection;
private SqlCommand _command;
private static readonly string _connectionString = "myConnectionString";
public void UploadHoldings(object objHoldingList)
{
List<Holding> holdingList = (List<Holding>)objHoldingList;
using (_connection = new SqlConnection(_connectionString))
{
_connection.Open();
DataReImported(_dtUpload);
for (int i = 0; i < holdingList.Count; i++)
{
string cmdText = "INSERT INTO HOLDINGS([FUND_CD], [SEDOLCHK], [NOMINAL], [CURR], [PRICE], [DATEU]) " +
"VALUES(@fundcode, @sedol, @nominal, @curr, @price, @dtUpload)";
_command = new SqlCommand(cmdText, _connection);
_command.Parameters.Add("@fundCode", SqlDbType.VarChar).Value = holdingList[i].FundCode;
_command.Parameters.Add("@sedol", SqlDbType.VarChar).Value = holdingList[i].IdSedol;
_command.Parameters.Add("@nominal", SqlDbType.Decimal).Value = holdingList[i].Nominal;
_command.Parameters.Add("@curr", SqlDbType.VarChar).Value = holdingList[i].Currency;
_command.Parameters.Add("@price", SqlDbType.Decimal).Value = holdingList[i].Price;
_command.Parameters.Add("@dtUpload", SqlDbType.Date).Value = _dtUpload;
_command.ExecuteNonQuery();
Console.WriteLine("Thread Number:" + _threadNum + " Security Number uploaded: " + i + " of " + holdingList.Count);
}
_connection.Close();
}
}
}
I have a list of about 7030 items. I save the items from the list to a table in SQL server. I thought, I could use multithreading to speed up the process, which it did, however, there emerged an issue.
The number of items being uploaded to the database does not change, but when I query the number of records in the table after running my code, its always different, say one time it will have uploaded 6925, next time 6831 etc. I cannot see why this is happening.
In the class where I get the data
void DatabaseUploadMultiThreading()
{
DateTime dtUpload = Program.UploadDate();
int numThread = 8;
int splitNum = _holdingList.Count / numThread;
int leftOver = _holdingList.Count - (splitNum * (numThread - 1));
DatabaseWriter[] dbArray = new DatabaseWriter[numThread];
List<Holding>[] holdingArray = new List<Holding>[numThread];
Task[] taskDB = new Task[numThread];
for (int i = 0; i < holdingArray.Length; i++)
{
dbArray[i] = new DatabaseWriter(i + 1, dtUpload);
if (i == (numThread - 1))
holdingArray[i] = _holdingList.GetRange(i * splitNum, leftOver);
else
holdingArray[i] = _holdingList.GetRange(i * splitNum, splitNum);
}
for (int i = 0; i < taskDB.Length; i++)
taskDB[i] = Task.Factory.StartNew(dbArray[i].UploadHoldings, holdingArray[i]);
try
{
Task.WaitAll(taskDB); // wait for all the threads to complete
}
catch (AggregateException ex)
{
ExceptionDispatchInfo.Capture(ex.InnerException).Throw();
}
}
The DatabaseWriter class snipet
class DatabaseWriter : IDisposable
{
#region variable declaration
private SqlConnection _connection;
private SqlCommand _command;
private static readonly string _connectionString = "myConnectionString";
public void UploadHoldings(object objHoldingList)
{
List<Holding> holdingList = (List<Holding>)objHoldingList;
using (_connection = new SqlConnection(_connectionString))
{
_connection.Open();
DataReImported(_dtUpload);
for (int i = 0; i < holdingList.Count; i++)
{
string cmdText = "INSERT INTO HOLDINGS([FUND_CD], [SEDOLCHK], [NOMINAL], [CURR], [PRICE], [DATEU]) " +
"VALUES(@fundcode, @sedol, @nominal, @curr, @price, @dtUpload)";
_command = new SqlCommand(cmdText, _connection);
_command.Parameters.Add("@fundCode", SqlDbType.VarChar).Value = holdingList[i].FundCode;
_command.Parameters.Add("@sedol", SqlDbType.VarChar).Value = holdingList[i].IdSedol;
_command.Parameters.Add("@nominal", SqlDbType.Decimal).Value = holdingList[i].Nominal;
_command.Parameters.Add("@curr", SqlDbType.VarChar).Value = holdingList[i].Currency;
_command.Parameters.Add("@price", SqlDbType.Decimal).Value = holdingList[i].Price;
_command.Parameters.Add("@dtUpload", SqlDbType.Date).Value = _dtUpload;
_command.ExecuteNonQuery();
Console.WriteLine("Thread Number:" + _threadNum + " Security Number uploaded: " + i + " of " + holdingList.Count);
}
_connection.Close();
}
}
}
0 commentaires:
Enregistrer un commentaire