I have developed a windows service to read data from a csv and write them back to the database.
After executing the program it will work fine until triggering an error after a few minutes when reached the below line
cmd.ExecuteNonQuery();
Screenshot of the error:
The data has been written to the database until this error pops up.
It will take up to 2-3 minutes to trigger the error.
I will include the related code chunk for this issue.
public void Insert()
{
if (this.OpenConnection() == true)
{
using(var reader = new StreamReader(@"C:UsersAdminsourceBargstedt.csv"))
{
List<string> listA = new List<string>();
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
var values = line.Split(',');
string querynew = "INSERT INTO new_jobs"
+ "(job_reference,status,code,no1,no2,no3,dimension,date_assigned,root,variable,number,stable,constant)"
+ "VALUES (?jobNo, ?strClientName, ?strClientReference, ?strJobCategory, ?datCommisioned, ?datPromisedDelivery, ?division, ?date_assigned, ?root, ?variable, ?number, ?stable, ?constant)";
MySqlCommand cmd = connection.CreateCommand();
cmd.CommandText= querynew;
cmd.Parameters.Add("?jobNo", MySqlDbType.VarChar).Value = (values[0]);
cmd.Parameters.Add("?strClientName", MySqlDbType.VarChar).Value =(values[1]);
cmd.Parameters.Add("?strClientReference", MySqlDbType.VarChar).Value = values[2];
cmd.Parameters.Add("?strJobCategory", MySqlDbType.VarChar).Value = values[3];
cmd.Parameters.Add("?datCommisioned", MySqlDbType.VarChar).Value = values[4];
cmd.Parameters.Add("?datPromisedDelivery", MySqlDbType.VarChar).Value = values[5];
cmd.Parameters.Add("?division", MySqlDbType.VarChar).Value = values[7];
cmd.Parameters.Add("?date_assigned", MySqlDbType.VarChar).Value = values[9];
cmd.Parameters.Add("?root", MySqlDbType.VarChar).Value = values[10];
cmd.Parameters.Add("?variable", MySqlDbType.VarChar).Value = values[11];
cmd.Parameters.Add("?number", MySqlDbType.VarChar).Value = values[12];
cmd.Parameters.Add("?stable", MySqlDbType.VarChar).Value = values[13];
cmd.Parameters.Add("?constant", MySqlDbType.VarChar).Value = values[14];
cmd.ExecuteNonQuery(); **error line
}
}
this.CloseConnection();
}
}
Just to make it more clear I'll include a screenshot of the csv file and db structure as well.
There are some spaces in the middle of the rows in csv and that is why I skipped row 6 and 8 in the source code.
screenshot of the phpMyAdmin db table
Edit:
MySql.Data.MySqlClient.MySqlException
HResult=0x80004005
Message=Fatal error encountered during command execution.
Source=MySql.Data
StackTrace:
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at SSHtest.DBConnect.Insert() in C:UsersAdminsource
eposackup newSSHtestSSHtestProgram.cs:line 248
at SSHtest.Service1.timer1_Tick(Object sender, ElapsedEventArgs e) in C:UsersAdminsource
eposackup newSSHtestSSHtestService1.cs:line 87
at System.Timers.Timer.MyTimerCallback(Object state)
Inner Exception 1:
MySqlException: Fatal error encountered attempting to read the resultset.
Inner Exception 2:
MySqlException: Reading from the stream has failed.
Inner Exception 3:
EndOfStreamException: Attempted to read past the end of the stream.
See Question&Answers more detail:
os