执行存储过程的C#函数和SqlCommand出现问题

这是我在这里的第一篇文章。我真的很喜欢这个地方，到目前为止我发现了很多有趣的东西，所以我决定登机！

我在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"))
                      <b>DlData.DownloadPhases(competitionId, competitionCountry, phaseId);</b>
               }
               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;
}

这就是SqlExecuteSp函数

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();
  }
}

当我尝试递归调用SqlExecuteSp函数时，它似乎正确地执行了它，但直到删除第一行时，我才能看到插入的新行。

因此，如果我在SQLServerManagementStudio中运行delete from tbl_competition_phases，它只删除"旧"行，从现在起，我可以看到第二次调用插入的行。

我认为这是由于一个不完整的事务导致的，所以，正如您所看到的，我添加了一个事务对象，但没有结果。

我知道c#中的递归调用不是最好的方法。无论如何，在这种情况下，我很确定不会有超过2个调用，所以我认为这不会是一个问题。

有什么建议吗？

非常感谢！

Damiano

public static void sqlExecuteSp(string cmdText, DataTable parDt)
{
    using (SqlConnection conn = new SqlConnection())
    {
        using (SqlCommand comm = new SqlCommand())                    
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["FTBLConnectionStringSuperUser"].ConnectionString;
            conn.Open();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                comm.Connection = conn;
                comm.CommandType = CommandType.StoredProcedure;
                comm.Transaction = trans;
                SqlParameter param = comm.Parameters.AddWithValue("@dt", parDt);
                param.SqlDbType = SqlDbType.Structured;
                comm.ExecuteNonQuery();
                trans.Commit();
            }
        }
        conn.Close();
    }
}