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) */
}
}
}
