private void SetUniqueKey(MySQLTableSchema oracleTable)
{
string sql = @"select tc.table_name, tc.constraint_name,kc.column_name
from information_schema.table_constraints tc,information_schema.key_column_usage kc
where upper(tc.constraint_type)='UNIQUE' 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.UniqueKeys = new List <Common.UniqueKey>();
foreach (DataRow row in table.Rows)
{
string column_name = row["COLUMN_NAME"] + string.Empty;
string constraint_name = row["CONSTRAINT_NAME"] + string.Empty;
Common.UniqueKey key = oracleTable.UniqueKeys.Find(it => it.ConstraintName == constraint_name);
if (key == null)
{
key = new Common.UniqueKey();
key.Columns = new ColumnCollection();
key.ConstraintName = constraint_name;
oracleTable.UniqueKeys.Add(key);
}
key.Columns.Add(oracleTable.Columns.Find(it => it.Name == column_name));
}
}
private void SetUniqueKey(MySQLTableSchema oracleTable)
{
string sql = @"select tc.table_name, tc.constraint_name,kc.column_name
from information_schema.table_constraints tc,information_schema.key_column_usage kc
where tc.constraint_type='UNIQUE' 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.UniqueKeys = new List <Common.UniqueKey>();
foreach (DataRow row in table.Rows)
{
string column_name = row["COLUMN_NAME"] + string.Empty;
string constraint_name = row["CONSTRAINT_NAME"] + string.Empty;
Common.UniqueKey key = oracleTable.UniqueKeys.Find(it => it.ConstraintName == constraint_name);
if (key == null)
{
key = new Common.UniqueKey();
key.Columns = new List <IColumn>();
key.ConstraintName = constraint_name;
oracleTable.UniqueKeys.Add(key);
}
key.Columns.Add(oracleTable.Columns.Find(it => it.Name == column_name));
}
}
private void SetUniqueKey(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
FROM USER_CONSTRAINTS UC
LEFT JOIN USER_CONS_COLUMNS UCC
ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
WHERE UC.CONSTRAINT_TYPE = 'U'
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);
List <Common.UniqueKey> uniques = new List <Common.UniqueKey>();
foreach (DataRow row in table.Rows)
{
string column_name = row["COLUMN_NAME"] + string.Empty;
string constraint_name = row["CONSTRAINT_NAME"] + string.Empty;
Common.UniqueKey key = uniques.Find(it => it.ConstraintName == constraint_name);
if (key == null)
{
key = new Common.UniqueKey();
key.Columns = new ColumnCollection();
key.ConstraintName = constraint_name;
uniques.Add(key);
}
key.Columns.Add(oracleTable.Columns.Find(it => it.Name == column_name));
}
oracleTable.UniqueKeys = uniques;
}
private void SetUniqueKey(SqlServerTableSchema oracleTable)
{
if (oracleTable == null)
{
return;
}
if (oracleTable.Columns == null || oracleTable.Columns.Count <= 0)
{
SetColumns(oracleTable);
}
string sql = @"SELECT IDX.NAME AS CONSTRAINT_NAME, COL.NAME AS COLUMN_NAME
FROM SYS.INDEXES IDX JOIN
SYS.INDEX_COLUMNS IDXCOL ON (IDX.OBJECT_ID = IDXCOL.OBJECT_ID AND IDX.INDEX_ID = IDXCOL.INDEX_ID AND
IDX.IS_UNIQUE_CONSTRAINT = 1) JOIN
SYS.TABLES TAB ON (IDX.OBJECT_ID = TAB.OBJECT_ID) JOIN
SYS.COLUMNS COL ON (IDX.OBJECT_ID = COL.OBJECT_ID AND IDXCOL.COLUMN_ID = COL.COLUMN_ID) where tab.name=@TABLE_NAME";
var para = new SqlParameter("@TABLE_NAME", oracleTable.Name.ToUpper());
DbHelper helper = new DbHelper(this._connectionString);
var table = helper.ListBySql(sql, para);
List <Common.UniqueKey> uniques = new List <Common.UniqueKey>();
foreach (DataRow row in table.Rows)
{
string column_name = row["COLUMN_NAME"] + string.Empty;
string constraint_name = row["CONSTRAINT_NAME"] + string.Empty;
Common.UniqueKey key = uniques.Find(it => it.ConstraintName == constraint_name);
if (key == null)
{
key = new Common.UniqueKey();
key.Columns = new ColumnCollection();
key.ConstraintName = constraint_name;
uniques.Add(key);
}
key.Columns.Add(oracleTable.Columns.Find(it => it.Name == column_name));
}
oracleTable.UniqueKeys = uniques;
}
private void SetUniqueKey(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
FROM USER_CONSTRAINTS UC
LEFT JOIN USER_CONS_COLUMNS UCC
ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
WHERE UC.CONSTRAINT_TYPE = 'U'
AND UC.TABLE_NAME =:TABLE_NAME";
var table = Db.Ado.GetDataTable(sql, new SugarParameter("TABLE_NAME", oracleTable.Name));
List <Common.UniqueKey> uniques = new List <Common.UniqueKey>();
foreach (DataRow row in table.Rows)
{
string column_name = row["COLUMN_NAME"] + string.Empty;
string constraint_name = row["CONSTRAINT_NAME"] + string.Empty;
Common.UniqueKey key = uniques.Find(it => it.ConstraintName == constraint_name);
if (key == null)
{
key = new Common.UniqueKey();
key.Columns = new List <IColumn>();
key.ConstraintName = constraint_name;
uniques.Add(key);
}
key.Columns.Add(oracleTable.Columns.Find(it => it.Name == column_name));
}
oracleTable.UniqueKeys = uniques;
}
private void SetUniqueKey(SqlServerTableSchema SqlserverTable)
{
if (SqlserverTable == null)
{
return;
}
if (SqlserverTable.Columns == null || SqlserverTable.Columns.Count <= 0)
{
SetColumns(SqlserverTable);
}
string sql = @"SELECT IDX.NAME AS CONSTRAINT_NAME, COL.NAME AS COLUMN_NAME
FROM SYS.INDEXES IDX JOIN
SYS.INDEX_COLUMNS IDXCOL ON (IDX.OBJECT_ID = IDXCOL.OBJECT_ID AND IDX.INDEX_ID = IDXCOL.INDEX_ID AND
IDX.IS_UNIQUE_CONSTRAINT = 1) JOIN
SYS.TABLES TAB ON (IDX.OBJECT_ID = TAB.OBJECT_ID) JOIN
SYS.COLUMNS COL ON (IDX.OBJECT_ID = COL.OBJECT_ID AND IDXCOL.COLUMN_ID = COL.COLUMN_ID) where tab.name=@TABLE_NAME";
var table = Db.Ado.GetDataTable(sql, new SugarParameter("TABLE_NAME", SqlserverTable.Name));
List <Common.UniqueKey> uniques = new List <Common.UniqueKey>();
foreach (DataRow row in table.Rows)
{
string column_name = row["COLUMN_NAME"] + string.Empty;
string constraint_name = row["CONSTRAINT_NAME"] + string.Empty;
Common.UniqueKey key = uniques.Find(it => it.ConstraintName == constraint_name);
if (key == null)
{
key = new Common.UniqueKey();
key.Columns = new List <IColumn>();
key.ConstraintName = constraint_name;
uniques.Add(key);
}
key.Columns.Add(SqlserverTable.Columns.Find(it => it.Name == column_name));
}
SqlserverTable.UniqueKeys = uniques;
}
