Passing DataTable over COM into R

Question

I am trying to pass data from SQL, to C#, then to an R server for data analysis then back to my web application; however, the COM interface that I am using does not allow complex data types to be passed between C# and R (no data tables). I have gotten it to work in the past by using the following code:

    int count = dataTable.Rows.Count;
    object[] y = new object[count];
    object[] x = new object[count];

    //R does not accept DataTables, so here we extract the data from
    //the table and pass it into 2 double arrays.
    for (int i = 0; i < count; i++)
    {
        y[i] = Convert.ToDouble(dataTable.Rows[i][0]);
        x[i] = Convert.ToDouble(dataTable.Rows[i][1]);
    }
    //Instantiate R connection
    StatConnector r = new STATCONNECTORSRVLib.StatConnectorClass();
    r.Init("R");
    r.SetSymbol("y", y);  //Passes column y into R
    r.SetSymbol("x", x);  //Passes column x into R

My problem now arises because I am no longer limited to just doubles, anything coming out of the SQL Database is fair game (int, varchar, etc...), and that I am no longer calling just 2 columns of data (it can be however many the user specifies).

How can I convert a datatable of dynamic size and dynamic data types into an array that would be safe to pass over into rcom?

Answer 1

In Professional Visual Basic 6.0 Business Objects by Rockford Lhotka (http://search.barnesandnoble.com/Professional-Visual-Basic-60-Business-Objects/Rockford-Lhotka/e/9781861001078), he makes various statements that the most effective data transfer structure between COM Interfaces across application boundaries is the String. I do not know if this is true, but I accept his qualifications. Therefore, I believe Biff MaGriff suggestion would be the a good simple implementation to your problem.

Answer 2

I would use CSV. Of course, I know nothing about RCOM :s Good luck!

public static string DataTableToCSV(DataTable myTable)
{
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < myTable.Columns.Count; i++)
    {
        sb.Append("\"");
        sb.Append(myTable.Columns[i].ColumnName);
        sb.Append("\"");
        if (i < myTable.Columns.Count - 1)
            sb.Append(",");
    }
    sb.AppendLine();
    foreach (DataRow dr in myTable.Rows)
    {
        for (int i = 0; i < dr.ItemArray.Length; i++)
        {
            sb.Append("\"");
            sb.Append(dr.ItemArray[i].ToString());
            sb.Append("\"");
            if (i < dr.ItemArray.Length - 1)
                sb.Append(",");
        }
        sb.AppendLine();
    }
    return sb.ToString();
}