"Return multiple result sets" and "MARS" instances

Multiple result sets are returned:

A simple query can return multiple result sets. Returning multiple result sets in a single query can improve the query efficiency and avoid occupying multiple database tutorial links at the same time.

 

  

   

    

     /// <Summary>
     
/// Return multiple result sets
     
/// </Summary>
     
/// <Returns> </returns>
     
Public void gettworesultset ()
     
{
     
Sqlconnection conn = new sqlconnection (_ connstr );
     
String SQL = "select * from table_1; select * from table_2 ";
     
Sqlcommand com = new sqlcommand (SQL, conn );
     
Using (conn)
     
{
     
Conn. open ();
     
Sqldatareader dr = com.exe cutereader ();
     
While (dr. read ())
     
{
     
//... The binding Code is omitted.
     
}
     

     
// Read the data to the next result set (the focus is here)
     
Dr. nextresult ();
     

     
While (dr. read ())
     
{
     
//... The binding Code is omitted.
     
}
     
}
     
}
    
   
   2. Use mars:
  

Ado.net provides a new feature named mars. Before this version, database connections can only represent one query result set within a limited period of time. If you use the mars feature, you can use a single database connection to represent multiple query result sets.

By default, the mars feature is disabled. To enable this feature, you only need to add a field attribute in the database connection string: multipleactiveresultsets = true

 

  

   

    

     /// <Summary>
     
/// Use mars (multiple active resultsets, multi-activity result set)
     
/// </Summary>
     
Public void getbymars ()
     
{
     
// Multipleactiveresultsets = true; enables the SQL mars
     
String connstr = @ "multipleactiveresultsets = true; datasource =. sqlexpress; integrated security = true ;";
     
Connstr + = @ "attachdbfilename = | datadirectory | mydatabase. mdf; user instance = true ";
     
Sqlconnection conn = new sqlconnection (connstr );
     

     
String sqlcata = "select * from catagory ";
     
Sqlcommand extends Cata = new sqlcommand (sqlcata, conn );
     

     
String SQL = "select * from movies where catagoryid = @ catagoryid ";
     
Sqlcommand cmd = new sqlcommand (SQL, conn );
     
Cmd. parameters. add ("@ catagoryid", sqldbtype.int );
     

     
Using (conn)
     
{
     
Conn. open ();
     
Sqldatareader drcata = cmdcata.exe cutereader ();
     
While (drcata. read ())
     
{
     
Int cataid = int. parse (drcata ["catagoryid"]. tostring ());
     

     
Cmd. parameters ["@ catagoryid"]. value = cataid;
     
Sqldatareader dr = cmd.exe cutereader ();
     
While (dr. read ())
     
{
     
//... The binding Code is omitted.
     
}
     
Dr. close ();
     
}
     
}
     
}