如果存在...查询无法正常工作 [英] If exists... Query cannot work

问题描述

我有表项，我需要检查表中是否存在ID然后删除记录并插入它。我的代码看起来像这样< br $> b $ b

Hi,

I have table Item,I need to check if the ID exists in table then delete the record and insert it.My code look like this

public bool Savedata(List<itemValues> lstitem)
       {

          string query = "IF EXISTS(SELECT ID from Item where ID=@ID)" +
                       "DELETE FROM Item WHERE ID=@ID;" +
                       "INSERT INTO Item(" +
                       "ItemName, " +
                       "Price, " +
                       "ID)  VALUES (" +
                       "@ItemName, " +
                       "@Price, " +
                       "@ID)";

           return Data.CreateItem(lstitem, query);
       }

public bool CreateItem(List<itemValues> lstitem, string query)
      {
          try
          {
              Connection Conn = new Connection();
              sqlCon = Conn.SqlConnection();

                  if (lstitem.Any())
                  {
                      foreach (var item in lstitem)
                      {

                          using (SqlCommand cmd = new SqlCommand(query, sqlCon))
                          {

                              cmd.CommandType = CommandType.Text;
                              cmd.Parameters.Add("@ItemName ", SqlDbType.VarChar).Value = item.itemName;
                              cmd.Parameters.Add("@Price", SqlDbType.Int).Value = item.Price;
                              cmd.Parameters.Add("@ID", SqlDbType.Int).Value = item.ID;

                              sqlCon.Open();
                              int rowsAffected = cmd.ExecuteNonQuery();

                              sqlCon.Close();
                          }

                      }


                  }
          }
          catch (Exception ex)
          {

              return false;
          }

          return true;
      }

它无法检查存在的数据，将列表中的所有数据都插入到Item表中。如何解决这个问题？< br $> b $ b

我尝试了什么：



我尝试使用If exists（。 ..）查询。

It cannot check exists data,insert all data in the list to Item table.How to solve this?

What I have tried:

I try with If exists(...) query.

推荐答案

为什么不使用UPDATE？

Why not just use UPDATE?

string query = "UPDATE Item SET 
               "ItemName = @ItemName, " +
               "Price = @Price " +
               "where ID = @ID";

您不需要 IF EXISTS - 这是多余的。简单删除记录并插入新记录。



但是，我实际上是这样做的：

You don't need IF EXISTS - it's redundant. Simple remove the record and insert the new one.

However, I would actually do it this way:

-- try to update the record
UPDATE	[mydatabase]
SET	ItemName = @itemName, Price = @price,
WHERE	ID = @ID;
-- if no record was updated, insert it	
IF (@@ROWCOUNT = 0)
    INSERT INTO [mydatabase](ID, ItemName, Price) VALUES (@ID, @itemName, @price);

最后，在将查询放入代码（或存储过程）之前，使用SSME测试查询）。

Finally, use SSME to test your queries before putting them into your code (or a stored procedure).

我没有看到很多人这样做。

这里的问题可能是由于范围没有正确定义。



最好的方法是创建一个可以在单次调用DB时执行的存储过程。您的存储过程应该看起来像 -

I haven't seen many people doing this.
The problem here could be due to the scope is not defined correctly.

The best way would be to create a stored procedure which can be executed on single call to the DB. Your stored proc should look like-

CREATE PROCEDURE myProcName
(
  @ID INT,
  @ItemName VARCHAR(50), -- or what ever as per your DB design
  @Price MONEY
)
AS
BEGIN
   IF EXISTS(SELECT ID from Item where ID=@ID)
   BEGIN
      DELETE FROM Item WHERE ID=@ID;

      INSERT INTO Item(ItemName, Price, ID)  
          VALUES (@ItemName, @Price, @ID)
   END
   --else condition goes here
END

这里的陈述被 BEGIN..END 关键字正确阻止。



但是，您也可以坚持使用参数化查询方法，在这种情况下，您需要多次调用数据库。

方法可能类似于 -

- 检查仅使用 SELECT 查询表中数据的存在并得到真/假值。

- 如果你得到真值，那么去吧提前执行 DELET E 命令，然后是另一个命令对象中的 INSERT 命令。



希望，它有助于：）

Here the statements are properly blocked with BEGIN..END keywords.

However, you can also stick to your parameterised query approach in which case you need to make multiple calls to the DB.
The approach could be something like-
--Check the existance of data in the table with just the SELECT query and get the true/false value.
--If you get true value then go ahead to execute the DELETE command and then the INSERT command in another command object.

Hope, it helps :)

这篇关于如果存在...查询无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！