如何修改更新命令npgsql postgresql c#的设置值 [英] How to modify the set value of update command npgsql postgresql c#

问题描述

我一直在尝试在 c# 上修改 postgresql 的 sql 命令.注释引号中的行是有效的，但我需要将集合从预定义更改为变量.虽然之后使用相同的方式将其定义为变量，使用用户在另一个表单上给出的文本，它似乎不起作用.用变量替换first_name"的正确方法是什么?

i've been trying to modify an sql command for postgresql on c#. The line in comment quotes is the one working, but i need to change the set from pre-defined to variable. Although after using the same way to define it as a variable, with a text given by the user on another form, it doesnt seem to work. Which is the correct way to replace the "first_name" with a variable ?

   String oldname = Actor.oldname;
String newname = Actor.newname;
String column = Actor.columnname;

try
    {
        string connstring = "Server=127.0.0.1; Port=5432; User Id=postgres; Password=72677267; Database=imdb;";

        NpgsqlConnection connection = new NpgsqlConnection(connstring);

        connection.Open();

        //NpgsqlCommand command = new NpgsqlCommand("UPDATE actor SET first_name = " + newname + " WHERE first_name =" + oldname + "", connection);

        NpgsqlCommand command = new NpgsqlCommand("UPDATE actor SET " + column + " = " + newname + " WHERE " + column + " =" + oldname + "", connection);

        NpgsqlDataReader dataReader = command.ExecuteReader();

        connection.Close();

         return dataItems;
    }
catch (Exception msg)
    {
        MessageBox.Show(msg.ToString());
        throw;
    }

推荐答案

您的上述代码示例存在一些问题，应该会使其无法工作.我对注释的代码行工作感到有点震惊.

You have a few issues with your code sample above that should prevent it from working. I'm a little shocked the commented code line works.

1. 您没有引用您更新的值
2. 即使您确实引用了它们，也应该使用参数.
3. 数据读取器用于读取.如果您正在执行 DML，您应该使用 ExecuteNonQuery.如果您有返回值，则可能需要使用数据读取器，但在这种情况下，您似乎不需要它.

1. You are not quoting the values you update
2. Even if you did quote them, you should use parameters instead.
3. The datareader is for reading. If you are executing DML, you should use ExecuteNonQuery. If you have returning values, there may be cause for a datareader, but in this case you don't appear to need it.

综上所述，动态 SQL 有时是不可避免的，但我建议在放弃和制作动态 SQL 之前尽可能使用所有可能的资源.如果必须，缓解这种情况的一种方法可能是拥有有限数量的选项，因此与其让他们更新任何字段，不如让他们从选项列表中选择一个字段.

All that said, dynamic SQL is sometimes unavoidable, but I'd recommend every possible recourse before giving up and making dynamic SQL. If you have to, one way to mitigate this might be to have a finite number of options, so rather than letting them update ANY field, let them choose a field from a list of options.

这仍然是动态 SQL，但它至少是参数化的，注入的可能性至少限于方法，这使得任何注入的可能性很小.

This is still dynamic SQL, but it's at least parameterized, and the injection possibilities are at least limited to the method, which makes any injection very unlikely.

public enum ActorField
{
    FirstName,
    LastName,
    Salutation
}

public void UpdateActorField(string OldName, string NewName, ActorField FieldId)
{
    string sql = "update actor set {0} = :NEW_NAME where {0} = :OLD_NAME";

    switch (FieldId)
    {
        case ActorField.FirstName
            sql = string.Format(sql, "first_name");
            break;
        case ActorField.LastName
            sql = string.Format(sql, "last_name");
            break;
        case ActorField.Salutation
            sql = string.Format(sql, "salutation");
            break;
    }

    using (NpgsqlCommand cmd = new NpgsqlCommand(sql, connection))
    {
        cmd.Parameters.AddWithValue("NEW_NAME", NewName);
        cmd.Parameters.AddWithValue("OLD_NAME", OldName);

        int updatedRows = cmd.ExecuteNonQuery();
    }
}

有一些更可爱的方法可以使用单个 SQL 语句而不使用动态 SQL 来完成此操作，但它们增加了很多复杂性，使其看起来应该是一个简单的问题.

There are cuter ways to do this with a single SQL statement and with no dynamic SQL, but they add a lot of complexity to what looks like it should be a simple problem.

这篇关于如何修改更新命令npgsql postgresql c#的设置值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！