MySQL Stored procs work every other call through .Net

The company I work for has an upcoming project that will be using MySQL as the back end with a C# ASP .Net front end. I threw tog开发者_Go百科ether a test app and db, and tings seem to work beautifully for the most part, except when I try to call any proc, it will work on the first call, not the next, then again, then not, without fail, it only ever works every other try.

Using workbench to call the proc works every time, as does connecting to the DB with VS2010, it's only the app code that fails. I have been pretty explicit about making sure the connection is opened and closed (and disposed of) every time, but with no luck.

here is a sample query; this is the proc I have been using most:

SELECT
        U.id,
        U.First_Name,
        U.Last_Name,
        U.Birth_Date,
        U.Created,
        U.Password
    FROM 
        test.user U;

As you can see, it is incredibly simple. It is not just that proc, however. I can call any procs in any order and get the same results; on, off, on, off, etc. If I call the query in code not using a proc, it works just fine, however the model that the company uses is procs for every call to the database (MSSQL) and they want to carry over the same standards to this application as well.

Just to be thorough, here is the connection string (values modified of course):

Server=127.0.0.1;Port=3306;Data Source=localhost;Database=testdb;User Id=test_user;Password=pword;

And the following is at the top of the Database manager:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;

using MySql.Data;
using MySql.Data.MySqlClient;

I am running the connection, command and data adapter with "using" blocks to make sure they are disposed of properly with no loose ends after they execute, and have even tried simply declaring their disposal specifically after they are closed just to be safe. Nothing changes the behavior.

Has anyone seen this before? Everything is running locally, in case that matters.

Here is the code that makes the call, and I may have solved it myself:

public static DataTable SPExecuteTable(string commandText = "", List paras = null) { DataTable dt = new DataTable(); Exception ex = null;

    using (MySqlConnection conn = new MySqlConnection(MySQL_Connection))
    {
        using (MySqlCommand com = MySQlComnd(commandText, paras))
        {
            try
            {
                conn.Open();
                com.Connection = conn;

                using (MySqlDataAdapter oda = new MySqlDataAdapter())
                {
                    oda.SelectCommand = com;
                    oda.Fill(dt);

                }
            }
            catch (Exception e)
            {
                ex = e;
            }
            finally
            {
                conn.Close();
            }
        }
    }

    if (ex != null)
        throw ex;

    return dt;
}

I sort of dumb-lucked into a solution:

    public static DataTable SPExecuteTable(string commandText = "", List<object> paras = null)
    {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        Exception ex = null;

            using (MySqlCommand com = MySQlComnd(commandText, paras))
            {
                try
                {
                    com.Connection.Open();

                    using (MySqlDataAdapter oda = new MySqlDataAdapter())
                    {
                        oda.SelectCommand = com;
                        oda.Fill(ds);
                        dt = ds.Tables[0];
                    }
                }
                catch (Exception e)
                {
                    ex = e;
                }
                finally
                {
                    com.Connection.Close();
                    com.Connection.Dispose();
                }
            }


        if (ex != null)
            throw ex;

        return dt;
    }

As you can see, if you fill a DataSet and then just grab the first table, it works. I am assuming that there is some underlying issue with the MySQL data adapter filling just a table, but the DataSet does not appear to share that problem.