naraomur
Member
- Joined
- Jun 26, 2023
- Messages
- 7
- Programming Experience
- 1-3
I have service that uploads and parses line by line into DataTable and records it via SQL bulk copy into the DB. Locally this service works fine with overall implementation in 20 secs and in LAN dev server takes a little longer in 46 secs. But when I run it on test server (the server for testing) the page is loading almost 1 minute and finally gives out '504 Gateway Time-out. The server didn't respond in time'. Although the SQL Table is being updated. If I upload less than half of the file then it works everywhere just fine. I am getting this error only on heavier (484613 lines) file.
Here is the code that holds the whole logic:
I tried(I thought maybe because of SQL):
I had set in app.settings the connection timeout to 120 seconds(2minutes), but still getting the same error.
What could be done from my side(source logic, db config, better tools for bulk copies). Threads operate and take the same amount of period by the way, tried it.
Here is the code that holds the whole logic:
C#:
public int UploadCardBins(byte[] cardBins, out string rows, out List<string> mismatchedRows, out int fileLines)
{
mismatchedRows = new List<string>();
fileLines = 0;
rows = null;
int resultCode = (int)ResultCode.Ok;
bool timeParsed = int.TryParse(ConfigurationManager.AppSettings["UploadCardBinSqlTimeOut"], out int timeOut);
try
{
Stream ms = new MemoryStream(cardBins);
StreamReader reader = new StreamReader(ms, System.Text.Encoding.UTF8);
DataTable table = RetrieveCardBinFromTxtFile(reader.ReadToEnd(), out mismatchedRows, out fileLines);
rows = table.Rows.Count.ToString();
string sql = ConfigurationManager.ConnectionStrings["DbConnection"].ConnectionString;
using (var connection = new SqlConnection(sql))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
bulkCopy.BatchSize = table.Rows.Count;
bulkCopy.DestinationTableName = "dbo.Dicts_CardBin";
try
{
var command = connection.CreateCommand();
if(timeParsed)
command.CommandTimeout = timeOut;
command.CommandText = "delete from dbo.Dicts_CardBin";
command.Transaction = transaction;
command.ExecuteNonQuery();
bulkCopy.WriteToServer(table);
Session.Flush();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
logger.Error("[{0}] {1}", ex.GetType(), ex);
resultCode = (int)ResultCode.GenericError;
}
finally
{
transaction.Dispose();
connection.Close();
}
}
}
}
catch (Exception ex)
{
logger.Error("[{0}] {1}", ex.GetType(), ex);
resultCode = (int)ResultCode.GenericError;
}
return resultCode;
}I tried(I thought maybe because of SQL):
I had set in app.settings the connection timeout to 120 seconds(2minutes), but still getting the same error.
What could be done from my side(source logic, db config, better tools for bulk copies). Threads operate and take the same amount of period by the way, tried it.
