C# - Trying to add each value from a database into a list, but it is only adding the final value?

Question

Here is my code:

        private List<string> readFromDatabase()
    {
        SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=" + fileName + ".sqlite;Version=3;");
        string sql = "SELECT * FROM formsData";

        m_dbConnection.Open();

        SQLiteDataReader dr = DatabaseHandler.ExecuteCommandReader(sql, m_dbConnection);

        List<string> tempList = new List<string>();

        if(dr.HasRows)
        {
            while (dr.Read())
            {
                tempList.Add(Convert.ToString(dr["fieldName"]));
                tempList.Add(Convert.ToString(dr["dataType"]));
                tempList.Add(Convert.ToString(dr["numberOfCharacters"]));
            }
        }
        return tempList;
    }

I am trying to make it add each value from the database into the list, however it is only adding the last value found, from the final column. Does anyone know how to solve this? Thank you...

When you say "final column", do you mean the bottommost record? — Mr Lister, Jan 06 '17 at 16:12
If you put break points into your loop do you see it adding all three items to tempList? Do you see it going through that loop for every line of your input? Also what are you using to test your list? Could it actually be that your code that consumes the list is broken? I can't see how this code could fail to add multiples of three things so the idea it only has one value seems wrong. — Chris, Jan 06 '17 at 16:13
When you execute that exact query using Sql Mgmt Studio, do you get more than 1 record? — CodingYoshi, Jan 06 '17 at 16:16
That doesn't fix your issue but you should provide a custom type to store the multiple properties/columns. For example a class `FormsData` with properties `FieldName`,`DataType` and `NumberOfCharacters`. Then fill a `List` in the loop. Otherwise your list stores only strings and one record in the database is splitted into three independent strings in the list. — Tim Schmelter, Jan 06 '17 at 16:16
Speaking of best practices, I don't like how this function opens the connection, but doesn't close it. And reading `*` columns from the database, but using only three fields, hm. — Mr Lister, Jan 06 '17 at 16:17
If we are talking about best practices then I believe its generally considered best practice to explicitly name the columns you want rather than using `SELECT *`. It minimizes the amount of data being passed around in the case there are more columns (and even if there aren't now more columns might be added in the future). — Chris, Jan 06 '17 at 16:18
@Chris I have created a textbox to print out the list when a button is pressed... I have used: { List readerList = readFromDatabase(); foreach(string s in readerList) { htmlTxtBox.Text = string.Join(" ", s); } — leightonblackburn, Jan 06 '17 at 16:55
@TimSchmelter I have used a class before, however just for now I am trying to use a list, and then later I will change it to use the class I've created. — leightonblackburn, Jan 06 '17 at 16:56
@MrLister by "final Column" I mean the very last record from the last column, "numberOfCharacters" — leightonblackburn, Jan 06 '17 at 16:58
@leightonblackburn: Your problem is with that output code. You need to join your strings and then put it in the text box. At the moment you are looping through the list and putting the entries in your text box but you aren't appending them, you are just replacing what is already there. What you probably wanted was just `htmlTxtBox.Text = string.Join(" ", readerList);` instead of the foreach loop. — Chris, Jan 06 '17 at 17:03
Voting to close because the code you have asked about is in fact working fine. — Chris, Jan 06 '17 at 17:03
@leightonblackburn: Lesson learned. If code is outputting unexpected stuff and you can't explain it then make sure it really is outputting something inexpected and its not just that you're looking at it wrong. Its also why using a debugger to examine things is often better (and should always be used as part of your debugging process). — Chris, Jan 06 '17 at 17:12

score 0 · Answer 1 · edited May 23 '17 at 12:01

you can use a DataTable instead of DataReader (even though its possible to achieve the same result with DataReader).
please check that discussion: Why is DataTable faster than DataReader.
also its a better practice to use Try-Catchsteatments when connecting to databases.

here is an example (based on your code) how to achieve that task usingDataTable:

private List<string> readFromDatabase()
    {
        DataTable dt = PullData();
        List<string> tempList = new List<string>();

        if (dt != null & dt.Rows.Count >0)
        {
            for (int i = 0; i < dt.Rows.Count; i++)

            {
                tempList.Add(Convert.ToString(dt.Rows[i]["fieldName"]));
                tempList.Add(Convert.ToString(dt.Rows[i]["dataType"]));
                tempList.Add(Convert.ToString(dt.Rows[i]["numberOfCharacters"]));
            }
        }

        return tempList;
    }


    public DataTable PullData()
    {

        try
        {
            string connString = @"your connection string here";
            string query = "select * from table";
            DataTable dataTable = new DataTable();

            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(query, conn);
            conn.Open();

            // create data adapter
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            // this will query your database and return the result to your datatable
            da.Fill(dataTable);
            conn.Close();
            da.Dispose();
            return dataTable;
        }

        catch (Exception ex)
        {
            return null;
        }

    }

score 0 · Answer 2 · answered Jan 06 '17 at 17:02

Make sure your record set is positioned at the first row before you start iterating through it.

Also, do you really want to take all the rows of the table and have them as sequential values in a list? Perhaps adding them to class might make more sense.

    public class Record{
      public string Name { get; set; }
      public string Type { get; set; }
      public int Size { get; set; }
    }

C# - Trying to add each value from a database into a list, but it is only adding the final value?

2 Answers2