如何迭代sqland中的表在匹配时删除一行 [英] How to iterate a table in sqland delete a row upon matching

问题描述

我在表单加载时保存了一个整数，当点击一个按钮我想在SQL中搜索表并查看它是否有这个整数我需要删除整行这是代码：

  private   void  button3_Click（ object  sender，EventArgs e）
 {
  try  
 {
 
 使用（ var  cmd106 =  new  SqlCommand（  select * from [dbo]。[incidentNoReserve]，cnn））
 {
 
 cnn.Open（）; 
 SqlDataReader reader = cmd106.ExecuteReader（）; 
  while （reader.Read（））
 {
  int  commonNo = Convert.ToInt16（reader [  incidentNoReserveId]）; 
  //  每个新行都会创建一个包含列的新词典 
  if （Convert.ToInt16（commonNo）== newLastIncidentNo）
 {
 newLastIncidentNo =（Convert.ToInt16（commonNo））; 
 使用（SqlCommand command =  new  SqlCommand（  从[dbo]中删除。[incidentNoReserve]其中incidentNoReserveId = @newLastIncidentNo，cnn））
 {
 cnn.Open （）; 
 command.Parameters.AddWithValue（  @ newLastIncidentNo，newLastIncidentNo）; 
 command.ExecuteNonQuery（）; 
 cnn.Close（）; 
} 
} 
} 
 reader.Close（）; 
 cnn.Close（）; 
} 
} 
  catch （例外情况）
 {
  //  如果发生异常，请将其写入控制台 
 Console.WriteLine（ex.ToString（））; 
} 
 最后 
 {
 cnn.Close（）; 
} 
  this  .Close（）; 
} 

我的尝试：



i已经使用了这个代码，直到它到达删除命令，它在cnn.open（）上停止并跳到范围之外，不知道问题是什么。

解决方案

我从哪里开始......这段代码有很多问题



首先，代码有点难以理解，但看起来很像就像你要枚举[incidentNoReserve]表中的所有记录一样，将它们全部删除。除了一次删除一个记录之外，还有更好的方法。



接下来，你只有一个与数据库的连接，但你如何做到这一点需要二。在读取器完成并关闭之前，具有活动Reader的连接不能用于任何其他内容。



沿着这些相同的行，你有连接， cnn，正在打开TWICE。它已经打开，你不能在没有先关闭它的情况下再打开它。



你的SELECT语句将返回incidentNoReserve表中的所有列（*），但你只是使用它它的一列。切勿使用*说明符。始终指定所需的每列。这样就限制了SQL服务器必须完成的工作量，限制了通过线路传递给代码的信息量，使代码更能抵抗模式更改并使其更容易调试。



您正在将记录ID转换为Int16。这会将您的记录Id值限制为最大值32767.在宏观方案中，记录的记录不是很多。



你真的想用这个做什么码？

i have an integer saved upon form load, when clicking a button i want to search the table in SQL and see if it has this integer i need to delete the entire row this is the code :

private void button3_Click(object sender, EventArgs e)
       {
           try
           {

               using (var cmd106 = new SqlCommand("select * from [dbo].[incidentNoReserve]", cnn))
               {

                   cnn.Open();
                   SqlDataReader reader = cmd106.ExecuteReader();
                   while (reader.Read())
                   {
                       int commonNo = Convert.ToInt16(reader["incidentNoReserveId"]);
                       //Every new row will create a new dictionary that holds the columns
                       if (Convert.ToInt16(commonNo) == newLastIncidentNo)
                       {
                           newLastIncidentNo = (Convert.ToInt16(commonNo));
                           using (SqlCommand command = new SqlCommand("Delete from[dbo].[incidentNoReserve] where incidentNoReserveId = @newLastIncidentNo", cnn))
                           {
                               cnn.Open();
                               command.Parameters.AddWithValue("@newLastIncidentNo", newLastIncidentNo);
                               command.ExecuteNonQuery();
                               cnn.Close();
                           }
                       }
                   }
                   reader.Close();
                   cnn.Close();
               }
           }
           catch (Exception ex)
           {
               //If an exception occurs, write it to the console
               Console.WriteLine(ex.ToString());
           }
           finally
           {
               cnn.Close();
           }
           this.Close();
       }

What I have tried:

i have used this code its fine until it reaches the delete command,it stops on cnn.open() and jumps outside the scope dont know whats the problem.

解决方案

Where do I begin... There's a ton of problems with this code

First, the code is a bit difficult to follow, but it looks like you're enumerating over all of the records in the [incidentNoReserve] table deleting them all. There are better ways of doing this than deleting records one at a time.

Next, you only have one connection to the database, but how you're doing it requires two. A connection that has an active Reader on it cannot be used for anything else until that Reader is done and closed.

Along those same lines, you have the connection, "cnn", being opened TWICE. It's already open, you cannot open it again without closing it first.

Your SELECT statement is returning all columns (*) in the incidentNoReserve table but you're only using a single column from it. Never use the * specifier. Always specify every column you want. That way you limit the amount of work the SQL server has to do, limit the amount of information going "over the wire" to your code, make your code more resistent to schema changes and make it easier to debug.

You're converting a record Id to an Int16. This limits your record Id values to a maximum of 32767. In the grand scheme of things, that's not very many records.

What are you really trying to do with this code?

这篇关于如何迭代sqland中的表在匹配时删除一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！