How can I determine if a DataTable column is unnamed?

Question

My subsystem is receiving SQL queries that are to be visualized in tables. I use DataTable because I have no control over the SQL queries that are to be visualized and they can easily be displayed in a DataGrid.

What I basically have is:

var query = "SELECT 1";

var connection = new SqlConnection(connectionString);
var adapter = new SqlDataAdapter(query, connection);
var table = new DataTable();

adapter.Fill(table);

Console.WriteLine(table.Columns[0].ColumnName);

In this case the column name in the DataTable will be set to Column1 because the input query does not specify a name for the column.

How can I determine if the column name is actually Column1 or generated by the SqlDataAdapter implementation because of the query not specifying the column name?

My first attempt was to check for Column(N), however I realized this is not reliable. What if the query actually specifies the name Column1? Also I am worried that "Column" may be translated to something else for a localized version of the .NET framework.

I was also thinking of parsing the input SQL to determine how the columns where named, but this quickly becomes difficult and/or error prone.

Answer 1

With SqlDataReader it is possible to get the actual column names. This is what SqlDataAdapter uses. The problem is that the SqlDataReader instance it is not accessible from outside of the SqlDataAdapter.

My solution is to re-implement the bare required functionality of the SqlDataAdapter.Fill method to set the actual column name.

I had to use Caption property instead of the ColumnName property because the DataTable implementation will change the ColumnName property value automatically to Column1 as there seems to be a requirement of the ColumnName property to be non-empty and unique.

Minimal Fill implementation:

var query = "SELECT 1";

var connection = new SqlConnection(connectionString);
connection.Open();

var table = new DataTable();

// Fill
var command = new SqlCommand(query, connection);
var reader = command.ExecuteReader();

var fieldCount = reader.FieldCount;
for (var index = 0; index < fieldCount; index++)
{
    var name = reader.GetName(index);
    var fieldType = reader.GetFieldType(index);

    table.Columns.Add(new DataColumn { ColumnName = name, Caption = name, DataType = fieldType });
}

var valuesBuffer = new object[fieldCount];
while (reader.Read())
{
    reader.GetValues(valuesBuffer);
    table.Rows.Add(valuesBuffer);
}

Console.WriteLine(table.Columns[0].Caption); // Output: <empty string>