Display results in a table format in C#

Question

I have wrote this method which I would like to display as output as a regular table.(For example column names followed by the data) I have not been able to find any method for this. Nor am I able to manipulate the WriteLine to accomplish this. Any ideas?

I am fairly new to programming and C#.

public void ShowQuery()  //to be used with showing data   
{
    try
    {
        SqlDataReader reader = _mySqlCommand.ExecuteReader(); 
        int columnCount = reader.FieldCount;

        while(reader.Read())  //How can I use display results as a normal table like in access?
        {

            for (int i = 0; i < columnCount; i++)
            {
                Console.WriteLine( reader[i]);

            }//end for 
        }//end while
        reader.Close();
        Console.Beep();
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
    }
}

Answer 1

A couple things you should look into:

• String.PadRight, String.PadLeft, allow you to add spaces until your string is a certain length.

• format strings, allow you to format your strings more easily. take note, that you can use Console.WriteLine in the same manner as you would String.Format

here's a sample

var s1 = "1983bi".PadLeft(10);
var s2 = "23".PadLeft(10);

Console.WriteLine("{0} | {1}", s1, s2);
Console.WriteLine("{0} | {1}", s2, s1);

Console.ReadKey(false);

Answer 2

If you wanna read from an SQL database and show the data in grid view, I'd recommend you to use an ORM like Entity Framework or maybe Linq to SQL. This makes the whole procedure a lot easier for you. But if you can't, maybe due to requirement, you have to create a model for your table:

public class MyTableModel{
    public int Id {get; set;}
    public String value {get; set;}
}

Now using normal querying :

con.Open();
List<MyTableModel> DataBind = new List<MyTableModel>();
cmd.CommandText = "SELECT Id, value FROM MyTable";
_DataReader = cmd.ExecuteReader();
if(_DataReader.HasRows) {
   while(_DataReader.Read()){
      DataBind.Add(new MyTableModel() {Id = _DataReader.GetInt(0), value =_DataReader.GetString(1) });
   }
}
con.Close();

Now using a GridView (for GUI) :

DatGrid DG = new DataGrid();
DG.Columns.Add("Id");
DG.Columns.Add("value");
DG.ItemSource = DataBind;

Or for Console app using:

foreach(var i in DataBind){
    Console.WriteLine("{0}\t{1}", i.Id, i.value);
}

Answer 3

Something like this will probably work:

private static void OutputTable(string query, bool showHeader)
{
    MySqlConnection conn = GetConn();

    MySqlCommand cmd = conn.CreateCommand();
    cmd.CommandText = query;
    MySqlDataReader reader = cmd.ExecuteReader();
    int columnCount = reader.FieldCount;

    List<List<string>> output = new List<List<string>>();

    if (showHeader)
    {
        List<string> values = new List<string>();
        for (int count = 0; count < columnCount; ++count)
        {
            values.Add(string.Format("{0}", reader.GetName(count)));
        }
        output.Add(values);
    }

    while (reader.Read())
    {
        List<string> values = new List<string>();
        for (int count = 0; count < columnCount; ++count)
        {
            values.Add(string.Format("{0}", reader[count]));
        }
        output.Add(values);
    }
    reader.Close();

    List<int> widths = new List<int>();
    for (int count = 0; count < columnCount; ++count)
    {
        widths.Add(0);
    }

    foreach (List<string> row in output)
    {
        for (int count = 0; count < columnCount; ++count)
        {
            widths[count] = Math.Max(widths[count], row[count].Length);
        }
    }

    //int totalWidth = widths.Sum() + (widths.Count * 1) * 3;
    //Console.SetWindowSize(Math.Max(Console.WindowWidth, totalWidth), Console.WindowHeight);

    foreach (List<string> row in output)
    {
        StringBuilder outputLine = new StringBuilder();

        for (int count = 0; count < columnCount; ++count)
        {
            if (count > 0)
            {
                outputLine.Append(" ");
            }
            else
            {
                outputLine.Append("| ");
            }
            string value = row[count];
            outputLine.Append(value.PadLeft(widths[count]));
            outputLine.Append(" |");
        }

        Console.WriteLine("{0}", outputLine.ToString());
    }
}