public static List<ForeignKeyInfo> GetTableSingleFieldForeignKeys(IDbConnection connection, string schema, string tableName)
{
var inf = InformationSchema(connection);
List<ForeignKeyInfo> foreignKeyInfos = new List<ForeignKeyInfo>();
if (Dialect.HasFlag(SqlDialect.Sqlite))
{
try
{
using (var reader =
SqlHelper.ExecuteReader(connection,
(String.Format("PRAGMA foreign_key_list({0});", tableName))))
{
while (reader.Read())
{
ForeignKeyInfo foreignKeyInfo = new ForeignKeyInfo();
foreignKeyInfo.FKTable = tableName;
foreignKeyInfo.FKColumn = reader.GetString(3);
foreignKeyInfo.PKTable = reader.GetString(2);
foreignKeyInfo.PKColumn = reader.GetString(4);
foreignKeyInfos.Add(foreignKeyInfo);
}
}
}
catch (Exception)
{
}
return foreignKeyInfos;
}
try
{
var c = new Alias(inf + "REFERENTIAL_CONSTRAINTS", "c");
var fk = new Alias(inf + "CONSTRAINT_COLUMN_USAGE", "fk");
var pk = new Alias(inf + "KEY_COLUMN_USAGE", "pk");
var list = connection.Query<ForeignKeyInfo>(new SqlQuery()
.From(c)
.From(fk)
.From(pk)
.Where(
fk._("TABLE_SCHEMA") == schema &
fk._("TABLE_NAME") == tableName &
fk._("CONSTRAINT_SCHEMA") == c._("CONSTRAINT_SCHEMA") &
fk._("CONSTRAINT_NAME") == c._("CONSTRAINT_NAME") &
pk._("CONSTRAINT_SCHEMA") == c._("UNIQUE_CONSTRAINT_SCHEMA") &
pk._("CONSTRAINT_NAME") == c._("UNIQUE_CONSTRAINT_NAME"))
.Select(fk["CONSTRAINT_NAME"], "FKName")
.Select(fk["TABLE_SCHEMA"], "FKSchema")
.Select(fk["TABLE_NAME"], "FKTable")
.Select(fk["COLUMN_NAME"], "FKColumn")
.Select(pk["TABLE_SCHEMA"], "PKSchema")
.Select(pk["TABLE_NAME"], "PKTable")
.Select(pk["COLUMN_NAME"], "PKColumn")
.OrderBy(fk["CONSTRAINT_NAME"]));
foreach (var foreignKeyInfo in list)
{
string priorName = "";
bool priorDeleted = false;
// eğer bir önceki ile aynıysa bunu listeye ekleme ve öncekini de sil
if (priorName == foreignKeyInfo.FKName)
{
if (!priorDeleted)
{
foreignKeyInfos.RemoveAt(foreignKeyInfos.Count - 1);
priorDeleted = true;
}
continue;
}
foreignKeyInfos.Add(foreignKeyInfo);
priorDeleted = false;
priorName = foreignKeyInfo.FKName;
}
}
catch (Exception)
{
}
return foreignKeyInfos;
}
public static List<ForeignKeyInfo> GetTableSingleFieldForeignKeys(IDbConnection connection, string schema, string tableName)
{
var inf = InformationSchema(connection);
List<ForeignKeyInfo> foreignKeyInfos = new List<ForeignKeyInfo>();
if (connection.GetDialect().ServerType.StartsWith("Sqlite", StringComparison.OrdinalIgnoreCase))
{
try
{
using (var reader =
SqlHelper.ExecuteReader(connection,
(String.Format("PRAGMA foreign_key_list({0});", tableName))))
{
while (reader.Read())
{
ForeignKeyInfo foreignKeyInfo = new ForeignKeyInfo();
foreignKeyInfo.FKTable = tableName;
foreignKeyInfo.FKColumn = reader.GetString(3);
foreignKeyInfo.PKTable = reader.GetString(2);
foreignKeyInfo.PKColumn = reader.GetString(4);
foreignKeyInfos.Add(foreignKeyInfo);
}
}
}
catch (Exception)
{
}
return foreignKeyInfos;
}
if (connection.GetDialect().ServerType.StartsWith("Firebird", StringComparison.OrdinalIgnoreCase))
{
var query = @"
select
PK.RDB$RELATION_NAME as PKTABLE_NAME
,ISP.RDB$FIELD_NAME as PKCOLUMN_NAME
,FK.RDB$RELATION_NAME as FKTABLE_NAME
,ISF.RDB$FIELD_NAME as FKCOLUMN_NAME
,(ISP.RDB$FIELD_POSITION + 1) as KEY_SEQ
,RC.RDB$UPDATE_RULE as UPDATE_RULE
,RC.RDB$DELETE_RULE as DELETE_RULE
,PK.RDB$CONSTRAINT_NAME as PK_NAME
,FK.RDB$CONSTRAINT_NAME as FK_NAME
from
RDB$RELATION_CONSTRAINTS PK
,RDB$RELATION_CONSTRAINTS FK
,RDB$REF_CONSTRAINTS RC
,RDB$INDEX_SEGMENTS ISP
,RDB$INDEX_SEGMENTS ISF
WHERE FK.RDB$RELATION_NAME = '{0}' and
FK.RDB$CONSTRAINT_NAME = RC.RDB$CONSTRAINT_NAME
and PK.RDB$CONSTRAINT_NAME = RC.RDB$CONST_NAME_UQ
and ISP.RDB$INDEX_NAME = PK.RDB$INDEX_NAME
and ISF.RDB$INDEX_NAME = FK.RDB$INDEX_NAME
and ISP.RDB$FIELD_POSITION = ISF.RDB$FIELD_POSITION
order by 1, 5";
try
{
using (var reader =
SqlHelper.ExecuteReader(connection,
(String.Format(query, tableName))))
{
while (reader.Read())
{
ForeignKeyInfo foreignKeyInfo = new ForeignKeyInfo();
foreignKeyInfo.FKTable = tableName;
foreignKeyInfo.FKColumn = reader.GetString(3).TrimEnd();
foreignKeyInfo.PKTable = reader.GetString(0).TrimEnd();
foreignKeyInfo.PKColumn = reader.GetString(1).TrimEnd();
foreignKeyInfos.Add(foreignKeyInfo);
}
}
}
catch (Exception)
{
}
return foreignKeyInfos;
}
if (connection.GetDialect().ServerType.StartsWith("Postgres", StringComparison.OrdinalIgnoreCase))
{
try
{
var list = connection.Query(
@"SELECT * FROM (
SELECT
o.conname AS constraint_name,
(SELECT nspname FROM pg_namespace WHERE oid=m.relnamespace) AS source_schema,
m.relname AS source_table,
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = m.oid AND a.attnum = o.conkey[1] AND a.attisdropped = false) AS source_column,
(SELECT nspname FROM pg_namespace WHERE oid=f.relnamespace) AS target_schema,
f.relname AS target_table,
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = f.oid AND a.attnum = o.confkey[1] AND a.attisdropped = false) AS target_column
FROM
pg_constraint o LEFT JOIN pg_class c ON c.oid = o.conrelid
LEFT JOIN pg_class f ON f.oid = o.confrelid LEFT JOIN pg_class m ON m.oid = o.conrelid
WHERE
o.contype = 'f' AND o.conrelid IN (SELECT oid FROM pg_class c WHERE c.relkind = 'r')) x
WHERE source_schema = @sh AND source_table = @tb
ORDER BY constraint_name", new { sh = schema, tb = tableName }).ToList();
foreach (var fk in list)
{
string priorName = "";
bool priorDeleted = false;
// eğer bir önceki ile aynıysa bunu listeye ekleme ve öncekini de sil
var fkName = fk.constraint_name as string;
if (priorName == fkName)
{
if (!priorDeleted)
{
foreignKeyInfos.RemoveAt(foreignKeyInfos.Count - 1);
priorDeleted = true;
}
continue;
}
var foreignKeyInfo = new ForeignKeyInfo
{
FKName = fkName,
FKSchema = fk.source_schema,
FKTable = fk.source_table,
FKColumn = fk.source_column,
PKSchema = fk.target_schema,
PKTable = fk.target_table,
PKColumn = fk.target_column
};
foreignKeyInfos.Add(foreignKeyInfo);
priorDeleted = false;
priorName = foreignKeyInfo.FKName;
}
}
catch (Exception ex)
{
ex.Log();
}
return foreignKeyInfos;
}
if (connection.GetDialect().ServerType.StartsWith("MySql", StringComparison.OrdinalIgnoreCase))
{
using (var reader =
SqlHelper.ExecuteReader(connection,
(String.Format(@"
SELECT k.COLUMN_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME
FROM information_schema.TABLE_CONSTRAINTS i
LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME
WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND i.TABLE_SCHEMA = '{0}'
AND i.TABLE_NAME = '{1}'"
, schema, tableName))))
{
while (reader.Read())
{
ForeignKeyInfo foreignKeyInfo = new ForeignKeyInfo();
foreignKeyInfo.FKTable = tableName;
foreignKeyInfo.FKColumn = reader.GetString(0);
foreignKeyInfo.PKTable = reader.GetString(1);
foreignKeyInfo.PKColumn = reader.GetString(2);
foreignKeyInfos.Add(foreignKeyInfo);
}
}
return foreignKeyInfos;
}
if (connection.GetDialect().ServerType.StartsWith("SqlServer", StringComparison.OrdinalIgnoreCase))
{
var c = new Alias(inf + "REFERENTIAL_CONSTRAINTS", "c");
var fk = new Alias(inf + "CONSTRAINT_COLUMN_USAGE", "fk");
var pk = new Alias(inf + "KEY_COLUMN_USAGE", "pk");
var list = connection.Query<ForeignKeyInfo>(new SqlQuery()
.From(c)
.From(fk)
.From(pk)
.Where(
fk._("TABLE_SCHEMA") == schema &
fk._("TABLE_NAME") == tableName &
fk._("CONSTRAINT_SCHEMA") == c._("CONSTRAINT_SCHEMA") &
fk._("CONSTRAINT_NAME") == c._("CONSTRAINT_NAME") &
pk._("CONSTRAINT_SCHEMA") == c._("UNIQUE_CONSTRAINT_SCHEMA") &
pk._("CONSTRAINT_NAME") == c._("UNIQUE_CONSTRAINT_NAME"))
.Select(fk["CONSTRAINT_NAME"], "FKName")
.Select(fk["TABLE_SCHEMA"], "FKSchema")
.Select(fk["TABLE_NAME"], "FKTable")
.Select(fk["COLUMN_NAME"], "FKColumn")
.Select(pk["TABLE_SCHEMA"], "PKSchema")
.Select(pk["TABLE_NAME"], "PKTable")
.Select(pk["COLUMN_NAME"], "PKColumn")
.OrderBy(fk["CONSTRAINT_NAME"]));
foreach (var foreignKeyInfo in list)
{
string priorName = "";
bool priorDeleted = false;
// eğer bir önceki ile aynıysa bunu listeye ekleme ve öncekini de sil
if (priorName == foreignKeyInfo.FKName)
{
if (!priorDeleted)
{
foreignKeyInfos.RemoveAt(foreignKeyInfos.Count - 1);
priorDeleted = true;
}
continue;
}
foreignKeyInfos.Add(foreignKeyInfo);
priorDeleted = false;
priorName = foreignKeyInfo.FKName;
}
}
return foreignKeyInfos;
}
