Format table to text output in C#

Question

I have a query versus an SQL database in C#. From this query I get a List like:

List<List<string>>()

this list can be changed to any other table type if required..

In Microsoft Server Management Studio it looks like this:

In Microsoft SQL Server Management Studio you can simply output the result as text, which looks like this:

Now as I am automating some tasks I will use C# rather than manually using MSSQL. Due to a GXP environment it is important to have every datachange in the Database Documented separately with a text report attached to a "ticket".

For now my query with Table Text generation looks like this:

SqlCommand command = new SqlCommand("SELECT '" + description + "' AS '" + description + "', CPPS_Site,Study_Id,CustNo,CPPS_Job FROM CDS.dbo.Studies " +
                                            "WHERE Study_Id = " + studyID, con);
List<string> overview = new List<string>();

overview.Add("|=========================================================|");
overview.Add("|"+description + "\t|CPPS_Site\t|Study_ID\t|CustNo\t|CPPS_Job|");
overview.Add("|---------------------------------------------------------|");
using (SqlDataReader resultQuery = command.ExecuteReader())
{
    var schemaTable = resultQuery.GetSchemaTable();
    while (resultQuery.Read())
    {
        string build = "|";
        build +=  resultQuery.GetSqlValue(0) + "\t|"
              + resultQuery.GetSqlValue(1) + "\t\t|"
              + resultQuery.GetSqlValue(2) + "\t\t|"
              + resultQuery.GetSqlValue(3) + "\t|"
              + resultQuery.GetSqlValue(4);
        build += "|";
        overview.Add(build);
     }
overview.Add("|=========================================================|");
     return overview;
 }

Result:

while this is technically working and would also be accepted, I am personally very unpleased with this ugly solution. For instance with strings of different length, the format will not fit anymore. Do you know a more clean solution?

This Nuget package might do what you want: nuget.org/packages/DanielStout.AsciiTableFormatter — Denxorz
– Denxorz, Commented Sep 25, 2018 at 14:57
and how do i use that? Im not familar with IEnumerable and I only find an example for manual use — julian bechtold
– julian bechtold, Commented Sep 25, 2018 at 15:17
I think you are familiar with IEnumerable. You just don't know it... :-) — JuanR
– JuanR, Commented Sep 25, 2018 at 15:22
thx for this advice.. i still cannot built something out of what i dont know — julian bechtold
– julian bechtold, Commented Sep 25, 2018 at 15:39
You could make a class with your 5 fields, add intance of this class per while-loop into the list (just like you do for the strings). And then give the list to the asciitableformatter package. — Denxorz
– Denxorz, Commented Sep 26, 2018 at 8:55

Panagiotis Kanavos · Accepted Answer · 2018-09-25 15:17:39Z

3

Composite string formatting provides an aligment component that can be used to specify a field size and align values left or right. For example

String.Format("|{0,5}",5);

will produce

|    5|

Each line can be written as :

var linePattern="|{0,-7}|{1,-17}|{2,-11}|{3,-14}|{4,-8}|";
var line= String.Format(linePattern
    resultQuery.GetSqlValue(0),
    resultQuery.GetSqlValue(1),
    resultQuery.GetSqlValue(2),
    resultQuery.GetSqlValue(3),
    resultQuery.GetSqlValue(4));

The header can be written using the same pattern:

var header=String.Format(linePattern,"Before","CPPS_Site","Study_ID","CustNo","CPPS_Job");

Writing both the header and line to the console will appear like this :

|Before |CPPS_Site        |Study_ID   |CustNo        |CPPS_Job|
|Before |1                |2          |C50030        |999     |

If the strings are larger than the field values though, they won't be truncated and the format will appear broken, eg:

|Before |CPPS_Site        |Study_ID   |CustNo        |CPPS_Job|
|Before123 |1                |2          |C50030        |999     |

answered Sep 25, 2018 at 15:17

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

julian bechtold Over a year ago

the broken format unfortunately is exactly the issue I have. but looks like something i can build on

Panagiotis Kanavos Over a year ago

@julianbechtold in this case you'd have to load all data in order to calculate field lengths. Perhaps you could load everything into a DataTable, or read the first eg 100 rows and use them to calculate field lengths

Collectives™ on Stack Overflow

Format table to text output in C#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related