How can i check what is variable into the SqlDataReader?

Question

I'm new to C#, and write this code for calling a SQL Server stored procedure:

using (SqlConnection con = new SqlConnection(Connection))
{
    using (SqlCommand cmd = new SqlCommand("CheckValidbehzad", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@p_bank", SqlDbType.VarChar).Value = p_bank;
        cmd.Parameters.Add("@p_pay_date", SqlDbType.VarChar).Value = p_pay_date;
        cmd.Parameters.Add("@p_bill_id", SqlDbType.VarChar).Value = p_bill_id;
        cmd.Parameters.Add("@p_payment_id", SqlDbType.VarChar).Value = p_payment;
        cmd.Parameters.Add("@p_ref_code", SqlDbType.VarChar).Value = p_ref_code;
        cmd.Parameters.Add("@p_branch", SqlDbType.VarChar).Value = p_branch;
        cmd.Parameters.Add("@p_channel_type", SqlDbType.VarChar).Value = p_channel;
        cmd.Parameters.Add("@p_send_date", SqlDbType.VarChar).Value = p_send_date;

        con.Open();
        reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            //TempCode = reader["PaymentID"].ToString();
        }
    }
}

That stored procedure sometimes return ErrorNumber in result and sometimes it returns PaymentID. How can I check this scenario?

if( reader has ErrorNumber field) then
    do something
else
    do something else

Thanks all.

See this on how to get column names: http://stackoverflow.com/questions/681653/can-you-get-the-column-names-from-a-sqldatareader and then you can write a simple loop/if to see what columns exist. — Peter B, Dec 28 '16 at 09:51

score 0 · Accepted Answer · answered Dec 28 '16 at 09:51

Not sure how exactly you can distinguish these two columns returned - if the column is present or missing (depending on the situation), then you can check for the presence of the column:

while (reader.Read())
{
    try
    {
        int paymenIdPos = reader.GetOrdinal("PaymentID");

        // if found --> read payment id 
        int paymentID = reader.GetInt32(paymenIdPos);
    }
    catch(IndexOutOfRangeException)
    {
        // if "PaymentID" is not found --> read the "ERrorNumber"
        int errorCode = reader.GetInt32("ErrorNumber");
    }           
}

score 0 · Answer 2 · answered Dec 28 '16 at 10:13

you can check with GetOrdinal, as marc_s suggested, or like this:

if (reader.GetSchemaTable().Select("ColumnName = 'PaymentId'").Length > 0)
{
    //do something here with pamynet
}
else if (reader.GetSchemaTable().Select("ColumnName = 'ErrorNumber'").Length > 0)
{
     //do your stuff here with error number
}

How can i check what is variable into the SqlDataReader?

2 Answers2