c# - Dataset from database is empty

Question

I'm accessing a table from my database. I have added the tables as a dataset in Visual Studio 2013, but when I try to use it, it comes out empty.

This is what I'm trying to do:

IQueryable<NorthwindDataSet.OrdersRow> LookupOrdersForYear(int year)
{
    using (var context = new NorthwindDataSet())
    {
        var orders =
            from order in context.Orders
            where order.OrderDate != null && order.OrderDate.Year >= year
            select order;
        return orders.ToList().AsQueryable();
    }
}

I found out that orders was empty, so I added

Console.WriteLine(context.Orders.Count);

which gave me an output of 0. What went wrong?

Answer 1

I found out that I needed to fill my dataset with data, or it would stay empty.

A SqlDataAdapter did the trick:

SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand command = new SqlCommand("SELECT * FROM Orders");
string connString = @"Data Source=localhostSQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";
SqlConnection conn = new SqlConnection(connString);

adapter.SelectCommand = command;
adapter.SelectCommand.Connection = conn;
adapter.Fill(context.Orders);

---

There are several overloaded methods for SqlDataAdapter.Fill, of which one takes a dataset and another takes a datatable. When I first used the SqlDataAdapter, I used the one that takes a dataset

adapter.Fill(context);

which still gave me an empty context.Orders. The correct one takes a datatable:

adapter.Fill(context.Orders);

This one worked and returned the dates as expected.