Following a few tutorials and such I was able to successfully create a collection class which inherits the functionality needed to create a DataTable which can be passed to a Sql Server's stored procedure as a table value parameter. Everything seems to be working well; I can get all of the rows added and it looks beautiful. However, upon closer inspection I notice that when I add a new row, the data for all of the previous rows is overwritten with the value for the new row. So if I have a row with a string value of "foo" and I add a second row with the value "bar", the second row will be inserted (making a DataTable with two rows) but both rows will have the value "bar". Can anyone see why this would be? Here is some of the code, which works but has been a bit simplified (the Tag class has been reduced for ease of explanation).
The following is the Collection class's:
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using Microsoft.SqlServer.Server;
namespace TagTableBuilder
{
public class TagCollection : List<Tag>, IEnumerable<SqlDataRecord>
{
IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
{
var sdr = new SqlDataRecord(
new SqlMetaData("Tag", SqlDbType.NVarChar)
);
foreach (Tag t in this)
{
sdr.SetSqlString(0, t.tagName);
yield return sdr;
}
}
}
public class Tag
{
public string tagName { get; set; }
}
}
These are called as follows:
//Create instance of collection
TagCollection tags = new TagCollection();
//Create instance of object
Tag _tag = new Tag();
foreach (string t in tagList)
{
//Add value to class propety
_tag.tagName = t;
//Add class to collection, this is where all previously added rows are overwritten
tags.Add(_tag);
}