What is the Sql command to get selected column names from a database table?

Question

I am able to bind my dropdownlist with the following lines of code.

Current Scenario : DropDownList bounded with all the column names of that table.

Requirement: DropDownList to be bounded with only specific column names of the table.

How to achieve this in SQL query in client side ?

private void BindDropDownLists()
{
    SqlDataSource.ConnectionString = connection;
    SqlDataSource.SelectCommand = "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME = 'MyTable')";

**// Only specific columns needed as DDL input.**
**// I Used AND COLUMN_NAME = 'Mycolumnname' it worked but how to hardocore specifc set of column names**

    //DropDownList5.DataSource = SqlDataSource;
    DropDownList5.DataTextField = "COLUMN_NAME";
    DropDownList5.DataValueField = "DATA_TYPE";
    DropDownList5.DataBind();
}

Define "specific column names"? – Allan S. Hansen May 08 '14 at 05:50 — Allan S. Hansen, May 08 '14 at 05:50

score 1 · Answer 1 · answered May 08 '14 at 06:04

The most feasible and correct option here is to load a XML file in your project.

Step wise execution -

Create a ConfigXML for you project
Place tags which you need as per your requirement and fill in the data value.
In your page or common class connect the ConfigXML file and set the properties for each entry in the ConfigXML file.
You can then pass the set of Column names through your ConfigXML file.

As per the above approach you will be able to modify the column names and increase or decrease the names as and when needed without modifying your code or any deployment. Just changing the XML file will do your work.

It is a long process to work now but you will find it beneficial for later stage.

SQL COMMAND -

can contain

"....WHERE (TABLE_NAME = 'MyTable') AND COLUMN_NAME IN ("+ ColumnNameListfromXML +")"

XML Config Entry will be

<FetchColumnName> 
       <ColumnNameList>Column1,Column2,Column3</ColumnNameList>     
</FetchColumnName>

score 0 · Accepted Answer · edited May 08 '14 at 06:17

0

Specify columns in the query

...WHERE (TABLE_NAME = 'MyTable') AND COLUMN_NAME='column1'
or
...WHERE (TABLE_NAME = 'MyTable') AND COLUMN_NAME IN ('column1','column2)
or
...WHERE (TABLE_NAME = 'MyTable') AND COLUMN_NAME LIKE '%column%'

edited May 08 '14 at 06:17

Havelock

6,913
4
34
42

answered May 08 '14 at 05:56

user2316116

6,726
1
21
35

score 0 · Answer 3 · answered May 08 '14 at 07:13

0

Correct Solution:

SqlDataSource.SelectCommand = "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME = 'VXRESULT' AND COLUMN_NAME IN ('Column1','column2','Column3'))";

answered May 08 '14 at 07:13

Shrivatsan

105
1
18

What is the Sql command to get selected column names from a database table?

3 Answers3