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?

Tim Schmelter · Accepted Answer · 2011-12-01 09:50:13Z

3

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.

edited Dec 1, 2011 at 9:50

answered Dec 1, 2011 at 9:08

Tim Schmelter

462k79 gold badges721 silver badges981 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ali Hasan Over a year ago

something like this SqlContext.Pipe.SendResultsRow();

Darshan Over a year ago

Yes, We can send Row by Row but this will impact on performance.... Thanks for your reply

RickNZ · Accepted Answer · 2011-12-02 14:16:22Z

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.

answered Dec 2, 2011 at 14:16

RickNZ

18.7k3 gold badges55 silver badges67 bronze badges

2 Comments

Darshan Over a year ago

Actually this also wont work for my situation i want to fetch data from two different SQL Server so connection string is different for both the queries. if i use as suggested by you then it will be fire on single database.

RickNZ Over a year ago

I thought you were fetching data from two instances, and returning it from a single CLR SP. So the client only has one connection string, and only issues one query, right? So why can't the CLR SP return two result sets in response to that query?

Ali Hasan · Accepted Answer · 2011-12-01 09:18:05Z

1

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

edited Dec 1, 2011 at 9:18

answered Dec 1, 2011 at 9:11

Ali Hasan

1,0754 gold badges17 silver badges44 bronze badges

1 Comment

Tim Schmelter Over a year ago

The Send method takes a SqlDataReader not a DataTableReader. This won't work like my answer.

Collectives™ on Stack Overflow

How to merge/combine two SqlDataReader Objects

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related