user3802389 user3802389 -4 years ago 133
C# Question

How to get column names and other metadata from a table or resultset

EX In "Data" table


  • type of column from moisture to end is Boolean

  • I want to get column name has data is "1" to combobox



Specifically, for each row of my result set, I need a collection of those column names which contain the value
1
so I can populate a combobox.

Sorry for my English I'm not good enough.

pic "Data" table

Answer Source

Using the MySQL .net connector (and any RDMS connector) when you are reading a result set from a query, you will have a DataReader object. In MySQL's case it is a MySqlDataReader.

For example (from http://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-sql-command.html)

       string sql = "SELECT * FROM data";
       MySqlCommand cmd = new MySqlCommand(sql, conn);
       MySqlDataReader rdr = cmd.ExecuteReader();

       while (rdr.Read())
       {
            /* iterate once per row */
            Console.WriteLine(rdr[0]+" -- "+rdr[1]); /* or whatever */
       }
       rdr.Close();

Once you have a valid DataReader, you can use the GetSchemaTable() method to obtain a DataTable collection of data describing the result set. This information comes from MySQL as part of the result set. For example:

       MySqlDataReader rdr = cmd.ExecuteReader();
       DataTable schema = rdr.GetSchemaTable();

This DataTable contains a row for each column in the result set. You can access useful information about your columns in your result set like this:

        foreach (DataRow rdrColumn in schema.Rows) {
            String columnName = rdrColumn[schema.Columns["ColumnName"]].ToString();
            String dataType = rdrColumn[schema.Columns["DataType"]].ToString();    
        }

There are also items named ColumnSize, NumericPrecision, NumericScale, and so forth. Each of these is available for each column in the result set if you need them.

Edit You can make a Dictionary holding the names of the result set's columns like this:

        Dictionary<int,String> columnNames = new Dictionary<int,string>();
        int index = 0;
        foreach (DataRow row in schema.Rows) {
            columnNames.Add(index,row[schema.Columns["ColumnName"]].ToString());
            index++;
        }

Thereafter, as you iterate through the rows, you can create a List of columns, by name, with a certain row value.

        while (rdr.Read()) {
            /* for each row */
            List<String> listOfColumns = new List<string>();

            for (int i = 0; i < rdr.FieldCount; i++) {
                var val = rdr[i];
                if ("1" == val) {
                    /* if the value of the column is 1, add the column name from the dictionary */
                    listOfColumns.Add(columnNames[i]);
                }
            }
        }

For examples of looking at result set metadata see here. http://etutorials.org/Programming/ado+net/Part+I+ADO.NET+Tutorial/Chapter+5.+DataReaders/5.4+DataReaders+and+Schema+Information/

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download