6

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   ??????????????
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
wotney
  • 1,039
  • 3
  • 21
  • 34

1 Answers1

12

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()
}
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • I'm not sure that you can pass an arbitrary collection as a Table Values Param. The MSDN docs talks of DataTable or objects that implements the DataReader interface. Also this article follows a different approach.http://blogs.inkeysolutions.com/2011/04/pass-collection-of-object-as-table.html – Steve Jul 11 '12 at 20:40
  • @Steve ok, then the collection might need to be a `List` or `DataTable`. I haven't tried this explicitly, I've only used `DataTable`, but I expected a collection to be compatible. – Aaron Bertrand Jul 11 '12 at 20:41
  • We do this with collections instead of datatables. Primarily it bypasses the need to translate existing collections into datatables prior to pushing into the TVP: http://lennilobel.wordpress.com/2009/07/29/sql-server-2008-table-valued-parameters-and-c-custom-iterators-a-match-made-in-heaven/ – NotMe Jul 11 '12 at 20:57
  • Thanks very much guys, I shall be looking into getting this working. No-one commented on which method would be most efficient - any ideas ? – wotney Jul 12 '12 at 08:06
  • @wotney do you mean which would be more efficient between a DataTable and a collection? If so, I have no idea, I have never profiled those from the C# side. If they both deal with TVPs then I have to assume the biggest bottleneck (the database) would be constant, and any differences between them in C# would be negligible. – Aaron Bertrand Jul 12 '12 at 12:22