With this setup on the database side of things, we proceed to develop our client application to work around the TVP limitation. The key to doing this is to use the SqlBulkCopy class in .NET Framework 4.6 or above. This class ‘understands’ Always Encrypted and should need minimal rework on the developer front. The reason for the minimal rework is that this class actually accepts a DataTable as parameter, which is previously what the TVP was passed as. This is an important point, because it will help minimize the changes to the application.
Let’s get this working! The high-level steps are outlined below; there is a full code listing at the end of this blog post as well.
The upshot is that, at least as of today, Table-Valued Parameters are not supported with Always Encrypted. Arvind does give an alternative, however, so click through for more information.