从两个逗号分隔的参数作为输入更新表 [英] Update a table from two comma separated parameter as input

问题描述

我在前端有一个 Gridview，其中 Grid 有两列:ID 和 Order，如下所示:

I have a Gridview in front end where Grid have two columns : ID and Order like this:

 ID        Order

 1           1
 2           2
 3           3
 4           4

现在用户可以像在前端 Gridview 中一样更新订单:

Now user can update the order like in front end Gridview:

ID        Order

 1           2
 2           4
 3           1
 4           3

现在，如果用户单击保存按钮，ID 和订单数据将作为@sID = (1,2,3,4) 和@sOrder = (2,4,1,3) 发送到存储过程

Now if the user click the save button the ID and order data is being sent to Stored Procedure as @sID = (1,2,3,4) and @sOrder = (2,4,1,3)

现在，如果我想更新订单并进行保存，我想将其存储到数据库中.通过存储过程如何更新到表中以便更新表，而选择它给我的结果如下:

Now if I want to update the order and make save I want to store it into database. Through Stored procedure how can update into the table so that the table is updated and while select it gives me the results like:

ID        Order

 1           2
 2           4
 3           1
 4           3

推荐答案

你可以使用 表值参数 以避免将分隔符分隔的值甚至 XML 发送到数据库.为此，您需要:

You could use a table valued parameter to avoid sending delimiter-separated values or even XML to the database. To do this you need to:

1. 在数据库中声明一个参数类型，像这样:

1. Declare a parameter type in the database, like this:

CREATE TYPE UpdateOrderType TABLE (ID int, Order int)

之后就可以定义过程来使用参数了

After that you can define the procedure to use the parameter as

CREATE PROCEDURE UpdateOrder (@UpdateOrderValues UpdateOrderType readonly)
AS
BEGIN
  UPDATE t
    SET OrderID = tvp.Order
    FROM <YourTable> t
      INNER JOIN @UpdateOrderValues tvp ON t.ID=tvp.ID
END

如您所见，与解析 XML 或分隔字符串相比，SQL 是微不足道的.

As you can see, the SQL is trivial compared to parsing XML or delimited strings.

使用 C# 中的参数:

Use the parameter from C#:

using (SqlCommand command = connection.CreateCommand()) {
  command.CommandText = "dbo.UpdateOrder";
  command.CommandType = CommandType.StoredProcedure;

  //create a table from your gridview data
  DataTable paramValue = CreateDataTable(orderedData) 
  SqlParameter parameter = command.Parameters
                            .AddWithValue("@UpdateOrderValues", paramValue );
  parameter.SqlDbType = SqlDbType.Structured;
  parameter.TypeName = "dbo.UpdateOrderType";

  command.ExecuteNonQuery();
}

其中 CreateDataTable 类似于:

//assuming the source data has ID and Order properties
private static DataTable CreateDataTable(IEnumerable<OrderData> source) {
  DataTable table = new DataTable();
  table.Columns.Add("ID", typeof(int));
  table.Columns.Add("Order", typeof(int));
  foreach (OrderData data in source) {
      table.Rows.Add(data.ID, data.Order);
  }
  return table;
}

(代码取自 this问题)

如您所见，这种方法(特定于 SQL-Server 2008 及更高版本)可以更轻松、更正式地将结构化数据作为参数传递给过程.更重要的是，您一直在使用类型安全，因此在字符串/xml 操作中往往会出现的大部分解析错误都不是问题.

As you can see this approach (specific to SQL-Server 2008 and up) makes it easier and more formal to pass in structured data as a parameter to a procedure. What's more, you're working with type safety all the way, so much of the parsing errors that tend to crop up in string/xml manipulation are not an issue.

这篇关于从两个逗号分隔的参数作为输入更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！