Merge select on SQL Server in C#

Question

Is there any way to merge 2 select that both of select return separate list of data

All I want to do is connecting to Database one times

First Query:

string strQuery1=
    string.Format("SELECT distinct top 10   Title,  Id FROM Table1");
IQueryable<MyCompanyDto> list =
                    _entities.Database.SqlQuery<MyCompanyDto>(strQuery1).AsQueryable();

Second Query:

string strQuery2=
        string.Format("SELECT   ProductName,  CountryID FROM Table2");
    IQueryable<MyProductDto> list =
                        _entities.Database.SqlQuery<MyProductDto>(strQuery2).AsQueryable();

http://stackoverflow.com/questions/12715620/how-do-i-return-multiple-result-sets-with-sqlcommand — Evgeni Dimitrov, Sep 03 '16 at 21:11

score 0 · Answer 1 · edited Mar 12 '20 at 15:32

It is possible to connect one time for both queries, but you have to create two DbCommand:

string strQuery1= string.Format("SELECT distinct top 10   Title,  Id FROM Table1");
string strQuery2= string.Format("SELECT   ProductName,  CountryID FROM Table2");

DataTable dataTable1 =  new DataTable();
DataTable dataTable2 =  new DataTable();

using (SqlConnection conn =  new SqlConnection(<connection_string>))
{
    conn.Open();

    using(DbCommand command = conn.CreateCommand())
    {
       command.Connection = conn;
       command.CommandText = strQuery1;

       using (var reader = dbCommand.ExecuteReader())
       { 
          dataTable1.Load(reader);
       }
    }
    using(DbCommand command = conn.CreateCommand())
    {
       command.Connection = conn;
       command.CommandText = strQuery2;

       using (var reader = dbCommand.ExecuteReader())
       { 
          dataTable2.Load(reader);
       }
    }
 }

Merge select on SQL Server in C#

1 Answers1