C# SQLCommand 不将数据写入数据库

Question

我有以下 SQL 语句(如果我在我的数据库中像这样执行它，它会起作用):

IF EXISTS (SELECT * FROM dbo.PopularityPokemon WHERE Dex_ID = '445')
   UPDATE dbo.PopularityPokemon SET TimesPicked = TimesPicked + 1
ELSE
   INSERT into dbo.PopularityPokemon (Dex_ID, TimesPicked)VALUES('445', 1);

我现在需要从代码隐藏中做到这一点。

我有以下两个参数:

private const int _pickCount = 1;
string dexID

dexID 是我调用的函数的参数。

创建新连接并打开它的标准过程工作正常，但在使用 SQLCommand 时我可能遇到语法错误:

try
{
    using (myConnection)
    {
        using (SqlCommand command = new SqlCommand())
        {
            command.Connection = myConnection;
            command.CommandType = CommandType.Text;
            command.CommandText = "IF EXISTS (SELECT * FROM dbo.PopularityPokemon WHERE Dex_ID = @Dex_ID) UPDATE dbo.PopularityPokemon SET TimesPicked = TimesPicked + @PickCount"
                + "ELSE" 
                + "INSERT into dbo.PopularityPokemon (Dex_ID, TimesPicked)"
                + "VALUES(@Dex_ID, @PickCount)";
            command.Parameters.AddWithValue("@Dex_ID", dexID);
            command.Parameters.AddWithValue("@PickCount", _pickCount);
            command.ExecuteNonQuery();
        }
    }
}

我错过了什么吗？

Answer 1

我认为您需要在创建命令的每个 字符串的末尾放置一个空格。

command.CommandText = "IF EXISTS (SELECT * FROM dbo.PopularityPokemon WHERE Dex_ID = @Dex_ID) UPDATE dbo.PopularityPokemon SET TimesPicked = TimesPicked + @PickCount "
            + "ELSE " 
            + "INSERT into dbo.PopularityPokemon (Dex_ID, TimesPicked) "
            + "VALUES(@Dex_ID, @PickCount)";

或者使用逐字字符串文字作为；

command.CommandText = @"IF EXISTS (SELECT * FROM dbo.PopularityPokemon WHERE Dex_ID = @Dex_ID) UPDATE dbo.PopularityPokemon SET TimesPicked = TimesPicked + @PickCount 
                        ELSE 
                        INSERT into dbo.PopularityPokemon (Dex_ID, TimesPicked) 
                        VALUES(@Dex_ID, @PickCount)";

也尽量不要使用AddWithValue。 It may generate unexpected and surprising results sometimes .使用 Add 方法重载来指定您的参数类型 ( SqlDbType ) 及其大小。