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

• The Table-Valued Stored Procedure Parameters feature was first included in SQL Server 2008
• Full working project source available here

Code Examples

1. File_UDT.sql

    CREATE TYPE File_UDT AS TABLE
    (
      FullPath varchar(900) PRIMARY KEY,
      ModifiedDate datetime,
      [Size] bigint
    )
    GO
   
    GRANT EXECUTE ON TYPE::dbo.File_UDT TO PUBLIC
    GO
   

    

2. Files_UploadCompare.sql

    CREATE PROCEDURE dbo.Files_UploadCompare
    @BackupProfileID INT,
    @NextDiscNumber INT = NULL OUT,
    @AllFiles File_UDT READONLY -- <= *****
    AS BEGIN
   
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
   
    -- new approach, simply return all files which don't match something already in the database
    -- then we don't have to worry about partial results left in the tables ...
    -- we just upload the current batch of files when we're with each burn and then start fresh with the next batch selection from there
    -- there will be no records in FileArchive unless they've been put there specifically as marking a "finalized" MediaSubset
   
    SELECT *,
      CONVERT(BIT, 0) AS Selected,
      CONVERT(BIT, 0) AS SkipError
    FROM @AllFiles a
    WHERE NOT EXISTS(
      SELECT 1
      FROM FileArchive fa
      JOIN [File] f ON fa.FileID = f.FileID
      WHERE f.FullPath = a.FullPath AND fa.ModifiedDate = a.ModifiedDate AND fa.Size = a.Size
    )
   
    DECLARE @IncrementalID int
    SELECT @IncrementalID = MAX(IncrementalID) FROM Incremental WHERE BackupProfileID = BackupProfileID
   
    SELECT @NextDiscNumber = isnull(COUNT(1),0)+1 FROM MediaSubset WHERE IncrementalID = @IncrementalID
   
    END
   

    

3. FileSystemNode.cs

    static private void ScanFolder(FolderNode folder, DataTable IncludedFiles)
    {
      DirectoryInfo dir = new DirectoryInfo(folder.FullPath);
      FileInfo[] files = dir.GetFiles("*.*", folder.IsSubSelected ? SearchOption.TopDirectoryOnly : SearchOption.AllDirectories);
      foreach (FileInfo file in files)
      {
        DataRow r = IncludedFiles.NewRow();
        r["FullPath"] = file.FullName;
        r["ModifiedDate"] = file.LastWriteTimeUtc;
        r["Size"] = file.Length; //megabytes
        IncludedFiles.Rows.Add(r);
      }
    }
   

    

4. MainWindow.xaml.cs

    using (Proc Files_UploadCompare = new Proc("Files_UploadCompare"))
    {
      Files_UploadCompare["@BackupProfileID"] = (int)cbxBackupProfiles.SelectedValue;
      Files_UploadCompare["@AllFiles"] = IncludedFilesTable; // <= ******
      WorkingFilesTable = Files_UploadCompare.ExecuteDataTable();
      lblCurrentDisc.Content = Files_UploadCompare["@NextDiscNumber"].ToString();
    }
   

Tips

• 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

• MSDN page on Table-Valued Parameters