How to Get Column Names of Table At Runtime in C#

How to get column names of table at runtime in C#?

You can use sql-

SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('TABLE_NAME') 

Get the column names of a table and store them in a string or var c# asp.net

You can use DbDateReader.GetSchemaTable.

DataTable schema = null;
using (var con = new MySql.Data.MySqlClient.MySqlConnection(connection))
{
    using (var schemaCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM TestTable", con))
    {
        con.Open();
        using (var reader = schemaCommand.ExecuteReader(CommandBehavior.SchemaOnly))
        {
            schema = reader.GetSchemaTable();
        }
    }
}
foreach (DataRow col in schema.Rows)
{
    Console.WriteLine("ColumnName={0}", col.Field<String>("ColumnName"));
}

The column name is in the first column of every row.

---

I am trying your method but
MySql.Data.MySqlClient.MySqlConnection(connection)) is throwing type
or namespace could not be found

I could have sworn that i have seen MySqlCommand and MySqlConnection. So you are using SQL-Server as rdbms instead?

using (var con = new SqlConnection(connection))
{
    using (var schemaCommand = new SqlCommand("SELECT * FROM TestTable;", con))
    {
        con.Open();
        using (var reader = schemaCommand.ExecuteReader(CommandBehavior.SchemaOnly))
        {
            schema = reader.GetSchemaTable();
        }
    }
}
// DataTable part is the same

From C#, how to find the names of the columns of a table in SQL Server?

You need to provide also the database-name + schema-name + table-name:

string table = string.Format("{0}.{1}.{2}", reader[0], reader[1], reader[2]); 
listBox2.Items.Add(table);

Then you can handle the SelectedIndexChanged event of the ListBox. Here you can use SqlCommand.ExecuteReader(CommandBehavior.SchemaOnly) to retrieve the schema informations only. DataReader.GetSchemaTable creates a DataTable with the column-metadata:

private void listBox2_SelectedIndexChanged(object sender, EventArgs e)
{
    string table = listBox2.SelectedItem.ToString();
    using (SqlConnection con = new SqlConnection(strConnect))
    {
        con.Open();
        using (SqlCommand com = new SqlCommand(@"SELECT * FROM " + table, con))
        {
            using (SqlDataReader reader = com.ExecuteReader(CommandBehavior.SchemaOnly))
            {
                listBoxColumns.Items.Clear();
                DataTable schemaTable = reader.GetSchemaTable();
                foreach(DataRow colRow in schemaTable.Rows)
                    listBoxColumns.Items.Add(colRow.Field<String>("ColumnName"));
            }
        }
    }
}

LINQ: Get Table Column Names

I assume you mean by using LINQ to SQL, in which case look at the DataContext.Mapping property. That's what I use.

If you don't mean that, perhaps you can elaborate on what you are trying to achieve?

get column name in addition to the result (oracle)

You can use reader.GetName(i), for example:

var reader = cmd.ExecuteReader();

var columns = new List<string>();

for(int i=0;i<reader.FieldCount;i++)
{
   columns.Add(reader.GetName(i));
}

How to return column names from SQL database

If you run the following SQL you'll get an empty rowset. From which you can interpret the column names by using a SqlCommand and DataReader.

using (var conn = new SqlConnection("your_conn_string"))
{
    var command = new SqlCommand("select * from [dbo].[tableName] where 1 = 2");
    conn.Open();

    using(var dr = command.ExecuteReader())
    {
        var columns = new List<string>();

        for(int i=0;i<reader.FieldCount;i++)
        {
           columns.Add(reader.GetName(i));
        }
    }
}

get column names from a table where one of the column name is a key word

Just enclosed them in square bracket:

select [key] from tbl

Or if you want your code to be ANSI-compliant, use double quote:

select "key" from tbl

Try this if this will work:

SqlDataAdapter adp = new SqlDataAdapter(" Select \"" + combofirstcolumn.SelectedItem.ToString() + "\"," + comboseccolumn.SelectedItem.ToString() + "\t from " + tablename, con);

---

Related Topics