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

Question

I was wondering how I could get the columns of a database table and store each of them in a string or string array. I have the following code but I believe it does not work. I'm using the default table that is given in asp.net. I've been able to write to this table no problem but I cannot figure out how to select from it and save the values retrieved. here is what I have in my code behind

    string connection = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
    SqlConnection conn = null;
    conn = new SqlConnection(connection);
    conn.Open();
    using (SqlCommand cmd = new SqlCommand())
    {

        string query = String.Format("SELECT column_name FROM USER_TAB_COLUMN WHERE table_name = 'TestTable'");
        cmd.Connection = conn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = query;
        columns.Text = cmd.ExecuteNonQuery().ToString();
        conn.Close();
    }

the error is Invalid object name 'USER_TAB_COLUMN'. I've tried removing this and using "SELECT column_name FROM TestTable" then it complains about column_name. Columns is a text box by the way.

Answer 1

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