CODEWITHSUNDEEP
c#.netmysqlsql
Umair A.
Umair A.

Reputation: 6873

MySQL Query To Retrieve Data With Column Names

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#.

Upvotes: 5

Views: 13498

Answers (5)

Umair A.
Umair A.

Reputation: 6873

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));
}

Upvotes: 6

triclosan
triclosan

Reputation: 5714

if you have determinated column count you can do it in pure mysql. For example for 3 column:

(select 
max(f1), max(f2), max(f3)
from (
select 
IF(ordinal_position=1,column_name,0) AS f1,
IF(ordinal_position=2,column_name,0) AS f2,
IF(ordinal_position=3,column_name,0) AS f3
from information_schema.columns where table_name='t') tt)
union
(select * from `t`)

Upvotes: 0

gbn
gbn

Reputation: 432271

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?

Upvotes: 5

beardhatcode
beardhatcode

Reputation: 4753

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']

Upvotes: 2

Alex Rashkov
Alex Rashkov

Reputation: 10015

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'
  )
)

Upvotes: 1

Related Questions