How can I tell if my SqlCeDataReader contains any vals after calling SqlCeCommand.ExecuteReader()?

Question

With the code below:

SqlCeDataReader myReader = cmd.ExecuteReader(CommandBehavior.SingleRow);
itemID = myReader.GetString(0);
packSize = myReader.GetString(1);

...I get an exception, "No data exists for the row/column"

I want it to silently "abort" rather than throwing an exception in this case. Is there a way to test first before the attempted assignment?

I tried this, but it does no good:

SqlCeDataReader myReader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (null != myReader.GetString(0))
{
    itemID = myReader.GetString(0);
}
if (null != myReader.GetString(1))
{
    packSize = myReader.GetString(1);
}

Another possibility would be to wrap it in a try..catch and "eat" the exception, but I don't know if that's the best way to go...

This is based on my knowledge of `SqlDataReader` rather than `SqlCeDataReader`, but in the former case you have to `.Read()` the first record first. A false (0) return indicates no more records. — Bob Kaufman, Mar 22 '13 at 18:05
Thanks; I had assumed calling ExecuteReader went and got everything. So I added "if (myReader.Read()) {}" Make your comment an answer, and I'll mark it as *the* answer. — B. Clay Shannon-B. Crow Raven, Mar 22 '13 at 18:20
Thanks for the offer. My response, as I suggested, was just a guess, which is why I placed it in the comments rather than the answers. Besides, I like @MD.Unicorn's additional check for .HasRows which had slipped my mind. — Bob Kaufman, Mar 22 '13 at 18:25

Mohammad Dehghan · Accepted Answer · 2013-03-22T18:27:49.710

4

This is because you didn't call Read() method after obtaining the DataReader. This does not mean that the result contains no data. It means that the row contains no data.

using (SqlCeDataReader myReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
    if (myReader.Read()) // It returns `false` if there is no data
    {
        itemID = myReader.GetString(0);
        packSize = myReader.GetString(1);
    }
}

If the result can contain multiple rows and you want to check to see if the result contains any data, use HasRows:

using (SqlCeDataReader myReader = cmd.ExecuteReader())
{
    if (myReader.HasRows)
    {
        while (myReader.Read())
        {
            // read values from `myReader`
        }
    }
}

edited Mar 22 '13 at 18:27

answered Mar 22 '13 at 18:14

Mohammad Dehghan

17,853
3
55
72

+1. For a single row, you can probably get away with just `if (myReader.Read()) { ... }`. – JosephHirn Mar 22 '13 at 18:15
@Ginosaji Oh! Of course! I didn't look at `CommandBehavior.SingleRow` and I thought it may have more than one row! – Mohammad Dehghan Mar 22 '13 at 18:21
Thanks; if BK doesn't make his answer an answer, which I already implemented, I'll mark this as such (even though I'm dubious that there are really Unicorns in Maryland). – B. Clay Shannon-B. Crow Raven Mar 22 '13 at 18:22
@Clay You're welcome. *M* and *D* are first letters of my first and last name. It has no relation to Maryland state! There are also non in my country! This is my nickname from old first days of programming! :) – Mohammad Dehghan Mar 22 '13 at 18:40

How can I tell if my SqlCeDataReader contains any vals after calling SqlCeCommand.ExecuteReader()?

1 Answers1