using System;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Data;

namespace ShowViewsColumns
{
  public partial class Form1 : Form
  {
    public Form1()
    {
      InitializeComponent();
    }

    OleDbConnection conn;
    DataTable columnSchema;

    private void Form1_Load(object sender, EventArgs e)
    {
      conn = new OleDbConnection(Properties.Settings.
        Default.NwindConnectionString); conn.Open();
      DataTable tableSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
      cboTables.SelectedIndexChanged += new EventHandler(
        cboTables_SelectedIndexChanged);
      cboColumns.SelectedIndexChanged += new EventHandler(
        cboSpalten_SelectedIndexChanged);
      cboTables.DisplayMember = Enum.GetName(typeof(TablesPos),
        TablesPos.TABLE_NAME);
      cboTables.DataSource = tableSchema; 
    }

    void cboTables_SelectedIndexChanged(object sender, EventArgs e)
    {
      cboColumns.DataSource = null;
      ComboBox cb = (ComboBox)sender; string tableName = cb.Text;
      string[] restrictions = new string[(int)TablesPos.TABLE_NAME + 1];
      restrictions[(int)TablesPos.TABLE_NAME] = tableName;
      columnSchema = conn.GetSchema(OleDbMetaDataCollectionNames.
        Columns, restrictions); 
      cboColumns.DisplayMember = Enum.GetName(typeof(ColumnsPos),
        ColumnsPos.COLUMN_NAME);        
      cboColumns.DataSource = columnSchema;
      dataGridView1.DataSource = columnSchema;
    }

    void cboSpalten_SelectedIndexChanged(object sender, EventArgs e)
    {
      ComboBox cb = (ComboBox)sender;
      string spaltenName = cb.Text;
      DataRow foundRow = null;
      foreach (DataRow row in columnSchema.Rows)
        if (row[(int)ColumnsPos.COLUMN_NAME].ToString() == spaltenName)
        { foundRow = row; break;
        }
      if (foundRow != null)
        MessageBox.Show(String.Format("Feldlänge von Spalte:'{0}' " +
          " in Tabelle:'{1}' mit Typ:'{2}' = {3}", spaltenName,
          foundRow[(int)ColumnsPos.TABLE_NAME].ToString(),
          Enum.GetName(typeof(OleDbType), 
            foundRow[(int)ColumnsPos.DATA_TYPE]),          
          foundRow[(int)ColumnsPos.CHARACTER_MAXIMUM_LENGTH].ToString()));
    }

    #region Position of Tables and Columns

    /// <summary>TABLES Rowset
    /// GUID: OleDbSchemaGuid.Tables
    /// Number of restriction columns: 4
    /// Restriction columns: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
    /// Default sort order: TABLE_TYPE, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
    /// Description: The TABLES rowset identifies the tables (including views) 
    /// defined in the catalog that are accessible to a given user.</summary>
    /// <remarks>http://MSDN.microsoft.com/en-us/library/ms716980.aspx</remarks>
    enum TablesPos
    {
      /// <summary>Catalog name. NULL if the provider does not support catalogs.</summary>
      TABLE_CATALOG,

      /// <summary>Unqualified schema name. NULL if the provider does not support schemas.</summary>
      TABLE_SCHEMA,

      /// <summary>Table name. This column cannot contain NULL.</summary>
      TABLE_NAME,

      /// <summary>Table type. One of the following or a provider-specific value:
      /// "ALIAS", "TABLE", "SYNONYM", "SYSTEM TABLE", "VIEW", "GLOBAL TEMPORARY",
      /// "LOCAL TEMPORARY", "SYSTEM VIEW".
      /// This column cannot contain NULL. </summary>
      TABLE_TYPE,

      /// <summary>GUID that uniquely identifies the table. Providers that do not use GUIDs to 
      /// identify tables should return NULL in this column.</summary>
      TABLE_GUID,

      /// <summary>Human-readable description of the table. Null if there is no 
      /// description associated with the column.</summary>
      DESCRIPTION,

      /// <summary>Property ID of the table. Providers that do not use PROPIDs 
      /// to identify columns should return NULL in this column.</summary>
      TABLE_PROPID,

      /// <summary>Date when the table was created or NULL if the provider 
      /// does not have this information. Note that 1.x providers do not
      /// return this column.</summary>
      DATE_CREATED,

      /// <summary>Date when the table definition was last modified or NULL 
      /// if the provider does not have this information.
      /// 1.x providers do not return this column.</summary>
      DATE_MODIFIED
    }


    /// <summary>COLUMNS Rowset 
    /// GUID: OleDbSchemaGuid.Columns
    /// Number of restriction columns: 4
    /// Restriction columns: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
    /// Default sort order: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
    /// Description: The COLUMNS rowset identifies the columns of tables    
    /// (including views) defined in the catalog that are accessible to a given user.</summary>
    /// <remarks>http://MSDN.microsoft.com/en-us/library/ms723052.aspx</remarks>
    enum ColumnsPos
    {
      /// <summary>Catalog name. NULL if the provider does not support catalogs.</summary>
      TABLE_CATALOG,

      /// <summary>Unqualified schema name. NULL if the provider does not support schemas.</summary>
      TABLE_SCHEMA,

      /// <summary>Table name. This column cannot contain a NULL.</summary>
      TABLE_NAME,

      /// <summary>The name of the column; this might not be unique. If this cannot be 
      /// determined, a NULL is returned. This column, together with the COLUMN_GUID and 
      /// COLUMN_PROPID columns, forms the column ID. One or more of these columns will 
      /// be NULL, depending on which elements of the DBID structure the provider uses.
      /// If possible, the resulting column ID should be persistent. However, some 
      /// providers do not support persistent identifiers for columns.
      /// The column ID of a base table should be invariant under views.</summary>
      COLUMN_NAME,

      /// <summary>Column GUID. Providers that do not use GUIDs to identify columns should 
      /// return NULL in this column.</summary>
      COLUMN_GUID,

      /// <summary>Column property ID. Providers that do not associate PROPIDs with columns 
      /// should return NULL in this column.</summary>
      COLUMN_PROPID,

      /// <summary>The ordinal of the column. Columns are numbered starting from one. 
      /// NULL if there is no stable ordinal value for the column.</summary>
      ORDINAL_POSITION,

      /// <summary>true — The column has a default value. false — The column does not 
      /// have a default value, or it is unknown whether the column has a default value.
      /// </summary>
      COLUMN_HASDEFAULT,

      /// <summary>Default value of the column. A provider may expose DBCOLUMN_DEFAULTVALUE 
      /// but not DBCOLUMN_HASDEFAULT (for SQL-92 tables) in the rowset returned by IColumnsRowset::GetColumnsRowset.
      /// If the default value is the NULL value, COLUMN_HASDEFAULT is VARIANT_TRUE 
      /// and the COLUMN_DEFAULT column is a NULL value.</summary>
      COLUMN_DEFAULT,

      /// <summary>A bitmask that describes column characteristics. The DBCOLUMNFLAGS 
      /// enumerated type specifies the bits in the bitmask. 
      /// For information about DBCOLUMNFLAGS, see
      /// [IColumnsInfo.GetColumnInfo] http://MSDN.microsoft.com/en-us/library/ms722704.aspx
      /// in the reference section. 
      /// This column cannot contain a NULL value. If COLUMN_NAME refers to a column 
      /// in a table or view that is updatable, one of either DBCOLUMNFLAGS_WRITE or 
      /// DBCOLUMNFLAGS_WRITEUNKNOWN should be set. For more information about these 
      /// flags, see DBCOLUMNFLAGS Enumerated Type.</summary>
      COLUMN_FLAGS,

      /// <summary>true — The column might be nullable.
      /// false — The column is known not to be nullable.
      /// </summary>
      IS_NULLABLE,

      /// <summary>The indicator of the column's data type. If the data type of the column 
      /// varies from row to row, this must be DBTYPE_VARIANT. This column cannot contain NULL. 
      /// For a list of valid type indicators, see:
      /// [Type Indicators] http://MSDN.microsoft.com/en-us/library/ms711251.aspx
      /// in [Appendix A: Data Types] http://MSDN.microsoft.com/en-us/library/ms723969.aspx
      /// (Supplement by Frank Dzaebel: For mapping under .NET use OleDbType casting)</summary>
      DATA_TYPE,

      /// <summary>The GUID of the column's data type. Providers that do not use 
      /// GUIDs to identify data types should return NULL in this column.</summary>
      TYPE_GUID,

      /// <summary>The maximum possible length of a value in the column. For character, 
      /// binary, or bit columns, this is one of the following:
      /// The maximum length of the column in characters, bytes, or bits, respectively, if one is defined. 
      /// For example, a CHAR(5) column in an SQL table has a maximum length of 5.
      /// The maximum length of the data type in characters, bytes, or bits, respectively, if the column 
      /// does not have a defined length.
      /// Zero (0) if neither the column nor the data type has a defined maximum length.
      /// NULL for all other types of columns.</summary>
      CHARACTER_MAXIMUM_LENGTH,

      /// <summary>Maximum length in octets (bytes) of the column, if the type of the column is character or binary. 
      /// A value of zero means the column has no maximum length. NULL for all other types of columns.</summary>
      CHARACTER_OCTET_LENGTH,

      /// <summary>If the column's data type is of a numeric data type other than VARNUMERIC, 
      /// this is the maximum precision of the column. The precision of columns with a data type of 
      /// DBTYPE_DECIMAL or DBTYPE_NUMERIC depends on the definition of the column. 
      /// For the precision of all other numeric data types, see 
      /// [Precision of Numeric Data Types] http://MSDN.microsoft.com/en-us/library/ms715867.aspx
      /// in [Appendix A: Data Types] http://MSDN.microsoft.com/en-us/library/ms723969.aspx
      /// If the column's data type is not numeric or is VARNUMERIC, this is NULL.</summary>
      NUMERIC_PRECISION,

      /// <summary>If the column's type indicator is DBTYPE_DECIMAL, DBTYPE_NUMERIC, or DBTYPE_VARNUMERIC, this 
      /// is the number of digits to the right of the decimal point. Otherwise, this is NULL.</summary>
      NUMERIC_SCALE,

      /// <summary>Datetime precision (number of digits in the fractional seconds portion) of the 
      /// column if the column is a datetime or interval type. 
      /// If the column's data type is not datetime, this is NULL. </summary>
      DATETIME_PRECISION,

      /// <summary>Catalog name in which the character set is defined. NULL if the provider does not support 
      /// catalogs or different character sets.</summary>
      CHARACTER_SET_CATALOG,

      /// <summary>Unqualified schema name in which the character set is defined. NULL if the provider does 
      /// not support schemas or different character sets.</summary>
      CHARACTER_SET_SCHEMA,

      /// <summary>Character set name. NULL if the provider does not support different character sets.</summary>
      CHARACTER_SET_NAME,

      /// <summary>Catalog name in which the collation is defined. NULL if the provider does not support 
      /// catalogs or different collations.</summary>
      COLLATION_CATALOG,

      /// <summary>Unqualified schema name in which the collation is defined. NULL if the provider does not 
      /// support schemas or different collations.</summary>
      COLLATION_SCHEMA,

      /// <summary>Collation name. NULL if the provider does not support different collations.</summary>
      COLLATION_NAME,

      /// <summary>Catalog name in which the domain is defined. NULL if the provider does 
      /// not support catalogs or domains.</summary>
      DOMAIN_CATALOG,

      /// <summary>Unqualified schema name in which the domain is defined. NULL if the provider 
      /// does not support schemas or domains.</summary>
      DOMAIN_SCHEMA,

      /// <summary>Domain name. NULL if the provider does not support domains.</summary>
      DOMAIN_NAME,

      /// <summary>Human-readable description of the column. For example, the description 
      /// for a column named Name in the Employee table might be "Employee name." 
      /// Null if this column is not supported by the provider, or if there is no 
      /// description associated with the column. </summary>
      DESCRIPTION
    }
    #endregion
  }
}