private void SetForeignKey(MySQLTableSchema oracleTable)
{
string sql = @"select tc.table_name, tc.constraint_name,kc.column_name,kc.referenced_table_name,kc.referenced_table_schema
from information_schema.table_constraints tc,information_schema.key_column_usage kc
where tc.constraint_type='FOREIGN KEY' and tc.constraint_name=kc.constraint_name
and tc.table_schema=kc.table_schema and tc.table_name=kc.table_name
and tc.table_name=@table_name and tc.table_schema=@table_schema";
MySqlParameter para0 = new MySqlParameter("@table_name", oracleTable.Name);
MySqlParameter para1 = new MySqlParameter("@table_schema", this.DatabaseName);
DbHelper helper = new DbHelper(this._connectionString);
var table = helper.ListBySql(sql, para0, para1);
oracleTable.ForiegnKeys = new List <Common.ForeignKey>();
foreach (DataRow row in table.Rows)
{
Common.ForeignKey key = new Common.ForeignKey();
key.Columns = new ColumnCollection();
string column_name = row["COLUMN_NAME"] + string.Empty;
string constraint_name = row["CONSTRAINT_NAME"] + string.Empty;
key.ConstraintName = constraint_name;
key.Columns.Add(oracleTable.Columns.Find(it => it.Name == column_name));
string foreignTable = row["referenced_table_name"] + string.Empty;
if (ContainForeignTable && key.ForeignTable == null && !string.IsNullOrEmpty(foreignTable))
{
string referenced_schema = row["referenced_table_schema"] + string.Empty;
var fac = new MySQLDataFactory(this._connectionString);
fac.ContainForeignTable = false;
fac._db_name = referenced_schema;
key.ForeignTable = fac.GetTableSchema(foreignTable);
}
oracleTable.ForiegnKeys.Add(key);
}
}
private void SetForeignKey(MySQLTableSchema oracleTable)
{
string sql = @"select tc.table_name, tc.constraint_name,kc.column_name,kc.referenced_table_name,kc.referenced_table_schema
from information_schema.table_constraints tc,information_schema.key_column_usage kc
where tc.constraint_type='FOREIGN KEY' and tc.constraint_name=kc.constraint_name
and tc.table_schema=kc.table_schema and tc.table_name=kc.table_name
and tc.table_name=@table_name and tc.table_schema=@table_schema";
var table = Db.Ado.GetDataTable(sql, new SugarParameter("@table_name", oracleTable.Name), new SugarParameter("@table_schema", DatabaseName));
oracleTable.ForiegnKeys = new List <Common.ForeignKey>();
foreach (DataRow row in table.Rows)
{
Common.ForeignKey key = new Common.ForeignKey();
key.Columns = new List <IColumn>();
string column_name = row["COLUMN_NAME"] + string.Empty;
string constraint_name = row["CONSTRAINT_NAME"] + string.Empty;
key.ConstraintName = constraint_name;
key.Columns.Add(oracleTable.Columns.Find(it => it.Name == column_name));
if (ContainForeignTable && key.ForeignTable == null)
{
string foreignTable = row["referenced_table_name"] + string.Empty;
string referenced_schema = row["referenced_table_schema"] + string.Empty;
var fac = new MySQLDbOperation(this.Db);
fac.ContainForeignTable = false;
fac._db_name = referenced_schema;
key.ForeignTable = fac.GetTableSchema(foreignTable);
}
oracleTable.ForiegnKeys.Add(key);
}
}
private void SetForeignKey(SqlServerTableSchema SqlserverTable)
{
if (SqlserverTable == null)
{
return;
}
if (SqlserverTable.Columns == null || SqlserverTable.Columns.Count <= 0)
{
SetColumns(SqlserverTable);
}
string sql = @"select obj.name CONSTRAINT_NAME,
main_col.name COLUMN_NAME,
sub.name FOREIGN_TABLE_NAME
from sysforeignkeys fk left join sys.tables main on fk.fkeyid=main.object_id
left join sys.tables sub on sub.object_id=fk.rkeyid
left join syscolumns main_col on fk.fkey=main_col.colid and fk.fkeyid=main_col.id
left join sysobjects obj on obj.id=fk.constid
where main.name=@TABLE_NAME";
var table = Db.Ado.GetDataTable(sql, new SugarParameter("TABLE_NAME", SqlserverTable.Name));
SqlserverTable.ForiegnKeys = new List <Common.ForeignKey>();
foreach (DataRow row in table.Rows)
{
string column_name = row["COLUMN_NAME"] + string.Empty;
string constraint_name = row["CONSTRAINT_NAME"] + string.Empty;
Common.ForeignKey key = new Common.ForeignKey();
key.Columns = new List <IColumn>();
key.ConstraintName = constraint_name;
if (key.ForeignTable == null && ContainForeignTable)
{
string forignTable = row["FOREIGN_TABLE_NAME"] + string.Empty;
var fac = new SqlServerDbOperation(Db);
fac.ContainForeignTable = false;
key.ForeignTable = fac.GetTableSchema(forignTable);
}
key.Columns.Add(SqlserverTable.Columns.Find(it => it.Name == column_name));
SqlserverTable.ForiegnKeys.Add(key);
}
}
private void SetForeignKey(OracleTableSchema oracleTable)
{
if (oracleTable == null)
{
return;
}
if (oracleTable.Columns == null || oracleTable.Columns.Count <= 0)
{
SetColumns(oracleTable);
}
string sql = @"SELECT UCC.CONSTRAINT_NAME, UCC.COLUMN_NAME,UC1.TABLE_NAME FOREIGN_TABLE_NAME
FROM USER_CONSTRAINTS UC
LEFT JOIN USER_CONS_COLUMNS UCC
ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
LEFT JOIN USER_CONSTRAINTS UC1 ON UC.R_CONSTRAINT_NAME=UC1.CONSTRAINT_NAME
WHERE UC.CONSTRAINT_TYPE = 'R'
AND UC.TABLE_NAME =:TABLE_NAME";
var table = Db.Ado.GetDataTable(sql, new SugarParameter("TABLE_NAME", oracleTable.Name));
oracleTable.ForiegnKeys = new List <Common.ForeignKey>();
foreach (DataRow row in table.Rows)
{
string column_name = row["COLUMN_NAME"] + string.Empty;
string constraint_name = row["CONSTRAINT_NAME"] + string.Empty;
Common.ForeignKey key = new Common.ForeignKey();
key.Columns = new List <IColumn>();
key.ConstraintName = constraint_name;
if (key.ForeignTable == null && ContainForeignTable)
{
string forignTable = row["FOREIGN_TABLE_NAME"] + string.Empty;
var fac = new OracleDbOperation(this.Db);
fac.ContainForeignTable = false;
key.ForeignTable = fac.GetTableSchema(forignTable);
}
key.Columns.Add(oracleTable.Columns.Find(it => it.Name == column_name));
oracleTable.ForiegnKeys.Add(key);
}
}
private void SetForeignKey(SqlServerTableSchema oracleTable)
{
if (oracleTable == null)
{
return;
}
if (oracleTable.Columns == null || oracleTable.Columns.Count <= 0)
{
SetColumns(oracleTable);
}
string sql = @"select obj.name CONSTRAINT_NAME,
main_col.name COLUMN_NAME,
sub.name FOREIGN_TABLE_NAME
from sysforeignkeys fk left join sys.tables main on fk.fkeyid=main.object_id
left join sys.tables sub on sub.object_id=fk.rkeyid
left join syscolumns main_col on fk.fkey=main_col.colid and fk.fkeyid=main_col.id
left join sysobjects obj on obj.id=fk.constid
where main.name=@TABLE_NAME";
var para = new SqlParameter("@TABLE_NAME", oracleTable.Name.ToUpper());
DbHelper helper = new DbHelper(this._connectionString);
var table = helper.ListBySql(sql, para);
oracleTable.ForiegnKeys = new List <Common.ForeignKey>();
foreach (DataRow row in table.Rows)
{
string column_name = row["COLUMN_NAME"] + string.Empty;
string constraint_name = row["CONSTRAINT_NAME"] + string.Empty;
Common.ForeignKey key = new Common.ForeignKey();
key.Columns = new ColumnCollection();
key.ConstraintName = constraint_name;
if (key.ForeignTable == null && ContainForeignTable)
{
string forignTable = row["FOREIGN_TABLE_NAME"] + string.Empty;
var fac = new SqlServerDataFactory(this._connectionString);
fac.ContainForeignTable = false;
key.ForeignTable = fac.GetTableSchema(forignTable);
}
key.Columns.Add(oracleTable.Columns.Find(it => it.Name == column_name));
oracleTable.ForiegnKeys.Add(key);
}
}
private void SetForeignKey(OracleTableSchema oracleTable)
{
if (oracleTable == null)
{
return;
}
if (oracleTable.Columns == null || oracleTable.Columns.Count <= 0)
{
SetColumns(oracleTable);
}
string sql = @"SELECT UCC.CONSTRAINT_NAME, UCC.COLUMN_NAME,UC1.TABLE_NAME FOREIGN_TABLE_NAME
FROM USER_CONSTRAINTS UC
LEFT JOIN USER_CONS_COLUMNS UCC
ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
LEFT JOIN USER_CONSTRAINTS UC1 ON UC.R_CONSTRAINT_NAME=UC1.CONSTRAINT_NAME
WHERE UC.CONSTRAINT_TYPE = 'R'
AND UC.TABLE_NAME =:TABLE_NAME";
OracleParameter para = new OracleParameter("TABLE_NAME", oracleTable.Name.ToUpper());
DbHelper helper = new DbHelper(this._connectionString);
var table = helper.ListBySql(sql, para);
oracleTable.ForiegnKeys = new List <Common.ForeignKey>();
foreach (DataRow row in table.Rows)
{
string column_name = row["COLUMN_NAME"] + string.Empty;
string constraint_name = row["CONSTRAINT_NAME"] + string.Empty;
Common.ForeignKey key = new Common.ForeignKey();
key.Columns = new ColumnCollection();
key.ConstraintName = constraint_name;
if (key.ForeignTable == null && ContainForeignTable)
{
string forignTable = row["FOREIGN_TABLE_NAME"] + string.Empty;
var fac = new OracleDataFactory(this._connectionString);
fac.ContainForeignTable = false;
key.ForeignTable = fac.GetTableSchema(forignTable);
}
key.Columns.Add(oracleTable.Columns.Find(it => it.Name == column_name));
oracleTable.ForiegnKeys.Add(key);
}
}
