Databases
StackOverflow2013
Postsdbo
POUsing a list of datarows to store data
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing a list of datarows to store data
    text
    copied!<p>I have a master-detail relationship between 2 classes. The master class will contain a list of many details. Currently I was using </p> <pre><code>public class Master: cloneable&lt;T&gt; { //other properties here... private List&lt;detailClass&gt; details public List&lt;detailClass&gt; Details { get { return details; } } } </code></pre> <p>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.</p> <p>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? </p> <p>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.</p> <hr> <p>Following <a href="https://stackoverflow.com/questions/3548764/using-a-list-of-datarows-to-store-data/3549030#3549030">casperOne's answer</a> I used the <code>IEnumerable&lt;SqldataRecord&gt;</code> 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.</p> <pre><code>public class DetailCollection: List&lt;Detail&gt;, IEnumerable&lt;SqlDataRecord&gt; { IEnumerator&lt;SqlDataRecord&gt; IEnumerable&lt;SqlDataRecord&gt;.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; } } } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload