7

I have this SQL Server 2008 UDT:

CREATE TYPE [dbo].[IdentityType] AS TABLE(
    [Id] [int] NOT NULL
)

Pretty simple. Basically allows me to hold onto a list of id's.

I have this stored procedure:

CREATE PROCEDURE [dbo].[Scoring_ScoreMultipleLocations]
    @LocationIds [IdentityType] READONLY,
    @DoRanking BIT = 0
AS
BEGIN
   -- irrelevant code.
END

Entity Framework 4.0 does not support executing stored procedures that take user defined table types as a parameter, so i'm reverting to classic ADO.NET. The only other option is passing a comma-seperated string.

Anyway, i found this article, but it's a little hard to follow.

I don't understand what this line of code is doing:

DataTable dt = preparedatatable();

They don't even provide that method, so i have no idea what i'm supposed to do here.

This is my method signature:

internal static void ScoreLocations(List<int> locationIds, bool doRanking)
{
   // how do i create DataTable??
}

So i have a list of int and need to pump that into the UDT.

Can anyone help me out/point me to a clear article on how to achieve this?

RPM1984
  • 72,246
  • 58
  • 225
  • 350

1 Answers1

10

This article might be a bit more help.

Essentially, you'll create a new DataTable that matches the schema, then pass it as a parameter.

The code of preparedatatable() probably would look something like:

var dt = new DataTable();
dt.Columns.Add("Id", typeof(int));
return dt;

After which, you'd have to add your locationIds:

foreach(var id in locationIds)
{
    var row = dt.NewRow();
    row["Id"] = id;
    dt.Rows.Add(row);
}

Then assign dt as a parameter:

var param = cmd.Parameters.AddWithValue("@LocationIDs", dt);
param.SqlDbType = SqlDbType.Structured;
param.TypeName = "dbo.IdentityType";
Chris Shaffer
  • 32,199
  • 5
  • 49
  • 61
  • Ahh k, that makes sense. Trying out now, stay tuned... (+1 for now). – RPM1984 Jan 18 '11 at 02:43
  • works, *but* - for some reason it's taking over 10 seconds, then finally sql profiler shows "Error - 2 - Aborted". The sql produced looks fine - when i run it in ssms it takes < sec. So i'm now trying this approach instead: http://stackoverflow.com/questions/2837350/table-valued-parameter-in-stored-procedure-and-the-entity-framework-4-0 Thanks anyway – RPM1984 Jan 18 '11 at 03:42
  • I have used this without it taking 10 seconds; Not sure what would cause that. Anyway, if the parameter only needs to be a list of integers, you are probably better off with a CSV list and a split function anyway. TVPs really help out when you are trying to send in multiple associated values for each row (eg, multiple columns in your type) where with a CSV list this would require a secondary delimiter (eg, a CSV list of pipe delimited data). – Chris Shaffer Jan 18 '11 at 11:15