How to retrieve real column name of aliased column

Question

I need to get the real column name from DataTable which I filled it by FillSchema() method, this is the query I used (SELECT ID AS [SNO],CATEG_NAME AS [Category] FROM Categories)

        var dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter("SELECT ID AS [SNO],CATEG_NAME AS [Category] FROM Categories",conn);
        da.FillSchema(dt, SchemaType.Source);
        dt.FillBySql(sql);

When I check dt.columns I find that there are 2 columns with the same alias name which mentioned in query. What can I do to get the real column names(id, categ_name)?

Why do you use aliases if you don't want them? It would be easy to `SELECT * FROM Categories` to get all column names. Another option is `ExecuteReader(CommandBehavior.SchemaOnly))`. http://stackoverflow.com/a/12161051/284240 Another option is using the `INFORMATION_SCHEMA.COLUMNS` table in the database. http://stackoverflow.com/a/600457/284240 — Tim Schmelter, Jan 20 '14 at 23:22
i used aliases to make caption for each field shown in data grid view and also need to retrieve the real column names to use them in another vital task — Ziad EL Naggar, Jan 21 '14 at 00:58
note that when i used ExecuteReader(CommandBehavior.SchemaOnly) it returned schema table contains every thing about each column in the mentioned query but unfortunately it can't identify aliased columns and give me ColumnName = SNO ,,, BaseColumnName = SNO ,,, IsAliased = — Ziad EL Naggar, Jan 21 '14 at 01:35

score 1 · Answer 1 · edited Nov 06 '18 at 08:18

I tried to add one column with the name same as alias column name but just let it blank. see column

then I execute this query:

SELECT 
    status_action,
    isread, 
    CONCAT( 
        FLOOR(HOUR(TIMEDIFF(work_given, sysdate())) / 24), 
        ' days, ', 
        MOD(HOUR(TIMEDIFF(work_given, sysdate())), 24), 
        ' hours, ', 
        MINUTE(TIMEDIFF(work_given, sysdate())), 
        ' minutes') **as** **workgivendays** 
from progressreport

And then I call it use MySqlDataReader like this :

MySqlDataReader mdr = cmd.ExecuteReader(); workgivendays = mdr.GetString("workgivendays"); and it works

score 0 · Answer 2 · answered Jan 21 '14 at 01:34

0

I believe that your best bet is to alter them in C#, not alias them in SQL. This is not a best practice, but this workaround has worked for me.

On a databound datagridview, you have to change the underlying datatable. This sample will change the name of the column from 'ID' to 'SNO'. Make sure you alter a copy the table, so the original remains unchanged.

var dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("SELECT ID,CATEG_NAME AS [Category] FROM Categories",conn);
da.FillSchema(dt, SchemaType.Source);

dt.FillBySql(sql);
DataTable clonedWithColumnsChanged= dt.Copy();
clonedWithColumnsChanged.Columns["ID"].ColumnName = "SNO";
dgvReports.DataSource = clonedWithColumnsChanged;

answered Jan 21 '14 at 01:34

wruckie

1,717
1
23
34

this solution don't solve my problem because i try to develop custom DataGridView and my target is to simplify everything ,, in my case i need to extract the real column names to generate Insert and Update Queries automatically based on extracted column names – Ziad EL Naggar Jan 21 '14 at 01:50
You could do inserts and Updates with a stored procedures. http://stackoverflow.com/questions/15142/what-are-the-pros-and-cons-to-keeping-sql-in-stored-procs-versus-code – wruckie Jan 21 '14 at 02:31

How to retrieve real column name of aliased column

2 Answers2