代码之家 › 专栏 › 技术社区 › alphadogg

从dictionary<int,stringbuilder>转到表值sqlparameter。怎么用?

table-valued-parameters dictionary sql-server-2008 sql-server c#

alphadogg · 技术社区 · 16 年前

我的代码有一个字典定义为:

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

每个keyValuePair字典中的每个值实际上是三个单独的值,当前创建如下:

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

如您所见,这三个值由一个“”分隔。字典中的“行”数可以在600到1200之间。我希望使用表值参数在一个操作中获取我的SQL Server 2008数据库中的所有这些内容。

表值参数定义如下:

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
)

从字典中获取可以传递到表值参数的内容的最佳方法是什么?我应该用字典以外的东西吗?或者,我是否需要将字典解析成另一个更好的数据结构?

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

谢谢。

5 回复 | 直到 9 年前

alphadogg 16 年前

由于TVP周围的文档有点差(不仅仅需要IEnumerable),而且无论如何都必须解析字典的内容,所以我决定将字典循环到一个数据表中,该数据表似乎是馈送TVP的首选对象。

Community Mohan Dere 9 年前

@narayanamarthi和@chrisgessler在使用 IEnumerable<SqlDataRecord> 接口而不是数据表。将集合复制到数据表只是在浪费CPU和内存,而没有收获。但是迭代器可以与集合分离。

所以有些注释:

不要使用 AddWithValue 创建时 SqlParameter S.这只是一个糟糕的惯例
不要将TVP中需要的3个不同值连接到字符串或StringBuilder中。TVP的全部目的是传递一个强类型的记录,因此将字段序列化为一个csv列表会破坏这个目的。而是使用Chris推荐的发票类。
取代你 invoiceDict.Add(...) 具有 List<Invoice>.Add(new Invoice(...));

创建要在集合上迭代的方法:

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

声明参数如下:

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

关于TVP,我在以下两个答案中有其他注释和链接:

Alfred Myers 16 年前

SqlBulkCopy 将在一个操作中发送它,但您需要将数据表或IDataReader传递给它,而不是将Dictionary<int、StringBuilder>传递给它,并且您还需要直接指向表,而不是使用存储过程。

narayanamarthi 16 年前

在包含集合的类上实现IEnumerable接口,然后显式实现IEnumerable的getEnumerator方法将达到此目的。有篇文章在 http://lennilobel.wordpress.com/2009/07/29/sql-server-2008-table-valued-parameters-and-c-custom-iterators-a-match-made-in-heaven/ . 请检查一下这个

Chris Gessler 13 年前

你需要实施 IEnumerable<SqlDataRecord> 在自定义集合对象上。在你的情况下,你应该改变你的 Dictionary<int, StringBuilder> 到 List<Invoice> 或 List<Tuple<int, string, int>>

例如:

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

然后简单地将自定义列表传递给 SqlParameter :

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

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