TvDatabase.TvBusinessLayer.ExecuteInsertProgramsSqlServerCommand C# (CSharp) Method

    private static void ExecuteInsertProgramsSqlServerCommand(IEnumerable<Program> aProgramList,
                                                              SqlConnection aConnection,
                                                              SqlTransaction aTransaction, int aDelay)
    {
      int aCounter = 0;
      SqlCommand sqlCmd = new SqlCommand();
      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", SqlDbType.Int);
      sqlCmd.Parameters.Add("startTime", SqlDbType.DateTime);
      sqlCmd.Parameters.Add("endTime", SqlDbType.DateTime);
      sqlCmd.Parameters.Add("title", SqlDbType.VarChar);
      sqlCmd.Parameters.Add("description", SqlDbType.VarChar);
      sqlCmd.Parameters.Add("seriesNum", SqlDbType.VarChar);
      sqlCmd.Parameters.Add("episodeNum", SqlDbType.VarChar);
      sqlCmd.Parameters.Add("genre", SqlDbType.VarChar);
      sqlCmd.Parameters.Add("originalAirDate", SqlDbType.DateTime);
      sqlCmd.Parameters.Add("classification", SqlDbType.VarChar);
      sqlCmd.Parameters.Add("starRating", SqlDbType.Int);
      sqlCmd.Parameters.Add("state", SqlDbType.Int);
      sqlCmd.Parameters.Add("parentalRating", SqlDbType.Int);
      sqlCmd.Parameters.Add("episodeName", SqlDbType.VarChar);
      sqlCmd.Parameters.Add("episodePart", SqlDbType.VarChar);

      try
      {
        sqlCmd.Connection = aConnection;
        sqlCmd.Transaction = aTransaction;
        // Prepare the command since we will reuse it quite often
        // sqlCmd.Prepare(); <-- this would need exact param field length definitions
      }
      catch (Exception ex)
      {
        Log.Info("BusinessLayer: ExecuteInsertProgramsSqlServerCommand - 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 % 2 == 0)
          {
            Thread.Sleep(aDelay);
          }
        }
        catch (SqlException msex)
        {
          string errorRow = sqlCmd.Parameters["idChannel"].Value + ", " + sqlCmd.Parameters["title"].Value + " : " +
                            sqlCmd.Parameters["startTime"].Value + "-" + sqlCmd.Parameters["endTime"].Value;
          switch (msex.Number)
          {
            case 2601:
              Log.Info("BusinessLayer: Your importer tried to add a duplicate entry: {0}", errorRow);
              break;
            case 8152:
              Log.Info("BusinessLayer: Your importer tried to add a too much info: {0}, {1}", errorRow, msex.Message);
              break;
            default:
              Log.Info("BusinessLayer: ExecuteInsertProgramsSqlServerCommand caused a SqlException - {0}, {1} {2}",
                       msex.Message, msex.Number,
                       msex.HelpLink);
              break;
          }
        }
        catch (Exception ex)
        {
          Log.Error("BusinessLayer: ExecuteInsertProgramsSqlServerCommand error - {0}, {1}", ex.Message, ex.StackTrace);
        }
      }
      return;
    }