CODEWITHSUNDEEP
c#sql-serverado.net
Darshan
Darshan

Reputation: 147

How to merge/combine two SqlDataReader Objects

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?

Upvotes: 6

Views: 6484

Answers (3)

RickNZ
RickNZ

Reputation: 18654

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.

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460108

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);

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.

Upvotes: 3

Ali Hasan
Ali Hasan

Reputation: 1075

First convert your datareader to datatables 

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

rd1 = SelectCmmand.ExecuteReader();
DataTable dt1 = new DataTable();
dt1.Load(rd1);

conn.Close();

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

rd2 = SelectCmmand.ExecuteReader();
    DataTable dt2 = new DataTable();
    dt2.Load(rd2);

conn.Close();

Then merge both of them

dt1.Merge(dt2);

Note the data from table 2 will added to table 1

Now convert back to data reader 

DataTableReader dtr;
dtr = dt1.CreateDataReader();        
SqlPipe sp;
sp = SqlContext.Pipe;
sp.Send(dtr);

Upvotes: 1

Related Questions