title: How can I use TableDirect for SQL Server CE?
body: <p>I have code that works for querying data from a SQL Server CE table and populating a generic list with it. That can be seen <a href="https://stackoverflow.com/questions/20412042/is-this-the-right-way-to-query-a-sql-server-ce-table-for-a-record-populating-an/20427526?noredirect=1#20427526">here</a>: </p>
  
  <p>But a comment there indicates I should trade in my horse-and-buggy for a Leer Jet; so, I'm trying to adapt code I found <a href="http://blog.ropardo.ro/2011/05/17/parametrized-queries-vs-table-direct-mode-sql-server-ce/" rel="nofollow noreferrer">here</a> and have this so far: </p>
  
  <pre><code>public static List&lt;HHSUtils.InventoryItem&gt; SelectLocalInventoryItemsTableDirect()
  {
      var invItems = new List&lt;HHSUtils.InventoryItem&gt;();
      using (var conn = new SqlCeConnection(dataSource))
      {
          conn.Open();
          SqlCeCommand cmd = conn.CreateCommand();
          cmd.CommandType = CommandType.TableDirect;
          cmd.CommandText = "InventoryItems";
          using (SqlCeResultSet rs cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
          {
              cmd.Prepare();
              while (rs.Read())
              {
                  var invItem = new HHSUtils.InventoryItem
                  {
                      Id = Convert.ToString(rs["Id"]),
                      PackSize = Convert.ToInt16(rs["PackSize"]),
                      Description = Convert.ToString(rs["Description"]),
                      DeptDotSubdept = Convert.ToDouble(rs["DeptDotSubdept"]),
                      Unit_Cost = Convert.ToDouble(rs["UnitCost"]),
                      Unit_List = Convert.ToDouble(rs["UnitList"]),
                      UPC_code = Convert.ToString(rs["UPCCode"]),
                      UPC_pack_size = Convert.ToInt16(rs["UPCPackSize"]),
                      CRV_Id = Convert.ToInt32(rs["CRVId"])
                  };
                  invItems.Add(invItem);
              }
          }
      }
      return invItems;
  }
  </code></pre>
  
  <p>...but since I'm simply looping through the result set to populate the generic list, I reckon I don't want ResultSetOptions.Updatable (and I'll need different code following that). Of the following possibilities:</p>
  
  <pre><code>Insensitive
  None
  Scrollable
  Sensitive
  Updatable
  </code></pre>
  
  <p>...which is best for my situation - Scrollable?</p>
  
  <h2>UPDATE</h2>
  
  <p>This seems to work fine, and fast, but I still don't know which ResultSetOptions property is optimal...<a href="http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.resultsetoptions%28v=vs.100%29.aspx" rel="nofollow noreferrer">This msdn article</a> talks about this enumeration, but doesn't exactly go into great depth about when they should/not be used.</p>