/*

I wasn't able to find a single example on how to actually use Dapper's new TVP, so I though I'd add one.

First of all, you will need to install the Dapper.TVP package from NuGet.
 
The main item to note is the need to create and populate a list of SqlDataRecords.  This is then used to used as part of the 
input parameter for Dapper's TableValueParameter.

The API is thus:
 new TableValueParameter("<@ParameterName>", "<TableValuedParameterType>", IEnumerable<SqlDataRecord>) where IEnumerable<SqlDataRecord> would be a list of records containing Ids or whatever was defined for the TableValuedParameterType.
                                                                      
 
Sample code:
*/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Dapper;
using Dapper.Tvp;
using Microsoft.SqlServer.Server;
 
namespace DataTableParameterConsoleApplication
{
    class Program
    {
      // Standard SQL server connection string stuff:
        private static string userName = "<username>";
        private static string password = "<password>";
        private static string dataSource = "<server>"; //database server, etc.
        private static string sampleDatabaseName = "<database_name>"; //i.e. "master", etc.
 
        static void Main(string[] args)
        {
            // Build the connection string from the variables defined above:
            var connString2Builder = new SqlConnectionStringBuilder();
            connString2Builder.DataSource = dataSource;
            connString2Builder.InitialCatalog = sampleDatabaseName;
            connString2Builder.Encrypt = true;
            connString2Builder.TrustServerCertificate = false;
            connString2Builder.UserID = userName;
            connString2Builder.Password = password;
 
            // Manual load an array of values for test purposes.  Typically these values would
            // be passed in from somewhere else.
            var updatedOrderIds = new [] { 1, 100, 10, 20 };
        
            // These next two sections are the parts that make the whole thing work:
            // Create a SqlDataRecord list to hold the "rows" you'll add shortly:
            var updatedOrderIdsParameter = new List<SqlDataRecord>();
            
            // Create the metadata once.  The metadata can contain multiple columns.
            // In this example there's just a single Id (int) column.
            // Basically this just matches the column name and data type of the 
            // SQL TableType variable you created in the database.
            var myMetaData = new SqlMetaData[] {new SqlMetaData("Id", SqlDbType.Int)};  
            
            // Populate the list with records containing the Ids from the Id array (above).
            // Note: This could be strings of whatever depending on how you defined the "table" 
            // in the SqlMetaData[] (above).
            // Also note the ordinal value of the column is provided since there can be
            // more than one column.
            foreach (var num in updatedOrderIds)
            {
                // Create a new record, i.e. row.
                var record = new SqlDataRecord(myMetaData);
                // Set the 1st colunm, i.e., position 0 with the correcponding value:
                record.SetInt32(0, num);
                
                // Add the new row to the table rows array:
                updatedOrderIdsParameter.Add(record);
            }
            
            // Once we have this "table" loaded, we just pass it as an input parameter to 
            // the TableValueParameter constructor, and we're done.
            try
            {
                using (var conn = new SqlConnection(connString2Builder.ToString()))
                {
                    conn.Open();
 
                    // Execute the user defined function or stored procedure and fetch the
                    // matching results:
                    var orderHistoryEntries =
                        conn.Query<dynamic>("select * from udf_GetOrderHistoryForOrderIds(@OrderIds)",
                                    new TableValueParameter("@OrderIds", "OrderIdsTableType",
                                                                       updatedOrderIdsParameter));
                                                                       
                    // Do something with the results:
                    // Additional code here...
                }  
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}