Passing an object collection as a parameter into SQL Server stored procedure
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()
}
wotney
Updated on July 12, 2020Comments
-
wotney almost 4 years
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 calledHobbies [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 ??????????????