public List <EstacionamentoModel> BuscarVeiculosEstacionados()
{
string sql = @"SELECT NUMERO_REGISTRO, MODELO_VEICULO, PLACA_VEICULO, DATA_ENTRADA,
DATA_SAIDA, OBSERVACAO_VEICULO FROM KARSTEN.TST001_CONTROLE_ENTRADA_VEICULO";
return(conexao.Query <EstacionamentoModel>(sql).ToList());
}
protected IList <QuerySourceCategory> GetSchemas(DB2Connection conn, string excludeSchemas)
{
string sql = string.Format(@"SELECT SCHEMANAME
FROM SYSCAT.SCHEMATA
WHERE SCHEMANAME NOT IN({0});", excludeSchemas);
var querySourceCategories = conn.Query <dynamic>(sql)
.Select(s => new QuerySourceCategory {
Name = s.SCHEMANAME
})
.ToList();
return(querySourceCategories);
//var tables = conn.GetSchema(DB2MetaDataCollectionNames.Schemas);
//var result = new List<QuerySourceCategory>();
//foreach (var row in tables.Rows.OfType<DataRow>())
//{
// var name = row["TABLE_SCHEMA"].ToString();
// if (!excludeSchemas.Contains(name))
// {
// result.Add(
// new QuerySourceCategory
// {
// Name = name
// });
// }
//}
//return result;
}
private IEnumerable <QuerySource> GetFunctions(DB2Connection conn, string schemaName)
{
string sql = string.Format(@"SELECT FUNCNAME FROM SYSCAT.FUNCTIONS WHERE FUNCSCHEMA = '{0}';", schemaName);
var result = conn.Query <dynamic>(sql)
.Select(s => new QuerySource
{
Name = s.FUNCNAME,
Type = SQLQuerySourceType.Function
}).ToList();
return(result);
}
public List <Relationship> LoadRelationships(string connectionString, List <string> schemas = null)
{
using (var conn = new DB2Connection(connectionString))
{
string sql = $@"SELECT CONSTNAME, TABSCHEMA, TABNAME, FK_COLNAMES, REFTABSCHEMA, REFTABNAME, PK_COLNAMES FROM SYSCAT.REFERENCES;";
var relationships = conn.Query <dynamic>(sql)
.Select(r => new Relationship
{
JoinQuerySourceName = r.TABSCHEMA + '.' + r.TABNAME,
ForeignQuerySourceName = r.REFTABSCHEMA + '.' + r.REFTABNAME,
JoinFieldName = r.FK_COLNAMES,
ForeignFieldName = r.PK_COLNAMES
})
.ToList();
return(relationships);
}
}
public List <QuerySourceParameter> GetQuerySourceParameters(string connectionString, string specificSchema, string specificName)
{
var dataTypeAdaptor = new DB2SupportDataType();
using (var conn = new DB2Connection(connectionString))
{
const string sqlFormat = @"SELECT PARMNAME, ROUTINENAME, ROUTINESCHEMA, TYPENAME, ROWTYPE, ORDINAL
FROM SYSIBM.SYSROUTINEPARMS
WHERE ROUTINESCHEMA NOT IN({0})";
var sql = string.Format(sqlFormat, ExcludeSchemas);
if (!string.IsNullOrEmpty(specificSchema) && !string.IsNullOrEmpty(specificName))
{
sql += string.Format(" AND ROUTINESCHEMA = '{0}' AND ROUTINENAME = '{1}';", specificSchema, specificName);
}
else
{
sql += ";";
}
var parametes =
conn.Query <dynamic>(sql)
.Select(s => new QuerySourceParameter
{
Name = string.Empty + s.PARMNAME,
QuerySourceName = s.ROUTINENAME,
Category = s.ROUTINESCHEMA,
DataType = s.TYPENAME,
IzendaDataType = dataTypeAdaptor.GetIzendaDataType(s.TYPENAME),
InputMode = s.ROWTYPE.Equals("P", StringComparison.OrdinalIgnoreCase),
Result = s.ROWTYPE.Equals("R", StringComparison.OrdinalIgnoreCase),
Position = s.ORDINAL,
Value = DBNull.Value,
AllowDistinct = dataTypeAdaptor.GetAllowDistinct(s.TYPENAME)
}).ToList();
return(parametes);
}
}