private void SetPrimaryKey(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 = 'P' 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); Common.PrimaryKey key = new Common.PrimaryKey(); key.Columns = new ColumnCollection(); foreach (DataRow row in table.Rows) { 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)); } oracleTable.PrimaryKey = key; }
private void SetPrimaryKey(MySQLTableSchema oracleTable) { string sql = @"select tc.table_name,tc.constraint_name,kc.column_name,tb.auto_increment from information_schema.table_constraints tc left join information_schema.key_column_usage kc on tc.constraint_name = kc.constraint_name and tc.table_schema = kc.table_schema and tc.table_name = kc.table_name left join information_schema.tables tb on tb.table_name = tc.table_name and tb.table_schema = tc.table_schema where upper(tc.constraint_type) = 'PRIMARY KEY' 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); Common.PrimaryKey key = new Common.PrimaryKey(); key.Columns = new ColumnCollection(); foreach (DataRow row in table.Rows) { string column_name = row["COLUMN_NAME"] + string.Empty; string constraint_name = row["CONSTRAINT_NAME"] + string.Empty; key.ConstraintName = constraint_name; IColumn pkCol = oracleTable.Columns.Find(it => it.Name == column_name); //pkCol.IsAutoIncrement = !string.IsNullOrEmpty(row["AUTO_INCREMENT"] + string.Empty); key.Columns.Add(pkCol); } oracleTable.PrimaryKey = key; }
private void SetPrimaryKey(MySQLTableSchema oracleTable) { string sql = @"select tc.table_name,tc.constraint_name,kc.column_name,tb.auto_increment from information_schema.table_constraints tc left join information_schema.key_column_usage kc on tc.constraint_name = kc.constraint_name and tc.table_schema = kc.table_schema and tc.table_name = kc.table_name left join information_schema.tables tb on tb.table_name = tc.table_name and tb.table_schema = tc.table_schema where tc.constraint_type = 'primary key' 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)); Common.PrimaryKey key = new Common.PrimaryKey(); key.Columns = new List <IColumn>(); foreach (DataRow row in table.Rows) { string column_name = row["COLUMN_NAME"] + string.Empty; string constraint_name = row["CONSTRAINT_NAME"] + string.Empty; key.ConstraintName = constraint_name; IColumn pkCol = oracleTable.Columns.Find(it => it.Name == column_name); pkCol.IsAutoIncrement = !string.IsNullOrEmpty(row["AUTO_INCREMENT"] + string.Empty); key.Columns.Add(pkCol); } oracleTable.PrimaryKey = key; }
private void SetPrimaryKey(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 = 'P' AND UC.TABLE_NAME =:TABLE_NAME"; var table = Db.Ado.GetDataTable(sql, new SugarParameter("TABLE_NAME", oracleTable.Name)); Common.PrimaryKey key = new Common.PrimaryKey(); key.Columns = new List <IColumn>(); foreach (DataRow row in table.Rows) { 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)); } oracleTable.PrimaryKey = key; }
private void SetPrimaryKey(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_PRIMARY_KEY = 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)); Common.PrimaryKey key = new Common.PrimaryKey(); key.Columns = new List <IColumn>(); foreach (DataRow row in table.Rows) { string column_name = row["COLUMN_NAME"] + string.Empty; string constraint_name = row["CONSTRAINT_NAME"] + string.Empty; key.ConstraintName = constraint_name; key.Columns.Add(SqlserverTable.Columns.Find(it => it.Name == column_name)); } SqlserverTable.PrimaryKey = key; }
private void SetPrimaryKey(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_PRIMARY_KEY = 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); Common.PrimaryKey key = new Common.PrimaryKey(); key.Columns = new ColumnCollection(); foreach (DataRow row in table.Rows) { 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)); } oracleTable.PrimaryKey = key; }