Going from Dictionary<int, StringBuilder> To Table-Valued SqlParameter. How?

Question

I have code that has a Dictionary defined as:

Dictionary<int, StringBuilder> invoiceDict = new Dictionary<int, StringBuilder>();

Each Value in each KeyValuePair the Dictionary is actually three separate values currently created as follows:

invoiceDict.Add(pdfCount+i, new StringBuilder(invoiceID.Groups[1].ToString() + "|" + extractFileName + "|" + pdfPair.Key));

As you can see, the three values are separated by a '|'. The number of "rows" in the Dictionary can range between 600-1200. I would like to use a table-valued parameter to get all of that in my SQL Server 2008 DB in one operation.

The table-valued parameter is defined as follows:

CREATE TYPE dbo.BatchSplitterInvoices AS TABLE
(
    InvoiceID varchar(24),
    NotePath varchar(512),
    BatchID varchar(50)
)

CREATE PROCEDURE dbo.cms_createBatchSplitterInvoices (
  @Invoices dbo.BatchSplitterInvoices READONLY,
  @StaffID int
)

What's the best way to get from the Dictionary to something that can be passed into the table-valued param? Should I use something else than the Dictionary? Or, do I need to parse the Dictionary into another, better data structure?

SqlParameter invoicesParam = cmd.Parameters.AddWithValue("@Invoices", invoiceDict.Values);
invoicesParam.SqlDbType = SqlDbType.Structured;

Thanks.

Answer 1

SqlBulkCopy will send it in one operation, but you will need to pass it a DataTable or an IDataReader instead of a Dictionary<int, StringBuilder> and you will also need to point directly to the table, instead of using a stored procedure.

Answer 2

Due to issue with somewhat poor documentation surrounding the TVPs (needs more than just IEnumerable), and having to parse the content of dictionaries anyways, I decided to loop through the Dictionary into a DataTable, which seems to be the preferred object to feed a TVP.

Answer 3

Implementing the IEnumerable interface on the class holding your collection and then explicitly implementing the GetEnumerator Method for the IEnemerable will serve the purpose. There is an article at http://lennilobel.wordpress.com/2009/07/29/sql-server-2008-table-valued-parameters-and-c-custom-iterators-a-match-made-in-heaven/. Please go through this