Did you check for the contents of the datatable? Are there any rows?
If not there might be a problem in your select statement.
Now a few hints/suggestions.
Don't use data adaptares, datasets...
Use a datareader. Also use parameters.
Ex. from a test project I'm currently working on:
public static List<LinqClasses.Project> GetProjectsNonObs(bool obsolete)
{
List<LinqClasses.Project> projects = null;
LinqClasses.Project curr = null;
SqlConnection con = new SqlConnection(Utils.Connections.GetReadConnection());
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "PROJECTS_SelectAllNonObs";
cmd.Parameters.AddWithValue("@obs", obsolete);
SqlDataReader dr = null;
try
{
con.Open();
dr = cmd.ExecuteReader();
projects = new List<DataLayerLinq.LinqClasses.Project>();
while (dr.Read())
{
curr = new DataLayerLinq.LinqClasses.Project();
curr.ID = dr.GetInt32(0);
curr.Description = dr.GetString(1);
curr.PN = dr.GetString(2);
curr.AMG = dr.GetString(3);
curr.IsComponent = dr.GetBoolean(4);
curr.Obsolete = dr.GetBoolean(5);
curr.Price = dr.GetDouble(6);
projects.Add(curr);
}
}
catch (Exception ex)
{
Utils.Logs.Write(ex, cmd);
MessageBox.Show("Could not load all projects:\r\n" + ex.Message);
}
finally
{
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
con = null;
}
return projects;
}
Where Project is a class that maps the sql table.
Doing things this way you have many advantages:
first you could debug in a more detailed way, you could check for each
project that is beeing read, a speedier version and finally more control to you.