How do I merge two results of a query?

Question

I am developing an application in C# and I would like to know how to put together two results of two darlings SQL in one. I have the following code:

public List<MalaDireta> ObterMalaDireta()
{    
    List<MalaDireta> resultado = new List<MalaDireta>();

    string sql_funcionario = "select id, nome, email from funcionario";
    string sql_fornecedor = "select id, nome, email from fornecedor";

    var conexao = new SqlConnection("string de conexão");

    var comando = new SqlCommand(sql_funcionario, conexao);

    try
    {
       conexao.Open();

       // como eu monto meu comando com as duas queries?

       var dataReader = comando.ExecuteReader();
       while (dataReader.Read())
       {
          MalaDireta m = new MalaDireta();
          m.Nome = dataReader["nome"];
          m.Email = dataReader["email"];

          resultado.Add(m);
       }
    }
    catch
    {
    }
    finally
    {
       conexao.Close();
    }

    return resultado;
}

Answer 1

How about making the union through the sql query itself using the UNION:

select id, nome, email from funcionario
union
select id, nome, email from fornecedor

In your case it would look like this:

string sql_todos = "select id, nome, email from funcionario " +
                   "union "+
                   "select id, nome, email from fornecedor";

Answer 2

The @Ecil response is adequate, but I add the use of UNION ALL.

What’s the difference between UNION and UNION ALL?

• UNION makes a kind of DISTINCT, inhibiting repeated results
• UNION ALL brings all results, even with repeated values

Is there any advantage in UNION ALL? In theory, it is more efficient for not applying the DISTINCT, that is, not comparing the values of the records.

Then all it takes is a simple change:

string sql_todos = "select id, nome, email from funcionario " +
               "union all "+
               "select id, nome, email from fornecedor";

This concept applies at least to SQL Server, Oracle, Mysql and Postgresql.

Answer 3

In theory, you just repeat the process of reopening the connection, run the other query, and go through the repetition loop by adding the results of your second query.

string sql_funcionario = "select id, nome, email from funcionario";
string sql_fornecedor = "select id, nome, email from fornecedor";

var conexao = new SqlConnection("string de conexão");


MalaDireta m = new MalaDireta();
var comando = new SqlCommand(sql_funcionario, conexao);

try
{
   conexao.Open();

   // como eu monto meu comando com as duas queries?

   var dataReader = comando.ExecuteReader();
   while (dataReader.Read())
   {

      m.Nome = dataReader["nome"];
      m.Email = dataReader["email"];

      resultado.Add(m);
   }
}
catch
{
}
finally
{
   conexao.Close();
}

var comando = new SqlCommand(sql_fornecedor, conexao);

try
{
   conexao.Open();

   // como eu monto meu comando com as duas queries?

   var dataReader = comando.ExecuteReader();
   while (dataReader.Read())
   {

      m.Nome = dataReader["nome"];
      m.Email = dataReader["email"];

      resultado.Add(m);
   }
}
catch
{
}
finally
{
   conexao.Close();
}

Answer 4

There are two ways:

1. If the structure of your queries are the same you can use a UNION:

var query = "select id, nome, email from funcionario union select id, nome, email from fornecedor";

1. You can send both queries in the same Sqlcommand. The Datareader class natively supports multiple results using the method dataReader.NextResult():

       var comando = new SqlCommand(string.Concat(sql_funcionario, ";", sql_fornecedor), conexao);
       var dataReader = comando.ExecuteReader();
       while (dataReader.NextResult()) {
           while (dataReader.Read()) {
               MalaDireta m = new MalaDireta();
               m.Nome = dataReader["nome"];
               m.Email = dataReader["email"];
   
               resultado.Add(m);
           }
       }
   

Answer 5

Dear, even if some hidden magic can be done to unite the two Restaders for readability I only see two options:

1. Put a Union in the query and place the business Logic on the bank side. (Kind of ugly)
2. Take the two Readers (converts to Enumerable entity) and merge with Linq, you can write Unit Tests to make the responsible code