Sqldatareader and rowcount

Question

Hi,

I have a query:

declare @Code nvarchar(100)
select @Code="BMW"
select name from NewCars where code=@Code
if @@rowcount = 0
Select name from OldCars where code=@Code

In Sql managment studio first part give me 0 resuklts, and second 1 one result, and that is ok, but in sqldatareader I use the same query ofcource without:

declare @Code nvarchar(100)
select @Code="BMW"

because I use:

cmd.Parameters.AddWithValue("@Code", "BMW");

And

 using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Name= reader["Name"].ToString();
                            }
                        }
                        else
                        {
                            throw new NotSupportedException("Lack of car with this Code");
                        }
                    }

gives me zero result

Answer 1

The database results multiple results, so if the first result is empty you would need to use reader.NextResult() to move on to the second result.

You can also make the query return a single results by checking if the first select would contain anything:

declare @Code nvarchar(100)
select @Code="BMW"
if (exists(select * from NewCars where code=@Code)) begin
  select name from NewCars where code=@Code
end else begin
  select name from OldCars where code=@Code
end