Clever Geek Handbook

Move from dictionary <int, StringBuilder> to SqlParameter table value. How?

I have some code that has a dictionary defined as:

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

Each value in each KeyValuePair 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 '|'. The number of "rows" in a dictionary can range from 600 to 1200. I would like to use the table-valued parameter to get it all in my SQL Server 2008 database in one operation.

The parameter defined by the table 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 what can be passed to a table parameter? Should I be using something other than Dictionary? Or do I need to parse the dictionary into a different, better data structure?

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

Thank.

+2


source to share


5 answers


Due to a poor documentation issue related to TVP (it takes more than just IEnumerable) and need to parse the content of dictionaries anyway, I decided to skip the dictionary in the DataTable, which seems to be the preferred object to feed the TVP.



+1


source


Both @narayanamarthi and @ChrisGessler are on the right track regarding using an interface IEnumerable<SqlDataRecord>

instead of a DataTable. Copying a collection to a DataTable just wastes CPU and memory on no gain. But an iterator can be decoupled from a collection.

So, some notes:

  • Don't use s AddWithValue

    when creating SqlParameter

    . It's just a bad grade.
  • Don't concatenate the 3 different values ​​you want in TVP, String or StringBuilder. The whole purpose of TVP is to pass a strongly typed record, so serializing the fields to a CSV list is defective. Use the Chris recommended Invoice class instead.
  • Replace invoiceDict.Add(...)

    withList<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 like this: 

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


I have additional notes and links in the following two answers regarding FST in general:

+1


source


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

0


source


Implementing the IEnumerable interface in a class that contains your collection and then explicitly implements the IEnemerable's GetEnumerator method will serve the purpose. There is an article http://lennilobel.wordpress.com/2009/07/29/sql-server-2008-table-valued-parameters-and-c-custom-iterators-a-match-made-in-heaven/ . Please walk through

0


source


You need to implement IEnumerable<SqlDataRecord>

in a custom collection object. In your case, you must change Dictionary<int, StringBuilder>

to List<Invoice>

orList<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 just go to the custom list SqlParameter

:

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

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


source






More articles:


All Articles