public static ColumnInfoCollection GetColumnData(this MySqlConnection connection, string tableName)
{
const string CMD_COLUMN_INFO_FORMAT =
@"SELECT
`COLUMN_NAME`,
`COLUMN_DEFAULT`,
`DATA_TYPE`,
`CHARACTER_MAXIMUM_LENGTH`,
`IS_NULLABLE`,
`ORDINAL_POSITION`,
`NUMERIC_PRECISION`,
`NUMERIC_SCALE`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA` = @DatabaseName
AND `TABLE_NAME` = @TableName;";
const string CMD_IS_PRIMARY_KEY_FORMAT =
@"SELECT `COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'HotelOffer'
AND `TABLE_NAME` = 'airport'
AND `COLUMN_KEY` = 'PRI';";
var list = new ColumnInfoCollection();
bool alreadyOpen = (connection.State != ConnectionState.Closed);
try
{
var foreignKeyColumns = connection.GetForeignKeyData(tableName);
if (!alreadyOpen)
{
connection.Open();
}
using (var command = new MySqlCommand(CMD_COLUMN_INFO_FORMAT, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.Add(new MySqlParameter
{
Direction = ParameterDirection.Input,
DbType = DbType.String,
ParameterName = "@DatabaseName",
Value = connection.Database
});
command.Parameters.Add(new MySqlParameter
{
Direction = ParameterDirection.Input,
DbType = DbType.String,
ParameterName = "@TableName",
Value = tableName
});
using (var reader = command.ExecuteReader())
{
ColumnInfo columnInfo = null;
while (reader.Read())
{
columnInfo = new ColumnInfo();
if (!reader.IsDBNull(0))
{
columnInfo.ColumnName = reader.GetString(0);
}
if (!reader.IsDBNull(1))
{
columnInfo.DefaultValue = reader.GetString(1);
}
else
{
columnInfo.DefaultValue = string.Empty;
}
if (foreignKeyColumns.Contains(columnInfo.ColumnName))
{
columnInfo.KeyType = KeyType.ForeignKey;
}
if (!reader.IsDBNull(3))
{
columnInfo.MaximumLength = reader.GetInt64(3);
}
try
{
// Based on: https://www.devart.com/dotconnect/mysql/docs/datatypemapping.html
string type = reader.GetString(2).ToLowerInvariant();
columnInfo.DataTypeNative = type;
switch (type)
{
case "bool": columnInfo.DataType = DbType.Boolean; break;
case "boolean": columnInfo.DataType = DbType.Boolean; break;
case "bit":
{
// TODO: This won't work. Because that is for character max length. We need to add 2 properties to
// ColumnInfo object: "NumericPrecision" and "NumericScale" to be used for numerical data types
if (columnInfo.MaximumLength == 1)
{
columnInfo.DataType = DbType.Boolean;
}
else
{
columnInfo.DataType = DbType.Int64;
}
}
break;
case "tinyint": columnInfo.DataType = DbType.SByte; break;
case "tinyint unsigned": columnInfo.DataType = DbType.Byte; break;
case "smallint": columnInfo.DataType = DbType.Int16; break;
case "year": columnInfo.DataType = DbType.Int16; break;
case "int": columnInfo.DataType = DbType.Int32; break;
case "integer": columnInfo.DataType = DbType.Int32; break;
case "smallint unsigned": columnInfo.DataType = DbType.UInt16; break;
case "mediumint": columnInfo.DataType = DbType.Int32; break;
case "bigint": columnInfo.DataType = DbType.Int64; break;
case "int unsigned": columnInfo.DataType = DbType.UInt32; break;
case "integer unsigned": columnInfo.DataType = DbType.UInt32; break;
case "float": columnInfo.DataType = DbType.Single; break;
case "double": columnInfo.DataType = DbType.Double; break;
case "real": columnInfo.DataType = DbType.Double; break;
case "decimal": columnInfo.DataType = DbType.Decimal; break;
case "numeric": columnInfo.DataType = DbType.Decimal; break;
case "dec": columnInfo.DataType = DbType.Decimal; break;
case "fixed": columnInfo.DataType = DbType.Decimal; break;
case "bigint unsigned": columnInfo.DataType = DbType.UInt64; break;
case "float unsigned": columnInfo.DataType = DbType.Decimal; break;
case "double unsigned": columnInfo.DataType = DbType.Decimal; break;
case "serial": columnInfo.DataType = DbType.Decimal; break;
case "date": columnInfo.DataType = DbType.DateTime; break;
case "timestamp": columnInfo.DataType = DbType.DateTime; break;
case "datetime": columnInfo.DataType = DbType.DateTime; break;
case "datetimeoffset": columnInfo.DataType = DbType.DateTimeOffset; break;
case "time": columnInfo.DataType = DbType.Time; break;
case "char":
{
if (columnInfo.MaximumLength == 36)
{
// This might not be a Guid, but most likely (99% sure) it will be
columnInfo.DataType = DbType.Guid;
}
else
{
columnInfo.DataType = DbType.String;
}
}
break;
case "varchar": columnInfo.DataType = DbType.String; break;
case "tinytext": columnInfo.DataType = DbType.String; break;
case "text": columnInfo.DataType = DbType.String; break;
case "mediumtext": columnInfo.DataType = DbType.String; break;
case "longtext": columnInfo.DataType = DbType.String; break;
case "set": columnInfo.DataType = DbType.String; break;
case "enum": columnInfo.DataType = DbType.String; break;
case "nchar": columnInfo.DataType = DbType.String; break;
case "national char": columnInfo.DataType = DbType.String; break;
case "nvarchar": columnInfo.DataType = DbType.String; break;
case "national varchar": columnInfo.DataType = DbType.String; break;
case "character varying": columnInfo.DataType = DbType.String; break;
case "binary": columnInfo.DataType = DbType.Binary; break;
case "varbinary": columnInfo.DataType = DbType.Binary; break;
case "tinyblob": columnInfo.DataType = DbType.Binary; break;
case "blob": columnInfo.DataType = DbType.Binary; break;
case "mediumblob": columnInfo.DataType = DbType.Binary; break;
case "longblob": columnInfo.DataType = DbType.Binary; break;
case "char byte": columnInfo.DataType = DbType.Binary; break;
case "geometry": columnInfo.DataType = DbType.Object; break;
default: columnInfo.DataType = DbType.Object; break;
}
//columnInfo.DataType = DataTypeConvertor.GetSystemType(reader.GetString(2).ToEnum<SqlDbType>(true));
}
catch (ArgumentNullException)
{
columnInfo.DataType = DbType.Object;
}
catch (ArgumentException)
{
columnInfo.DataType = DbType.Object;
}
if (!reader.IsDBNull(4))
{
if (reader.GetString(4).ToUpperInvariant().Equals("NO"))
{
columnInfo.IsNullable = false;
}
else
{
columnInfo.IsNullable = true;
}
}
if (!reader.IsDBNull(5))
{
columnInfo.OrdinalPosition = reader.GetInt32(5);
}
if (!reader.IsDBNull(6))
{
columnInfo.Precision = reader.GetInt32(6);
}
if (!reader.IsDBNull(7))
{
columnInfo.Scale = reader.GetInt32(7);
}
list.Add(columnInfo);
}
}
}
}
finally
{
if (!alreadyOpen && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
#region Primary Keys
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = CMD_IS_PRIMARY_KEY_FORMAT;
command.Parameters.Add(new MySqlParameter
{
Direction = ParameterDirection.Input,
DbType = DbType.String,
ParameterName = "@DatabaseName",
Value = connection.Database
});
command.Parameters.Add(new MySqlParameter
{
Direction = ParameterDirection.Input,
DbType = DbType.String,
ParameterName = "@TableName",
Value = tableName
});
alreadyOpen = (connection.State != ConnectionState.Closed);
if (!alreadyOpen)
{
connection.Open();
}
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
string pkColumn = reader.GetString(0);
var match = list[pkColumn];
if (match != null)
{
match.KeyType = KeyType.PrimaryKey;
}
}
}
if (!alreadyOpen)
{
connection.Close();
}
}
#endregion Primary Keys
return(list);
}
public static ColumnInfoCollection GetColumnData(this OleDbConnection connection, string tableName)
{
var restrictions = new object[] { null, null, tableName };
var foreignKeyRestrictions = new object[] { null, null, null, null, null, tableName };
connection.Open();
DataTable columnsSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, restrictions);
DataTable primaryKeySchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, restrictions);
DataTable foreignKeySchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, foreignKeyRestrictions);
connection.Close();
var columnData = new ColumnInfoCollection();
foreach (DataRow row in columnsSchema.Rows)
{
var columnInfo = new ColumnInfo
{
ColumnName = row.Field <string>("COLUMN_NAME"),
DataType = DataTypeConvertor.GetSystemType((OleDbType)row.Field <int>("DATA_TYPE")),
IsNullable = row.Field <bool>("IS_NULLABLE")
};
if (row["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
{
columnInfo.MaximumLength = row.Field <long>("CHARACTER_MAXIMUM_LENGTH");
}
columnInfo.OrdinalPosition = row.Field <long>("ORDINAL_POSITION");
if (row.Field <bool>("COLUMN_HASDEFAULT"))
{
columnInfo.DefaultValue = row.Field <string>("COLUMN_DEFAULT");
}
if (primaryKeySchema != null)
{
foreach (DataRow pkRow in primaryKeySchema.Rows)
{
if (columnInfo.ColumnName == pkRow.Field <string>("COLUMN_NAME"))
{
columnInfo.KeyType = KeyType.PrimaryKey;
break;
}
}
}
if (columnInfo.KeyType == KeyType.None)
{
if (foreignKeySchema != null)
{
foreach (DataRow fkRow in foreignKeySchema.Rows)
{
if (columnInfo.ColumnName == fkRow.Field <string>("FK_COLUMN_NAME"))
{
columnInfo.KeyType = KeyType.ForeignKey;
break;
}
}
}
}
columnData.Add(columnInfo);
}
columnsSchema.DisposeIfNotNull();
primaryKeySchema.DisposeIfNotNull();
foreignKeySchema.DisposeIfNotNull();
return(columnData);
}
public static ColumnInfoCollection GetColumnData(this NpgsqlConnection connection, string tableName, string schema = "public")
{
const string CMD_COLUMN_INFO_FORMAT =
@"SELECT ""column_name"", ""column_default"", ""data_type"", ""character_maximum_length"", ""is_nullable"", ""ordinal_position"", ""numeric_precision"", ""numeric_scale""
FROM information_schema.""columns""
WHERE TABLE_NAME = '{0}';";
const string CMD_IS_PRIMARY_KEY_FORMAT =
@"SELECT ""column_name""
FROM information_schema.""key_column_usage"" kcu
INNER JOIN information_schema.""table_constraints"" tc ON kcu.""constraint_name"" = tc.""constraint_name""
WHERE kcu.""table_name"" = '{0}'
AND tc.""constraint_type"" = 'PRIMARY KEY'";
var list = new ColumnInfoCollection();
bool alreadyOpen = (connection.State != ConnectionState.Closed);
try
{
var foreignKeyColumns = connection.GetForeignKeyData(tableName);
if (!alreadyOpen)
{
connection.Open();
}
using (var command = new NpgsqlCommand(string.Format(CMD_COLUMN_INFO_FORMAT, tableName), connection))
{
command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
ColumnInfo columnInfo = null;
while (reader.Read())
{
columnInfo = new ColumnInfo();
if (!reader.IsDBNull(0))
{
columnInfo.ColumnName = reader.GetString(0);
}
if (!reader.IsDBNull(1))
{
columnInfo.DefaultValue = reader.GetString(1);
}
else
{
columnInfo.DefaultValue = string.Empty;
}
if (foreignKeyColumns.Contains(columnInfo.ColumnName))
{
columnInfo.KeyType = KeyType.ForeignKey;
}
//else
//{
try
{
// TODO: SqlDbType won't work for PG!! Need to update this ASAP... get System.Type from PG type
string type = reader.GetString(2).ToLowerInvariant();
switch (type)
{
case "bigint": columnInfo.DataType = DbType.Int64; break;
case "bigserial": columnInfo.DataType = DbType.Int64; break;
case "bit": columnInfo.DataType = DbType.Boolean; break;
case "bit varying": columnInfo.DataType = DbType.Binary; break;
case "boolean": columnInfo.DataType = DbType.Boolean; break;
case "box": columnInfo.DataType = DbType.Object; break;
case "bytea": columnInfo.DataType = DbType.Binary; break;
case "character": columnInfo.DataType = DbType.String; break;
case "character varying": columnInfo.DataType = DbType.String; break;
case "cidr": columnInfo.DataType = DbType.Object; break;
case "circle": columnInfo.DataType = DbType.Object; break;
case "date": columnInfo.DataType = DbType.DateTime; break;
case "double precision": columnInfo.DataType = DbType.Double; break;
case "inet": columnInfo.DataType = DbType.Object; break;
case "integer": columnInfo.DataType = DbType.Int32; break;
case "interval": columnInfo.DataType = DbType.Time; break;
case "json": columnInfo.DataType = DbType.String; break;
case "line": columnInfo.DataType = DbType.Object; break;
case "lseg": columnInfo.DataType = DbType.Object; break;
case "macaddr": columnInfo.DataType = DbType.Object; break;
case "money": columnInfo.DataType = DbType.Decimal; break;
case "numeric": columnInfo.DataType = DbType.Decimal; break;
case "path": columnInfo.DataType = DbType.Object; break;
case "point": columnInfo.DataType = DbType.Object; break;
case "polygon": columnInfo.DataType = DbType.Object; break;
case "real": columnInfo.DataType = DbType.Single; break;
case "smallint": columnInfo.DataType = DbType.Int16; break;
case "smallserial": columnInfo.DataType = DbType.Int16; break;
case "serial": columnInfo.DataType = DbType.Int32; break;
case "text": columnInfo.DataType = DbType.String; break;
case "time without time zone": columnInfo.DataType = DbType.Time; break;
case "time with time zone": columnInfo.DataType = DbType.DateTimeOffset; break;
case "timestamp without time zone": columnInfo.DataType = DbType.DateTime; break;
case "timestamp with time zone": columnInfo.DataType = DbType.DateTime; break;
case "tsquery": columnInfo.DataType = DbType.Object; break;
case "tsvector": columnInfo.DataType = DbType.Object; break;
case "txid_snapshot": columnInfo.DataType = DbType.Object; break;
case "uuid": columnInfo.DataType = DbType.Guid; break;
case "xml": columnInfo.DataType = DbType.Xml; break;
default: columnInfo.DataType = DbType.Object; break;
}
}
catch (ArgumentNullException)
{
columnInfo.DataType = DbType.Object;
}
catch (ArgumentException)
{
columnInfo.DataType = DbType.Object;
}
if (columnInfo.DefaultValue != null &&
columnInfo.DefaultValue.Contains("nextval"))
{
columnInfo.IsAutoIncremented = true;
columnInfo.DefaultValue = string.Empty;
}
//}
if (!reader.IsDBNull(3))
{
columnInfo.MaximumLength = reader.GetInt32(3);
}
if (!reader.IsDBNull(4))
{
if (reader.GetString(4).ToUpperInvariant().Equals("NO"))
{
columnInfo.IsNullable = false;
}
else
{
columnInfo.IsNullable = true;
}
}
if (!reader.IsDBNull(5))
{
columnInfo.OrdinalPosition = reader.GetInt32(5);
}
if (!reader.IsDBNull(6))
{
columnInfo.Precision = reader.GetInt32(6);
}
if (!reader.IsDBNull(7))
{
columnInfo.Scale = reader.GetInt32(7);
}
list.Add(columnInfo);
}
}
}
}
finally
{
if (!alreadyOpen && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
#region Primary Keys
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(CMD_IS_PRIMARY_KEY_FORMAT, tableName);
alreadyOpen = (connection.State != ConnectionState.Closed);
if (!alreadyOpen)
{
connection.Open();
}
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
string pkColumn = reader.GetString(0);
ColumnInfo match = list[pkColumn];
if (match != null)
{
match.KeyType = KeyType.PrimaryKey;
}
}
}
if (!alreadyOpen)
{
connection.Close();
}
}
#endregion Primary Keys
return(list);
}
public TableResultSet(int resultIndex, ColumnInfoCollection columns, ResultType type)
{
ResultIndex = resultIndex;
Columns = columns;
Type = type;
}