Microsoft.ApplicationBlocks. SqlHelper.ExecuteReader issue.

Yes, I had issues with the said title. This is specifically when a reader is declared under a SqlTransaction. In the end when the transaction also includes insert/delete and updates, the changes will not reflect.

Please look at this wrong code.

        public ReturnedCheckEntity SelectByID(int id)
        {
        
            if(WithTransaction)
            {
                using(SqlDataReader reader = SqlHelper.ExecuteReader(
                    Transaction,
                    _commandSelectByID,
                    id))
                {
                    return ProcessRow(reader);
                }
 
            }
            else
            {
                using(SqlDataReader reader = SqlHelper.ExecuteReader(
                    ConnectionString,
                    _commandSelectByID,
                    id))
                {
                    return ProcessRow(reader);
                }
 
            }
        }

This now is the right code.

public ReturnedCheckEntity SelectByID(int id)
        {
            if (WithTransaction)
            {
 
                using (SqlCommand cmd = new SqlCommand(_commandSelectByID, Connection, Transaction))
                {
 
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@ID", id));
 
                    using
                    (
                        SqlDataReader r =
                            cmd.ExecuteReader
                                ()
                    )
                    {
 
                        return ProcessRow(r);
 
                    }
                }
 
            }
            else
            {
                using
                    (
                        SqlDataReader r =
                            SqlHelper.ExecuteReader
                                (
                                    ConnectionString
                                    , System.Data.CommandType.StoredProcedure
                                    , _commandSelectByID
                                    , new SqlParameter("@ID", id)
                                )
                    )
                {
 
                    return ProcessRow(r);
 
                }
            }
        }

You must not use SqlHelper’s Execute reader instead use the native SqlCommand object for creating a SqlDataReader.

Happy Coding!