CODEWITHSUNDEEP
c#sqlsql-serverentity-frameworklist
Patrice Pezillier
Patrice Pezillier

Reputation: 4586

How can I pass a list of object in a SQL Procedure with SQL Server 2008?

How can I pass a list of object in a SQL Procedure with SQL Server 2008 ?

I want to avoid this : 

foreach (var item in itemList)
{
   myContext.ExecuteStoreCommand("EXEC MyAtomicProc ... item ...);
}

I want to do the foreach in a store procedure.

How can I pass a c# list of object and execute this procedure in EF :

myContext.ExecuteStoreCommand("EXEC MyGlobalProc ... itemList ...);

Upvotes: 1

Views: 1126

Answers (2)

Slappy
Slappy

Reputation: 4092

What I like to do is serialize the list of objects into a string, then pass that as a DB parameter.

Then in the stored proc you can use XML transform to create a temp table that you can join in onto. This lets you easily do bulk updates and inserts (using left join)

This converts a list of key value pairs to a temp table. You can extract it to a list of anything really.:

DECLARE @xmlHandle INT 
DECLARE @pixelParametersTable TABLE (
 [Key] VARCHAR(10) PRIMARY KEY, 
 [Value] VARCHAR(MAX)) 
EXEC sp_xml_preparedocument @xmlHandle output, @pixelParameters

INSERT INTO @pixelParametersTable 
SELECT [Key], [Value]
FROM  OPENXML (@xmlHandle, '/PixelRequestParameters/Parameter',1)
WITH (  
 [Key] VARCHAR(10) 'Key',
 [Value] VARCHAR(8000) 'Value'
) 
EXEC sp_xml_removedocument @xmlHandle

Upvotes: 1

TomTom
TomTom

Reputation: 62157

You can not. You can:

  • Provide scalar values (variables)
  • Provide a TABLE of values

But you CAN NOT pass in objects. Objects do not exist at the SQL layer in a way that is compatible with the .NET runtime.

Upvotes: 1

Related Questions