Returning Data Rows to List with SqlDataReader

Question

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.

However, my code is assigning each value to an individual list item.

So instead of 1000 list items = 1000 rows of data, I have 33,000 list items.

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

How can I have 1000 list items with 33 values each?

   public static List<string> loadSQL(String query, String connectString)
    {
        List<string> dataList = new List<string>();

        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 < reader.FieldCount; i ++) 
                            { 
                                dataList.Add(Convert.ToString(reader.GetValue(i)));
                            }
                    }
                }
            }
        return dataList;

        }
    }

... update ...

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?

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

            {
                connection.Open();

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

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

                    }
                }
            }
        }

Btw, You're returning `dataList` before you dispose your `connection` . — choz, Jun 27 '16 at 01:50

sujith karivelil · Accepted Answer · 2019-07-17T13:53:12.127

5

The best option for you to do this task is DataTable, But you don't want to use it. So, the net option will be, Create a class based on the query-output then use a List<objectOftheClass>. But in your case, the Input query will be changed all times so a common class will not be meaningful Since you are trying to make it generic. So the option you can follow is List<List<string>> or List<List<object>>. As per this the method signature will be like the following:

public static List<object[]> loadSQL(string query, string connectString)
{
    List<object[]> dataList = new List<object[]>();

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

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    object[] tempRow = new object[reader.FieldCount];
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        tempRow[i] = reader[i];
                    }
                    dataList.Add(tempRow);
                }
            }
        }
    }
    return dataList;
}

Why List<object>? why not `List?:

The reader will give you the column data as the same type of column in the table. If it is object then you need not convert it every time.

** Note:-** Change String to string for the arguments in the method signature. You can find a reason here

edited Jul 17 '19 at 13:53

answered Jun 27 '16 at 01:48

sujith karivelil

26,861
6
46
76

As in the other answer below, using the secondary list is not the best answer as the FieldCount is fixed so you can use an array. Either a List – Gary Holland Jun 27 '16 at 02:13
Thank you for the feedback. 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 – Richard Jun 27 '16 at 02:21
@Richard : I'm Little bit confused, Hope that the current code will give you 1000 list-Items each one contains 33 sub items, right? Then why you need to stop after 1000rows? – sujith karivelil Jun 27 '16 at 02:27
@un-lucky My database only contains 1000 rows. I want to have one list item per row, then 33 sub items which represent the columns – Richard Jun 27 '16 at 02:29
@GaryHolland : Yes you are correct. I have updated the answer. Now let me know is there any significance for the down-vote? – sujith karivelil Jun 27 '16 at 02:34
This is good. I can get the data into the List – KMR Mar 18 '20 at 09:19

Gary Holland · Answer 2 · 2016-06-27T02:29:44.040

0

You can use an array within the list to achieve what you are trying to do. Here is a quick example using your code:

public static List<string[]> loadSQL(String query, String connectString)
{
List<string[]> dataList = new List<string[]>();

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

    {
        connection.Open();

        using (SqlDataReader reader = command.ExecuteReader())
        {
            int rowcounter = 0;

            while (reader.Read())
            {
                string[] value = new string[reader.FieldCount];

                for (int i = 0; i < reader.FieldCount; i ++) 
                    { 
                        value[i] = Convert.ToString(reader.GetValue(i));
                    }

                dataList.Add(value);
                rowcounter++;
            }
        }
    }
return dataList;

}

Alertnately, if you want to use the List, you will need to embed the values a single string, using a comma separator or something similar.

edited Jun 27 '16 at 02:29

answered Jun 27 '16 at 01:47

Gary Holland

2,009
14
17

You can post it as a new question since it contains lots of syntax errors. like `object[i]=...` – sujith karivelil Jun 27 '16 at 02:06
Have you tested it? Still having syntax errors in your answer. Instead for downvoting other answers, correct yours first – sujith karivelil Jun 27 '16 at 02:19
Yes. Interested in your answer, but I'm receiving the error 'The name 'value' does not exist in the current context ' on dataList.Add(value); any idea why? – Richard Jun 27 '16 at 02:26
Sorry on mobile now so can't test. I think error was due to using object[] instead of string[]. Try now and I'll fix properly when I'm back in computer. – Gary Holland Jun 27 '16 at 02:30
@un-lucky I downvoted due to reason in comments you were declaring list where array was better answer. – Gary Holland Jun 27 '16 at 02:32

score 0 · Answer 3 · answered Jun 27 '16 at 01:48

You can use a List<List<string>> like this:

public static List<List<string>> loadSQL(String query, String connectString)
    {
        List<List<string>> dataList = new List<List<string>>();

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

            {
                connection.Open();

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var l = new List<string>();
                        for (int i = 0; i < reader.FieldCount; i ++) 
                            { 
                                l.Add(Convert.ToString(reader.GetValue(i)));
                            }
                        dataList.Add(l);
                    }
                }
            }
        return dataList;

        }
    }

I would suggest the List as the FieldCount is fixed, so there is no reason for the overhead of a second dynamic array. — Gary Holland, Jun 27 '16 at 01:52
Sorry, I should have been clearer. The FieldCount will need to be variable in future implementations.. — Richard, Jun 27 '16 at 02:23

Returning Data Rows to List with SqlDataReader

3 Answers3