MySQL Query To Retrieve Data With Column Names

Question

I am writing some tool which has to retrieve column names of the retrieved data set when any query is applied to it.

If you're familiar with phpMyAdmin, you would realize the SQL pane does what is it runs your query and shows result with the column names. I wonder how hard the query would be, it always results in column names and what's actually the programming behind the scene? Is it like, it analyzes the query and then find table names from it and then first retrieve column names using query show columns from table-name and then the data?

Is there any better way?

UPDATED Sorry for incomplete information, I think I must say I am using MySQL connector for .NET and am using C#.

Solution 1

Your columns are part of the metadate of the DataReader or DataTable that you load in c#

From MSDN for DataTable:

private void PrintColumnNames(DataSet dataSet)
{
    // For each DataTable, print the ColumnName.
    foreach(DataTable table in dataSet.Tables)
    {
        foreach(DataColumn column in table.Columns)
        {
            Console.WriteLine(column.ColumnName);
        }
    }
}

For a DataReader, see Can you get the column names from a SqlDataReader?

Solution 2

I resolved my issue using following code.

var QueryCommand = new MySqlCommand(txtQuery.Text, Connection);
var ResultReader = QueryCommand.ExecuteReader();

for (var f = 0; f < ResultReader.FieldCount; f++)
{
   ResultGrid.Columns.Add("column" + f, ResultReader.GetName(f));
}

Solution 3

you can use SHOW COLUMNS

mysql> SHOW COLUMNS FROM City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+

See: http://dev.mysql.com/doc/refman/5.0/en/show-columns.html

But INFORMATION_SCHEMA is the ANSI means of determining columns, tables, constraints, etc. INFORMATION_SCHEMA.COLUMNS is equivalent to SHOW COLUMNS:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_name = 'tbl_name'
  [AND table_schema = 'db_name']
  [AND column_name LIKE 'wild']

Solution 4

You need a programing language to do same as phpMyAdmin. You can use mysql_fetch_assoc()

It will return a row of data in which each of the elements will have for key the name of the column:

PHP solution

array(
  [0] => array(
    [id] => 1,
    [name] => 'Test name'
  ),
  [1] => array(
    [id] => 2,
    [name] => 'Name 2'
  )
)

View more solutions

Comments

[Umair A.]

The need is to get them with data itself.

[Alex Rashkov]

You hadn't specified any language in your question.

[Umair A.]

You solution is no doubt helpful from PHP perspective as I didn't realize this either.

[ZygD]

Which is similar to my answer which is for c#. Is there a ResultReader in c# natively?

[Umair A.]

I will mark your solution as answer because you got more votes than my own solution :)