I have a master-detail relationship between 2 classes. The master class will contain a list of many details. Currently I was using
public class Master: cloneable<T>
{
//other properties here...
private List<detailClass> details
public List<detailClass> Details
{
get { return details; }
}
}
inside the master class. While saving this class, I need to use a datatable for the details list before passing it into a sp. (since we are using table value params in sql2008). reason for using tvp, is that 1 master can contain upwards of 10k details, and tvp is a very efficient way of dumping all that info into the db very fast.
Qs:When I convert the list to a datatable for db insertion, there is a double memory usage for the same data. Is there a better way of saving the details in master, other than directly using a datatable?
Qs:I next option was to try using a List details, and do datatable.ImportRow(row). But I do not know, how I can add data into a row, without having any columns defined. I also dont know how any external object can acess the individual detail fields in such a list.
Following casperOne's answer I used the IEnumerable<SqldataRecord>
method and was able to insert data by streaming and without having to create an additional datatable in memory.To be helpful for any one else looking for similar solution, I am posting the code below.
public class DetailCollection: List<Detail>, IEnumerable<SqlDataRecord>
{
IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
{
// mapping the properties of the Detail object to the
// user defined table type in sql
SqlMetaData[] metaDataArray = new SqlMetaData[4];
//-1 indicates varchar(max) sql data type
metaDataArray[0] = new SqlMetaData("Col1", SqlDbType.VarChar, -1);
metaDataArray[1] = new SqlMetaData("Col2", SqlDbType.TinyInt);
metaDataArray[2] = new SqlMetaData("Col3", SqlDbType.VarChar,100);
metaDataArray[3] = new SqlMetaData("Col4", SqlDbType.Int);
SqlDataRecord sdr = new SqlDataRecord(metaDataArray);
foreach (Detail detailRecord in this)
{
sdr.SetValue(0, detailRecord.Property1);
sdr.SetValue(1, Convert.ToByte(detailRecord.Property2));
sdr.SetValue(2, detailRecord.Property3);
sdr.SetValue(3, detailRecord.Property4);
yield return sdr;
}
}
}