Execute multiple SQL statements using DataReader - ADO.NET

Q.  How do you execute multiple SQL statements using a DataReader?
- Published on 19 Oct 15

a. Call the ExecuteReadermethod of two Command objects and assign the results tothe same instance of a DataReader.
b. Call the ExecuteReadermethod of a single Command object twice.
c. Set the Command.CommandTextproperty to multiple SQL statements delimited by a semicolon.
d. Set the Command.CommandTypeproperty to multiple result sets.

ANSWER: Set the Command.CommandTextproperty to multiple SQL statements delimited by a semicolon.
 
You can execute more than one SQL statements delimited by a semicolon.
For this you have to set the CommandText property of a Command object to multipleSQL statements separated by semicolons (;). After calling the ExecuteReader method, theDataReader will hold the number of result sets equal to the number of SQL statements executed.

Example:
String sqlQuery = ”select * from table1; select * from table2”;
SqlConnection con = new SqlConnection(connectionString);
SqlCommandcmd = new SqlCommand ();
Con.Open();
SqlDataReaderdr = cmd.ExecuteReader();

While(dr.read())
{
// Process the table1
}
Dr.NextResult();

While(dr.read())
{
// Process the table2

}

Post your comment / Share knowledge


Enter the code shown above:
 
(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)