how to convert the entire content of a DataTable column to a delimited string in C#?
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 da开发者_StackOverflow中文版ta 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".
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.
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.
This could help: How to convert a DataTable to a string in C#?
精彩评论