将对象集合作为参数传递到SQL Server存储过程 [英] Passing an object collection as a parameter into SQL Server stored procedure

问题描述

对于是否可以做某件事，以及是否将成为最有效的做事方式，我有一个普遍的疑问！

I have a general question on whether something can be done - and whether it will be the most efficient way of doing it !

总结:我可以将对象集合作为参数传递给存储过程吗?

To summarise: can I pass an object collection as a parameter to a stored procedure?

比方说，我有一个名为 Users [UserID，Forename，Surname] 的SQL Server表另一个表称为 Hobbyies [HobbyID，UserID，HobbyName，HobbyTypeID]

Let's say that I have a SQL Server table called Users [UserID, Forename, Surname] and another table called Hobbies [HobbyID, UserID, HobbyName, HobbyTypeID]

此设置是针对用户记录多个爱好.

This set up is to record multiple hobbies against a user.

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

In my application, I want to update the user record.

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

Normally - I would update the user table and then in code, loop through each hobby and update the hobbies table record by record.

如果我要更新用户的姓氏和他们的2个爱好，这将需要3次调用数据库.

If I'm updating the user forename and 2 of their hobbies, this would require 3 calls to the database.

(1次调用存储过程以更新姓氏/姓氏，2次调用存储过程以更新2条嗜好记录)

(1 call to a stored procedure to update the forename/surname, and 2 calls to a stored procedure to update the 2 hobby records)

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

My question is:
Can I make just 1 call to the database by passing all the parameters to just 1 stored procedure.

例如

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

我可以这样做吗?(哪种方法会更有效?)存储过程是什么样的?

Can I do this ? (and which way would be more efficient?) What would the Stored Procedure look like ?

ALTER PROCEDURE [dbo].[User_Update]

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

推荐答案

假定使用SQL Server 2008+，则可以使用表值参数进行此操作.首先在SQL Server中创建表类型:

Assuming SQL Server 2008+, you can do this using a table-valued parameter. First in SQL Server create a table type:

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

然后您的存储过程将显示:

Then your stored procedure would say:

@Hobbies dbo.HobbiesTVP READONLY

在C#中(很抱歉，我不知道vb.net的等效语言)如下所示(但是，如果您只有一个UserID，则不必成为集合的一部分，对吗?):正如Steve所指出的，

In C# (sorry I don't know vb.net equivalent) it would be as follows (but if you just have one UserID, this doesn't need to be part of the collection, does it?):

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

这篇关于将对象集合作为参数传递到SQL Server存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！