dotConnect for MySQL Documentation
In This Topic
    Multiple Query Execution
    In This Topic

    dotConnect for MySQL supports execution of batch SQL statements separated by ';' separator. For example, you can execute following SQL:

    select * from emp; select * from dept
    

    if you write next code:

    MySqlCommand MySqlCommand1;
    MySqlDataReader reader;
    MySqlCommand1.CommandText = "select * from emp; select * from dept";
    reader = MySqlCommand1.ExecuteReader();
    
    
    Dim MySqlCommand1 as MySqlCommand
    Dim reader as MySqlDataReader
    MySqlCommand1.CommandText = "select * from emp; select * from dept"
    reader = MySqlCommand1.ExecuteReader()
    
    

    As a result of the execution you get open data reader positioned on the first result of the query. Call MySqlDataReader.NextResult method to advance to the next result. If you set MySqlCommand.FetchAll property to true you can reach any result using MySqlDataReader.CurrentResult and MySqlDataReader.ResultCount properties.

    You can use multiple results queries to specify MySqlDataAdapter.InsertCommand.

    For example,

    MySqlDataAdapter adapter;
    MySqlCommand cmd;
    DataSet dataSet;
    cmd.CommandText = "insert into animals (id, name) values (:id, :name);+
        select id, name from animals where id = last_insert_id()";
    ...
    adapter.Update(dataSet);
    
    
    Dim adapter as MySqlDataAdapter
    Dim cmd as MySqlCommand
    Dim dataSet as DataSet
    cmd.CommandText = "insert into animals (id, name) values (:id, :name); &_
        select id, name from animals where id = last_insert_id()"
    ...
    adapter.Update(dataSet)
    
    

    This method allows you to get values of auto increment fields and fields with the default values.

    See Also

    MySqlDataReader Class  | Devart.Data.MySql Namespace