private void FillReferencedTables()
{
string strSql =
"SELECT OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_OWNER,R_CONSTRAINT_NAME,DELETE_RULE\n" +
" FROM SYS.ALL_CONSTRAINTS\n" +
" WHERE TABLE_NAME = :TABLE_NAME\n" +
" AND OWNER = :OWNER\n" +
" AND CONSTRAINT_TYPE IN ('R')\n" +
" ORDER BY CONSTRAINT_TYPE,CONSTRAINT_NAME";
var param = new Devart.Data.Oracle.OracleParameter("TABLE_NAME", this.Name);
var param2 = new Devart.Data.Oracle.OracleParameter("OWNER", this.Owner.Name);
DataTable dt = this.Owner.OwnerUser.FillDataTable(strSql, new DbParameter[]{
param,param2
});
foreach (DataRow item in dt.Rows)
{
string constName = item["R_CONSTRAINT_NAME"].ToString();
string constTable = FindConstTableName(constName);
if (!_referencedTables.Contains(constTable))
_referencedTables.Add(constTable);
}
}
private void FillColumns()
{
string strSelectCols = "SELECT T.TABLE_NAME,T.COLUMN_NAME,T.DATA_TYPE,T.DATA_LENGTH,T.NULLABLE,T.COLUMN_ID,T.QUALIFIED_COL_NAME,T.DATA_DEFAULT,X.COMMENTS FROM DBA_TAB_COLS T\n" +
" LEFT JOIN USER_COL_COMMENTS X ON X.table_name = T.TABLE_NAME AND X.column_name = T.COLUMN_NAME \n" +
" WHERE T.TABLE_NAME = :TABLE_NAME AND T.OWNER = :OWNER AND SUBSTR(T.COLUMN_NAME,0,4) != 'SYS_' ORDER BY COLUMN_ID ASC";
var param = new Devart.Data.Oracle.OracleParameter("TABLE_NAME", this.Name);
var param2 = new Devart.Data.Oracle.OracleParameter("OWNER", this.Owner.Name);
DataTable dt = this.Owner.OwnerUser.FillDataTable(strSelectCols, new DbParameter[]{
param,param2
});
foreach (DataRow dr in dt.Rows)
{
Column col = new Column(this);
col.Name = dr[1].ToString();
col.ValueLength = int.Parse(dr["DATA_LENGTH"].ToString());
col.DataType = StringToColType(dr["DATA_TYPE"].ToString());
col.Nullable = (dr["NULLABLE"].ToString() == "Y") ? true : false;
col.Default = dr["DATA_DEFAULT"].ToString();
col.ColumnId = int.Parse(dr["COLUMN_ID"].ToString());
//if (dr["COMMENTS"].ToString().Length > 0)
//{
// ColumnComment comment = new ColumnComment();
// comment.Column = col;
// comment.Comment = dr["COMMENTS"].ToString();
// col.Comment = comment;
//}
this.Columns.Add(col);
}
}
internal System.Data.IDataParameter AddParam(string ParamName, Devart.Data.Oracle.OracleDbType OracleDbType)
{
System.Data.IDbDataParameter param = new Devart.Data.Oracle.OracleParameter(ParamName, OracleDbType);
this.AddParam(param);
return param;
}
public override void OnBuildObject()
{
string strSelectView = "SELECT VIEW_NAME,TEXT,TEXT_LENGTH FROM USER_VIEWS where VIEW_NAME =:VIEW_NAME";
var param = new Devart.Data.Oracle.OracleParameter("VIEW_NAME", this.Name);
DataTable dt = this.Owner.OwnerUser.FillDataTable(strSelectView, new DbParameter[] {
param
});
this.SqlStatment = dt.Rows[0]["TEXT"].ToString();
this.TextLength = int.Parse(dt.Rows[0]["TEXT_LENGTH"].ToString());
}
private string FindConstTableName(string constName)
{
string sqlString = "SELECT TABLE_NAME\n" +
" FROM SYS.ALL_CONSTRAINTS\n" +
" WHERE CONSTRAINT_NAME = :CONST_NAME \n" +
" AND OWNER = :OWNER \n" +
" AND CONSTRAINT_TYPE IN ('P', 'U')";
var param = new Devart.Data.Oracle.OracleParameter("CONST_NAME", constName);
var param2 = new Devart.Data.Oracle.OracleParameter("OWNER", this.Owner.Name);
DataTable dt = this.Owner.OwnerUser.FillDataTable(sqlString, new DbParameter[]{
param,param2
});
if (dt.Rows.Count > 0)
return dt.Rows[0][0].ToString();
return "";
}
private void FillKeys()
{
string strSql =
"SELECT I.OWNER,\n" +
" I.CONSTRAINT_NAME,\n" +
" I.CONSTRAINT_TYPE,\n" +
" I.TABLE_NAME,\n" +
" I.DELETE_RULE,\n" +
" I.STATUS,\n" +
" U.COLUMN_NAME,\n" +
" U2.TABLE_NAME AS REFTABLE,U2.COLUMN_NAME AS REFCOL\n" +
" FROM USER_CONSTRAINTS I\n" +
" INNER JOIN USER_CONS_COLUMNS U ON U.CONSTRAINT_NAME = I.CONSTRAINT_NAME\n" +
" AND U.OWNER = I.OWNER\n" +
" LEFT JOIN USER_CONS_COLUMNS U2 ON U2.constraint_name = I.R_CONSTRAINT_NAME\n" +
" WHERE I.TABLE_NAME = :TABLE_NAME \n" +
" AND I.CONSTRAINT_TYPE IN ('P', 'R', 'U')";
var param = new Devart.Data.Oracle.OracleParameter("TABLE_NAME", this.Name);
DataTable dt = this.Owner.OwnerUser.FillDataTable(strSql, new DbParameter[]{
param
});
foreach (DataRow dr in dt.Rows)
{
Key key = new Key();
var item = Keys.Where(p => p.Name == dr["CONSTRAINT_NAME"].ToString()).FirstOrDefault();
if (item != null)
{
key = item;
Column col = new Column();
col.Name = dr["COLUMN_NAME"].ToString();
key.Columns.Add(col);
}
else
{
key.Name = dr["CONSTRAINT_NAME"].ToString();
key.OwnerTable = this;
key.Owner = this.Owner;
key.KeyType = StringToKeyTypes(dr["CONSTRAINT_TYPE"].ToString());
key.OnDeleteAction = OnDeleteActionEnums.NoAction;
key.Enabled = (dr["STATUS"].ToString() == "DISABLED") ? false : true;
if (key.KeyType == KeyTypesEnum.Foreign)
{
key.ReferencingTable = dr["REFTABLE"].ToString();
key.ReferencingColumns = dr["REFCOL"].ToString();
}
Column col = new Column();
col.Name = dr["COLUMN_NAME"].ToString();
key.Columns.Add(col);
this.Keys.Add(key);
}
}
}
