Returning Data Rows to List<string> with SqlDataReader
<p>I'm trying to create a generic SqlDataReader which converts a table with 33 columns into a list.  I would like each list item to contain all 33 column values for each row.  </p>

<p>However, my code is assigning each value to an individual list item.  </p>

<p>So instead of 1000 list items = 1000 rows of data, I have 33,000 list items. </p>

<p>I would prefer to use a list over a datatable, because the list comparisons I need to do are much simpler. </p>

<p>How can I have 1000 list items with 33 values each? </p>

<pre><code>   public static List&lt;string&gt; loadSQL(String query, String connectString)
    {
        List&lt;string&gt; dataList = new List&lt;string&gt;();

        using (SqlConnection connection = new SqlConnection(connectString))
        {
            using (SqlCommand command = new SqlCommand(query, connection))

            {
                connection.Open();

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {

                        for (int i = 0; i &lt; reader.FieldCount; i ++) 
                            { 
                                dataList.Add(Convert.ToString(reader.GetValue(i)));
                            }
                    }
                }
            }
        return dataList;

        }
    }
</code></pre>

<p>... update ... </p>

<p>corrected to the following. It returns the list items correctly. However, my list contains 33,000 items containing 33 items each.  How can I control the loop so it stops after 1000 rows? </p>

<pre><code>        using (SqlConnection connection = new SqlConnection(connectString))
        {
            using (SqlCommand command = new SqlCommand(query, connection))

            {
                connection.Open();

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {

                       List&lt;string&gt; tempRow = new List&lt;string&gt;();
                        for (int i = 0; i &lt; reader.FieldCount; i ++) 
                            { 
                                tempRow.Add(Convert.ToString(reader.GetValue(i)));
                            }
                        dataList.Add(tempRow);

                    }
                }
            }
        }
</code></pre>