C# Multiple Result Sets

By: Rajesh P.S.

The DataReader Object in C# provides a stream-based, forward-only, read-only retrieval of query results from the Data Source without the ability to update the data.

ExecuteReader() method

When the ExecuteReader() method is invoked on the SqlCommand Object, it instantiates a SqlClient.SqlDataReader Object in C#. This SqlDataReader Object allows you to efficiently read and access the retrieved data from the Data Source.

SqlDataReader sqlReader = sqlCmd.ExecuteReader();

In certain scenarios, there may be a need to execute multiple SQL statements using the same Command Object. In such cases, the SqlDataReader can handle multiple ResultSets. Each ResultSet represents the result of an individual SQL statement within the batch executed.

NextResult() method

To retrieve multiple ResultSets from the SqlDataReader, you can use the NextResult() method. After reading the first ResultSet, invoking the NextResult() method advances the SqlDataReader to the next ResultSet, if available. This allows you to sequentially access the data from each ResultSet within the SqlDataReader.

Continue Reading...

By utilizing the NextResult() method iteratively, you can navigate through all the ResultSets returned by the SqlDataReader and retrieve the corresponding data as needed.

sqlReader.NextResult();

In the following source code you can see how to get multiple result sets from SqlDataReader() .

Full Source C#

using System; using System.Windows.Forms; using System.Data.SqlClient; namespace WindowsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string connetionString = null; SqlConnection sqlCnn ; SqlCommand sqlCmd ; string sql = null; connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"; sql = "Select top 2 * from product; select top 2 * from ordermaster; select top 2 * from orderdetails"; sqlCnn = new SqlConnection(connetionString); try { sqlCnn.Open(); sqlCmd = new SqlCommand(sql, sqlCnn); SqlDataReader sqlReader = sqlCmd.ExecuteReader(); while (sqlReader.Read()) { MessageBox.Show ("From first SQL - " + sqlReader.GetValue(0) + " - " + sqlReader.GetValue(1)); } sqlReader.NextResult(); while (sqlReader.Read()) { MessageBox.Show("From second SQL - " + sqlReader.GetValue(0) + " - " + sqlReader.GetValue(1)); } sqlReader.NextResult(); while (sqlReader.Read()) { MessageBox.Show("From third SQL - " + sqlReader.GetValue(0) + " - " + sqlReader.GetValue(1)); } sqlReader.Close(); sqlCmd.Dispose(); sqlCnn.Close(); } catch (Exception ex) { MessageBox.Show("Can not open connection ! "); } } } }

Conclusion

The DataReader Object in C# provides a stream-based, forward-only, read-only retrieval of query results from the Data Source. The SqlDataReader instantiated by the ExecuteReader() method in the SqlCommand Object facilitates this data retrieval. In situations where multiple SQL statements are executed using the Command Object, the SqlDataReader can handle multiple ResultSets. The NextResult() method allows you to navigate between these ResultSets and retrieve data from each one.

Next > C# Table Schema from SqlDataReader

Related Links