Execute multiple SQL statements using DataReader - ADO.NET
Q. How do you execute multiple SQL statements using a DataReader?- Published on 19 Oct 15a. 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
}
