How to merge/combine two SqlDataReader Objects

Question

I am working on a SQL server monitoring project. In this I want to fetch data from all the SQL Server instances installed on machine. For this, I have written a CLR Stored procedure, in which the data came in two different SqlDataReader objects and I want to merge these two datareder objects.

Is it possible to merge two SQLdatareader objects?

Following is the situation where I am facing this issue:

SqlConnection conn = new SqlConnection("ConnectionSting of 1st SQLServerInstance")
string query = "select dbid,uid,cpu from [master].[sys].sysprocesses";

SqlCommand SelectCmmand = new SqlCommand(query, conn);            
SqlDataReader rd1;            
conn.Open();
rd1 = SelectCmmand.ExecuteReader();
conn.Close();

conn = new SqlConnection("ConnectionSting of 2nd SQLServerInstance")

SqlCommand SelectCmmand = new SqlCommand(query, conn);            
SqlDataReader rd2;            
conn.Open();
rd2 = SelectCmmand.ExecuteReader();
conn.Close();

SqlPipe sp;
sp = SqlContext.Pipe;
sp.Send(?????);

Now, sp.Send(??) method wants SQLDataReader object as a parameter where I want to send the above data fetched from two different connectionstring.

So, How should i merge/combine rd1 and rd2?

Answer 1

You could load two DataTables with the readers, merge them and call DataTable.CreateDataReader().

For example (not tested):

DataTable dt1 = New DataTable();
dt1.Load(rd1);
DataTable dt2 = New DataTable();
dt2.Load(rd2);
dt1.Merge(dt2);
DataTableReader mainReader = dt1.CreateDataReader();
Common.DbDataReader reader = (Common.DbDataReader)mainReader;
sp.Send((SqlClient.SqlDataReader)reader);

• DataTable.Load(iDataReader)
• DataTable.Merge
• DataTable.CreateDataReader

Edit: I'm afraid that my code does not work because you cannot cast the DbDataReader to a SqlDataReader.

One way would be (again, not tested) to use SqlPipe.SendResultsRow to send all records row by row.

Answer 2

You can't merge SqlDataReaders.

Have you considered just returning two result sets? It would be as though you did SELECT * FROM Table1, followed by SELECT * FROM Table2 in the same batch or stored procedure. On the client side, use SqlDataReader.NextResult() to advance from the first result set to the second one.