Is there a better way to do a List of Objects?

I am trying to create a list of objects. Is there a better way?

   // List
    public List<page> Select()
    {
      List<page> _list = new List<page>();
      string  SqlStatement = "select * from Pages";
      SqlConnection thisConnection = new SqlConnection(connStr);
      // Open the Connection
      thisConnection.Open();

      var thisCommand = thisConnection.CreateCommand();
      thisCommand.CommandText = SqlStatement;
      SqlDataReader thisReader = thisCommand.ExecuteReader();

      while (thisReader.Read())
      {
        // Create a new instance of the Current Page Object
        page currentPage = new page();
        // Fill the instance of the Current Page Object
        currentPage.PageID = Convert.ToInt32(thisReader["PageID"]);
        currentPage.ParentID = Convert.ToInt32(thisReader["ParentID"]);
        currentPage.CategoryID = Convert.ToInt32(thisReader[开发者_C百科"CategoryID"]);
        currentPage.Name = thisReader["Name"].ToString();
        currentPage.PageHTMLContent = thisReader["PageHTMLContent"].ToString();
        currentPage.NavigationText = thisReader["NavigationText"].ToString();
        currentPage.TopMenu = Convert.ToBoolean(thisReader["TopMenu"]);
        currentPage.SubMenu = Convert.ToBoolean(thisReader["SubMenu"]);
        currentPage.DisplayOrder = Convert.ToInt32(thisReader["DisplayOrder"]);
        currentPage.Active = Convert.ToBoolean(thisReader["Active"]);
        // Add the instance of the Current Page Object to the List<>.
        _list.Add(currentPage);
      }
      // Close the Database
      thisConnection.Close();
      return _list;      

    }

Using LINQ to Datasets might make your code a little more readable. You should also be sure to wrap your objects with using statements where possible:

public List<page> Select()
{
    var sqlStatement = "select * from pages";

    var sqlResults = new DataTable();

    using(SqlConnection conn = new SqlConnection(connStr))
    {
        using(SqlCommand command = new SqlCommand(sqlStatement, conn))
        {
            var adapter = new SqlDataAdapter(command);
            adapter.Fill(sqlResults);
        }
    }

    return sqlResults.AsEnumerable().Select(r => new page {
               PageID = r.Field<int>("PageID"),
               ParentID = f.Field<int>("ParentID"),
               CategoryID = r.Field<int>("CategoryID"),
               Name = r.Field<string>("Name"),
               PageHtmlContent = r.Field<string>("PageHTMLContent"),
               // Fill the rest of the properties
               Active = r.Field<bool>("Active")
           }).ToList();
}

Well, the easiest way is to use some kind of ORM (NHibernate, EF, etc).

If you have to pull it from the db and map it yourself, the main things I would change are:

1) Wrap your SqlConnection and SqlCommand objects in using(){} blocks.
2) Don't use Select *, call out your specific columns.
3) If you can, use a stored procedure instead of an inline sql statement.

Automapper may be able to help. I haven't used it yet, but it seems to do something very similar to this. There are also lots of other ORM solutions out there to map data to objects. NHibernate is a popular one.

I saw this earlier (Microsoft.Data.dll), still can't believe it will work.

var db = Database.OpenConnectionString(connString);
for(var row in db.Query("select * from Pages"))
{
   page currentPage = new page();
   currentPage.PageID  = row.PageID;
}

NOTE: I don't think this is smart in enterprise design... but if you want something quick and dirty....

I would recommend doing EF in this case and just mapping the table to an entity.

Seeing how all your property names match your column names, you could just loop through each column in the reader and use reflection to set it to the pages relevant property?