ExecuteNonQuery需要一个开放和可用的连接.连接的当前状态为关闭

本文关键字：连接状态一个 ExecuteNonQuery | 更新日期: 2023-09-27 18:33:33

当尝试运行程序时，它会抛出当前连接状态已关闭的异常。
在这里，我在单个连接中使用了两个条件和两个命令..我也怀疑 1 个尝试块对于两个条件是否足够......

public void Insert(DataTable dt)
{
    SqlConnection Dist = new SqlConnection(ConfigurationManager.ConnectionStrings["Distil"].ToString());
    Dist.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = Dist;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "SHA_DIS_Insertion";
    foreach (DataRow dr in dt.Rows)
    {
        if (dr["EPFNODE"].ToString() == "EXPERIENCE")
        {
            cmd.Parameters.AddWithValue("@EPF_ID", dr["EPF_ID"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_ORGANISATION", dr["EPFW_ORGANISATION"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_COUNTRY", dr["EPFW_COUNTRY"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_CITY", dr["EPFW_CITY"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_OCITY", dr["EPFW_OCITY"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_INDUSTRY", dr["EPFW_INDUSTRY"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_OINDUSTRY", dr["EPFW_OINDUSTRY"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_CATEGORY", dr["EPFW_CATEGORY"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_OCATEGORY", dr["EPFW_OCATEGORY"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_POSITION", dr["EPFW_POSITION"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_OPOSITION", dr["EPFW_OPOSITION"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_NATUREEMP", dr["EPFW_NATUREEMP"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_FROMMONTH", dr["EPFW_FROMMONTH"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_FROMYEAR", dr["EPFW_FROMYEAR"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_TOMONTH", dr["EPFW_TOMONTH"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_TOYEAR", dr["EPFW_TOYEAR"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_JOBPROFILE", dr["EPFW_JOBPROFILE"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_CHIGHLIGHTS", dr["EPFW_CHIGHLIGHTS"].ToString());
            cmd.Parameters.AddWithValue("@EPFW_WORKREFERENCE", dr["EPFW_WORKREFERENCE"].ToString());
            cmd.Parameters.AddWithValue("@EPF_USER", 3);
            //try
            //{
            //    cmd.ExecuteNonQuery();
            //}
            //catch (Exception Ex)
            //{
            //    throw Ex;
            //}
            //finally
            //{
            //   //cmd.Dispose();
            //   // Dist.Close();
            //    // Dist.Dispose();
            //}
        }
        if (dr["EPFNODE"].ToString() == "PROJECTS")
        {
            SqlCommand cmd1 = new SqlCommand();
            cmd1.Connection = Dist;
            cmd1.CommandType = CommandType.StoredProcedure;
            cmd1.CommandText = "SHA_PRO_Insertion";
            cmd1.Parameters.AddWithValue("@EPF_ID", dr["EPF_ID"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_PROJECTTITLE", dr["EPFP_PROJECTTITLE"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_COUNTRY", dr["EPFP_COUNTRY"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_CITY", dr["EPFP_CITY"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_OCITY", dr["EPFP_OCITY"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_CAREER", dr["EPFP_CAREER"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_OCAREER", dr["EPFP_OCAREER"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_CATEGORY", dr["EPFP_CATEGORY"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_OCATEGORY", dr["EPFP_OCATEGORY"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_POSITION", dr["EPFP_POSITION"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_OPOSITION", dr["EPFP_OPOSITION"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_SKILL", dr["EPFP_SKILL"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_OSKILL", dr["EPFP_OSKILL"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_INDUSTRY", dr["EPFP_INDUSTRY"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_OINDUSTRY", dr["EPFP_OINDUSTRY"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_CLIENTNAME", dr["EPFP_CLIENTNAME"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_NATUREOFEMP", dr["EPFP_NATUREOFEMP"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_STARTMONTH", dr["EPFP_STARTMONTH"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_STARTYEAR", dr["EPFP_STARTYEAR"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_ENDMONTH", dr["EPFP_ENDMONTH"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_ENDYEAR", dr["EPFP_ENDYEAR"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_DESCRIPTION", dr["EPFP_DESCRIPTION"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_PCATEGORY", dr["EPFP_PCATEGORY"].ToString());
            cmd1.Parameters.AddWithValue("@EPFP_POCATEGORY", dr["EPFP_POCATEGORY"].ToString());
            cmd1.Parameters.AddWithValue("@EPF_USER", 3);
            try
            {
                cmd1.ExecuteNonQuery();
            }
            catch (Exception Exx)
            {
                throw Exx;
            }
            finally
            {
                Dist.Close();
              //  Dist.Dispose();
            }
        }
    }
}

您必须关闭foreach循环之外的连接。

您必须关闭foreach loop或

添加以下代码行：

try
{
   if(Dist.State != ConnectionState.Open)
      Dist.Open();
   cmd1.ExecuteNonQuery();
}