public void AddTableDocumentation(DatabaseDefinition dd)
{
var reader = Executer.ExecuteQuery(@"
SELECT
SCHEMA_NAME(t.schema_id) as SchemaName,
t.name AS TableName,
p.value AS Property
FROM
sys.tables AS t
INNER JOIN sys.extended_properties AS p ON p.major_id = t.object_id AND p.minor_id = 0 AND p.class = 1
AND p.name = 'MS_Description'");
var tables = dd.GetTables();
foreach (var row in reader.Rows)
{
// TODO SchemaAndTableName.Schema might be null on default schema?
var table = tables.Find(t => t.SchemaAndTableName.Schema == row.GetAs <string>("SchemaName") && t.SchemaAndTableName.TableName == row.GetAs <string>("TableName"));
if (table != null)
{
var description = row.GetAs <string>("Property");
if (!string.IsNullOrEmpty(description))
{
description = description.Replace("\\n", "\n", StringComparison.OrdinalIgnoreCase).Trim();
var descriptionProperty = new SqlTableDescription(table, description);
table.Properties.Add(descriptionProperty);
}
}
}
}
public OracleIdentityReader12c(SqlStatementExecuter executer, SchemaNamesToRead schemaNames)
: base(executer, schemaNames)
{
var sqlStatement = GetStatement();
AddSchemaNamesFilter(ref sqlStatement, "owner");
_queryResult = Executer.ExecuteQuery(sqlStatement).Rows.ToList();
}
public OracleTableReader12c(SqlStatementExecuter executer, SchemaNamesToRead schemaNames)
: base(executer, schemaNames)
{
var sqlStatement = GetStatement();
AddSchemaNamesFilter(ref sqlStatement, "all_tab_columns.owner");
_queryResult = Executer.ExecuteQuery(sqlStatement).Rows.ToLookup(x => x.GetAs <string>("SCHEMAANDTABLENAME"));
}
public OraclePrimaryKeyReader12c(SqlStatementExecuter executer, SchemaNamesToRead schemaNames)
: base(executer, schemaNames)
{
var sqlStatement = GetKeySql();
AddSchemaNamesFilter(ref sqlStatement, "cons.owner");
sqlStatement += "\r\nORDER BY cols.table_name, cols.POSITION";
_queryResult = Executer.ExecuteQuery(sqlStatement).Rows.ToList();
}
public OracleForeignKeyReader12c(SqlStatementExecuter executer, SchemaNamesToRead schemaNames)
: base(executer, schemaNames)
{
var sqlStatement = GetStatement();
AddSchemaNamesFilter(ref sqlStatement, "cons.owner");
sqlStatement += "\r\nORDER BY owner, table_name, position";
_queryResult = Executer.ExecuteQuery(sqlStatement).Rows.ToList();
}
public override List <SchemaAndTableName> GetSchemaAndTableNames()
{
var sqlStatement = @"
SELECT ss.name schemaName, so.name tableName FROM sys.objects so
INNER JOIN sys.schemas ss ON ss.schema_id = so.schema_id
WHERE type = 'U'";
AddSchemaNamesFilter(ref sqlStatement, "ss.name");
return(Executer.ExecuteQuery(sqlStatement).Rows
.ConvertAll(row => new SchemaAndTableName(row.GetAs <string>("schemaName"), row.GetAs <string>("tableName")))
);
}
public List <SchemaAndTableName> GetSchemaAndTableNames()
{
var sqlStatement = @"
SELECT t.table_name tableName, t.owner schemaName
FROM dba_tables t, dba_users u
WHERE t.owner = u.username";
AddSchemaNamesFilter(ref sqlStatement, "t.owner");
return(Executer.ExecuteQuery(sqlStatement).Rows
.Select(row => new SchemaAndTableName(row.GetAs <string>("SCHEMANAME"), row.GetAs <string>("TABLENAME")))
.ToList());
}
public void AddTableDocumentation(SqlTable table)
{
var reader = Executer.ExecuteQuery(new SqlStatementWithParameters(
SqlGetTableDocumentation + " AND SCHEMA_NAME(t.schema_id) = @SchemaName AND t.name = @TableName", table.SchemaAndTableName.Schema, table.SchemaAndTableName.TableName));
foreach (var row in reader.Rows)
{
var description = row.GetAs <string>("Property");
if (!string.IsNullOrEmpty(description))
{
description = description.Replace("\\n", "\n", StringComparison.OrdinalIgnoreCase).Trim();
var descriptionProperty = new SqlTableDescription(table, description);
table.Properties.Add(descriptionProperty);
}
}
}
public List <Person> readAll()
{
List <Person> output = new List <Person>();
try
{
string query = "SELECT * FROM person";
Executer.ExecuteQuery(out output, query);
}
catch (Exception)
{
throw;
}
return(output);
}
public Person readByName(string name)
{
Person output = new Person();
try
{
string query = "SELECT * FROM person WHERE name=@Name";
MySqlParameter[] parameters = { new MySqlParameter("@Name", name) };
Executer.ExecuteQuery(out output, query, parameters);
}
catch (Exception)
{
throw;
}
return(output);
}
public Person read(int id)
{
Person output = new Person();
try
{
string query = "SELECT * FROM person WHERE ID=@Id";
MySqlParameter[] parameters = { new MySqlParameter("@Id", id) };
Executer.ExecuteQuery(out output, query, parameters);
}
catch (Exception)
{
throw;
}
return(output);
}
