Sql Stored Procedure Calls

[Joe]

Calling stored procedures in Sql Server from C# is awkward because of the specific parameter names/types for each stored proc. So rather than actually call stored procedures like we did in Fox:

procedure EndProcessSession
lparameters tnConnHdl, tnKeyVal
    local lcSql, lnResult, lnRetVal

    lnRetVal = 0
    lcSql = "EXEC StoredProcedureName " + AllTrim(Str(tnKeyVal)) + ", ?@lnRetVal"

    lnResult = SQLExec(tnConnHdl, lcSql)
    if lnResult < 0
        do Log_Error with Error(), LineNo(), Program() ;
            , "Could not End Session: " + Transform(tnKeyVal) + CRLF + Message() in Mc_ComPc
        return .F.
    endif
    return .T.
endproc

I tried running 3 statements from C# to do the same work, which works fine. Be sure to include OUTPUT in line 6:

static public bool EndProcessSession(int KeyVal)
{
    string Sql = "";
    bool Result = true;
    Sql = "declare @lnRetVal as integer " +
          "EXEC StoredProcedureName " + KeyVal.ToString().Trim() + ", @lnRetVal OUTPUT " +
          "select @lnRetVal RetVal";
    DataSet dsProcessSession = null;
    Result = Connection.ExecuteSelect(Sql, ref dsProcessSession);
    if(Result == false || dsProcessSession.Tables[0].Rows[0]["RetVal"].Equals(null))
        Error.LogError("EndProcessSession()", "Could not End Session: " + KeyVal.ToString().Trim() + Strings.CRLF);
    dsProcessSession = null;
    return Result;
}