public DataTable GetTableColumns(string databaseName, string tableName)
{
TraceContext("GetTableColumns: tab=" + tableName);
DataTable metaData = new DataTable();
try
{
metaData = context.CreateColumnsDataTable();
CreateColumns(tableName, metaData, null);
}
finally
{
}
metaData.TableName = "GetTableColumns";
TraceTable("GetTableColumns-" + databaseName, metaData);
return(metaData);
}
DataTable IMyMetaPlugin.GetViewColumns(string database, string view)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateColumnsDataTable();
SqlCeConnection conn = new SqlCeConnection();
conn.ConnectionString = context.ConnectionString;
SqlCeCommand cmd = new SqlCeCommand();
cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='" +
view + "'";
cmd.Connection = conn;
DataTable dt = new DataTable();
SqlCeDataAdapter adapter = new SqlCeDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dt);
foreach (DataRow r in dt.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_CATALOG"] = r["TABLE_CATALOG"];
row["TABLE_SCHEMA"] = r["TABLE_SCHEMA"];
row["TABLE_NAME"] = r["TABLE_NAME"];
row["COLUMN_NAME"] = r["COLUMN_NAME"];
row["ORDINAL_POSITION"] = r["ORDINAL_POSITION"];
row["DESCRIPTION"] = r["DESCRIPTION"];
if (r["IS_NULLABLE"] != DBNull.Value)
{
string isNullable = (string)r["IS_NULLABLE"];
isNullable = isNullable.ToUpper();
row["IS_NULLABLE"] = (isNullable == "NO") ? false : true;
}
if ((bool)r["COLUMN_HASDEFAULT"])
{
row["COLUMN_HASDEFAULT"] = true;
row["COLUMN_DEFAULT"] = r["COLUMN_DEFAULT"];
}
if (r["AUTOINC_INCREMENT"] != DBNull.Value)
{
row["IS_AUTO_KEY"] = true;
row["AUTO_KEY_SEED"] = Convert.ToInt32(r["AUTOINC_SEED"]);
row["AUTO_KEY_INCREMENT"] = Convert.ToInt32(r["AUTOINC_INCREMENT"]);
}
string type = (string)r["DATA_TYPE"];
int charMax = 0;
short precision = 0;
short scale = 0;
if (r["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
{
charMax = (int)r["CHARACTER_MAXIMUM_LENGTH"];
}
if (r["NUMERIC_PRECISION"] != DBNull.Value)
{
precision = (short)r["NUMERIC_PRECISION"];
}
if (r["NUMERIC_SCALE"] != DBNull.Value)
{
scale = (short)r["NUMERIC_SCALE"];
}
row["TYPE_NAME"] = type;
row["TYPE_NAME_COMPLETE"] = this.GetDataTypeNameComplete(type, charMax, precision, scale);
row["NUMERIC_PRECISION"] = precision;
row["NUMERIC_SCALE"] = scale;
row["CHARACTER_MAXIMUM_LENGTH"] = charMax;
row["IS_COMPUTED"] = (type == "timestamp") ? true : false;
}
}
finally
{
}
return(metaData);
}
DataTable IMyMetaPlugin.GetViewColumns(string database, string view)
{
DataTable metaData = new DataTable();
//IVistaDBDatabase db = null;
try
{
metaData = context.CreateColumnsDataTable();
using (VistaDBConnection conn = new VistaDBConnection())
{
conn.ConnectionString = context.ConnectionString;
conn.Open();
string sql = "SELECT * FROM GetViewColumns('" + view + "')";
using (VistaDBCommand cmd = new VistaDBCommand(sql, conn))
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter(cmd))
{
DataTable views = new DataTable();
da.Fill(views);
foreach (DataRow vistaRow in views.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
int width = Convert.ToInt32(vistaRow["COLUMN_SIZE"]);
int dec = 0;
int length = 0;
int octLength = width;
bool timestamp = false;
string type = vistaRow["DATA_TYPE_NAME"] as string;
switch (type)
{
case "Char":
case "NChar":
case "NText":
case "NVarchar":
case "Text":
case "Varchar":
length = width;
width = 0;
dec = 0;
break;
case "Currency":
case "Double":
case "Decimal":
case "Single":
break;
case "Timestamp":
timestamp = true;
break;
default:
width = 0;
dec = 0;
break;
}
string def = Convert.ToString(vistaRow["DEFAULT_VALUE"]);
row["TABLE_NAME"] = view;
row["COLUMN_NAME"] = vistaRow["COLUMN_NAME"];
row["ORDINAL_POSITION"] = vistaRow["COLUMN_ORDINAL"];
row["IS_NULLABLE"] = vistaRow["ALLOW_NULL"];
row["COLUMN_HASDEFAULT"] = def == string.Empty ? false : true;
row["COLUMN_DEFAULT"] = def;
row["IS_AUTO_KEY"] = vistaRow["IDENTITY_VALUE"];
row["AUTO_KEY_SEED"] = vistaRow["IDENTITY_SEED"];
row["AUTO_KEY_INCREMENT"] = vistaRow["IDENTITY_STEP"];
row["TYPE_NAME"] = type;
row["NUMERIC_PRECISION"] = width;
row["NUMERIC_SCALE"] = dec;
row["CHARACTER_MAXIMUM_LENGTH"] = length;
row["CHARACTER_OCTET_LENGTH"] = octLength;
row["DESCRIPTION"] = vistaRow["COLUMN_DESCRIPTION"];
if (timestamp)
{
row["IS_COMPUTED"] = true;
}
}
}
}
}
}
catch {}
return(metaData);
}
public DataTable GetViewColumns(string database, string view)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateColumnsDataTable();
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS WHERE TABLE_NAME='" + view + "' and TABLE_CAT='" + database + "'";
cmd.Connection = conn;
using (EfzDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_CATALOG"] = r["TABLE_CAT"];
row["TABLE_SCHEMA"] = r["TABLE_SCHEM"];
row["TABLE_NAME"] = r["TABLE_NAME"];
row["COLUMN_NAME"] = r["COLUMN_NAME"];
row["ORDINAL_POSITION"] = r["ORDINAL_POSITION"];
row["DESCRIPTION"] = r["REMARKS"];
row["COLUMN_HASDEFAULT"] = false;
if (r["IS_NULLABLE"] != DBNull.Value)
{
row["IS_NULLABLE"] = r["IS_NULLABLE"];
}
if (r["COLUMN_DEF"] != DBNull.Value)
{
row["COLUMN_HASDEFAULT"] = true;
row["COLUMN_DEFAULT"] = r["COLUMN_DEF"];
}
if (r["IS_GENERATED"] != DBNull.Value && r["IDENTITY_INCREMENT"] != DBNull.Value)
{
row["IS_AUTO_KEY"] = true;
row["AUTO_KEY_SEED"] = Convert.ToInt32(r["IDENTITY_START"]);
row["AUTO_KEY_INCREMENT"] = Convert.ToInt32(r["IDENTITY_INCREMENT"]);
}
int type = Convert.ToInt32(r["DATA_TYPE"]); // dbType enum code
string typeName = (string)r["TYPE_NAME"]; // dbType enum code
int charMax = 0;
int precision = 0;
int scale = 0;
if (r["COLUMN_SIZE"] != DBNull.Value)
{
charMax = Convert.ToInt32(r["COLUMN_SIZE"]);
}
if (r["COLUMN_SIZE"] != DBNull.Value)
{
precision = Convert.ToInt32(r["COLUMN_SIZE"]);
}
if (r["DECIMAL_DIGITS"] != DBNull.Value)
{
scale = Convert.ToInt32(r["DECIMAL_DIGITS"]);
}
row["DATA_TYPE"] = type;
row["TYPE_NAME"] = typeName;
row["TYPE_NAME_COMPLETE"] = this.GetDataTypeNameComplete(typeName, charMax, precision, scale);
row["NUMERIC_PRECISION"] = precision;
row["NUMERIC_SCALE"] = scale;
row["CHARACTER_MAXIMUM_LENGTH"] = charMax;
//TODO: we will have to find the best way to implement this later?
//row["IS_COMPUTED"] = (type == "timestamp") ? true : false;
}
}
}
finally { }
return(metaData);
}
public DataTable GetTableColumns(string database, string table)
{
using (OdbcConnection c = this.NewConnection as OdbcConnection)
{
DataTable columns = context.CreateColumnsDataTable();
DataTable columnData = new DataTable();
// Build the SQL statement
StringBuilder sb = new StringBuilder("select sys.syscolumn.*, sys.sysdomain.* from sys.systable ");
sb.Append(" join sys.syscolumn on sys.systable.table_id=sys.syscolumn.table_id ");
sb.Append(" join sys.sysdomain on sys.syscolumn.domain_id=sys.sysdomain.domain_id where sys.systable.table_name='");
sb.Append(table);
sb.Append("' order by sys.syscolumn.column_id");
OdbcCommand sql = new OdbcCommand();
sql.CommandText = sb.ToString();
sql.CommandType = CommandType.Text;
sql.Connection = c;
OdbcDataAdapter adapter = new OdbcDataAdapter(sql);
c.Open();
adapter.Fill(columnData);
foreach (DataRow columnDataRow in columnData.Rows)
{
DataRow row = columns.NewRow();
row["TABLE_NAME"] = table;
row["COLUMN_NAME"] = columnDataRow["column_name"];
row["ORDINAL_POSITION"] = columnDataRow["column_id"];
string columnDefault = columnDataRow["default"].ToString();
row["COLUMN_DEFAULT"] = columnDefault;
row["COLUMN_HASDEFAULT"] = (columnDefault != string.Empty) ? true : false;
row["IS_NULLABLE"] = (columnDataRow["nulls"].ToString() == "Y") ? true : false;
row["CHARACTER_MAXIMUM_LENGTH"] = columnDataRow["width"];
row["CHARACTER_OCTET_LENGTH"] = columnDataRow["width"];
row["NUMERIC_PRECISION"] = columnDataRow["precision"];
row["NUMERIC_SCALE"] = columnDataRow["scale"];
row["DATA_TYPE"] = columnDataRow["domain_id"];
row["TYPE_NAME"] = columnDataRow["domain_name"];
row["TYPE_NAME_COMPLETE"] = columnDataRow["domain_name"];
columns.Rows.Add(row);
}
//metaData.Columns.Add("TABLE_CATALOG", Type.GetType("System.String"));
//metaData.Columns.Add("TABLE_SCHEMA", Type.GetType("System.String"));
//metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_GUID", Type.GetType("System.Guid"));
//metaData.Columns.Add("COLUMN_PROPID", Type.GetType("System.Int64"));
//metaData.Columns.Add("ORDINAL_POSITION", Type.GetType("System.Int64"));
//metaData.Columns.Add("COLUMN_HASDEFAULT", Type.GetType("System.Boolean"));
//metaData.Columns.Add("COLUMN_DEFAULT", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_FLAGS", Type.GetType("System.Int64"));
//metaData.Columns.Add("IS_NULLABLE", Type.GetType("System.Boolean"));
//metaData.Columns.Add("DATA_TYPE", Type.GetType("System.Int32"));
//metaData.Columns.Add("TYPE_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("TYPE_NAME_COMPLETE", Type.GetType("System.String"));
//metaData.Columns.Add("TYPE_GUID", Type.GetType("System.Guid"));
//metaData.Columns.Add("CHARACTER_MAXIMUM_LENGTH", Type.GetType("System.Int64"));
//metaData.Columns.Add("CHARACTER_OCTET_LENGTH", Type.GetType("System.Int64"));
//metaData.Columns.Add("NUMERIC_PRECISION", Type.GetType("System.Int32"));
//metaData.Columns.Add("NUMERIC_SCALE", Type.GetType("System.Int16"));
//metaData.Columns.Add("DATETIME_PRECISION", Type.GetType("System.Int64"));
//metaData.Columns.Add("CHARACTER_SET_CATALOG", Type.GetType("System.String"));
//metaData.Columns.Add("CHARACTER_SET_SCHEMA", Type.GetType("System.String"));
//metaData.Columns.Add("CHARACTER_SET_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("COLLATION_CATALOG", Type.GetType("System.String"));
//metaData.Columns.Add("COLLATION_SCHEMA", Type.GetType("System.String"));
//metaData.Columns.Add("COLLATION_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("DOMAIN_CATALOG", Type.GetType("System.String"));
//metaData.Columns.Add("DOMAIN_SCHEMA", Type.GetType("System.String"));
//metaData.Columns.Add("DOMAIN_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("DESCRIPTION", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_LCID", Type.GetType("System.Int32"));
//metaData.Columns.Add("COLUMN_COMPFLAGS", Type.GetType("System.Int32"));
//metaData.Columns.Add("COLUMN_SORTID", Type.GetType("System.Int32"));
//metaData.Columns.Add("IS_COMPUTED", Type.GetType("System.Boolean"));
//metaData.Columns.Add("IS_AUTO_KEY", Type.GetType("System.Boolean"));
//metaData.Columns.Add("AUTO_KEY_SEED", Type.GetType("System.Int32"));
//metaData.Columns.Add("AUTO_KEY_INCREMENT", Type.GetType("System.Int32"));
c.Close();
columns.AcceptChanges();
return(columns);
}
}
private DataTable Mapper(IDataReader reader)
{
DataTable metaData = _context.CreateColumnsDataTable();
while (reader.Read())
{
DataRow row = metaData.NewRow();
//metaData.Columns.Add("TABLE_CATALOG", Type.GetType("System.String"));
//metaData.Columns.Add("TABLE_SCHEMA", Type.GetType("System.String"));
//metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_GUID", Type.GetType("System.Guid"));
//metaData.Columns.Add("COLUMN_PROPID", Type.GetType("System.Int64"));
//metaData.Columns.Add("ORDINAL_POSITION", Type.GetType("System.Int64"));
//metaData.Columns.Add("COLUMN_HASDEFAULT", Type.GetType("System.Boolean"));
//metaData.Columns.Add("COLUMN_DEFAULT", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_FLAGS", Type.GetType("System.Int64"));
//metaData.Columns.Add("IS_NULLABLE", Type.GetType("System.Boolean"));
//metaData.Columns.Add("DATA_TYPE", Type.GetType("System.Int32"));
//metaData.Columns.Add("TYPE_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("TYPE_NAME_COMPLETE", Type.GetType("System.String"));
//metaData.Columns.Add("TYPE_GUID", Type.GetType("System.Guid"));
//metaData.Columns.Add("CHARACTER_MAXIMUM_LENGTH", Type.GetType("System.Int64"));
//metaData.Columns.Add("CHARACTER_OCTET_LENGTH", Type.GetType("System.Int64"));
//metaData.Columns.Add("NUMERIC_PRECISION", Type.GetType("System.Int32"));
//metaData.Columns.Add("NUMERIC_SCALE", Type.GetType("System.Int16"));
//metaData.Columns.Add("DATETIME_PRECISION", Type.GetType("System.Int64"));
//metaData.Columns.Add("CHARACTER_SET_CATALOG", Type.GetType("System.String"));
//metaData.Columns.Add("CHARACTER_SET_SCHEMA", Type.GetType("System.String"));
//metaData.Columns.Add("CHARACTER_SET_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("COLLATION_CATALOG", Type.GetType("System.String"));
//metaData.Columns.Add("COLLATION_SCHEMA", Type.GetType("System.String"));
//metaData.Columns.Add("COLLATION_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("DOMAIN_CATALOG", Type.GetType("System.String"));
//metaData.Columns.Add("DOMAIN_SCHEMA", Type.GetType("System.String"));
//metaData.Columns.Add("DOMAIN_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("DESCRIPTION", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_LCID", Type.GetType("System.Int32"));
//metaData.Columns.Add("COLUMN_COMPFLAGS", Type.GetType("System.Int32"));
//metaData.Columns.Add("COLUMN_SORTID", Type.GetType("System.Int32"));
//metaData.Columns.Add("IS_COMPUTED", Type.GetType("System.Boolean"));
//metaData.Columns.Add("IS_AUTO_KEY", Type.GetType("System.Boolean"));
//metaData.Columns.Add("AUTO_KEY_SEED", Type.GetType("System.Int32"));
//metaData.Columns.Add("AUTO_KEY_INCREMENT", Type.GetType("System.Int32"));
row["TABLE_CATALOG"] = _database;
row["TABLE_SCHEMA"] = reader.GetString(0);
row["TABLE_NAME"] = reader.GetString(1);
row["COLUMN_NAME"] = reader.GetString(2).Trim();
row["ORDINAL_POSITION"] = reader.GetInt32(3);
row["COLUMN_HASDEFAULT"] = (reader.GetChar(4) == 'Y');
if (!reader.IsDBNull(5))
{
row["COLUMN_DEFAULT"] = reader.GetString(5);
}
row["IS_NULLABLE"] = (reader.GetChar(6) == 'Y');
row["TYPE_NAME"] = reader.GetString(7).Trim();
row["TYPE_NAME_COMPLETE"] = reader.GetString(7).Trim();
row["CHARACTER_MAXIMUM_LENGTH"] = reader.GetInt32(8);
row["NUMERIC_SCALE"] = reader.GetInt32(9);
row["IS_COMPUTED"] = false;
row["IS_AUTO_KEY"] = false;
metaData.Rows.Add(row);
}
metaData.AcceptChanges();
return(metaData);
}
