How to get data from DB, store as List<>, and display the results to view

Question

I am trying to get data as a list from the database but it is not showing any results. I tried to debug but after this line it doesn't let me to step over / F10:

DataSet ds = new DataSet();
da.Fill(ds);

I am trying to do this by following this example on here: link 1 and here link 2 but finding it difficult hence I thought that I should ask here.

Can someone please explain why it is not displaying the results and as such what I may be doing wrong here? how do I work around and achieve it to display the data?

Here's the full controller code for your inspection:

public static List<DBTrack> GetListOfTracks()
{
    if (DBTrackData == null)
    {
       string myConnectionString = "Data Source="; // I have taken off the source
       string mySelectString = "SELECT TrackID, AddedDate, TrackName, ArtistName from TBL_Track";
       SqlDataAdapter da = new SqlDataAdapter();
       DataSet ds = new DataSet();
       da.Fill(ds);

       OleDbConnection myConnection = new OleDbConnection(myConnectionString);
       OleDbCommand myCommand = new OleDbCommand(mySelectString, myConnection);
       myCommand.Connection.Open();
       OleDbDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

       List<DBTrack> list = new List<DBTrack>();
       while (myReader.Read())
       {
           DBTrack data = new DBTrack();
           data.TrackID = (Guid)(myReader["TrackID"]);
           data.AddedDate = (DateTime)myReader["AddedDate"];
           data.TrackName = (string)myReader["TrackName"];
           data.ArtistName = (string)myReader["ArtistName"];
           list.Add(data);
       };
    }

   return DBTrackData;
}

EDIT:

SqlConnection mySQLconnection = new SqlConnection(@"Data Source=server-2\SQLExpress;Initial Catalog=End;Integrated Security=False;User ID=test1;Password=**");
SqlCommand mySelectString = new SqlCommand("SELECT TrackID, AddedDate, TrackName, ArtistName from TBL_Track");

using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();

using (SqlCommand myCommand = new SqlCommand // The best overload method System.Data.SqlClient.SqlCommand.SqlCommand has some invalid arguments
(mySelectString, mySQLconnection))           // Cannot convert from System.Data.SqlClient.SqlCommand to 'string' 
 {

Answer 1

You should set the DBTrackData to the result of the list:

while (myReader.Read())
{
    ...
}

DBTrackData = list;

You could also directly edit the DBTrackData in your code:

private static List<DBTrack> DBTrackData
public static List<DBTrack> GetListOfTracks()
{
    if (DBTrackData == null)
    {
       ...

       DBTrackData = new List<DBTrack>();
       while (myReader.Read())
       {
           DBTrack data = new DBTrack();

           ...

           DBTrackData.Add(data);
       };
    }

   return DBTrackData;
}

So, in full it should be:

public static List<DBTrack> GetListOfTracks()
{
    try
    {
        if (DBTrackData == null)
        {
            string myConnectionString = "Data Source="; // I have taken off the source
            string mySelectString = "SELECT TrackID, AddedDate, TrackName, ArtistName from TBL_Track";

            using (OleDbConnection myConnection = new OleDbConnection(myConnectionString))
            {
               myConnection.Open();

               using (OleDbCommand myCommand = new OleDbCommand(mySelectString, myConnection))
               {
                   OleDbDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

                   List<DBTrack> list = new List<DBTrack>();

                   while (myReader.Read())
                   {
                       DBTrack data = new DBTrack();
                       data.TrackID = (Guid)(myReader["TrackID"]);
                       data.AddedDate = (DateTime)myReader["AddedDate"];
                       data.TrackName = (string)myReader["TrackName"];
                       data.ArtistName = (string)myReader["ArtistName"];

                       list.Add(data);
                   }

                   DBTrackData = list;
               }
            }
        }

        return DBTrackData;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

    return DBTrackData;
}

Answer 2

Your main problem is that you are using two different ways of getting the data, but the first is only partially implemented. You are using a data adapter and a data reader. Your data adapter isn't even being passed a connection or query, so this is why it is not working. So you can just remove that part of the code.

It's also easier to read if you immediately return when DBTrackData is not null rather than have a big if block over the whole code. You will then have something like:

public static List<DBTrack> GetListOfTracks()
{
   if (DBTrackData != null) return DBTrackData;

   string myConnectionString = "Data Source="; // I have taken off the source
   string mySelectString = "SELECT TrackID, AddedDate, TrackName, ArtistName from TBL_Track";

   OleDbConnection myConnection = new OleDbConnection(myConnectionString);
   OleDbCommand myCommand = new OleDbCommand(mySelectString, myConnection);
   myCommand.Connection.Open();
   OleDbDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

   List<DBTrack> list = new List<DBTrack>();
   while (myReader.Read())
   {
       DBTrack data = new DBTrack();
       data.TrackID = (Guid)(myReader["TrackID"]);
       data.AddedDate = (DateTime)myReader["AddedDate"];
       data.TrackName = (string)myReader["TrackName"];
       data.ArtistName = (string)myReader["ArtistName"];
       list.Add(data);
   };

   //Setting DBTrackData means these values will get returned on every call to GetListOfTracks for this instance of the class
   DBTrackData = list;

   return list;
}