Going from Dictionary 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.

Incidentally, your use of StringBuilder in your example is not useful. You've concatenated the strings as you would normally, passed a single string into the StringBuilder, then returned it. It's more useful to use something like string.Concat with the individual strings as parameters to the function. — Richard Nienaber, Sep 12 '09 at 13:36
@flesh: No particular reason. I am trying to avoid multiple calls to the DB and wanted to make my client less "chatty". I am investigating this option, as opposed to using multiple params, big SQL strings or awkward XML blocks. Lastly, I can't use SqlBulkCopy because I need to process on the db. — alphadogg, Sep 12 '09 at 17:12
I've found TVPs to be painfully slow (see http://stackoverflow.com/questions/6071818) - they look like a good solution, but just about every other way is quicker. — Keith, May 20 '11 at 14:01

score 1 · Accepted Answer · answered Sep 21 '09 at 18:45

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.

score 1 · Answer 2 · edited May 23 '17 at 12:02

Both @narayanamarthi and @ChrisGessler are on the right track with regards to using the IEnumerable<SqlDataRecord> interface instead of a DataTable. Copying the collection to a DataTable is just wasting CPU and memory for no gain. But the iterator can be separate from the collection.

So some notes:

Don't use AddWithValue when creating SqlParameters. It is just a bad pratice
Don't concatenate the 3 distinct values that you want in the TVP into a String or StringBuilder. The entire purpose of a TVP is to pass in a strongly-typed record so serializing the fields into a CSV list defeats the purpose. Instead use the Invoice class as recommended by Chris.
Replace your invoiceDict.Add(...) with List<Invoice>.Add(new Invoice(...));

Create a method to iterate over the collection:

private static IEnumerable<SqlDataRecord> SendRows(List<Invoice> Invoices)
{
  SqlMetaData[] _TvpSchema = new SqlMetaData[] {
    new SqlMetaData("InvoiceID", SqlDbType.Int),
    new SqlMetaData("NotePath", SqlDbType.VarChar, 512),
    new SqlMetaData("BatchID", SqlDbType.VarChar, 50)
  };
  SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);

  foreach(Invoice _Invoice in Invoices)
  {
    _DataRecord.SetInt32(0, _Invoice.Id);
    _DataRecord.SetString(1, _Invoice.NotePath);
    _DataRecord.SetString(2, _Invoice.BatchId);
    yield return _DataRecord;
  }
}

Declare the parameter as follows:

SqlParameter _InvoiceParam = cmd.Parameters.Add("@Invoices", SqlDbType.Structured);
_InvoiceParam.Value = SendRows(Invoices);

I have additional notes and links in the following two answers with regards to TVPs in general:

Chris Gessler · Answer 3 · 2013-02-26T14:00:11.203

You need to implement IEnumerable<SqlDataRecord> on a custom collection object. In your case, you should change your Dictionary<int, StringBuilder> to List<Invoice> or List<Tuple<int, string, int>>

For example:

class Invoice
{
  public int Id { get; set; }
  public string NotePath { get; set; }
  public int BatchId { get; set; }
}

class InvoiceCollection : List<Invoice>, IEnumerable<SqlDataRecord>
{
  IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
  {
    SqlDataRecord r - new SqlDataRecord(
      new SqlMetaData("InvoiceId", SqlDataType.Int), 
      new SqlMetaData("NotePath", SqlDataType.VarChar), 
      new SqlMetaData("BatchId", SqlDataType.Int)
    );

    foreach(var item in this)
    {
      r.SetInt32(0, item.Id);
      r.SetString(1, item.NotePath);
      r.SetInt32(2, item.BatchId);
      yield return r;
    }
}

Then simply pass the custom list to a SqlParameter:

SqlCommand cmd = new SqlCommand("dbo.InvoiceInsUpd", connection);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter sqlParam = cmd.Parameters.AddWithValue("@Invoices", invoices);
sqlParam.SqlDbType = SqlDbType.Structured;

score 0 · Answer 4 · answered Oct 26 '09 at 04:53

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

Going from Dictionary To Table-Valued SqlParameter. How?

4 Answers4