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?

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.

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

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

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

eg.

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   ??????????????

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating