Search code examples
c#sqltextoutputtabular

Format table to text output in C#

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:

enter image description here

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

enter image description here

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:

enter image description here

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?

Solution

  • 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     |