// Dapper Plus

// Doc: https://dapper-tutorial.net/parameter-table-valued-parameter

​

// @nuget: Dapper -Version 1.60.6

​

using Dapper;

using System;

using System.Data;

using System.Data.SqlClient;

public class Program

{    

    public class Customer

    {

        public int CustomerID { get; set; }

        public string Code { get; set; }

        public string Name { get; set; }

    }

    public static void Main()

    {

        CreateDatabase();

​

        var dt = new DataTable();

        dt.Columns.Add("Code");

        dt.Columns.Add("Name");

        for(int i = 0; i < 5; i++) {

            dt.Rows.Add("Code_" + i, "Name_" + i);

        }

        var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer());

        connection.Execute("Customer_Seed", new { Customers = dt.AsTableValuedParameter("TVP_Customer") }, commandType: CommandType.StoredProcedure);

​

        FiddleHelper.WriteTable("1 - Customers", connection.Query<Customer>("SELECT * FROM Customer"));

    }

    public static void CreateDatabase()

    {

        var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer());

        // CREATE the Table

        connection.Execute(@"

CREATE TABLE [Customer]

(

    [CustomerID] [INT] IDENTITY(1,1) NOT NULL,

    [Code] [VARCHAR](20) NULL,

    [Name] [VARCHAR](20) NULL,

    CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 

    (

        [CustomerID] ASC

    )

)

        ");

        // CREATE the TVP type

        connection.Execute(@"

CREATE TYPE TVP_Customer AS TABLE

(

    [Code] [VARCHAR](20) NULL,

    [Name] [VARCHAR](20) NULL

)

        ");

        // CREATE the Stored Procedure             

        connection.Execute(@"

CREATE PROCEDURE Customer_Seed

    @Customers TVP_Customer READONLY

AS

BEGIN

    INSERT INTO Customer (Code, Name)

    SELECT Code, Name

    FROM @Customers

END

        ");

    }

}