Has anyone had any success with Subsonic3, linq, MySQL and Stored Procedures

Question

Hi, I have been trying to get MySQL stored procedures running with the linq templates in Subsonic3. I added some functions to the MySQL.ttinclude file that seems to have generated the stored procedure reference classes. However when I run the code and call the stored procedures I seem to always get NULL results:

    public DataSet SPTotalCallsByHour(int period)
    {
        rt.rtDB ee = new rt.rtDB();
        StoredProcedure sp = ee.Totals_By_Hour(period.ToString());
        sp.Execute();

        return (DataSet)sp.Output;
    }

Has anyone got MySQL stored procedures working with Subsonic3? If so can you please explain how you got them to work?

Did you use the ttinclude files straight out of the subsonic 3 release?

These are the two functions I added to the MySQL.ttinclude file:

List<SPParam> GetSPParams(string spName){
var result=new List<SPParam>();
MySqlCommand cmd = new MySqlCommand();
using(conn=new MySqlConnection(ConnectionString))
{
    conn.Open();
    cmd.Connection = conn;
    cmd.CommandText = spName;
    cmd.CommandType = CommandType.StoredProcedure;

    try
    {
            MySqlCommandBuilder.DeriveParameters(cmd);
    }
    catch
    {
    }

    if(cmd.Parameters.Count > 0)
    {
            foreach(MySqlParameter param in cmd.Parameters)
            {
                    SPParam p = new SPParam();
                    p.SysType = GetSysType(param.MySqlDbType.ToString());
                    p.DbType = param.DbType.ToString();
                    p.Name = param.ParameterName;
                    p.CleanName=CleanUp(p.Name);
                    result.Add(p);
            }
    }
    conn.Close();
}

return result;

}

List<SP> GetSPs(){
var result=new List<SP>();
string[] spNames = GetSPList();

foreach(string spName in spNames){
    var sp=new SP();
    sp.Name=spName;
    sp.CleanName=CleanUp(sp.Name);
    sp.Parameters=GetSPParams(sp.Name);
    result.Add(sp);        
}

return result;

}

Answer 1

i have never got stored procedures to work for mysql and subsonic, but i havent tried so hard either. my reasoning behind that you have something better than inline sql.

i think i speak for the masses that stored procedures was used to get around having database code (loads of sql) inside the application, dotted here n there so programmers would use stored procedures to seperate the 2 concerns and make it easier to update/change/repair.

now if you use subsonic or any other kind of DAL and entities then you have no need to write sql statements as such, you just write code.... subsonic, linq etc know what to do with that and translate it for you.

so you dont really need stored procedures, if your database has a problem, you just run your TT files again.

the rest is code, your program. so maybe it is not the answer you would like, but sack the stored procedures and make use of how much fun it is to use something like subsonic and well forget all about your database and just think about the code.

Answer 2

I had a problem executing a SP with no parameter and trying to read back ouput. Seems you have to do this work around;

SubSonic.StoredProcedure sp = SPs.UspNoParamProc();
//Stored Procedure command Is Null... ensure command is created
string dummy = sp.Command.CommandSql;

rptList.DataSource = sp.GetReader();
rptList.DataBind();

Not sure if this problem continues to exist in 3.0 or not. Might try it out.