Print Contents Of A DataTable

Question

Currently I have code which looks up a database table through a SQL connection and inserts the top five rows into a Datatable (Table).

using(SqlCommand _cmd = new SqlCommand(queryStatement, _con))
{
    DataTable Table = new DataTable("TestTable");

    SqlDataAdapter _dap = new SqlDataAdapter(_cmd);

    _con.Open();
    _dap.Fill(Table);
    _con.Close();
}

How do I then print the contents of this table to the console for the user to see?

After digging around, is it possible that I should bind the contents to a list view, or is there a way to print them directly? I'm not concerned with design at this stage, just the data.

Any pointers would be great, thanks!

Solution 1

you can try this code :

foreach(DataRow dataRow in Table.Rows)
{
    foreach(var item in dataRow.ItemArray)
    {
        Console.WriteLine(item);
    }
}

Update 1

DataTable Table = new DataTable("TestTable");
using(SqlCommand _cmd = new SqlCommand(queryStatement, _con))
{
    SqlDataAdapter _dap = new SqlDataAdapter(_cmd);
    _con.Open();
    _dap.Fill(Table);
    _con.Close();

}
Console.WriteLine(Table.Rows.Count);
foreach(DataRow dataRow in Table.Rows)
{
    foreach(var item in dataRow.ItemArray)
    {
        Console.WriteLine(item);
    }
}

Solution 2

Here is another solution which dumps the table to a comma separated string:

using System.Data;

public static string DumpDataTable(DataTable table)
        {
            string data = string.Empty;
            StringBuilder sb = new StringBuilder();

            if (null != table && null != table.Rows)
            {
                foreach (DataRow dataRow in table.Rows)
                {
                    foreach (var item in dataRow.ItemArray)
                    {
                        sb.Append(item);
                        sb.Append(',');
                    }
                    sb.AppendLine();
                }

                data = sb.ToString();
            }
            return data;
        }

Solution 3

Proper Tabular display

static void print_results(DataTable data)
    {
        Console.WriteLine();
        Dictionary<string, int> colWidths = new Dictionary<string, int>();

        foreach (DataColumn col in data.Columns)
        {
            Console.Write(col.ColumnName);
            var maxLabelSize = data.Rows.OfType<DataRow>()
                    .Select(m => (m.Field<object>(col.ColumnName)?.ToString() ?? "").Length)
                    .OrderByDescending(m => m).FirstOrDefault();

            colWidths.Add(col.ColumnName, maxLabelSize);
            for (int i = 0; i < maxLabelSize - col.ColumnName.Length + 10; i++) Console.Write(" ");
        }

        Console.WriteLine();

        foreach (DataRow dataRow in data.Rows)
        {
            for (int j = 0; j < dataRow.ItemArray.Length; j++)
            {
                Console.Write(dataRow.ItemArray[j]);
                for (int i = 0; i < colWidths[data.Columns[j].ColumnName] - dataRow.ItemArray[j].ToString().Length + 10; i++) Console.Write(" ");
            }
            Console.WriteLine();
        }
    }

Solution 4

foreach (DataRow dr in myDataTable.Rows)
{
    foreach (var item in dr.ItemArray)
    {
        Console.Write(item + " ");
    }
    Console.WriteLine();
}

---

¹ A previous answer was similar to this, but didnt cleanly format the separation between line entries and cells

Solution 5

This is done by data table that holds single table

        SqlConnection con = new SqlConnection(constr);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from info", con);
        SqlDataAdapter ad = new SqlDataAdapter(cmd);

        DataTable dt = new DataTable();
        ad.Fill(dt);
        Console.WriteLine(dt.Columns[0].ColumnName.ToString());
        Console.WriteLine(dt.Rows[1].ItemArray[0].ToString());

This is done by data set that holds set of table

        SqlConnection con = new SqlConnection(constr);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from info", con);
        SqlDataAdapter ad = new SqlDataAdapter(cmd);

        DataSet dt = new DataSet();
        ad.Fill(dt);
        Console.WriteLine(dt.Tables[0].Columns[0].ColumnName.ToString());
        Console.WriteLine(dt.Tables[0].Rows[0].ItemArray[0].ToString());

both will give same result. only data set contains number of index of table.

View more solutions

Comments

[William]

Hi, thanks for the answer. This compiles okay but nothing is being shown, is it possible there is nothing there?

[Arshad]

can you put a break point an check whether the DataTable is empty or not ?

[William]

I just worked it out - there is data in the table, it isn't printing to console though.

[William]

The updated code still doesn't show the data.. the code is inside public Form1() {.............. Should it be somewhere else?

[Arshad]

is it windows or console application ?

[William]

It is windowsformsapplication.

[Arshad]

let us continue this discussion in chat

[curious coder]

works and looks great!

[MFerreira]

Preferred solution for those needing something similar to pandas dataframe printed output, or simply SSMS / VSCode tabular output. Thank you!