Click here to Skip to main content
15,867,834 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi guys.
I'm experiencing something weird trying to use a recursive call in c#.
This is what I'm doing.

C#
//routine for downloading Phases
    public static int DownloadPhases(string competitionId, string competitionCountry, string competitionPhase = "")
    {
      DataTable dt = new DataTable();
      DateTime phaseStart = DateTime.Now, phaseEnd = DateTime.Now, outDate;
      XmlDocument xmlDoc = new XmlDocument();
      bool isDate = false, isNumeric = false;
      string url = "", phaseId = "", phaseCode = "", phaseName = "", phaseDesc = "";
      int phaseSort = 0, outNum = 0, result = 0;

      //load structure for inserting new Phases
      SqlUtilities.sqlLoadDt(ConfigurationManager.AppSettings["sqlSel_insCompetitionsPhase"], ref dt);
      DataColumn[] keyColumn2 = new DataColumn[3];
      keyColumn2[0] = dt.Columns["phase_ID"];
      keyColumn2[1] = dt.Columns["phase_Competition"];
      keyColumn2[2] = dt.Columns["phase_Country"];
      dt.PrimaryKey = keyColumn2;

      try
      {
        if (competitionPhase == "")
          url = buildUrl("15", "", "", competitionCountry, competitionId);
        else
          url = buildUrl("15", "", "", competitionCountry, competitionPhase);
        loadXml(xmlDoc, url);

        //set a nodelist with all the <category> tags for Phases
        XmlNodeList competitions = xmlDoc.GetElementsByTagName("Item");
        //for each node in the the nodelist, get all the infos about it
        foreach (XmlNode competition in competitions)
        {
          //phase
          phaseId = competition.Attributes.GetNamedItem("id").Value.ToString();
          phaseCode = competition.Attributes.GetNamedItem("code").Value.ToString();
          phaseName = competition.Attributes.GetNamedItem("name").Value.ToString();
          phaseDesc = competition.Attributes.GetNamedItem("description").Value.ToString();
          isDate = DateTime.TryParse(competition.Attributes.GetNamedItem("comp_start").Value.ToString(), out outDate);
          if (isDate == true) phaseStart = outDate;
          isDate = DateTime.TryParse(competition.Attributes.GetNamedItem("comp_end").Value.ToString(), out outDate);
          if (isDate == true) phaseEnd = outDate;
          isNumeric = int.TryParse(competition.Attributes.GetNamedItem("sort").Value.ToString(), out outNum);
          if (isNumeric == true) phaseSort = outNum;

          //adding competition to datatable dt
          if (phaseId != "")
          {
            try
            {
              dt.Rows.Add(phaseId, competitionId, competitionCountry, phaseCode, phaseStart, phaseEnd, phaseDesc, phaseName, phaseSort);
              result += 1;
              if (phaseDesc.Contains("NOT LEAF"))
                DlData.DownloadPhases(competitionId, competitionCountry, phaseId);
            }
            catch (Exception ex)
            {
              ex.Message.ToString();
            }
            finally
            {
              phaseId = "";
            }
          }
        } //end foreach phase
      }

      catch (Exception ex)
      {
        ex.Message.ToString();
      }

      finally
      {
        //call stored procedure to insert Phases
        SqlUtilities.sqlExecuteSp("dbo.usp_insCompetitionsPhase", dt);
      }
      return result;
    }


And this is the sqlExecuteSp function

C#
public static void sqlExecuteSp(string cmdText, DataTable parDt)
    {
      try
      {
        SqlConnection conn = new SqlConnection();
        SqlCommand comm = new SqlCommand();
        SqlParameter param = new SqlParameter();
        SqlTransaction trans;

        conn.ConnectionString = ConfigurationManager.ConnectionStrings["FTBLConnectionStringSuperUser"].ConnectionString;
        conn.Open();
        using (conn)
        {
          trans = conn.BeginTransaction();
          comm.CommandText = cmdText;
          comm.Connection = conn;
          comm.CommandType = CommandType.StoredProcedure;
          comm.Transaction = trans;
          param = comm.Parameters.AddWithValue("@dt", parDt);
          param.SqlDbType = SqlDbType.Structured;
          
          comm.ExecuteNonQuery();
          trans.Commit();
        }
        conn.Close();
      }
      catch (Exception ex)
      {
        ex.Message.ToString();
      }
    }


When I try to call the sqlExecuteSp function recursivly, it seems to execute it correctly, but I can't see the new rows inserted till when I delete the first ones.

So, If I run "delete from tbl_competition_phases" in sql management studio, it only deletes the "old" rows, and from now on I can see the rows inserted with the second call.

I thought this happened due to an incomplete transaction so, as you can see, I added a transaction object, without results.

Any suggest?

Thank you very much!

Damiano
Posted
Comments
ashok rathod 23-Sep-14 5:00am    
place your trans variable in outside try and in your catch block write trans.Rollback(); and in then check
leotato008 23-Sep-14 5:56am    
Now it only inserts the first group of rows related to the first "batch".
Slacker007 23-Sep-14 5:00am    
I don't like to use nested try/catch statements. Use only one.
BillWoodruff 23-Sep-14 6:14am    
I'd suggest you try writing the method as not static and see if you have the same issues. Ideally, the recursive code should only create the minimum set of new objects required in each call to it.
leotato008 23-Sep-14 6:48am    
No Bill, it doesn't change anything. Thank you.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900