Home > SQL Server > Table-Valued Parameters: Reuse model collection types

Table-Valued Parameters: Reuse model collection types


This is the last post of the Table-Valued Parameters (TVP) series. In the previous post we saw we can use TVP with CodeFluent Entities. We started with a single column TVP, then we create a more complex table type. The last thing is that CodeFluent Entities can generate a TVP per entity. Let’s see what does this means:

Database:

CREATE TYPE [dbo].[CustomerType] AS TABLE (
 [Customer_Id] [int] NOT NULL,
 [Customer_FirstName] [nvarchar] (256) NULL,
 [Customer_LastName] [nvarchar] (256) NULL,
 [_rowVersion] [binary] (8) NULL)
GO

CREATE PROCEDURE [dbo].[Category_ProcessCategoriesWithCollection]
(
 @categories [dbo].[CategoryType] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_categories int; SELECT @_c_categories= COUNT(*) FROM @categories
SELECT * FROM Category
INNER JOIN @categories AS c
ON [Category].[Category_Name] LIKE c.Category_Name + '%'
RETURN
GO

Business Object Model:

public static void ProcessCategoriesWithCollection(
    Samples.TVP.CategoryCollection categories
)
{
    CodeFluent.Runtime.CodeFluentPersistence persistence =
      CodeFluentContext.Get(
        Samples.TVP.Constants.Samples_TVPStoreName
      ).Persistence;
    persistence.CreateStoredProcedureCommand(
      null,
      "Category",
      "ProcessCategoriesWithCollection");
    persistence.AddArrayParameterObject("@categories", categories);
    ...
}

So we have a strongly typed code generated the model. How do we generate this code?

First we have to set the table type name (name used to create the database type)


Then we indicate the BOM producer to use user defined type (UDT):


Yep, only two simple attributes to set and CodeFluent Entities generates lots of code for you J

Happy Querying,

The R&D Team.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s