ADO.NET 获取存储过程参数或返回值

今天遇到获取存储过程返回值问题. 化了1个小时才解决. 特此记录一下.

使用SQLHelper中的ExecuteReader方法执行存储过程   SQLHelper 是petshop中的SQLHelper类

SQLHelper中ExecuteReader 方法

 

public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
    SqlCommand cmd = new SqlCommand();
    SqlConnection conn = new SqlConnection(connectionString);

    // we use a try/catch here because if the method throws an exception we want to 
    // close the connection throw code, because no datareader will exist, hence the 
    // commandBehaviour.CloseConnection will not work
    try
    {
        PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
        SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        cmd.Parameters.Clear();
        return rdr;
    }
    catch
    {
        conn.Close();
        throw;
    }
}

ADO.NET调用存储过程代码片段

SqlParameter[] parms = new SqlParameter[]{ new SqlParameter("@Passport",SqlDbType.Char,32),
                                                            new SqlParameter("@FileHash",SqlDbType.Binary,20),
                                                            new SqlParameter("@Size",SqlDbType.BigInt),
                                                            new SqlParameter("@ParentID",SqlDbType.UniqueIdentifier),
                                                            new SqlParameter("@FileName",SqlDbType.NVarChar,260),
                                                            new SqlParameter("@IsAdd",SqlDbType.Bit), 
                                                            new SqlParameter("@ReturnValue",SqlDbType.Int)
};

parms[0].Value = sessionID;
parms[1].Value = fileHash.ToByteString();
parms[2].Value = fileSize;
parms[3].Value = new Guid(parentID);
parms[4].Value = fileName;
parms[5].Value = only_add; 
parms[6].Direction = ParameterDirection.ReturnValue;

using (var rdr = SqlHelper.ExecuteReader(SqlHelper.FileStorage, CommandType.StoredProcedure, "uspAppUploadFile", parms))
{
    while (rdr.Read())
    {
        file.id = rdr.GetGuid(0).ToString("N");
        file.parent_id = rdr.GetGuid(1).ToString("N");
        file.name = rdr.GetString(2);
        file.hash = ((byte[])rdr[3]).ToHexString();
        file.size = rdr.GetInt64(4);
        file.version = rdr.GetInt32(5);
        file.modify_time = rdr.GetDateTime(6).ToString("yyyy-MM-dd hh:mm:ss");
    }
} 

int returnvalue = (int)parms[6].Value;

这里parms[6].Value值始终是null

使用ExecuteReader执行存储过程需要注意的地方是需要先关闭DataReader后才可以获取存储过程的输出参数和返回值.

上面的代码使用了using 会自动关闭. 貌似没什么问题. 但是返回值始终是null经过仔细检查 发现下面一段代码有问题

SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;

这里的DataReader 还未关闭 参数就已经清空了. 导致获取输出参数和返回值为null