线程和SQL插入删除奇怪的问题

Question

我有一个 Windows 应用程序，它调用服务然后写入数据库。在写结果之前，我删除了旧数据。

Windows 应用程序使用每 5 分钟运行一次的计时器调用该服务。

问题是：即使我使用删除，代码也不会删除记录。如果我不使用计时器调用它，它就可以工作。

我使用事务，使用 EF 不起作用。它仍然不删除记录。

 static void Main(string[] args)

 {

        //////Start Timer

        Timer timer = new Timer();

        timer.Interval = 5 * 60 * 1000; // converts ms to minutes

        timer.Elapsed += new ElapsedEventHandler(InsertRecords);

        timer.Enabled = true;

        timerFired.WaitOne();        

 }

 public void InsertRecords()

 { 

     using (SqlConnection connection = new SqlConnection(connectionstr))

     {

         connection.Open();

         // Delete old entries

         SqlTransaction trans = connection.BeginTransaction();

         string sql = "Delete  from PhilaMethod ";

         SqlCommand cmd = new SqlCommand(sql, connection, trans);

         trans.Commit();

         connection.Close();

     }

     var conn = connectionstr;

     string SQL1 = "";

     foreach (PhilaMethod phila in phila2)

     {

         SQL1 += "INSERT INTO PhilaMethod(Name,PS1,PS2,RSI1,) values ('" + phila.Name + "','"+phila.PS1+"','"+phila.PS2+"','"+phila.RSI1+"','"+phila.RSI2+"'); ";

     }

     string SQL2 = "Delete  from philamethod";

     using (SqlConnection connection = new SqlConnection(conn))

     {

            connection.Open();

            SqlTransaction sqlTran = connection.BeginTransaction();

            SqlCommand command = connection.CreateCommand();

            command.Transaction = sqlTran;

            try

            {

                command.CommandText = SQL2;

                int rowsAffected = command.ExecuteNonQuery();

                if(rowsAffected >= 0)

                {

                    command.CommandText = SQL2;

                    int rowsAffected2 = command.ExecuteNonQuery();

                    if(rowsAffected2 == 0)

                    {

                        command.CommandText = SQL1;

                        rowsAffected += command.ExecuteNonQuery();

                    }

                }

      

Answer 1

您需要执行命令。using (SqlConnection connection = new SqlConnection(connectionstr))&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; connection.Open();&nbsp; &nbsp; &nbsp; &nbsp; // Delete old entries&nbsp; &nbsp; &nbsp; &nbsp; SqlTransaction trans = connection.BeginTransaction();&nbsp; &nbsp; &nbsp; &nbsp; string sql = "Delete&nbsp; from PhilaMethod ";&nbsp; &nbsp; &nbsp; &nbsp; SqlCommand cmd = new SqlCommand(sql, connection, trans);&nbsp; &nbsp; &nbsp; &nbsp; cmd.ExecuteNonQuery(); // <--- added this&nbsp; &nbsp; &nbsp; &nbsp; trans.Commit();&nbsp; &nbsp; &nbsp; &nbsp; connection.Close();&nbsp; &nbsp; }

Answer 2

您的代码存在一些问题。您应该始终处理一次性物品。通过调用Dispose()或使用using {}块，这是首选方式。1.）您没有处理您的 SqlCommand 对象您应该将它们包装在 using 语句中以避免此错误和可能的内存泄漏。2.) 您没有处理您的 SqlTransaction 对象您应该始终将其包装在 using 语句中以避免可能的内存泄漏。3.）像其他人已经写过你必须执行一个命令。;)&nbsp;using (SqlConnection connection = new SqlConnection(connectionstr))&nbsp;{&nbsp; &nbsp; &nbsp;connection.Open();&nbsp; &nbsp; &nbsp;// Delete old entries&nbsp; &nbsp; &nbsp;SqlTransaction trans = connection.BeginTransaction();&nbsp; &nbsp; &nbsp;string sql = "Delete&nbsp; from PhilaMethod ";&nbsp; &nbsp; &nbsp;SqlCommand cmd = new SqlCommand(sql, connection, trans);&nbsp; &nbsp; &nbsp;trans.Commit();&nbsp; &nbsp; &nbsp;connection.Close();&nbsp;}在此代码中，缺少执行。;) 所以你的代码应该如下所示。（未测试）&nbsp;using (SqlConnection connection = new SqlConnection(connectionstr))&nbsp;{&nbsp; &nbsp; &nbsp;connection.Open();&nbsp; &nbsp; &nbsp;// Delete old entries&nbsp; &nbsp; &nbsp;using (SqlTransaction trans = connection.BeginTransaction())&nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp;string sql = "Delete&nbsp; from PhilaMethod ";&nbsp; &nbsp; &nbsp; &nbsp;using (SqlCommand cmd = new SqlCommand(sql, connection, trans))&nbsp; &nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;cmd.ExecuteNonQuery(); // or whatever method you need&nbsp; &nbsp; &nbsp; &nbsp;}&nbsp; &nbsp; &nbsp; &nbsp;trans.Commit();&nbsp; &nbsp; &nbsp;}&nbsp; &nbsp; &nbsp;connection.Close();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;}