private static void ExecuteInsertProgramsMySqlCommand(IEnumerable<Program> aProgramList, MySqlConnection aConnection,
MySqlTransaction aTransaction, int aDelay)
{
int aCounter = 0;
MySqlCommand sqlCmd = new MySqlCommand();
List<Program> currentInserts = new List<Program>(aProgramList);
sqlCmd.CommandText =
"INSERT INTO Program (idChannel, startTime, endTime, title, description, seriesNum, episodeNum, genre, originalAirDate, classification, starRating, state, parentalRating, episodeName, episodePart) VALUES (?idChannel, ?startTime, ?endTime, ?title, ?description, ?seriesNum, ?episodeNum, ?genre, ?originalAirDate, ?classification, ?starRating, ?state, ?parentalRating, ?episodeName, ?episodePart)";
sqlCmd.Parameters.Add("?idChannel", MySqlDbType.Int32);
sqlCmd.Parameters.Add("?startTime", MySqlDbType.DateTime);
sqlCmd.Parameters.Add("?endTime", MySqlDbType.DateTime);
sqlCmd.Parameters.Add("?title", MySqlDbType.VarChar);
sqlCmd.Parameters.Add("?description", MySqlDbType.VarChar);
sqlCmd.Parameters.Add("?seriesNum", MySqlDbType.VarChar);
sqlCmd.Parameters.Add("?episodeNum", MySqlDbType.VarChar);
sqlCmd.Parameters.Add("?genre", MySqlDbType.VarChar);
sqlCmd.Parameters.Add("?originalAirDate", MySqlDbType.DateTime);
sqlCmd.Parameters.Add("?classification", MySqlDbType.VarChar);
sqlCmd.Parameters.Add("?starRating", MySqlDbType.Int32);
sqlCmd.Parameters.Add("?state", MySqlDbType.Int32);
sqlCmd.Parameters.Add("?parentalRating", MySqlDbType.Int32);
sqlCmd.Parameters.Add("?episodeName", MySqlDbType.Text);
sqlCmd.Parameters.Add("?episodePart", MySqlDbType.Text);
try
{
sqlCmd.Connection = aConnection;
sqlCmd.Transaction = aTransaction;
// Prepare the command since we will reuse it quite often
sqlCmd.Prepare();
}
catch (Exception ex)
{
Log.Info("BusinessLayer: ExecuteInsertProgramsMySqlCommand - Prepare caused an Exception - {0}", ex.Message);
}
foreach (Program prog in currentInserts)
{
sqlCmd.Parameters["?idChannel"].Value = prog.IdChannel;
sqlCmd.Parameters["?startTime"].Value = prog.StartTime;
sqlCmd.Parameters["?endTime"].Value = prog.EndTime;
sqlCmd.Parameters["?title"].Value = prog.Title;
sqlCmd.Parameters["?description"].Value = prog.Description;
sqlCmd.Parameters["?seriesNum"].Value = prog.SeriesNum;
sqlCmd.Parameters["?episodeNum"].Value = prog.EpisodeNum;
sqlCmd.Parameters["?genre"].Value = prog.Genre;
sqlCmd.Parameters["?originalAirDate"].Value = prog.OriginalAirDate;
sqlCmd.Parameters["?classification"].Value = prog.Classification;
sqlCmd.Parameters["?starRating"].Value = prog.StarRating;
sqlCmd.Parameters["?state"].Value = 0; // prog.Notify;
sqlCmd.Parameters["?parentalRating"].Value = prog.ParentalRating;
sqlCmd.Parameters["?episodeName"].Value = prog.EpisodeName;
sqlCmd.Parameters["?episodePart"].Value = prog.EpisodePart;
try
{
// Finally insert all our data
sqlCmd.ExecuteNonQuery();
aCounter++;
// Avoid I/O starving
if (aCounter % 3 == 0)
{
Thread.Sleep(aDelay);
}
}
catch (MySqlException myex)
{
string errorRow = sqlCmd.Parameters["?idChannel"].Value + ", " + sqlCmd.Parameters["?title"].Value + " : " +
sqlCmd.Parameters["?startTime"].Value + "-" + sqlCmd.Parameters["?endTime"].Value;
switch (myex.Number)
{
case 1062:
Log.Info("BusinessLayer: Your importer tried to add a duplicate entry: {0}", errorRow);
break;
case 1406:
Log.Info("BusinessLayer: Your importer tried to add a too much info: {0}, {1}", errorRow, myex.Message);
break;
default:
Log.Info("BusinessLayer: ExecuteInsertProgramsMySqlCommand caused a MySqlException - {0}, {1} {2}",
myex.Message,
myex.Number, myex.HelpLink);
break;
}
}
catch (Exception ex)
{
Log.Info("BusinessLayer: ExecuteInsertProgramsMySqlCommand caused an Exception - {0}, {1}", ex.Message,
ex.StackTrace);
}
}
return;
}