how to convert the entire content of a DataTable column to a delimited string in C#?

Question

I am retrieving data from an MSSQL server using the SqlDataAdapter and DataSet. From that DataSet I am creating a DataTable. My goal is to convert each column of the table into a string where the elements are comma delimited. I figured that I would try the string conversion first before making the delimiter work.

The code runs in the code-behind of an ASP.Net page. The ultimate goal is to pass the string to a jscript variable, it's a "functional requirement" that I create a delimited string from the columns and that it has to end up as a jscript variable.

Here's what I have thus far:

    DataSet myDataSet = new DataSet();
    mySqlDataAdapter.Fill(myDataSet);
    DataTable temperature = myDataSet.Tables["Table"];

    // LOOP1
    foreach (DataRow row in temperature.Rows)
    // this loop works fine and outputs all elements
    // of the table to the web page, this is just to
    // test things out
    {
        foreach (DataColumn col in temperature.Columns)
        {
            Response.Write(row[col] + " ### ");
        }
        Response.Write("<br>");
    }

    // LOOP2
    foreach (DataColumn column in temperature.Columns)
    // this loop was meant to take all elements for each
    // column and create a string, then output that string
    {
        Response.Write(column.ToString() + "<br>");
    }

In LOOP1 things work fine. My data has 4 columns, all are appropriately rendered with one record per row on the web page.

I saw the code for LOOP2 at http://msdn.microsoft.com/en-us/library/system.data.datacolumn.tostring.aspx which seems to do exactly what I need except it does not actually do what I want.

The only thing LOOP2 does is write 4 lines to the web page. Each line has the header of the respective table column but none of the additional data. Clearly there's either a logic flaw on my part or I misunderstand how DataColumn and .toString for it works. Please help me out on this one. Thanks in advance.

EDIT: Here's an SQL query result example, this is what the Table looks like: Table quesry result @ ImageShack

What I want to end up are four strings, here's an example for the string that would be created from the second column: "-6.7, -7, -7.2, -7.3, -7.3".

Answer 1

This code will concatenate values from cells under each column with ", ":

foreach (var column in temperature.Columns)
{
    DataColumn dc = column as DataColumn;
    string s = string.Join(", ", temperature.Rows.OfType<DataRow>()
                                                 .Select(r => r[dc]));
    // do whatever you need with s now
}

For example, for DataTable defined as:

DataTable table = new DataTable();
table.Columns.Add(new DataColumn("Column #1"));
table.Columns.Add(new DataColumn("Column #2"));
table.Rows.Add(1, 2);
table.Rows.Add(11, 22);
table.Rows.Add(111, 222);

... it will produce "1, 11, 111" and "2, 22, 222" strings.

---

Edit: I saw you chose to declare column as var as opposed to DataColumn, is that a matter of personal preference/style or is there an issue with coding?

Consider following scenario (on the same data table example as above):

// we decide we'll use results later, storing them temporarily here
List<IEnumerable<string>> columnsValues = new List<IEnumerable<string>>();
foreach (DataColumn column in temperature.Columns)
{
    var values = temperature.Rows.OfType<DataRow>()
                                 .Select(r => r[column].ToString())
    columnsValues.Add(values);
}

We assume we now got list of list of column values. So, when we print them, like this:

foreach (var lisOfValues in columnsValues)
{
    foreach (var value in listOfValues)
    {
        Debug.Write(value + " ");
    }

    Debug.WriteLine("");
}

We expect to see 1 11 111 followed by 2 22 222. Right?

Wrong.

This code will output 2 22 222 twice. Why? Our .Select(r => r[column].ToString()) captures column variable - not its value, but variable itself - and since we don't use it immediately, once we're out of loop all we know is last value of column.

To learn more about this concept search for closures and captured variables - for example, in posts like this.

Summary:

In this very case you can go with DataColumn in foreach statement. It doesn't matter here because we're enumerating through our .Select(r => r[dc]) either way inside the loop (precisely, string.Join does that for us), producing results before we get to next iteration - whatever we capture, is used immediately.