Table-Valued Parameters: Basics
First, what is Table-Valued Parameters ?
Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data.
You can use table-valued parameters (TVP) to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.
Great news, you can use TVP with CodeFluent Entities! Let’s see how you can use TVP to bulk load rows based on a list of id.
Create a new CFQL method:
Note the usage of “” after the type name. The generated method is:
public static CustomerCollection LoadByIds(System.Guid ids)
And the generated stored procedure:
CREATE TYPE [dbo].[cf_type_Customer_LoadByIds_0] AS TABLE ( [Item] [uniqueidentifier] NULL) GO CREATE PROCEDURE [dbo].[Customer_LoadByIds] ( @ids [dbo].[cf_type_Customer_LoadByIds_0] READONLY ) AS SET NOCOUNT ON DECLARE @_c_ids int; SELECT @_c_ids= COUNT(*) FROM @ids SELECT DISTINCT [Customer].[Customer_Id], [Customer].[Customer_FirstName], [Customer].[Customer_LastName], [Customer].[Customer_DateOfBirth] FROM [Customer] WHERE [Customer].[Customer_Id] IN (((SELECT * FROM @ids))) RETURN GO
Table-Valued Parameters require at least SQL Server 2008. Don’t forget to change the target of the SQL Server producer to use at least this version. Additionally set Legacy String Array Mode to false.
Additionally set Legacy String Array Mode to false.
The R&D Team.