public override IList<ForeignKey> GetAllForeignKeys()
{
string sql = @"SELECT TableName=T.name,ForeignKey=FC.name,ReferenceTableName=R.name,ReferenceKey=RC.name FROM sysforeignkeys F LEFT JOIN " +
"sysobjects T ON F.fkeyid=T.id LEFT JOIN sysobjects R ON F.rkeyid=R.id LEFT JOIN " +
"syscolumns FC ON F.fkeyid=FC.id AND F.fkey=FC.colid LEFT JOIN syscolumns RC ON F.rkeyid=RC.id AND F.rkey=RC.colid " +
"WHERE T.xtype='U' AND R.xtype='U'";
this.Database.Connection.Open();
DataTable foreignKeyTable = this.Database.ExecuteDataTable(sql, false);
this.Database.Connection.Close();
List<ForeignKey> keys = new List<ForeignKey>();
ForeignKey foreignKey;
foreach (DataRow row in foreignKeyTable.Rows)
{
foreignKey = new ForeignKey();
foreignKey.TableName = row["TableName"].ToString();
foreignKey.Key = row["ForeignKey"].ToString();
foreignKey.ReferenceTableName = row["ReferenceTableName"].ToString();
foreignKey.ReferenceKey = row["ReferenceKey"].ToString();
keys.Add(foreignKey);
}
return keys;
}
public override IList<ForeignKey> GetAllForeignKeys()
{
string sql = @"select con.table_name TableName,col.column_name ForeignKey,r.table_name ReferenceTableName,r.column_name ReferenceKey from " +
"user_constraints con,user_cons_columns col, (select t2.table_name,t2.column_name,t1.r_constraint_name " +
"from user_constraints t1,user_cons_columns t2 where t1.r_constraint_name=t2.constraint_name) r " +
"where con.constraint_name=col.constraint_name and con.r_constraint_name=r.r_constraint_name";
this.Database.Connection.Open();
DataTable foreignKeyTable = this.Database.ExecuteDataTable(sql, false);
this.Database.Connection.Close();
List<ForeignKey> keys = new List<ForeignKey>();
ForeignKey foreignKey;
foreach (DataRow row in foreignKeyTable.Rows)
{
foreignKey = new ForeignKey();
foreignKey.TableName = row["TableName"].ToString();
foreignKey.Key = row["ForeignKey"].ToString();
foreignKey.ReferenceTableName = row["ReferenceTableName"].ToString();
foreignKey.ReferenceKey = row["ReferenceKey"].ToString();
keys.Add(foreignKey);
}
return keys;
}
public override IList<ForeignKey> GetAllForeignKeys()
{
this.Database.Connection.Open();
DataTable foreignKeyTable = (this.Database.Connection as OleDbConnection).GetOleDbSchemaTable(
OleDbSchemaGuid.Foreign_Keys, null);
this.Database.Connection.Close();
if(foreignKeyTable==null||foreignKeyTable.Rows.Count==0)
{
return new List<ForeignKey>();
}
IList<ForeignKey> foreignKeys = new List<ForeignKey>();
ForeignKey foreignKey = null;
foreach (DataRow row in foreignKeyTable.Rows)
{
foreignKey = new ForeignKey();
foreignKey.TableName = row["PK_TABLE_NAME"].AsString();
foreignKey.Key = row["PK_COLUMN_NAME"].AsString();
foreignKey.ReferenceTableName = row["FK_TABLE_NAME"].AsString();
foreignKey.ReferenceKey = row["FK_COLUMN_NAME"].AsString();
foreignKeys.Add(foreignKey);
}
return foreignKeys;
}
public override IList<ForeignKey> GetAllForeignKeys()
{
string foreignSql = @"SELECT TABLE_NAME,COLUMN_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where CONSTRAINT_NAME<>'PRIMARY' and TABLE_SCHEMA='{0}'";
DataTable foreignKeyTable = GetTable(foreignSql);
if (foreignKeyTable == null || foreignKeyTable.Rows.Count == 0)
{
return new List<ForeignKey>();
}
IList<ForeignKey> foreignKeys = new List<ForeignKey>();
ForeignKey foreignKey = null;
foreach (DataRow row in foreignKeyTable.Rows)
{
foreignKey = new ForeignKey();
foreignKey.TableName = row["TABLE_NAME"].AsString();
foreignKey.Key = row["COLUMN_NAME"].AsString();
foreignKey.ReferenceTableName = row["REFRENCED_TABLE_NAME"].AsString();
foreignKey.ReferenceKey = row["REFRENCED_COLUMN_NAME"].AsString();
foreignKeys.Add(foreignKey);
}
return foreignKeys;
}
public override IList<ForeignKey> GetAllForeignKeys()
{
this.Database.Connection.Open();
DataTable foreignKeyTable = (this.Database.Connection as SQLiteConnection).GetSchema("FOREIGNKEYS");
this.Database.Connection.Close();
if (foreignKeyTable == null || foreignKeyTable.Rows.Count == 0)
{
return new List<ForeignKey>();
}
IList<ForeignKey> foreignKeys = new List<ForeignKey>();
ForeignKey foreignKey = null;
foreach (DataRow row in foreignKeyTable.Rows)
{
foreignKey = new ForeignKey();
foreignKey.TableName = row["TABLE_NAME"].AsString();
foreignKey.Key = row["FKEY_FROM_COLUMN"].AsString();
foreignKey.ReferenceTableName = row["FKEY_TO_TABLE"].AsString();
foreignKey.ReferenceKey = row["FKEY_TO_COLUMN"].AsString();
foreignKeys.Add(foreignKey);
}
return foreignKeys;
}