public static ForeignKeyInfoCollection GetForeignKeyData(this OleDbConnection connection, string tableName)
{
var foreignKeyRestrictions = new object[] { null, null, null, null, null, tableName };
bool alreadyOpen = (connection.State != ConnectionState.Closed);
if (!alreadyOpen)
{
connection.Open();
}
DataTable foreignKeySchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, foreignKeyRestrictions);
if (!alreadyOpen)
{
connection.Close();
}
var foreignKeyData = new ForeignKeyInfoCollection();
foreach (DataRow row in foreignKeySchema.Rows)
{
var fkInfo = new ForeignKeyInfo
{
ForeignKeyColumn = row.Field <string>("FK_COLUMN_NAME"),
ForeignKeyName = row.Field <string>("FK_NAME"),
ForeignKeyTable = row.Field <string>("FK_TABLE_NAME"),
PrimaryKeyColumn = row.Field <string>("PK_COLUMN_NAME"),
PrimaryKeyName = row.Field <string>("PK_NAME"),
PrimaryKeyTable = row.Field <string>("PK_TABLE_NAME")
};
foreignKeyData.Add(fkInfo);
}
return(foreignKeyData);
}
public static ForeignKeyInfoCollection GetForeignKeyData(this NpgsqlConnection connection, string tableName, string schema = "public")
{
const string CMD_FOREIGN_KEYS_FORMAT =
@"SELECT
FK.""table_name"" AS FK_Table,
CU.""column_name"" AS FK_Column,
PK.""table_name"" AS PK_Table,
PT.""column_name"" AS PK_Column,
C.""constraint_name"" AS Constraint_Name
FROM information_schema.""referential_constraints"" C
INNER JOIN information_schema.""table_constraints"" FK ON C.""constraint_name"" = FK.""constraint_name""
INNER JOIN information_schema.""table_constraints"" PK ON C.""unique_constraint_name"" = PK.""constraint_name""
INNER JOIN information_schema.""key_column_usage"" CU ON C.""constraint_name"" = CU.""constraint_name""
INNER JOIN
(
SELECT i1.""table_name"", i2.""column_name""
FROM information_schema.""table_constraints"" i1
INNER JOIN information_schema.""key_column_usage"" i2 ON i1.""constraint_name"" = i2.""constraint_name""
WHERE i1.""constraint_type"" = 'PRIMARY KEY'
) PT ON PT.""table_name"" = PK.""table_name""
WHERE FK.""table_name"" = '{0}'
ORDER BY 1,2,3,4";
ForeignKeyInfoCollection foreignKeyData = new ForeignKeyInfoCollection();
bool alreadyOpen = (connection.State != ConnectionState.Closed);
if (!alreadyOpen)
{
connection.Open();
}
using (var command = new NpgsqlCommand(string.Format(CMD_FOREIGN_KEYS_FORMAT, tableName), connection))
{
command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
foreignKeyData.Add(new ForeignKeyInfo(
reader.GetString(0),
reader.GetString(1),
reader.GetString(2),
reader.GetString(3),
string.Empty,
reader.GetString(4)));
}
}
}
if (!alreadyOpen)
{
connection.Close();
}
return(foreignKeyData);
}
public static ForeignKeyInfoCollection GetForeignKeyData(this SqlConnection connection, string tableName)
{
const string CMD_FOREIGN_KEYS_FORMAT =
@"SELECT FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE FK.TABLE_NAME = '{0}'
ORDER BY 1,2,3,4";
var foreignKeyData = new ForeignKeyInfoCollection();
bool alreadyOpen = (connection.State != ConnectionState.Closed);
if (!alreadyOpen)
{
connection.Open();
}
using (var command = new SqlCommand(string.Format(CMD_FOREIGN_KEYS_FORMAT, tableName), connection))
{
command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
foreignKeyData.Add(new ForeignKeyInfo(
reader.GetString(0),
reader.GetString(1),
reader.GetString(2),
reader.GetString(3),
string.Empty,
reader.GetString(4)));
}
}
}
if (!alreadyOpen)
{
connection.Close();
}
return(foreignKeyData);
}
public static ForeignKeyInfoCollection GetForeignKeyData(this MySqlConnection connection, string tableName)
{
const string CMD_FOREIGN_KEYS_FORMAT =
@"SELECT
TABLE_NAME AS FK_Table,
COLUMN_NAME AS FK_Column,
REFERENCED_TABLE_NAME AS PK_Table,
REFERENCED_COLUMN_NAME AS PK_Column,
CONSTRAINT_NAME AS Constraint_Name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @TableName
AND CONSTRAINT_NAME <> 'PRIMARY';";
var foreignKeyData = new ForeignKeyInfoCollection();
bool alreadyOpen = (connection.State != ConnectionState.Closed);
if (!alreadyOpen)
{
connection.Open();
}
using (var command = new MySqlCommand(CMD_FOREIGN_KEYS_FORMAT, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.Add(new MySqlParameter
{
Direction = ParameterDirection.Input,
DbType = DbType.String,
ParameterName = "@TableName",
Value = tableName
});
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
foreignKeyData.Add(new ForeignKeyInfo(
reader.IsDBNull(0) ? null : reader.GetString(0),
reader.IsDBNull(1) ? null : reader.GetString(1),
reader.IsDBNull(2) ? null : reader.GetString(2),
reader.IsDBNull(3) ? null : reader.GetString(3),
string.Empty,
reader.IsDBNull(4) ? null : reader.GetString(4)));
}
}
}
if (!alreadyOpen)
{
connection.Close();
}
return(foreignKeyData);
}
//public virtual FieldCollection GetFields(string tableName)
//{
// using (DbConnection connection = CreateDbConnection(DbProviderName, ConnectionDetails.ConnectionString))
// {
// using (DbCommand command = connection.CreateCommand())
// {
// command.CommandType = CommandType.Text;
// command.CommandText = string.Format(Constants.Data.CMD_SELECT_INFO_SCHEMA_COLUMNS, tableName);
// FieldCollection fields = new FieldCollection();
// connection.Open();
// using (DbDataReader reader = command.ExecuteReader())
// {
// while (reader.Read())
// {
// Field field = new Field();
// field.Name = reader.GetString(0);
// if (!reader.IsDBNull(1))
// { field.Ordinal = reader.GetInt32(1); }
// if (!reader.IsDBNull(2))
// { field.Type = GetScaffolderFieldType(reader.GetString(2)); }
// if (!reader.IsDBNull(3))
// { field.IsRequired = reader.GetString(3) == "YES"; }
// if (!reader.IsDBNull(4))
// { field.MaxLength = reader.GetInt32(4); }
// fields.Add(field);
// }
// }
// connection.Close();
// try
// {
// command.CommandText = string.Format(Constants.Data.CMD_IS_PRIMARY_KEY_FORMAT, tableName);
// var foreignKeyColumns = GetForeignKeyInfo(tableName);
// connection.Open();
// using (DbDataReader reader = command.ExecuteReader())
// {
// while (reader.Read())
// {
// string pkColumn = reader.GetString(0);
// Field match = fields.SingleOrDefault(f => f.Name == pkColumn);
// if (match != null)
// {
// match.IsPrimaryKey = true;
// }
// }
// }
// connection.Close();
// }
// catch (Exception x)
// {
// TraceService.Instance.WriteConcat(TraceEvent.Error, "Error: Could not get primary key info - ", x.Message);
// if (connection.State != ConnectionState.Closed)
// {
// connection.Close();
// }
// }
// return fields;
// }
// }
//}
#endregion Old
public virtual ForeignKeyInfoCollection GetForeignKeyInfo(string tableName)
{
using (var connection = CreateDbConnection(DbProviderName, ConnectionDetails.ConnectionString))
{
const string query =
@"SELECT FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE FK.TABLE_NAME = '{0}'
ORDER BY 1,2,3,4";
var foreignKeyData = new ForeignKeyInfoCollection();
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(query, tableName);
command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
foreignKeyData.Add(new ForeignKeyInfo(
reader.GetString(0),
reader.GetString(1),
reader.GetString(2),
reader.GetString(3),
string.Empty,
reader.GetString(4)));
}
}
}
connection.Close();
return(foreignKeyData);
}
}
public static ForeignKeyInfoCollection GetForeignKeyData(this MySqlConnection connection, string tableName)
{
const string CMD_FOREIGN_KEYS_FORMAT =
@"SELECT
TABLE_NAME AS FK_Table,
COLUMN_NAME AS FK_Column,
REFERENCED_TABLE_NAME AS PK_Table,
REFERENCED_COLUMN_NAME AS PK_Column,
CONSTRAINT_NAME AS Constraint_Name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'kore_blogposts'
AND CONSTRAINT_NAME <> 'PRIMARY';";
var foreignKeyData = new ForeignKeyInfoCollection();
bool alreadyOpen = (connection.State != ConnectionState.Closed);
if (!alreadyOpen)
{
connection.Open();
}
using (var command = new MySqlCommand(string.Format(CMD_FOREIGN_KEYS_FORMAT, tableName), connection))
{
command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
foreignKeyData.Add(new ForeignKeyInfo(
reader.GetString(0),
reader.GetString(1),
reader.GetString(2),
reader.GetString(3),
string.Empty,
reader.GetString(4)));
}
}
}
if (!alreadyOpen)
{
connection.Close();
}
return(foreignKeyData);
}
public static IEnumerable <ColumnInfo> GetColumnData(this SqlConnection connection, string tableName)
{
const string CMD_COLUMN_INFO_FORMAT =
@"SELECT COLUMN_NAME, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{0}'";
List <ColumnInfo> list = new List <ColumnInfo>();
try
{
ForeignKeyInfoCollection foreignKeyColumns = connection.GetForeignKeyData(tableName);
connection.Open();
SqlCommand command = new SqlCommand(string.Format(CMD_COLUMN_INFO_FORMAT, tableName), connection);
command.CommandType = CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
ColumnInfo columnInfo = null;
//ForeignKeyInfoCollection foreignKeyColumns = new ForeignKeyInfoCollection();
//using (SqlConnection connection2 = new SqlConnection(connection.ConnectionString))
//{
// //Must use separate connection for this
// foreignKeyColumns = connection2.GetForeignKeyData(tableName);
//}
while (reader.Read())
{
columnInfo = new ColumnInfo();
if (!reader.IsDBNull(0))
{
columnInfo.Name = reader.GetString(0);
}
if (!reader.IsDBNull(1))
{
columnInfo.DefaultValue = reader.GetString(1);
}
else
{
columnInfo.DefaultValue = string.Empty;
}
if (foreignKeyColumns.Contains(columnInfo.Name))
{
columnInfo.IsForeignKeyColumn = true;
}
else
{
try
{
columnInfo.Type = reader.GetString(2).ToEnum <SqlDbType>(true);
}
catch (ArgumentNullException)
{
columnInfo.Type = SqlDbType.Variant;
}
catch (ArgumentException)
{
columnInfo.Type = SqlDbType.Variant;
}
}
if (!reader.IsDBNull(3))
{
columnInfo.MaxLength = reader.GetInt32(3);
}
if (!reader.IsDBNull(4))
{
if (reader.GetString(4).ToUpperInvariant().Equals("NO"))
{
columnInfo.IsRequired = false;
}
else
{
columnInfo.IsRequired = true;
}
}
list.Add(columnInfo);
}
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
return(list);
}
public static ForeignKeyInfoCollection GetForeignKeyData(this SqlConnection connection, string tableName)
{
#region Testing
//string oleConnectionString = connection.ConnectionString;
//if (!oleConnectionString.Contains("Provider"))
//{
// oleConnectionString = oleConnectionString.Prepend("Provider=SQLOLEDB;");
//}
//using (OleDbConnection oleConnection = new OleDbConnection(oleConnectionString))
//{
// return oleConnection.GetForeignKeyData(tableName);
//}
#endregion Testing
const string CMD_FOREIGN_KEYS_FORMAT =
@"SELECT FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE FK.TABLE_NAME = '{0}'
ORDER BY 1,2,3,4";
ForeignKeyInfoCollection foreignKeyData = new ForeignKeyInfoCollection();
connection.Open();
using (SqlCommand command = new SqlCommand(string.Format(CMD_FOREIGN_KEYS_FORMAT, tableName), connection))
{
command.CommandType = CommandType.Text;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
foreignKeyData.Add(new ForeignKeyInfo(
reader.GetString(0),
reader.GetString(1),
reader.GetString(2),
reader.GetString(3),
string.Empty,
reader.GetString(4)));
}
}
}
connection.Close();
return(foreignKeyData);
}
public static ColumnInfoCollection GetColumnData(this SqlConnection connection, string tableName)
{
#region Testing
//string oleConnectionString = connection.ConnectionString;
//if (!oleConnectionString.Contains("Provider"))
//{
// oleConnectionString = oleConnectionString.Prepend("Provider=SQLOLEDB;");
//}
//using (OleDbConnection oleConnection = new OleDbConnection(oleConnectionString))
//{
// return oleConnection.GetColumnData(tableName);
//}
#endregion Testing
const string CMD_COLUMN_INFO_FORMAT =
@"SELECT COLUMN_NAME, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{0}'";
const string CMD_IS_PRIMARY_KEY_FORMAT =
@"SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'IsPrimaryKey') = 1
AND TABLE_NAME = '{0}'";
ColumnInfoCollection list = new ColumnInfoCollection();
try
{
ForeignKeyInfoCollection foreignKeyColumns = connection.GetForeignKeyData(tableName);
connection.Open();
using (SqlCommand command = new SqlCommand(string.Format(CMD_COLUMN_INFO_FORMAT, tableName), connection))
{
command.CommandType = CommandType.Text;
using (SqlDataReader 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
{
columnInfo.DataType = DataTypeConvertor.GetSystemType(reader.GetString(2).ToEnum <SqlDbType>(true));
}
catch (ArgumentNullException)
{
columnInfo.DataType = typeof(object);
}
catch (ArgumentException)
{
columnInfo.DataType = typeof(object);
}
//}
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;
}
}
list.Add(columnInfo);
}
}
}
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
#region Primary Keys
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = string.Format(CMD_IS_PRIMARY_KEY_FORMAT, tableName);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string pkColumn = reader.GetString(0);
ColumnInfo match = list[pkColumn];
if (match != null)
{
match.KeyType = KeyType.PrimaryKey;
}
}
}
connection.Close();
}
#endregion Primary Keys
return(list);
}
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_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'";
ColumnInfoCollection list = new ColumnInfoCollection();
bool alreadyOpen = (connection.State != ConnectionState.Closed);
try
{
ForeignKeyInfoCollection foreignKeyColumns = connection.GetForeignKeyData(tableName);
if (!alreadyOpen)
{
connection.Open();
}
using (var command = new MySqlCommand(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;
}
if (!reader.IsDBNull(3))
{
columnInfo.MaximumLength = reader.GetInt32(3);
}
try
{
// Based on: https://www.devart.com/dotconnect/mysql/docs/datatypemapping.html
string type = reader.GetString(2).ToLowerInvariant();
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.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 static ForeignKeyInfoCollection GetForeignKeyData(this SqlConnection connection, string tableName, string schema = "dbo")
{
const string CMD_FOREIGN_KEYS_FORMAT =
@"SELECT FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE FK.TABLE_NAME = @TableName
AND C.CONSTRAINT_SCHEMA = @SchemaName
ORDER BY 1,2,3,4";
var foreignKeyData = new ForeignKeyInfoCollection();
bool alreadyOpen = (connection.State != ConnectionState.Closed);
if (!alreadyOpen)
{
connection.Open();
}
using (var command = new SqlCommand(CMD_FOREIGN_KEYS_FORMAT, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.Add(new SqlParameter
{
Direction = ParameterDirection.Input,
DbType = DbType.String,
ParameterName = "@TableName",
Value = tableName
});
command.Parameters.Add(new SqlParameter
{
Direction = ParameterDirection.Input,
DbType = DbType.String,
ParameterName = "@SchemaName",
Value = schema
});
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
foreignKeyData.Add(new ForeignKeyInfo(
reader.IsDBNull(0) ? null : reader.GetString(0),
reader.IsDBNull(1) ? null : reader.GetString(1),
reader.IsDBNull(2) ? null : reader.GetString(2),
reader.IsDBNull(3) ? null : reader.GetString(3),
string.Empty,
reader.IsDBNull(4) ? null : reader.GetString(4)));
}
}
}
if (!alreadyOpen)
{
connection.Close();
}
return(foreignKeyData);
}
public static ForeignKeyInfoCollection GetForeignKeyData(this NpgsqlConnection connection, string tableName, string schema = "public")
{
const string CMD_FOREIGN_KEYS_FORMAT =
@"SELECT
FK.""table_name"" AS FK_Table,
CU.""column_name"" AS FK_Column,
PK.""table_name"" AS PK_Table,
PT.""column_name"" AS PK_Column,
C.""constraint_name"" AS Constraint_Name
FROM information_schema.""referential_constraints"" C
INNER JOIN information_schema.""table_constraints"" FK ON C.""constraint_name"" = FK.""constraint_name""
INNER JOIN information_schema.""table_constraints"" PK ON C.""unique_constraint_name"" = PK.""constraint_name""
INNER JOIN information_schema.""key_column_usage"" CU ON C.""constraint_name"" = CU.""constraint_name""
INNER JOIN
(
SELECT i1.""table_name"", i2.""column_name""
FROM information_schema.""table_constraints"" i1
INNER JOIN information_schema.""key_column_usage"" i2 ON i1.""constraint_name"" = i2.""constraint_name""
WHERE i1.""constraint_type"" = 'PRIMARY KEY'
) PT ON PT.""table_name"" = PK.""table_name""
WHERE FK.""table_name"" = @TableName
AND FK.""table_schema"" = @SchemaName
ORDER BY 1,2,3,4";
var foreignKeyData = new ForeignKeyInfoCollection();
bool alreadyOpen = (connection.State != ConnectionState.Closed);
if (!alreadyOpen)
{
connection.Open();
}
using (var command = new NpgsqlCommand(CMD_FOREIGN_KEYS_FORMAT, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.Add(new NpgsqlParameter
{
Direction = ParameterDirection.Input,
DbType = DbType.String,
ParameterName = "@TableName",
Value = tableName
});
command.Parameters.Add(new NpgsqlParameter
{
Direction = ParameterDirection.Input,
DbType = DbType.String,
ParameterName = "@SchemaName",
Value = schema
});
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
foreignKeyData.Add(new ForeignKeyInfo(
reader.IsDBNull(0) ? null : reader.GetString(0),
reader.IsDBNull(1) ? null : reader.GetString(1),
reader.IsDBNull(2) ? null : reader.GetString(2),
reader.IsDBNull(3) ? null : reader.GetString(3),
string.Empty,
reader.IsDBNull(4) ? null : reader.GetString(4)));
}
}
}
if (!alreadyOpen)
{
connection.Close();
}
return(foreignKeyData);
}
