Передача коллекции объектов в качестве параметра в хранимую процедуру SQL Server

Question

У меня есть общий вопрос о том, можно ли что-то сделать - и будет ли это наиболее эффективным способом сделать это!

Подводя итог: могу ли я передать коллекцию объектов в качестве параметра хранимой процедуре?

Допустим, у меня есть таблица SQL Server с именем Users [UserID, Forename, Surname]и еще один стол называется Hobbies [HobbyID, UserID, HobbyName, HobbyTypeID]

Эта настройка предназначена для записи нескольких хобби с пользователем.

В моем приложении я хочу обновить запись пользователя.

Обычно - я обновляю пользовательскую таблицу, а затем в коде, перебираю каждое хобби и обновляю запись в таблице хобби за записью.

Если я обновляю имя пользователя и 2 его хобби, для этого потребуется 3 вызова базы данных.

(1 вызов хранимой процедуры для обновления имени и фамилии и 2 вызова хранимой процедуры для обновления 2 записей хобби)

Мой вопрос:
Могу ли я сделать всего один вызов в базу данных, передав все параметры только одной хранимой процедуре.

например.

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()
}