CODEWITHSUNDEEP
sql-serverasp.net-mvcado.net
user2541688
user2541688

Reputation:

SQL Server query in ADO.NET only returning one result

I'm learning asp.net mvc with ado.net, I've got this method in my repository class

public IEnumerable<int?> GetAllUniqueYears()
{
    using (IDbConnection dbConnection = dbConnectionFactory.CreateConnection())
    {
        using (IDbCommand cmd = dbConnection.CreateCommand())
        {
            cmd.CommandText = "SELECT DISTINCT YEAR(DateAdded) As Year FROM GeoCounters";
            cmd.CommandTimeout = 1000;

            using (IDataReader reader = cmd.ExecuteReader())
            {
                if (!reader.Read())
                {
                    yield return null;
                }
                else
                {
                    yield return (int)reader["Year"];
                }
            }
        }
    }
}

When I load my view it's only got a single result, but when I run the query in SQL Server Management Studio there are 4 results

The controller action 

public ActionResult Index()
{
        HomeIndexViewModel model = new HomeIndexViewModel()
        {
            GeoCounterDefinitions = geodefrepo.GetAll()
            .Select((x,y) => new SelectListItem
                {
                    Text = x.Id + " " + x.FriendlyDesc,
                    Value = (y + 1).ToString()
                }),
            Years = geocounterrepo.GetAllUniqueYears()
            .Select(x => new SelectListItem
            {
                Text = x.ToString(),
                Value = x.ToString()
            })
        };

        return View(model);
    }

Upvotes: 0

Views: 50

Answers (1)

Win
Win

Reputation: 62270

You need a while loop.

using (IDataReader reader = cmd.ExecuteReader())
{
   while (reader.Read())
   {
      yield return (int)reader["Year"];
   }
}

Upvotes: 2

Related Questions