protected override IEnumerable <MyColumn> GetPrimayColumns(MyTable table)
{
using (OracleConnection conn = new OracleConnection(GetConnectString()))
{
var cmd = conn.CreateCommand();
cmd.CommandText = string.Format("SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME = '{0}' AND CONSTRAINT_TYPE ='P' AND OWNER = '{1}'", table.Name.Split('.').Last(), table.Name.Split('.').First());
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.Read())
{
var constraintName = reader[0].ToString();
var secondCmd = conn.CreateCommand();
secondCmd.CommandText =
string.Format("SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS WHERE CONSTRAINT_NAME = '{0}'",
constraintName);
var secondReader = secondCmd.ExecuteReader();
while (secondReader.Read())
{
yield return(table.Columns.SingleOrDefault(o => o.Name == secondReader[0].ToString()));
}
}
}
}
protected abstract IEnumerable <MyColumn> GetPrimayColumns(MyTable table);
protected abstract void FillTableDescription(MyTable table);
protected override void FillTableDescription(MyTable table)
{
using (OracleConnection conn = new OracleConnection(GetConnectString()))
{
var cmd = conn.CreateCommand();
var isGeometry = table.Name.EndsWith("_SDOGEOM");
var user = table.User;//isGeometry ? "CADGIS" : "GZDLGIS";
cmd.CommandText = string.Format("SELECT COLUMN_NAME,DATA_TYPE,OWNER FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='{0}' AND OWNER ='{1}'", table.Name, user);
table.Name = string.Format("{0}.{1}", user, table.Name);
conn.Open();
var reader = cmd.ExecuteReader();
var columns = new List <string>();
while (reader.Read())
{
var columnName = reader["COLUMN_NAME"].ToString();
if (reader["DATA_TYPE"].ToString() == "SDO_GEOMETRY")
{
table.Columns.Add(new MyColumn {
Name = columnName, TypeName = GetTypeName("SDO_GEOMETRY"), Table = table
});
}
else
{
columns.Add(reader["COLUMN_NAME"].ToString());
}
}
//"_SDOGEOM"
string sql = string.Format("SELECT {0} FROM {1}", string.Join(",", columns.ToArray()), table.Name);
string updateViewSql = string.Format("select COLUMN_NAME from all_tab_columns where table_name ='{0}'", table.UpdateView);
//conn.Open();
var testCmd = conn.CreateCommand();
testCmd.CommandText = sql;
var testReader = testCmd.ExecuteReader();
var testUpdateViewCmd = conn.CreateCommand();
testUpdateViewCmd.CommandText = updateViewSql;
var testUpdateViewReader = testUpdateViewCmd.ExecuteReader();
var schemaTable = testReader.GetSchemaTable();
DataTable updateViewSchemaTable = null;
if (testUpdateViewReader != null)
{
updateViewSchemaTable = GetUpdateViewColumns(testUpdateViewReader);
}
IList <MyColumn> selectOnlyColumns = updateViewSchemaTable != null
? GetSelectOnlyColumns(schemaTable
, updateViewSchemaTable)
: new List <MyColumn>();
if (schemaTable != null)
{
foreach (DataRow row in schemaTable.Rows)
{
var dataType = row["DataType"] as Type;
var isNull = ((bool)row["AllowDBNull"]) && dataType != typeof(string);
var column = new MyColumn
{
Name = row["ColumnName"].ToString(),
TypeName = dataType.ToString(),
IsNullable = isNull,
Table = table,
IsKey = !row.IsNull("IsKey") && (bool)row["IsKey"]
};
if (row.IsNull("NumericPrecision"))
{
column.NumericPrecision = null;
}
else
{
column.NumericPrecision = Math.Min((short)row["NumericPrecision"], (short)29);
}
column.IsSelectOnly = IsSelectOnly(column, selectOnlyColumns);
table.Columns.Add(column);
}
}
/*
* COLUMN_NAME
* DATA_TYPE
* DATA_LENGTH
* NULLABLE
*/
//while (reader.Read())
//{
// var dataType = reader["DATA_TYPE"].ToString();
// decimal? scale = reader["DATA_SCALE"] as decimal?;
// string typename = (dataType == "NUMBER" && scale.HasValue && (int)(decimal)reader["DATA_SCALE"] > 0) ? "double":GetTypeName(dataType);
// MyColumn column = new MyColumn
// {
// Name = reader["COLUMN_NAME"].ToString(),
// TypeName = typename,
// IsNullable = reader["NULLABLE"].ToString() == "Y" && typename != "Geometry"
// };
// table.Columns.Add(column);
//}
}
table.PrimaryKey.AddRange(GetPrimayColumns(table));
table.Columns.Sort((o1, o2) => o1.Name.CompareTo(o2.Name));
}
