Convert an Data Table to a string?

Question

Been having truble with data tables. What i want do to is to take out a colum from an Excel document and later use it for creating a PDF file.

here is the code where i take out the data and puts in a Datagrid view.

void ExcelOpen()
{
     string PathConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +textBox1.Text+ ";Extended Properties=Excel 12.0;";
     OleDbConnection conn = new OleDbConnection(PathConn);
     OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("SELECT * FROM[" + textBox3.Text + "$"+Kolumn1.Text+":"+Kolumn1.Text+"]", conn);
     DataTable dt = new DataTable();
     myDataAdapter.Fill(dt);
     dataGridView1.DataSource = dt;

     // a try to convert the data to an Array.
     List<String> str = new List<string>();
     foreach (DataRow row in dt.Rows)
     {
         foreach (DataColumn col in dt.Columns)
         {

             stringColumnValue +="" + Convert.ToString(row[col]);
         }
         str.Add(stringColumnValue.Trim());
     }
     string.Join(" ", str.ToArray());
}

Possible duplicate: http://stackoverflow.com/questions/6617804/how-to-convert-a-column-of-datatable-to-a-list — Karthik, Aug 06 '15 at 11:35

CharithJ · Answer 1 · 2015-08-06T11:38:46.187

1

Use the ItemArray property of the DataRow. Try something like below.

foreach (DataRow row in dt.Rows)
{
    var stringArr = dataTable.row.ItemArray.Select(x =>x.ToString()).ToArray();
}

Bonus Note:

Below query is open for SQL injection attacks. You should use Parameters instead of string concatenation in sql statements.

OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("SELECT * FROM[" + textBox3.Text + "$"+Kolumn1.Text+":"+Kolumn1.Text+"]", conn);

edited Aug 06 '15 at 11:38

answered Aug 06 '15 at 11:33

CharithJ

46,289
20
116
131

Tryed it out, Typed MessageBox.Show(stringArr[0]); but got nothing out... at the same time the datagrid showed a 7 – Dan Sonne Aug 06 '15 at 11:40

score 0 · Answer 2 · answered Aug 06 '15 at 11:38

0

try this way

List<string> s = dt.AsEnumerable().Select(x => x[0].ToString()).ToList();

    foreach(string e in s)
        Console.WriteLine(e);

answered Aug 06 '15 at 11:38

Nalaka

1,165
7
12

While this code may answer the question, it would be better to include some context, explaining how it works and when to use it. Code-only answers are not useful in the long run. – ryanyuyu Aug 06 '15 at 13:15

score 0 · Answer 3 · answered Aug 06 '15 at 12:30

You are doing += on the strColumnValue, meaning every time you go through the loop you keep adding to that string.

Then you add strColumnValue to str which is an array of strings. So if your rows were "A", "B", "C", after 3 loops StrColumnValue would be "ABC" and str would be [ "A", "AB", "ABC" ]

Then you do the join at the end, but you don't assign the result of the join to any variable, so that statement has no effect. If it was assigned it would look like "A AB ABC"

Its like there are 3 different methods of building the string being used together.

When you are creating a string from many smaller strings, the most efficient method is StringBuilder. See below.

var sb = new StringBuilder();
foreach (DataRow row in dt.Rows)
{
    foreach (DataColumn col in dt.Columns)
    {
        sb.Append(Convert.ToString(row[col])));
        sb.Append(" ");
    }
}
var fullString = sb.ToString();

Convert an Data Table to a string?

3 Answers3