private void ConvertRowToSchemaColumns(ref SchemaFieldDefinition fd, DataRow row, DbConnection connection) { // Helper function to convert schema information (in a DataRow variable) into our own format if (connection is OdbcConnection) { // ODBC try { fd.name = (string)row[m_utilsODBC.Schema_Columns_ODBC_Name]; // For ODBC data types, preferentially use "TYPE_NAME" to determine the field type. ODBC must work // across multiple data sources and the mappings from underlying SQL data types to ODBC type // identifiers are only approximate. // Note: The "DATA_TYPE" column for ODBC data sources is actually an Int16 fd.type = Convert.ToInt32(row[m_utilsODBC.Schema_Columns_ODBC_Type]); fd.typeName = (string)row[m_utilsODBC.Schema_Columns_ODBC_TypeName]; fd.size = (int)row[m_utilsODBC.Schema_Columns_ODBC_Size]; fd.nullable = (string)row[m_utilsODBC.Schema_Columns_ODBC_Nullable]; } catch (Exception ex) { Console.WriteLine(UtilitiesGeneral.FormatException( this.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message)); fd.name = "(name)"; // The value contained in COUNTER or INTEGER columns is "4", but the value of "4" in // System.Data.Odbc.OdbcType is "Char" (and the "Int" type is "10"). These appear to be different // types...to be investigated. fd.type = (int)OdbcType.Int; fd.typeName = "(type)"; fd.size = -1; fd.nullable = "(nullable)"; } } else if (connection is OleDbConnection) { // OleDB try { fd.name = (string)row[m_utilsOleDB.Schema_Columns_OleDB_Name]; fd.type = (int)row[m_utilsOleDB.Schema_Columns_OleDB_Type]; if (Enum.IsDefined(typeof(OleDbType), fd.type)) { fd.typeName = ((OleDbType)fd.type).ToString(); } // Note: // NUMERIC_PRECISION => Int32 // CHARACTER_MAXIMUM_LENGTH => Int64 // DATETIME_PRECISION => Int64 fd.size = Convert.ToInt32(row[m_utilsOleDB.GetOleDBTypeSchemaSizeColumn(fd.type)]); fd.nullable = Convert.ToString(row[m_utilsOleDB.Schema_Columns_OleDB_Nullable]); } catch (Exception ex) { Console.WriteLine(UtilitiesGeneral.FormatException( this.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message)); fd.name = "(name)"; fd.type = (int)OleDbType.Integer; fd.typeName = "(type)"; fd.size = -1; fd.nullable = "(nullable)"; } } }
private void GetSchemaColumns(ref List <string> columns, DbConnection connection, string table) { // Retrieve "Columns" schema information, which differs between connection types. To see all columns, // use "GetSchemaColumnsFull". #region Schema details // See the "Data type conversions" section for conversion of the ODBC "TYPE_NAME" or OleDB "DATA_TYPE" to .NET types /* ### OdbcConnection ### * See https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/odbc-schema-collections * ColumnName DataType Description * TABLE_CAT String Name of database (eg. C:\Apps\Data\Northwind 97) * TABLE_SCHEM String ? * TABLE_NAME String Name of table to which this column belongs (eg. Products) * COLUMN_NAME String Field name (eg. ProductID) * DATA_TYPE Int16 Data type as a numerical code (eg. "4" representing COUNTER or INTEGER) * TYPE_NAME String Data type as a string (eg. "COUNTER") * COLUMN_SIZE Int32 Set by the data type, except VARCHAR and NVARCHAR (eg. 1 for BIT, 10 for INTEGER, etc) * BUFFER_LENGTH Int32 As above (eg. 1 for BIT, 2 for SMALLINT, 4 for INTEGER, 21 for CURRENCY, etc) * DECIMAL_DIGITS Int16 Null for string types and 0 for integral types * NUM_PREC_RADIX Int16 ? (Appears to be the base for numerical fields) * NULLABLE Int16 Does this field allow null values? 0 for the key column. * REMARKS String Description of field * COLUMN_DEF String ? * SQL_DATA_TYPE Int16 Appears to be the same as DATA_TYPE * SQL_DATETIME_SUB Int16 Appears to be 3 for fields of type "DATETIME", otherwise null * CHAR_OCTET_LENGTH Int32 Appears to be the same as BUFFER_LENGTH for non-integral types * ORDINAL_POSITION Int32 Column number (eg. 1) * IS_NULLABLE String Does this field allow null values as string? NO for the key column. * ORDINAL ? Column number (eg. 1) * Note: Schema information may differ between ODBC drivers */ /* ### OleDbConnection ### * See https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ole-db-schema-collections * ColumnName DataType Description * TABLE_CATALOG String ? * TABLE_SCHEMA String ? * TABLE_NAME String Name of table to which this column belongs (eg. Products) * COLUMN_NAME String Field name (eg. HireDate) * COLUMN_GUID Guid ? * COLUMN_PROPID Int64 ? * ORDINAL_POSITION Int64 Column number (eg. 1) * COLUMN_HASDEFAULT Boolean Mostly null. If available, then COLUMN_DEFAULT will have a value. * COLUMN_DEFAULT String Mostly empty. If available, the default value for the field. * COLUMN_FLAGS Int64 ? * IS_NULLABLE Boolean Does this field allow null values? False for the key column. * DATA_TYPE Int32 Numerical value representing the data type (eg. 130 for "WChar" or string) * TYPE_GUID Guid ? * CHARACTER_MAXIMUM_LENGTH Int64 Null for integral types. Number of characters for non-integral types. * CHARACTER_OCTET_LENGTH Int64 If available, appears to be double CHARACTER_MAXIMUM_LENGTH * NUMERIC_PRECISION Int32 Set by the data type, analogous to OdbcConnection::COLUMN_SIZE * NUMERIC_SCALE Int16 ? * DATETIME_PRECISION Int64 Mostly null. If available, appears to indicate the field of type "Date". * CHARACTER_SET_CATALOG String ? * CHARACTER_SET_SCHEMA String ? * CHARACTER_SET_NAME String ? * COLLATION_CATALOG String ? * COLLATION_SCHEMA String ? * COLLATION_NAME String ? * DOMAIN_CATALOG String ? * DOMAIN_SCHEMA String ? * DOMAIN_NAME String ? * DESCRIPTION String Description of field */ #endregion // Schema details #region Data type conversions /* DAO ODBC OleDB C# VB * TypeName Type TypeName Type TypeName Type * ================ ================ ==================== =================== * String Text 10 VARCHAR -9 WChar 130 string String * Memo 1 LONGCHAR -1 WChar 130 string String * * Integer Boolean 1 BIT -7 Boolean 11 bool Boolean * Byte 2 BYTE -6 UnsignedTinyInt 17 byte Byte * Integer 3 SMALLINT 5 SmallInt 2 short Short * Long 4 INTEGER 4 Integer 3 int Integer * * Float Single 6 REAL 7 Single 4 float Single * Double 7 DOUBLE 8 Double 5 double Double * Currency 5 CURRENCY 2 Currency 6 decimal Decimal * * DateTime Date 8 DATETIME 93 Date 7 DateTime Date * * Other LongBinary 11 LONGBINARY -4 Binary 128 ? ? */ #endregion // Data type conversions // Specify the Catalog, Schema, Table Name, Column Name to get the specified column(s) // * Index 0 represents Catalog // * Index 1 represents Schema // * Index 2 represents Table Name // * Index 3 represents Column Name SchemaFieldDefinition fd = new SchemaFieldDefinition(); string[] columnRestrictions = new String[4]; columnRestrictions[2] = table; DataTable schemaColumns = connection.GetSchema(Schema_Columns, columnRestrictions); if (schemaColumns.Rows.Count > 0) { columns.Add(m_fieldHeader); foreach (DataRow row in schemaColumns.Rows) { ConvertRowToSchemaColumns(ref fd, row, connection); columns.Add(string.Format(Schema_Header_Column_Formatting, fd.name, fd.type, fd.typeName, fd.size, fd.nullable)); // Alternatively, you code it right here: /*if (connection is OdbcConnection) * { * // ODBC * columns.Add(string.Format(Schema_Header_Column_Formatting, * row[Schema_Columns_ODBC_Name], * row[Schema_Columns_ODBC_Type], * row[Schema_Columns_ODBC_TypeName], * row[Schema_Columns_ODBC_Size], * row[Schema_Columns_ODBC_Nullable])); * } * (etc) */ } } }