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".

Do you want the data as displayed on the page i.e. one string per record? — amit_g, Apr 21 '11 at 21:23
Currently yes, one string per column, meaning all records in the column are converted into a single string. The reason for all of this though is to pass that string to a jscript variable rather than response.write() it. — Reality Extractor, Apr 21 '11 at 21:38

score 9 · Accepted Answer · edited May 23 '17 at 12:34

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.

thanks for your quick response. I am quite new to all of this and not exactly sure why VS2010 doesn't like "OfType", here's what it has to say about it: Error 1 'System.Data.DataRowCollection' does not contain a definition for 'OfType' and no extension method 'OfType' accepting a first argument of type 'System.Data.DataRowCollection' could be found (are you missing a using directive or an assembly reference?) I am working with .Net 4, the OfType seems to be pre-2.0? — Reality Extractor, Apr 21 '11 at 21:52
Your file is missing System.Linq namespace, simply add `using System.Linq;`. — k.m, Apr 21 '11 at 21:59
Brilliant! Thank you very much indeed. Sadly I can't vote this up yet. — Reality Extractor, Apr 21 '11 at 22:07
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 it like so `foreach (DataColumn column in temperature.Columns) { string s = string.Join(", ", temperature.Rows.OfType().Select(r => r[column])); // do whatever you need with s now}` — Reality Extractor, Apr 21 '11 at 22:49

score 0 · Answer 2 · answered Apr 21 '11 at 21:21

0

The link you have posted clearly states

The Expression value, if the property is set; otherwise, the ColumnName property.

and that is what is happening. You get column names.

answered Apr 21 '11 at 21:21

amit_g

30,880
8
61
118

score 0 · Answer 3 · edited May 23 '17 at 12:34

0

This could help: How to convert a DataTable to a string in C#?

edited May 23 '17 at 12:34

Community

1
1

answered Apr 21 '11 at 21:27

Priyank

10,503
2
27
25

Thanks for your reply Priyank, the traditional looping through the rows and columns and assembling a string from the return works, that's basically what my LOOP1 already does except it doesn't build the string. I am trying to understand the DataColumn property and how to use it properly. – Reality Extractor Apr 21 '11 at 21:58

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

3 Answers3