CODEWITHSUNDEEP
c#sql.netdapper
PixelPaul
PixelPaul

Reputation: 2799

Dapper stored procedure that includes TableValueParameter

I'm having difficulty understanding the correct Dapper syntax to pass a parameters that includes a TableValueParameter(TVP) along with other parameters to a stored procedure. A simplified version of my code:

CLASS

public class GalleryViewModel
{
    public string GalleryName { get; set; }
    public string Category { get; set; }
    public IEnumerable<Image> Images { get; set; }
}

public class Image
{
    public string FileName { get; set; }
    public int Order { get; set; }
}

C# Snippet

var param = new DynamicParameters();
param.Add("@Images", model.Images);//TVP
param.Add("@GalleryName", model.GalleryName);
param.Add("@Category", model.Category);

connection.Execute("CreateGallery", param, commandType: CommandType.StoredProcedure);

SQL Snippet

--Create table type
CREATE TYPE GalleryImageType AS TABLE   
( 
    FileName    NVARCHAR(64),
    Order       INT
)
GO 

CREATE PROCEDURE [dbo].[CreateGallery]
    @Images             GalleryImageType READONLY,
    @GalleryName        NVARCHAR(32),
    @Category           NVARCHAR(32)
AS
-- SPROC contents here....

Upvotes: 3

Views: 2210

Answers (1)

Aldo
Aldo

Reputation: 304

Use an instance of DataTable instead of IEnumerable:

DataTable dt = new DataTable();
dt.Columns.Add("FileName");
dt.Columns.Add("Order");

foreach (var image in viewmodel.Images)
{
    dt.Rows.Add(image.FileName, image.Order);
}

var param = new DynamicParameters();
param.Add("@Images", dt);//TVP
param.Add("@GalleryName", model.GalleryName);
param.Add("@Category", model.Category);

connection.Execute("CreateGallery", param, commandType: CommandType.StoredProcedure);

Upvotes: 6

Related Questions