/* BeejBløg */

Dec 22, 2011 - 2 minute read - Comments -

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
    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

  1. 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

  1. 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®;
    }
    }

  2. 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:

  • (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: