c# to generate a list of columns from a sql query string

Question

I have a simple routine that takes a SELECT statement and uses a DataTable.

I use the same SELECT statement and pass it through a routine to generate an array of column names and data types.

As long as the SELECT statement is simple

e.g SELECT Firstname,Secondname...etc...

... my routine can add the column information to my array

However, if the SELECT statement is a little more complex

e.g. SELECT Firstname,Secondname AS Surname... etc...

... my routine will fall over because Surname is not a column name.

More complexity can occur when you factor in subqueries and aggregate columns.

I am looking for a more elegant approach if there is one to acquire any base column names from a SELECT statement?

Where does the 'routine' get the column names from? Are you using a dataadaptor to populate the datatable, in which case the columns should be fine? — Troy Jennings
– Troy Jennings, Commented Oct 17, 2013 at 13:34
As long as your DB is simple and efficient designed you can find an elegant query. If your "names" table is complicated and complex every query will have troubles getting the results.. — apomene
– apomene, Commented Oct 17, 2013 at 13:35
So, you are asking how to parse an SQL Statment into a schema? — Jodrell
– Jodrell, Commented Oct 17, 2013 at 13:42

Tigran · Accepted Answer · 2013-10-17 13:34:12Z

2

You have a DataTable.Columns property were every object is a DataColumn, which is populated based on the query you made.

So if you used as inside your SQL syntax, you should find the same name inside that collection too.

answered Oct 17, 2013 at 13:34

Tigran

62.3k8 gold badges90 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2890085 Over a year ago

Using my original example, "Surname" would get returned from the DataColumn, not Secondname, which is not what I need

Tigran Over a year ago

@user2890085: don't think you can achieve this, as this is SQL syntax that generates data back to DataTable. Not very sure if there is any other property that refers to column real name, as from data perspective the real name is actually that one declared in "as" clause.

Tigran Over a year ago

@user2890085: you can always execute another query that asks for real column names, but this sound kind of overkill..

Felice Pollano · Accepted Answer · 2013-10-17 15:44:14Z

0

You can use an ExecuteReader to execute the query. Then you can use GetName() to obtain the name of each field coming back.

edited Oct 17, 2013 at 15:44

answered Oct 17, 2013 at 13:37

Felice Pollano

33.4k10 gold badges77 silver badges126 bronze badges

2 Comments

user2890085 Over a year ago

Thanks for that. I should have mentioned that this is a MsAccess table, but the GetName() method will be useful if/when we port to MSSQL.

Felice Pollano Over a year ago

It is a member of DbDataReader, so it is DB independent actually. I update the response to reflect that, more correct in any case

Collectives™ on Stack Overflow

c# to generate a list of columns from a sql query string

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related