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 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 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);
}
