Using SqlDataReader to fill an ArrayList?

Question

I'm trying to implement a method which will take a given connection string and return an ArrayList containing the contents of a SQL view.

I've verified the validity of the connection string and the view itself. However I don't see what the problem is in the code below. In debug, when it runs the ExecuteReader method and then try to enter the while loop to iterate through the records in the view, it immediately bails because for some reason sqlReader.Read() doesn't.

    public ArrayList GetEligibles(string sConnectionString)
    {
        string sSQLCommand = "SELECT field1, field2 FROM ViewEligible";

        ArrayList alEligible = new ArrayList();

        using (SqlConnection sConn = new SqlConnection(sConnectionString))
        {
            // Open connection.
            sConn.Open();

            // Define the command.
            SqlCommand sCmd = new SqlCommand(sSQLCommand, sConn);

            // Execute the reader.
            SqlDataReader sqlReader = sCmd.ExecuteReader(CommandBehavior.CloseConnection);

            // Loop through data reader to add items to the array.
            while (sqlReader.Read()) 
            {
                EligibleClass Person = new EligibleClass();

                Person.field1 = sqlReader["field1"].ToString();
                Person.field2 = sqlReader["field2"].ToString();

                alEligible.Add(Person);
            }

            // Call Close when done reading.
            sqlReader.Close();
        }

        return alEligible;
    }

Note, EligibleClass is just a class object representing one row of the view's results.

Answer 1

A couple of things I would check:

1. Is the connection string ok
2. Does the user in your connection string have access to the database/view
3. Can you access that database from the pc your at
4. Does the ViewEligable view exist
5. Does the view contain a field1 and field2 column.

Here one way you could possibly clean up that code somewhat (assuming you have .net 2.0)

    public List<EligibleClass> GetEligibles(string sConnectionString)
    {
        List<EligibleClass> alEligible = null;

        try
        {
            using (SqlConnection sConn = new SqlConnection(sConnectionString))
            {
                sConn.Open();
                using (SqlCommand sCmd = new SqlCommand())
                {
                    sCmd.Connection = sConn;
                    sCmd.CommandText = "SELECT field1, field2 FROM ViewEligible";

                    using (SqlDataReader sqlReader = sCmd.ExecuteReader())
                    {
                        while (sqlReader.Read())
                        {
                            EligibleClass Person = new EligibleClass();

                            Person.field1 = sqlReader.GetString(0);
                            Person.field2 = sqlReader.GetString(1);

                            if (alEligible == null) alEligible = new List<EligibleClass>();
                            alEligible.Add(Person);
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
           // do something.
        }

        return alEligible;
    }