How to use output parameter and select query result of a sql server stored procedure in ado.net?

Question

Hi I want to get the value of output parameter and as well as the result set of select query.
I used ExecuteNonQuery, it gives proper value for output parameter.
I used ExecuteReader it does not give proper value for output parameter but it gives proper value for select query.
So what should I use to get both the results.

    ALTER PROCEDURE [dbo].[XYZ] 
(  
 @szUserName varchar(50),  
 @iOutDistinceBankCount int out
)  
AS  
BEGIN  
declare @iCountDistinctBanks int;
set @iCountDistinctBanks = (select count (distinct a.DCC_BANK_ID )
 from DEF a with(nolock)
join ABC b with(nolock) on
 a.ROLEID = b.ROLEID
where b.USERNAME = @szUserName and b.STATUS_ID = 2)

if ((@iCountDistinctBanks > 1) or (@iCountDistinctBanks = 0))
    begin
        set @iOutDistinceBankCount = @iCountDistinctBanks 
    end

else
    begin
        set @iOutDistinceBankCount = 1;
            select a.DCC_BANK_ID as DCC_BANK_ID
            from DEF a with(nolock)
            join ABC b with(nolock) on
            a.ROLEID = b.ROLEID
            where b.USERNAME = @szUserName and b.STATUS_ID = 2
    end

END 

This is my C# Code.

Int32 i32DistinctDCCBankCount = -1;
            Int64 i64BankStaticID = -1;
            InitDB();
            m_command = new SqlCommand("DCC_spUIDCCBankIdAccordingUser", m_con);
            m_command.Parameters.Add("@szUserName", System.Data.SqlDbType.VarChar, 50).Value = MerchantName;

            SqlParameter output = new SqlParameter("@iOutDistinceBankCount", System.Data.SqlDbType.Int);
            output.Direction = System.Data.ParameterDirection.Output;
            m_command.Parameters.Add(output);

            m_command.CommandType = System.Data.CommandType.StoredProcedure;
            m_con.Open();
           // m_reader = m_command.ExecuteReader();
            m_command.ExecuteNonQuery();
            i32DistinctDCCBankCount = Convert.ToInt32(m_command.Parameters["@iOutDistinceBankCount"].Value);


                    if (i32DistinctDCCBankCount == 0)
                    {
                        iDistinctDCCBankCount = 0;
                        return i32DistinctDCCBankCount;
                    }
                    else if (i32DistinctDCCBankCount > 1)
                    {
                        iDistinctDCCBankCount = i32DistinctDCCBankCount;
                        return -2;
                    }
                    else if (i32DistinctDCCBankCount == 1)
                    {
                        i64BankStaticID = Convert.ToInt64(m_reader["DCC_BANK_ID"]);
                        iDistinctDCCBankCount = i32DistinctDCCBankCount;
                        return i64BankStaticID;
                    }


            iDistinctDCCBankCount = 0;
            return 0;

Answer 1

This same query can be executed directly with the Command.ExecuteReader (or ExecuteNonQuery if you don't have a rowset to process), but there are several other steps you'll need to take to process the returned values. Remember, you'll have to complete processing for all rowsets before attempting to capture the Return value or OUTPUT parameters. The following code shows how to use the ExecuteReader and a loop to process the rowsets, and then capture the Return value and OUTPUT parameters. You'll find that OUTPUT parameters (even a lot of them) can be handled far faster than even a single row of data returned by a SELECT.

here is example

    With cmd.Parameters
        cn.Open()
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        ' Process rowset(s)
        bolEOF = dr.Read
        Do
            Do While bolEOF = True
                ' Process rows
                bolEOF = dr.Read()
            Loop
        Loop While dr.NextResult = True
        cmd.Cancel()
// you need to close dataReader first
        dr.Close()


        Debug.WriteLine("@iOutDistinceBankCount:" & _
            .Item("@iOutDistinceBankCount").Value.ToString)
    End With

Answer 2

One possible way is to use ado's NextRecordSet and not use output parameter at all

if ((@iCountDistinctBanks > 1) or (@iCountDistinctBanks = 0))
    begin
        set @iOutDistinceBankCount = @iCountDistinctBanks 
        select @iOutDistinceBankCount as OutDistinceBankCount 
        select 0 where 0 = 1
    end

else
    begin
        set @iOutDistinceBankCount = 1;
        select @iOutDistinceBankCount as OutDistinceBankCount 

            select a.DCC_BANK_ID as DCC_BANK_ID
            from DEF a with(nolock)
            join ABC b with(nolock) on
            a.ROLEID = b.ROLEID
            where b.USERNAME = @szUserName and b.STATUS_ID = 2
    end

END 

in code

get the first record set which is your parameter then rs = rs.NextRecordSet() ... read your rows

performance: output parameter vs select really is not a consideration here