ExecuteScalar method to retrieve SQL Server 2019 table column list in Visual Studio 2019 c-sharp is incorrectly ASCII sorted

Question

I have a table in my SQL Server database with the following stucture:

CREATE TABLE [file].[NumeTestINV](
    [Category] [nvarchar](255) NULL,
    [Class] [nvarchar](255) NULL,
    [Company] [nvarchar](255) NULL,
    [Division] [nvarchar](255) NULL,
    [Jan] [float] NULL,
    [Feb] [float] NULL,
    [Mar] [float] NULL,
    [Apr] [float] NULL,
    [May] [float] NULL,
    [Jun] [float] NULL,
    [Jul] [float] NULL,
    [Aug] [float] NULL,
    [Sep] [float] NULL,
    [Oct] [float] NULL,
    [Nov] [float] NULL,
    [Dec] [float] NULL
) ON [PRIMARY]

I am trying to retrieve its column list via C# code in a Visual Studio 2019 SSIS Script Task. I have the following code c-sharp snippet.

//Get Matching Column List from SQL Server
string SQLColumnList = "";
SqlCommand cmd = myADONETConnection.CreateCommand();
cmd.CommandText = SQLQueryToGetMatchingColumn;
SQLColumnList = (string)cmd.ExecuteScalar();
MessageBox.Show(" Matching Columns: " + SQLColumnList);

However, the problem is that the column list is being outputted incorrectly sorted in ASCII. I am not sure why my SQLColumnList is not coming out with the correct SQL table column order as it is physically and, instead is is coming out as the following?

"[Apr],[Aug],[Category],[Class],[Company],[Dec],[Division],[Feb],[Jan],[Jul],[Jun],[Mar],[May],[Nov],[Oct],[Sep]"

I was expecting the following output:

"[Category],[Class],[Company],[Division],[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]"

Here is the code for SQLQueryToGetMatchingColumn, my source is an Excel file with the same column list in the same order as my physical SQL table:

SQLQueryToGetMatchingColumn = "select STUFF((Select  ',['+Column_Name+']' from Information_schema.Columns where Table_Name='" +
                            TableName + "' and Table_SChema='" + SchemaName + "'" +
                            "and Column_Name in (" + @ExcelHeaderColumn + ") for xml path('')),1,1,'') AS ColumnList";

Here is the output for SQLQueryToGetMatchingColumn:

"select STUFF((Select  ',['+Column_Name+']' from Information_schema.Columns where Table_Name='NumeTestINV' and Table_SChema='file'and Column_Name in ('Category','Class','Company','Division','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') for xml path('')),1,1,'') AS ColumnList"

The CommandText output is the following:

"select STUFF((Select  ',['+Column_Name+']' from Information_schema.Columns where Table_Name='NumeTestINV' and Table_SChema='file'and Column_Name in ('Category','Class','Company','Division','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') for xml path('')),1,1,'') AS ColumnList"

I am hoping that this is a small code change in my snippet and that I don't have to completely change the method by which I am retrieving my SQL Column list.

Order in SQL is an illusion you have in your mind, unless you use a ORDER BY complete enough. Without the ORDER BY the SQL is free to return the rows in any order. — xanatos, Jan 31 '21 at 17:03
Hi Charlieface, `SQLQueryToGetMatchingColumn = "select STUFF((Select ',['+Column_Name+']' from Information_schema.Columns where Table_Name='" + TableName + "' and Table_SChema='" + SchemaName + "'" + "and Column_Name in (" + @ExcelHeaderColumn + ") for xml path('')),1,1,'') AS ColumnList";` — shawnmandel, Jan 31 '21 at 17:22
Hi jdwend, `CommandText = "select STUFF((Select ',['+Column_Name+']' from Information_schema.Columns where Table_Name='NumeTestINV' and Table_SChema='file'and Column_Name in ('Category','Class','Company','Division','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') for xml path('')),1,1,'') AS ColumnList"` — shawnmandel, Jan 31 '21 at 17:23

score 2 · Accepted Answer · answered Jan 31 '21 at 17:21

If you want the columns in order, try something like this:

select string_agg( quotename(name), ', ') within group (order by columnproperty(c.object_id, c.name, 'ordinal') ) columnList
from sys.columns c
where c.object_id = object_id('[file].[NumeTestINV]')

or

select string_agg( quotename(column_name), ', ') within group (order by ordinal_position) columnList
from information_schema.COLUMNS
where TABLE_SCHEMA = 'file'
  and TABLE_NAME = 'NumeTestINV'

or with the old XML conatenation,

select STUFF((Select  ','+ quotename(Column_Name) from Information_schema.Columns where Table_Name='NumeTestINV' and Table_SChema='file'and Column_Name in ('Category','Class','Company','Division','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') order by ordinal_position for xml path('')),1,1,'') AS ColumnList

David, this was EXACTLY what I was looking for! I had ended up simply adding your suggested 'order by ordinal_position' to my SQLQueryToGetMatchingColumn definition. — shawnmandel, Jan 31 '21 at 17:54

ExecuteScalar method to retrieve SQL Server 2019 table column list in Visual Studio 2019 c-sharp is incorrectly ASCII sorted

1 Answers1