SQL Server Table-Valued Stored Procedure Parameters <=> ADO.Net

Nutshell:

  1. Declare a User Defined Type (UDT)
  2. Declare a stored proc parm of that UDT
  3. Fill an ADO.Net DataTable with the same columns as the UDT
  4. Assign the DataTable to a Parameter of an ADO.Net SqlCommand corresponding to the sproc

Notes:

Code Examples:

  1. File_UDT.sql
  2. Files_UploadCompare.sql
  3. FileSystemNode.cs
  4. MainWindow.xaml.cs

Tips:

  • (from here): If the login that SqlCommandBuilder.DeriveParameters is run under does not have permission to access the UDT, no error will be thrown – the method will return successfully, but the SqlCommand.Parameters collection will not contain the UDT parameter.!!!
  • Granting permissions on a type (from here): GRANT EXECUTE ON TYPE::dbo.MyType TO public;

Links: