Small utility method to take any list of objects in C# and import them to MS SQL as a temp table, do something with it and then dispose the temp table of data

ImportData.cs

public static void ImportData<T>(string tableName, List<T> data, string connectionString, Func<string, SqlConnection, SqlTransaction, bool> funcWithData, Dictionary<string, Type> customColumns = null)
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        var transaction = connection.BeginTransaction();
        try
        {
            var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            var columns = properties.Select(o => $"{o.Name} {GetTSqlType(o.PropertyType)}").ToList();
            if (customColumns != null)
            {
                foreach (var key in customColumns.Keys)
                {
                    columns.Add($"{key} {GetTSqlType(customColumns[key])}");
                }
            }

            var tempTableName = $"##{tableName}";
            using (var cmd = new SqlCommand($"CREATE TABLE {tempTableName} ({string.Join(",", columns)})", connection, transaction))
            {
                cmd.ExecuteNonQuery();
            }

            var localTempTable = new DataTable(tableName);

            foreach (var pi in properties)
            {
                var id = new DataColumn();
                id.DataType = Nullable.GetUnderlyingType(pi.PropertyType) ?? pi.PropertyType;
                id.ColumnName = pi.Name;
                localTempTable.Columns.Add(id);
            }

            foreach (var item in data)
            {
                var row = localTempTable.NewRow();
                foreach (var pi in properties)
                {
                    row[pi.Name] = pi.GetValue(item) ?? DBNull.Value;
                }
                localTempTable.Rows.Add(row);

            }
            localTempTable.AcceptChanges();
            using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
            {
                bulkCopy.DestinationTableName = tempTableName;
                bulkCopy.WriteToServer(localTempTable);
            }

            if (funcWithData(tempTableName, connection, transaction))
            {
                using (var cmd = new SqlCommand($"DROP TABLE {tempTableName}", connection, transaction))
                {
                    cmd.ExecuteNonQuery();
                }
                transaction.Commit();
            }
            else
            {
                transaction.Rollback();
            }
        }
        catch
        {
            transaction.Rollback();
        }
        finally
        {
            transaction.Dispose();
            connection.Close();
        }
    }
}

public static string GetTSqlType(Type type)
{
    type = Nullable.GetUnderlyingType(type) ?? type;
    if (type == typeof(long))
    {
        return "BIGINT";
    }
    if (type == typeof(byte[]))
    {
        return "VARBINARY(MAX)";
    }
    if (type == typeof(bool))
    {
        return "BIT";
    }
    if (type == typeof(string) || type == typeof(char[]))
    {
        return "NVARCHAR(MAX)";
    }
    if (type == typeof(DateTime))
    {
        return "DATETIME";
    }
    if (type == typeof(DateTimeOffset))
    {
        return "DATETIMEOFFSET";
    }
    if (type == typeof(decimal))
    {
        return "DECIMAL(20,5)";
    }
    if (type == typeof(double))
    {
        return "FLOAT";
    }
    if (type == typeof(int))
    {
        return "INT";
    }
    if (type == typeof(float))
    {
        return "REAL";
    }
    if (type == typeof(short))
    {
        return "SMALLINT";
    }
    if (type == typeof(TimeSpan))
    {
        return "TIME";
    }
    if (type == typeof(Guid))
    {
        return "UNIQUEIDENTIFIER";
    }
    throw new NotImplementedException(nameof(GetTSqlType));
}