c# - 使用 ADO.NET 更新一行中的多个列

Question

我正在开发一个项目(非公开)，并且我正在尝试使用 ADO.NET 更新我的数据库代码。我已经编写了插入、检索全部、按 ID 检索和按状态检索的工作代码，我似乎无法弄清楚更新。我已经做了相当多的搜索，我拥有的代码代表了我找到并尝试适应我的程序的信息。我只是使用垃圾数据，以便在使用任何有形数据之前尝试查看更新。

下面是我在专门用于查询的类中的查询代码。

public Ticket UpdateTicket(int id, string status, int customerId, int helpDeskStaffId, string problemDesc, string resolution, string followUpRequired, string followUpComplete, DateTime ticketDate, DateTime resolvedDate)
    {
        Ticket ticket = new Ticket(id, status, customerId, helpDeskStaffId, problemDesc, resolution, followUpRequired, followUpComplete, ticketDate, resolvedDate);
        SqlCommand cmdInsert;
        SqlConnection conn = null;
        try
        {
            string connectString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
            conn = new SqlConnection(connectString);
            "UPDATE Ticket SET Status = @Status, HelpDeskStaffId = @HelpDeskStaffId, ProblemDesc = @ProblemDesc, Resolution = @Resolution, FollowUpRequired = @FollowUpRequired, FollowUpComplete = @FollowUpComplete, TicketDate = @TicketDate, ResolvedDate = @ResolvedDate, CustomerId = @CustomerId WHERE TicketId=@id";
            conn.Open();
            cmdInsert = new SqlCommand(sql2, conn);
            cmdInsert.Parameters.AddWithValue("@id", id);
            cmdInsert.Parameters.AddWithValue("@Status", status);
            cmdInsert.Parameters.AddWithValue("@HelpDeskStaffId", helpDeskStaffId);
            cmdInsert.Parameters.AddWithValue("@ProblemDesc", problemDesc);
            cmdInsert.Parameters.AddWithValue("@Resolution", resolution);
            cmdInsert.Parameters.AddWithValue("@FollowUpRequired", followUpRequired);
            cmdInsert.Parameters.AddWithValue("@FollowUpComplete", followUpComplete);
            cmdInsert.Parameters.AddWithValue("@TicketDate", ticketDate);
            cmdInsert.Parameters.AddWithValue("@ResolvedDate", resolvedDate);
            cmdInsert.Parameters.AddWithValue("@CustomerId", customerId);

        }
        catch (SqlException ex)
        {
            Console.Error.WriteLine(ex.Message);
        }
        finally
        {
            if (conn != null)
                conn.Close();
        }
        return ticket;
    }

与输入的 ID 相关的行内没有数据更新。

TicketUtil ticketUtil = new TicketUtil();
            Ticket ticket = ticketUtil.UpdateTicket(6, "Open", 1, 3, "Broken Pencils", "Buy New One", "No", "No", DateTime.Today, new DateTime(1753, 1, 1));

我的最终目标是能够使用此行上方的代码对更新进行硬编码，然后将其与一些控制台提示一起使用以允许更新信息。然而，甚至无法对解决方案进行硬编码，我什至无法考虑用户输入版本。

Answer 1

您的更新声明似乎有误。试试这个:

UPDATE Ticket 
SET Status = @Status, 
HelpDeskStaffId = @HelpDeskStaffId, 
ProblemDesc = @ProblemDesc, 
Resolution = @Resolution, 
FollowUpRequired = @FollowUpRequired, 
FollowUpComplete = @FollowUpComplete, 
TicketDate = @TicketDate, 
ResolvedDate = @ResolvedDate, 
CustomerId = @CustomerId 
WHERE TicketId=@id;

更新
正如 Alex K. 在他的评论中所写，您没有执行该命令。

我认为您的代码应该如下所示:

public Ticket UpdateTicket(int id, string status, int customerId, int helpDeskStaffId, string problemDesc, string resolution, string followUpRequired, string followUpComplete, DateTime ticketDate, DateTime resolvedDate)
{
    var ticket = new Ticket(id, status, customerId, helpDeskStaffId, problemDesc, resolution, followUpRequired, followUpComplete, ticketDate, resolvedDate);

    var connectString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;            
    var sql2 = "UPDATE Ticket SET Status = @Status, HelpDeskStaffId = @HelpDeskStaffId, ProblemDesc = @ProblemDesc, Resolution = @Resolution, FollowUpRequired = @FollowUpRequired, FollowUpComplete = @FollowUpComplete, TicketDate = @TicketDate, ResolvedDate = @ResolvedDate, CustomerId = @CustomerId WHERE TicketId=@id";

    using(var conn = new SqlConnection(connectString))
    {
        using(var cmd = new SqlCommand(sql2, conn))
        {
        cmd.Parameters.AddWithValue("@id", id);
        cmd.Parameters.AddWithValue("@Status", status);
        cmd.Parameters.AddWithValue("@HelpDeskStaffId", helpDeskStaffId);
        cmd.Parameters.AddWithValue("@ProblemDesc", problemDesc);
        cmd.Parameters.AddWithValue("@Resolution", resolution);
        cmd.Parameters.AddWithValue("@FollowUpRequired", followUpRequired);
        cmd.Parameters.AddWithValue("@FollowUpComplete", followUpComplete);
        cmd.Parameters.AddWithValue("@TicketDate", ticketDate);
        cmd.Parameters.AddWithValue("@ResolvedDate", resolvedDate);
        cmd.Parameters.AddWithValue("@CustomerId", customerId);
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            Console.Error.WriteLine(ex.Message);
        }
        }
    }
    return ticket;
}