CODEWITHSUNDEEP
c#sql-server-2005t-sql
user278618
user278618

Reputation: 20242

Sqldatareader and rowcount

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

Upvotes: 1

Views: 763

Answers (1)

Guffa
Guffa

Reputation: 700212

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

Upvotes: 3

Related Questions