How to use Dapper's new Table Valued Parameter (TVP) in C#

gistfile1.cs

/*

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);
            }
        }
    }
}

Where could I find the source code for Dapper.Tvp?

It should be noted that as of Dapper 1.26, this Dapper.TVP NuGet package is no longer needed. Dapper now handles TVP's itself. For reference, see: http://stackoverflow.com/questions/6232978/does-dapper-support-sql-2008-table-valued-parameters and http://stackoverflow.com/questions/26242363/using-dapper-tvp-tablevalueparameter-with-other-parameters/26246018

Unfortunately .NET Core no longer supports DataTables. So, what to do, what to do.

I guess you do it with dynamic parameters https://github.com/StackExchange/Dapper/blob/61e965eed900355e0dbd27771d6469248d798293/Dapper.Tests/Tests.Parameters.cs

@jon49 It's coming back in netstandard2.0. As soon as SqlClient updates their bits, I'll get an alpha up.