public override ITableSchema GetTableSchema(string table_name) { string sql = @"SELECT UO.OBJECT_NAME AS TABLE_NAME,UO.OBJECT_TYPE,UC.COMMENTS,UV.TEXT FROM USER_OBJECTS UO LEFT JOIN USER_TAB_COMMENTS UC ON UC.TABLE_NAME=UO.OBJECT_NAME LEFT JOIN USER_VIEWS UV ON UV.VIEW_NAME=UO.OBJECT_NAME WHERE UO.OBJECT_TYPE IN ('VIEW','TABLE') AND UO.OBJECT_NAME=:TABLE_NAME ORDER BY UO.OBJECT_NAME ASC"; DbHelper helper = new DbHelper(this._connectionString); //Console.WriteLine(this._connectionString); var data = helper.ListBySql(sql, new OracleParameter("TABLE_NAME", table_name.ToUpper())); //Console.WriteLine("查询数据条数:" + data.Rows.Count); string objectType = data.Rows[0]["OBJECT_TYPE"] + string.Empty; OracleTableSchema oracleTable = new OracleTableSchema(); oracleTable.Name = table_name; oracleTable.Comment = data.Rows[0]["COMMENTS"] + string.Empty; oracleTable.ObjectType = objectType; if (objectType == "VIEW") { oracleTable.ViewScript = data.Rows[0]["TEXT"].ToString(); } SetColumns(oracleTable); SetForeignKey(oracleTable); SetUniqueKey(oracleTable); SetPrimaryKey(oracleTable); return(oracleTable); }
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 SetColumns(OracleTableSchema oracleTable) { if (oracleTable == null) { return; } oracleTable.Columns = new ColumnCollection(); string sql = @"SELECT TC.COLUMN_NAME, TC.DATA_TYPE, NVL(DECODE(TC.CHAR_LENGTH,0,TC.DATA_PRECISION,TC.CHAR_LENGTH),TC.DATA_LENGTH) AS DATA_LENGTH, TC.DATA_PRECISION, NVL(TC.DATA_SCALE, -1) DATA_SCALE, TC.NULLABLE, TC.DATA_DEFAULT, CC.COMMENTS FROM USER_TAB_COLUMNS TC LEFT JOIN USER_COL_COMMENTS CC ON TC.COLUMN_NAME = CC.COLUMN_NAME AND TC.TABLE_NAME = CC.TABLE_NAME WHERE TC.TABLE_NAME=:TABLE_NAME ORDER BY TC.COLUMN_ID ASC"; List <IColumn> columns = new List <IColumn>(); OracleParameter para = new OracleParameter("TABLE_NAME", oracleTable.Name); DbHelper helper = new DbHelper(this._connectionString); var table = helper.ListBySql(sql, para); foreach (DataRow row in table.Rows) { int scale = Convert.ToInt32(row["DATA_SCALE"]); string data_type = row["DATA_TYPE"] + string.Empty; OracleColumn column = new OracleColumn { Name = row["COLUMN_NAME"] + string.Empty, Comment = row["COMMENTS"] + string.Empty, CsharpType = OracleUtils.TransformDatabaseType(data_type, scale), DbType = data_type, DefaultValue = (row["DATA_DEFAULT"] + string.Empty).Trim('\r', '\n'), IsNullable = (row["NULLABLE"] + string.Empty) == "Y", Length = Convert.ToInt32(row["DATA_LENGTH"]), Scale = scale, Table = oracleTable, IsNumeric = OracleUtils.IsNumeric(data_type) }; oracleTable.Columns.Add(column); } }
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); } }
public override DatabaseSchema GetDatabaseSchema() { DatabaseSchema db = new DatabaseSchema(); db.Tables = new List <ITableSchema>(); string sql = @"SELECT UO.OBJECT_NAME AS TABLE_NAME,UO.OBJECT_TYPE,UC.COMMENTS,UV.TEXT FROM USER_OBJECTS UO LEFT JOIN USER_TAB_COMMENTS UC ON UC.TABLE_NAME=UO.OBJECT_NAME LEFT JOIN USER_VIEWS UV ON UV.VIEW_NAME=UO.OBJECT_NAME WHERE UO.OBJECT_TYPE IN ('VIEW','TABLE') ORDER BY UO.OBJECT_NAME ASC"; DbHelper helper = new DbHelper(this._connectionString); var data = helper.ListBySql(sql, null); foreach (DataRow row in data.Rows) { OracleTableSchema table = new OracleTableSchema(); table.Name = row["TABLE_NAME"] + string.Empty; table.Comment = row["COMMENTS"] + string.Empty; db.Tables.Add(table); } return(db); }