将对象集合作为参数传递到SQL Server存储过程

Question

关于是否可以做某事，以及是否将是最有效的做事方式，我有一个普遍的问题！

总结一下：我可以将对象集合作为参数传递给存储过程吗？

假设我有一个名为SQL Server的表Users [UserID, Forename, Surname] ，另一个名为Hobbies [HobbyID, UserID, HobbyName, HobbyTypeID]

此设置是为了记录用户的多个爱好。

在我的应用程序中，我想更新用户记录。

通常-我将更新用户表，然后在代码中循环遍历每个爱好，并逐条记录更新爱好表记录。

如果我要更新用户的姓氏和他们的2个爱好，则需要对数据库进行3次调用。

（1调用存储过程以更新姓氏/姓氏，2调用存储过程以更新2项爱好记录）

我的问题是：是否
可以通过将所有参数传递给1个存储过程来仅对数据库进行1次调用。

例如。

intUserID = 1
strForename = "Edward"
strSurname = "ScissorHands"

dim objHobbyCollection as New List(Of Hobby)
'Assume that I have 2 hobby objects, each with their hobbyID, UserID, HobbyName & HobbyTypeID

Dim params As SqlParameter()
params = New SqlParameter() {
    New SqlParameter("@UserID", intUserID),
    New SqlParameter("@Forename", strForename),
    New SqlParameter("@Surname", strSurname),
    New SqlParameter("@Hobbies", objHobbyCollection) 
    }

我可以这样做吗？（哪种方式更有效？）存储过程是什么样的？

ALTER PROCEDURE [dbo].[User_Update]

 @UserID    INT
,@Forename      NVARCHAR(50) = NULL
,@Surname   NVARCHAR(50) = NULL
,@Hobbies   ??????????????

Answer 1

假设使用SQL Server 2008+，则可以使用表值参数来执行此操作。首先在SQL Server中创建一个表类型：

CREATE TYPE dbo.HobbiesTVP AS TABLE
(
  HobbyID INT PRIMARY KEY,
  HobbyName NVARCHAR(50),
  HobbyTypeID INT
);

然后，您的存储过程将显示：

@Hobbies dbo.HobbiesTVP READONLY

在C＃中（对不起，我不了解vb.net等效项）它将如下所示（但是，如果您只有一个UserID，则不必成为集合的一部分，对吗？）：

// as Steve pointed out, you may need to have your hobbies in a DataTable.

DataTable HobbyDataTable = new DataTable();
HobbyDataTable.Columns.Add(new DataColumn("HobbyID"));
HobbyDataTable.Columns.Add(new DataColumn("HobbyName"));
HobbyDataTable.Columns.Add(new DataColumn("HobbyTypeID"));

// loop through objHobbyCollection and add the values to the DataTable,
// or just populate this DataTable in the first place

using (connObject)
{
    SqlCommand cmd = new SqlCommand("dbo.User_Update", connObject);
    cmd.CommandType = CommandType.StoredProcedure;
    // other params, e.g. @UserID
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@Hobbies", HobbyDataTable);
    tvparam.SqlDbType = SqlDbType.Structured;
    // ...presumably ExecuteNonQuery()
}