/// <summary>
/// Get a collection of views from the system.
/// </summary>
public override ViewSchema[] GetViews()
{
ArrayList collection = new ArrayList();
SybaseCommand command = new SybaseCommand();
command.Connection = connection;
command.CommandText =
"SELECT su.name AS owner, so.name as table_name, so.id as table_id, " +
" so.crdate as created_date, so.type as table_type " +
"FROM dbo.sysobjects so, dbo.sysusers su " +
"WHERE type = 'V' " +
"AND su.uid = so.uid " +
"ORDER BY 1, 2";
SybaseDataReader r = command.ExecuteReader();
while (r.Read()) {
ViewSchema view = new ViewSchema();
view.Provider = this;
try {
view.Name = r.GetString(1);
view.SchemaName = r.GetString(0);
view.OwnerName = r.GetString(0);
StringBuilder sb = new StringBuilder();
sb.AppendFormat ("-- View: {0}\n", view.Name);
sb.AppendFormat ("-- DROP VIEW {0};\n\n", view.Name);
string source = GetSource(view.Owner + "." + view.Name);
sb.AppendFormat (" {0}\n);", source);
view.Definition = sb.ToString ();
//view.Comment = r.GetString(5);
} catch (Exception e) {
}
collection.Add(view);
}
r.Close ();
r = null;
command.Dispose();
command = null;
return (ViewSchema[]) collection.ToArray (typeof (ViewSchema));
}
/// <summary>
/// Get a list of tables in the system.
/// </summary>
public override TableSchema[] GetTables()
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList ();
SybaseCommand command = new SybaseCommand();
command.Connection = connection;
command.CommandText =
"SELECT su.name AS owner, so.name as table_name, so.id as table_id, " +
" so.crdate as created_date, so.type as table_type " +
"FROM dbo.sysobjects so, dbo.sysusers su " +
"WHERE type IN ('S','U') " +
"AND su.uid = so.uid " +
"ORDER BY 1, 2";
SybaseDataReader r = command.ExecuteReader();
while (r.Read()) {
TableSchema table = new TableSchema();
table.Provider = this;
table.Name = r.GetString(1);
table.IsSystemTable = r.GetString(4) == "S" ? true : false;
table.SchemaName = r.GetString(0);
table.OwnerName = r.GetString(0);
table.Comment = "";
StringBuilder sb = new StringBuilder();
sb.AppendFormat ("-- Table: {0}\n", table.Name);
sb.AppendFormat ("-- DROP TABLE {0};\n\n", table.Name);
sb.AppendFormat ("CREATE TABLE {0} (\n", table.Name);
ColumnSchema[] columns = table.Columns;
string[] parts = new string[columns.Length];
for (int i = 0; i < parts.Length; i++) {
parts[i] = "\t" + columns[i].Definition;
}
sb.Append (String.Join (",\n", parts));
ConstraintSchema[] cons = table.Constraints;
parts = new string[cons.Length];
if (cons.Length > 0)
sb.Append (",\n");
for (int i = 0; i < parts.Length; i++) {
parts[i] = "\t" + cons[i].Definition;
}
sb.Append (String.Join (",\n", parts));
sb.Append ("\n);\n");
//sb.AppendFormat ("COMMENT ON TABLE {0} IS '{1}';", table.Name, table.Comment);
table.Definition = sb.ToString();
collection.Add (table);
}
r.Close ();
r = null;
command.Dispose ();
command = null;
return (TableSchema[]) collection.ToArray (typeof (TableSchema));
}
