How to fill a dataset from a stored procedure using MySql and Mono

I am trying to fill a dataset with the result of a stored procedure using MySql and Mono.

The stored procedure takes in some values, creates a new row in a table and then selects some rows.

When I try this I get back an empty dataset and my database does not have any new records. I have checked the basics:

• The Database is running and I can connect.
• The stored procedure name and parameter names are correct.
• The parameter types are correct.
• The stored procedure works correctly from an sql command line.

If I don't use a stored procedure and set the command.commandtext to some sql it returns a dataset with data.

Here is a code sni开发者_JS百科ppet that showing what I am trying to do.

ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings [this.provider];
DbProviderFactory factory = DbProviderFactories.GetFactory (connectionStringSettings.ProviderName);

using(DbConnection connection = factory.CreateConnection())
{
    connection.ConnectionString = connectionStringSettings.ConnectionString;
    connection.Open();

    using(DbCommand command = connection.CreateCommand()) {
        command.CommandText = "createUser";
        command.CommandType = CommandType.StoredProcedure;

        dbParameter dbParameter = factory.CreateParameter();
        dbParameter.ParameterName = "@name";
        dbParameter.Value = "Fred.Smith";
        command.Parameters.Add(dbParameter);

        DataSet dataset = new DataSet();
        DbDataAdapter adaptor = factory.CreateDataAdapter();
        adaptor.SelectCommand = command;
        adaptor.Fill(dataset);

        //This is all ways empty
        return dataset;
    }
}

---

Why not call the stored procedure from an SQL statement:

Pseudo code: I'm not a Mono man.

query.SQLText = " CALL CreateUser('Fred Smith'); ";
query.Open;

If the stored procedure returns a result set, it should work the same as a SELECT.

Note that not all frameworks support stored procedures that return result sets. And MySQL does not support SELECT CALL Createuser('Fred Smith');

Alternatively you can change the stored procedure so that it puts the result-set into a temporary table.
And then select from that table in a separate select.