Skip to content

An extension to provide Table-Valued Parameters to Dapper. .NET Core support available!

License

Notifications You must be signed in to change notification settings

ayberkcanturk/Dapper.TableValuedParameter

Repository files navigation

Dapper.TableValuedParameter Build status

Installation NuGet version

It's as easy as PM> Install-Package Dapper.TableValuedParameter from nuget

Quick Start

Pass your "Tvp" class instance as a parameter to Dapper Query method.

Just an example;

	CREATE TYPE dbo.SaleOrderDetailType AS TABLE  
	( UnitPrice MONEY, OrderQty INT ) 
    
    	CREATE PROCEDURE [dbo].[GetSaleOrderDetail] @SaleOrderDetail dbo.[SaleOrderDetailType] READONLY
	AS
	BEGIN
			SELECT 
					SOD.UnitPrice, 
					SOD.OrderQty
			FROM 
					SaleOrderDetail AS SOD WITH(NOLOCK) 
					WHERE SOD.UnitPrice = @UnitPrice AND SOD.OrderQty = @OrderQty
	END    
	public class SaleOrderDetailTypeDto()
	{
		[Column(Name:"SellingPrice")]
		[Map(SqlDbType.Money)] //.NET framework data type has more than one possibility in SqlDbType such as decimal, money,smallmoney etc. So we have to explictly it.
		public decimal Price { get; set; } 

		public int OrderQty { get; set; } //It  will be automatically mapped to SqlDbType.Int
	}

	using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString))
	{
		IEnumerable<SaleOrderDetailTypeDto> tvpDto = new List<SaleOrderDetailTypeDto>()
		{
		... //This is the parameter which is going to be projected to your table-valued parameter.
		};

		return db.Query<SaleOrderDetail>("dbo.GetSaleOrderDetail", new Tvp("@SaleOrderDetail", "dbo.SaleOrderDetailType", tvpDto), commandType: CommandType.StoredProcedure).ToList(); 
    }