Can I use LINQ to convert a List<MyObjectType> into a DataSet?

Question

I am completely new to LINQ in C#/.NET. I understand that I could use it to convert a DataSet into an Array/List, am I able to go in the opposite direction?

I'm using NPlot to generate a graph of captured prices, which are stored in a List, where PriceInformation is a class containing two public doubles and a DateTime.

Any suggestions very welcome.

Answer 1

There's a method called CopyToDataTable. That method will only help if you already have a IEnumerable(DataRow)

Here's how I'd do this:

//extension method to convert my type to an object array.
public static object[] ToObjectArray(this MyClass theSource)
{
  object[] result = new object[3];
  result[0] = theSource.FirstDouble;
  result[1] = theSource.SecondDouble;
  result[2] = theSource.TheDateTime;

  return result;
}


//some time later, new up a dataTable, set it's columns, and then...

DataTable myTable = new DataTable()

DataColumn column1 = new DataColumn();
column1.DataType = GetType("System.Double");
column1.ColumnName = "FirstDouble";
myTable.Add(column1);

DataColumn column2 = new DataColumn();
column2.DataType = GetType("System.Double");
column2.ColumnName = "SecondDouble";
myTable.Add(column2);

DataColumn column3 = new DataColumn();
column3.DataType = GetType("System.DateTime");
column3.ColumnName = "TheDateTime";
myTable.Add(column3);

// ... Each Element becomes an array, and then a row
MyClassList.ForEach(x => myTable.Rows.Add(x.ToObjectArray());

Answer 2

if MyObjectType is a linq-generated entity, and those objects are not already associated to a data context you can call

foreach( MyObjectType value in myList )
{
    dataContext.MyObkectTypes.InsertOnSubmit(value);
}
dataContext.SubmitChanges();

However, at this time linq-to-sql isn't terribly efficient at bulk updates. If myList was 1000 items, you would have 1000 insert statements.

For very large lists you could convert the List<MyObjectType> into xml and use sql servers ability to bulk insert using xml. You would attach the sql server stored procedure to the datacontext.

string xml = CreateInsertXml( myList );
dataContext.usp_MyObjectsBulkInsertXml(xml);

---

example of sql server stored procedure for bulk insert via xml

-- XML is expected in the following format:
--
--  <List>
--   <Item>
--    <PlotID>1234</PlotID>
--    <XValue>2.4</SmsNumber>  
--    <YValue>3.2</ContactID>
--          <ResultDate>12 Mar 2008</ResultDate>
--   </Item>
--   <Item>
--    <PlotID>3241</PlotID>
--    <XValue>1.4</SmsNumber>  
--    <YValue>5.2</ContactID>
--          <ResultDate>3 Mar 2008</ResultDate>
--   </Item>
--  </List>

CREATE PROCEDURE [dbo].usp_MyObjectsBulkInsertXml
(
    @MyXML XML
)
AS

DECLARE @DocHandle INT
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @MyXML

INSERT INTO MyTable (
    PlotID,
    XValue,
    YValue,
    ResultDate
) 
SELECT
    X.PlotID,
    X.XValue,
    X.YValue,
    X.ResultDate
FROM OPENXML(@DocHandle, N'/List/Item', 2)
WITH (
    PlotID INT,
    XValue FLOAT,
    YValue FLOAT,
    ResultDate DATETIME
) X

EXEC sp_xml_removedocument @DocHandle

GO