SqlDataReader into List<> - Quickest way

Question

When I am reading data from a sql db and want to add all the items into a list (ie. eonumlist) below. Do I need to specifically assign each field or can I mass assign this data? I'm doing this for a report and want to get the data quickly. Maybe I should use a dataset instead. I have 40+ fields to bring into the report and want to do this quickly. Looking for suggestions.

public static List<EngOrd> GetDistinctEONum()
{
    List<EngOrd> eonumlist = new List<EngOrd>();
    SqlConnection cnn = SqlDB.GetConnection();
    string strsql = "select distinct eonum " +
        "from engord " +
        "union " +
        "select 'zALL' as eonum " +
        "order by eonum desc";
    SqlCommand cmd = new SqlCommand(strsql, cnn);
    try
    {
        cnn.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            EngOrd engord = new EngOrd();
            engord.EONum = reader["eonum"].ToString();
            engord.Name = reader["name"].ToString();
            engord.Address = reader["address"].ToString();
            eonumlist.Add(engord);
        }
        reader.Close();
    }
    catch (SqlException ex)
    {
        throw ex;
    }
    finally
    {
        cnn.Close();
    }
    return eonumlist;                
}

When you say "quickly" are you talking in terms of coding time, or execution time? Why are you catching an exception, only to throw it again? And why are you explicitly closing the connection rather than just using a `using` statement? (Ideally, use a `using` statement for the command and the data reader, too.) — Jon Skeet, Dec 10 '13 at 13:54
Sidenote: Familiarize yourself with the `using` statement and with rethrowing exceptions. — usr, Dec 10 '13 at 13:57
Get rid of that `catch` clause. You're just screwing up your exception stack trace. — John Saunders, Dec 10 '13 at 13:58
I am not familiar with the using statement. Can you easily provide a simple example. Otherwise, I'll do some searching. Thanks. — maverick07281975, Dec 10 '13 at 13:58
John - can you provide an example on how you would recode it? I asked this question because I don't want to have to manually type 40+ fields here to get data from the reader into the list... any ideas on that too? — maverick07281975, Dec 10 '13 at 14:00
I bet it would have been a lot quicker than writing the question. The mistakes you have are not related to how you are reading the fields, you can do the 40 fields, just do it in a mapper function and call it in your while, and fix the try catch problem. — Luis Tellez, Dec 10 '13 at 14:08
Create a Wrapper that accepts a class and uses the properties (Reflection) of the class to read the SQLDataReader (you will still have to create the properties) i believe there is already something on nuget that does this but i forget the name atm (here is something i have written although the code has been altered since) http://stackoverflow.com/questions/15667008/trying-to-create-generic-type-reader-using-reflection — RoughPlace, Dec 10 '13 at 14:22

PowPowPowell · Accepted Answer · 2013-12-10T14:31:10.727

I do something similar storing data from a db into a combo box.

To do this i use the following code.

 public static void FillDropDownList(System.Windows.Forms.ComboBox cboMethodName, String myDSN, String myServer)
        {
            SqlDataReader myReader;
            String ConnectionString = "Server="+myServer+"\\sql2008r2;Database="+myDSN+";Trusted_Connection=True;";

            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                cn.Open();
                try
                {
                    SqlCommand cmd = new SqlCommand("select * from tablename", cn);
                    using (myReader = cmd.ExecuteReader())
                    {
                        while (myReader.Read())
                        {
                            cboMethodName.Items.Add(myReader.GetValue(0).ToString());
                        }
                    }
                }
                catch (SqlException e)
                {
                    MessageBox.Show(e.ToString());
                    return;
                }
            }
        }

This connects to the database and reads each record of the table adding the value in column 0 (Name) to a combo box.

I would think you can do something similar with a list making sure the index values are correct.

It's a sad example of mixing application layers. Don't do it. — PepitoSh, Mar 20 '19 at 08:56

score -1 · Answer 2 · answered Dec 10 '13 at 14:09

-1

Store the data as xml, then deserialize the xml to the list.

answered Dec 10 '13 at 14:09

conway

9
3

The man asked for "quickly." Why build a normalized datatable? Take a list of objects [List] and serialize it to string. Store that string. When you want the list again get the string from the database and deserialize. You can use this method to manage dozens of lists of complex objects where the list is not likely to grow exponentially. Once you've built your data access methods (so as to be able to store xml strings against an ID for the type -- I use a simple enum -- you can create a new custom object, create and populate the list and store and retrieve it in few minutes. – conway Dec 10 '13 at 16:20

SqlDataReader into List<> - Quickest way

2 Answers2