public MetaTableColumnCollection GetTableColumns(string owner, string tableName)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
MetaTableColumnCollection columns = new MetaTableColumnCollection(owner, tableName);
string sql = "DESCRIBE " + tableName;
if (infoSchemaExists)
{
sql = "DESCRIBE " + owner + "." + tableName;
}
// TODO: use INFORMATION_SCHEMA.TABLES instead of DESCRIBE
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader();
int c = 0;
while (reader.Read())
{
// 0 = Column name
// 1 = Date Type (Length)
// 2 = NULL
string sDataType = reader.GetString(1);
MetaTableColumn column = new MetaTableColumn(
"",
tableName,
reader.GetString(0),
sDataType,
0,
0,
0,
reader.GetString(2).Equals("NULL") ? true : false,
c);
columns.Add(column);
c++;
}
reader.Close();
reader = null;
return(columns);
}
public MetaTableColumnCollection GetTableColumns(string owner, string tableName)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
MetaTableColumnCollection columns = new MetaTableColumnCollection(owner, tableName);
// TODO: get is nullable
string sql =
"select su.name as owner, so.name as table_name, sc.name as column_name, " +
" st.name as date_type, sc.length as column_length, " +
" sc.prec as data_preceision, sc.scale as data_scale, " +
" 0 as isnullable, sc.colid as column_id " +
"from dbo.syscolumns sc, dbo.sysobjects so, " +
" dbo.systypes st, dbo.sysusers su " +
"where sc.id = so.id " +
"and so.type in ('U','S') " +
"and so.name = '" + tableName + "' " +
"and sc.usertype = st.usertype " +
"and su.uid = so.uid " +
"order by sc.colid";
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
MetaTableColumn column = new MetaTableColumn(
reader.GetString(0),
reader.GetString(1),
reader.GetString(2),
reader.GetString(3),
GetInt(reader, 4),
GetInt(reader, 5),
GetInt(reader, 6),
GetInt(reader, 7) == 1 ? true : false,
GetInt(reader, 8));
columns.Add(column);
}
reader.Close();
reader = null;
return(columns);
}
public MetaTableColumn this[string name]
{
get
{
MetaTableColumn p = null;
foreach (object o in list)
{
p = (MetaTableColumn)o;
if (p.Name.Equals(name))
{
return(p);
}
}
throw new Exception("MetaTableColumn not found");
}
}
public MetaTableColumnCollection GetTableColumns(string owner, string tableName)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
MetaTableColumnCollection columns = new MetaTableColumnCollection(owner, tableName);
string sql =
"SELECT OWNER, TABLE_NAME, COLUMN_NAME, " +
"DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID " +
"FROM ALL_TAB_COLUMNS " +
"WHERE OWNER = '" + owner + "' " +
"AND TABLE_NAME = '" + tableName + "' " +
"ORDER BY COLUMN_ID";
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
MetaTableColumn column = new MetaTableColumn(
reader.GetString(0),
reader.GetString(1),
reader.GetString(2),
reader.GetString(3),
GetInt(reader, 4),
GetInt(reader, 5),
GetInt(reader, 6),
reader.GetString(7).Equals("N") ? false : true,
GetInt(reader, 8));
columns.Add(column);
}
reader.Close();
reader = null;
return(columns);
}
public MetaTableColumnCollection GetTableColumns(string owner, string tableName)
{
if(con.State != ConnectionState.Open)
con.Open();
MetaTableColumnCollection columns = new MetaTableColumnCollection (owner, tableName);
string sql =
"select su.name as owner, so.name as table_name, sc.name as column_name, " +
" st.name as date_type, sc.length as column_length, " +
" sc.xprec as data_preceision, sc.xscale as data_scale, " +
" sc.isnullable, sc.colid as column_id " +
"from dbo.syscolumns sc, dbo.sysobjects so, " +
" dbo.systypes st, dbo.sysusers su " +
"where sc.id = so.id " +
"and so.xtype in ('U','S') " +
"and so.name = '" + tableName + "' " +
"and sc.xusertype = st.xusertype " +
"and su.uid = so.uid " +
"order by sc.colid";
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
MetaTableColumn column = new MetaTableColumn(
reader.GetString(0),
reader.GetString(1),
reader.GetString(2),
reader.GetString(3),
GetInt(reader,4),
GetInt(reader,5),
GetInt(reader,6),
GetInt(reader,7) == 1 ? true : false,
GetInt(reader,8));
columns.Add(column);
}
reader.Close();
reader = null;
return columns;
}
public MetaTableColumnCollection GetTableColumns(string owner, string tableName)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
MetaTableColumnCollection columns = new MetaTableColumnCollection(owner, tableName);
string sql =
"select c.relname as table_name, a.attname as column_name, " +
" t.typname as date_type, a.attlen, a.atttypmod, a.attnotnull, a.attnum " +
"from pg_class c, pg_attribute a, pg_type t " +
"where c.relname = '" + tableName + "'" +
"and c.oid = a.attrelid " +
"and a.attname not in ('tableoid','cmax','xmax','cmin','xmin','oid','ctid') " +
"and t.oid = a.atttypid " +
"and a.attnum > 0 " +
"order by a.attnum";
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string sColumnName = reader.GetString(1);
string sDataType = reader.GetString(2);
int nLen = (int)reader.GetInt16(3);
int nMod = (int)reader.GetInt32(4);
bool bNullable = reader.GetBoolean(5);
int nColId = (int)reader.GetInt16(6);
int nSize = 0;
int nPrecision = 0;
int nScale = 0;
switch (sDataType)
{
case "int2":
case "int4":
case "int8":
nSize = nLen;
break;
case "char":
case "varchar":
case "bpchar":
nSize = nMod - 4;
break;
case "numeric":
nPrecision = nMod / 65536;
nScale = (nMod % 65536) - 4;
break;
case "text":
break;
default:
nSize = nLen;
break;
}
MetaTableColumn column = new MetaTableColumn(
"",
tableName,
sColumnName,
sDataType,
nSize,
nPrecision,
nScale,
bNullable,
nColId);
columns.Add(column);
}
reader.Close();
reader = null;
return(columns);
}
public MetaTableColumnCollection GetTableColumns(string owner, string tableName)
{
if(con.State != ConnectionState.Open)
con.Open();
MetaTableColumnCollection columns = new MetaTableColumnCollection (owner,tableName);
string sql =
"SELECT OWNER, TABLE_NAME, COLUMN_NAME, " +
"DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID " +
"FROM ALL_TAB_COLUMNS " +
"WHERE OWNER = '" + owner + "' " +
"AND TABLE_NAME = '" + tableName + "' " +
"ORDER BY COLUMN_ID";
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
MetaTableColumn column = new MetaTableColumn(
reader.GetString(0),
reader.GetString(1),
reader.GetString(2),
reader.GetString(3),
GetInt(reader,4),
GetInt(reader,5),
GetInt(reader,6),
reader.GetString(7).Equals("N") ? false : true,
GetInt(reader,8));
columns.Add(column);
}
reader.Close();
reader = null;
return columns;
}
public MetaTableColumnCollection GetTableColumns(string owner, string tableName)
{
if(con.State != ConnectionState.Open)
con.Open();
MetaTableColumnCollection columns = new MetaTableColumnCollection (owner, tableName);
string sql =
"select c.relname as table_name, a.attname as column_name, " +
" t.typname as date_type, a.attlen, a.atttypmod, a.attnotnull, a.attnum " +
"from pg_class c, pg_attribute a, pg_type t " +
"where c.relname = '" + tableName + "'" +
"and c.oid = a.attrelid " +
"and a.attname not in ('tableoid','cmax','xmax','cmin','xmin','oid','ctid') " +
"and t.oid = a.atttypid " +
"and a.attnum > 0 " +
"order by a.attnum";
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {
string sColumnName = reader.GetString(1);
string sDataType = reader.GetString(2);
int nLen = (int) reader.GetInt16(3);
int nMod = (int) reader.GetInt32(4);
bool bNullable = reader.GetBoolean(5);
int nColId = (int) reader.GetInt16(6);
int nSize = 0;
int nPrecision = 0;
int nScale = 0;
switch(sDataType)
{
case "int2":
case "int4":
case "int8":
nSize = nLen;
break;
case "char":
case "varchar":
case "bpchar":
nSize = nMod - 4;
break;
case "numeric":
nPrecision = nMod / 65536;
nScale = (nMod % 65536) - 4;
break;
case "text":
break;
default:
nSize = nLen;
break;
}
MetaTableColumn column = new MetaTableColumn(
"",
tableName,
sColumnName,
sDataType,
nSize,
nPrecision,
nScale,
bNullable,
nColId);
columns.Add(column);
}
reader.Close();
reader = null;
return columns;
}
public MetaTableColumnCollection GetTableColumns(string owner, string tableName)
{
if(con.State != ConnectionState.Open)
con.Open();
MetaTableColumnCollection columns = new MetaTableColumnCollection (owner, tableName);
DataTable table2 = GetSchema ("Columns", new string[] {null, null, tableName, null});
for (int r = 0; r < table2.Rows.Count; r++)
{
DataRow row2 = table2.Rows[r];
string columnName = row2["COLUMN_NAME"].ToString();
string dataType = row2["COLUMN_DATA_TYPE"].ToString();
int columnSize = 0;
if (row2["COLUMN_SIZE"] != DBNull.Value)
columnSize = (int) row2["COLUMN_SIZE"];
int precision = 0;
if (row2["NUMERIC_PRECISION"] != DBNull.Value)
precision = (int) row2["NUMERIC_PRECISION"];
int scale = 0;
if (row2["NUMERIC_SCALE"] != DBNull.Value)
scale = (int) row2["NUMERIC_SCALE"];
bool isNullable = false; // FIXME: is nullable
//short n = 0;
//if (row2["IS_NULLABLE"] != DBNull.Value)
// n = (short) row2["IS_NULLABLE"];
//
//if (n == 1)
// isNullable = true;
int pos = 0; // FIXME: ordinal position
//if (row2["ORDINAL_POSITION"] != DBNull.Value)
// pos = (int) row2["ORDINAL_POSITION"];
MetaTableColumn column = new MetaTableColumn(
"",
tableName,
columnName,
dataType,
columnSize,
precision,
scale,
isNullable,
pos);
columns.Add(column);
}
return columns;
}
public MetaTableColumnCollection GetTableColumns(string owner, string tableName)
{
if(con.State != ConnectionState.Open)
con.Open();
MetaTableColumnCollection columns = new MetaTableColumnCollection (owner, tableName);
string sql = "DESCRIBE " + tableName;
if (infoSchemaExists)
sql = "DESCRIBE " + owner + "." + tableName;
// TODO: use INFORMATION_SCHEMA.TABLES instead of DESCRIBE
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader();
int c = 0;
while(reader.Read())
{
// 0 = Column name
// 1 = Date Type (Length)
// 2 = NULL
string sDataType = reader.GetString(1);
MetaTableColumn column = new MetaTableColumn(
"",
tableName,
reader.GetString(0),
sDataType,
0,
0,
0,
reader.GetString(2).Equals("NULL") ? true : false,
c);
columns.Add(column);
c ++;
}
reader.Close();
reader = null;
return columns;
}
public MetaTableColumnCollection GetTableColumns(string owner, string tableName)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
MetaTableColumnCollection columns = new MetaTableColumnCollection(owner, tableName);
DataTable table2 = GetSchema("Columns", new string[] { null, null, tableName, null });
for (int r = 0; r < table2.Rows.Count; r++)
{
DataRow row2 = table2.Rows[r];
string columnName = row2["COLUMN_NAME"].ToString();
string dataType = row2["COLUMN_DATA_TYPE"].ToString();
int columnSize = 0;
if (row2["COLUMN_SIZE"] != DBNull.Value)
{
columnSize = (int)row2["COLUMN_SIZE"];
}
int precision = 0;
if (row2["NUMERIC_PRECISION"] != DBNull.Value)
{
precision = (int)row2["NUMERIC_PRECISION"];
}
int scale = 0;
if (row2["NUMERIC_SCALE"] != DBNull.Value)
{
scale = (int)row2["NUMERIC_SCALE"];
}
bool isNullable = false; // FIXME: is nullable
//short n = 0;
//if (row2["IS_NULLABLE"] != DBNull.Value)
// n = (short) row2["IS_NULLABLE"];
//
//if (n == 1)
// isNullable = true;
int pos = 0; // FIXME: ordinal position
//if (row2["ORDINAL_POSITION"] != DBNull.Value)
// pos = (int) row2["ORDINAL_POSITION"];
MetaTableColumn column = new MetaTableColumn(
"",
tableName,
columnName,
dataType,
columnSize,
precision,
scale,
isNullable,
pos);
columns.Add(column);
}
return(columns);
}
