Criar Instrução SQL de Select usando SMO

Objectivo: criar instrução SQL de Select que devolva todos os campos de uma tabela. Deve também “descodificar” as foreign keys de uma tabela enviando para isso um campo não chave da tabela referenciada.
Ferramentas: SQL Server Management Objects (SMO)
Principais objectos e propriedades: Table, Table.Columns, Column, Column.Identity, Column.InPrimaryKey
Notas:

  • chave primária não composta
  • campos foreign key podem ser nulos pelo que não podemos optar sempre pelo INNER JOIN
  • é assumida a existência de uma função chamada GetReferencedTableName() que devolve o nome da tabela que está a ser referencia por uma foreign key.
  • é assumida a existência de uma função chamada GetReferencedKeyColumnName() que devolve o primeiro campo nao chave de uma tabela referenciada. o nome desse campo aparecerá para fora com o nome da coluna inicial + o sufixo “_description”
  • os nomes das tabelas referencias são convertidos para aliases do tipo t_(numero) em que numero é um simples contador que assume que a lista de foreign keys vem sempre pela mesma ordem
  • as colunas CreatedOn, CreatedBy, lastUpdatedOn, lastUpdatedBy existem sempre

Exemplo do resultado pretendido

Dim sql As String = "SELECT {0}TM_Deslocacoes.IdDeslocacao, {0}TM_Deslocacoes.IdColaborador, convert(varchar(500), t_0.Nome) [IdColaborador_description], {0}TM_Deslocacoes.IdMeioDeslocacao, convert(varchar(500), t_1.Nome) [IdMeioDeslocacao_description], {0}TM_Deslocacoes.LocalDestino, convert(varchar(500), t_2.Designacao) [LocalDestino_description], {0}TM_Deslocacoes.DataPartida, {0}TM_Deslocacoes.LocalPartida, convert(varchar(500), t_3.Designacao) [LocalPartida_description], {0}TM_Deslocacoes.DataRegresso, {0}TM_Deslocacoes.Direccao, convert(varchar(500), t_4.Description) [Direccao_description], {0}TM_Deslocacoes.NumeroPessoas, {0}TM_Deslocacoes.Observacoes, {0}TM_Deslocacoes.CreatedOn, {0}TM_Deslocacoes.CreatedBy, {0}TM_Deslocacoes.LastUpdatedOn, {0}TM_Deslocacoes.LastUpdatedBy, {0}TM_Deslocacoes.LastUpdated FROM {0}TM_Deslocacoes INNER JOIN {0}TM_Colaboradores t_0 ON {0}TM_Deslocacoes.IdColaborador = t_0.IdColaborador INNER JOIN {0}TM_DefinicaoMeiosDeslocacaoDisponiveis t_1 ON {0}TM_Deslocacoes.IdMeioDeslocacao = t_1.IdMeioDeslocacaoDisponivel INNER JOIN {0}TM_DefinicaoLocalidades t_2 ON {0}TM_Deslocacoes.LocalDestino = t_2.IdLocalidade  LEFT OUTER JOIN {0}TM_DefinicaoLocalidades t_3 ON {0}TM_Deslocacoes.LocalPartida = t_3.IdLocalidade  INNER JOIN {0}TM_Parametros t_4 ON {0}TM_Deslocacoes.Direccao = t_4.Id       WHERE 	IdDeslocacao = @IdDeslocacao"

 

Código

private static string CreateMethodFindAll_SelectStatement(Table table, Database database)
{

    StringBuilder sb = new StringBuilder(200);
    database.Tables.Refresh();
    table.Refresh();

    sb.Append("SELECT ");
    bool primeiro = true;
    int fkCount = 0;

    foreach (Column c in table.Columns)
    {
        if (primeiro) primeiro = false; else sb.Append(", ");
        sb.AppendFormat("{{0}}{0}.{1}", table.Name, c.Name);
        if (c.IsForeignKey)
        {
            string fkt = GetReferencedTableName(table, c);
            if (fkt != "")
            {
                Table fkTable = database.Tables[fkt];
                if (fkTable != null)
                {
                    sb.AppendFormat(", convert(varchar(500), t_{0}.{1}) [{2}_description]", fkCount, GetFirstNonKeyColumnName(fkTable), c.Name);
                    fkCount++;
                }
            }
        }
    }

    return sb.ToString();
}

private static string CreateMethodFindAll_JoinStatement(Table table, Database database)
{
    StringBuilder sb = new StringBuilder(200);

    bool primeiro = true;
    int fkCount = 0;

    fkCount = 0;
    foreach (Column c in table.Columns)
    {
        if (primeiro) primeiro = false; else sb.Append(" ");
        if (c.IsForeignKey)
        {
            string fkt = GetReferencedTableName(table, c);
            if (fkt != "")
            {
                Table fkTable = database.Tables[fkt];
                if (fkTable != null)
                {
                    if (c.Nullable)
                        sb.AppendFormat("LEFT OUTER JOIN {{0}}{2} t_{7} ON {{0}}{4}.{5} = t_{7}.{6}", table.Parent.ToString(), fkTable.Schema, fkTable.Name, table.Schema, table.Name, c.Name, GetReferencedKeyColumnName(database, table, c), fkCount);
                    else
                        sb.AppendFormat("INNER JOIN {{0}}{2} t_{7} ON {{0}}{4}.{5} = t_{7}.{6}", table.Parent.ToString(), fkTable.Schema, fkTable.Name, table.Schema, table.Name, c.Name, GetReferencedKeyColumnName(database, table, c), fkCount);
                    fkCount++;
                }
            }
        }
    }

    return sb.ToString();
}

private static string CreateMethodFindAll_sql(Table table, Database database)
{
    StringBuilder sb = new StringBuilder(200);

    sb.Append(CreateMethodFindAll_SelectStatement(table, database));

    sb.AppendFormat(" FROM {{0}}{0}", table.Name);

    sb.Append(CreateMethodFindAll_JoinStatement(table, database));

    return sb.ToString();
}

 

Deixe um comentário

O seu endereço de email não será publicado. Campos obrigatórios marcados com *