How to merge / merge two SqlDataReader objects

Question

I am working on a SQL server monitoring project. In this, I want to get data from all instances of SQL Server installed on the machine. To do this, I wrote a CLR stored procedure in which data entered two different SqlDataReader objects, and I want to combine these two datareder objects.

Is it possible to combine two SQLdatareader objects?

Below is the situation when I ran into this problem:

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 the sp.Send (??) method wants the SQLDataReader object to be the parameter where I want to send the above data from two different connection strings.

So how do I combine / combine rd1 and rd2?

Answer 1

You can load two data tables using readers, combine them, and call DataTable.CreateDataReader() .

For example (not verified):

 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

Change I am afraid my code is not working because you cannot use DbDataReader for SqlDataReader .

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

Answer 2

You cannot combine SqlDataReaders.

Did you think you were simply returning two sets of results? It would be as if you would select SELECT * FROM Table1 and then SELECT * FROM Table2 in the same batch or stored procedure. On the client side, use SqlDataReader.NextResult () to move from the first result set to the second.

Answer 3

Convert your datareader to datatables first

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

Please note that the data from table 2 will be added to table 1

Now go back to the data reader

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