title: Getting specific column data from database in C#
body: <p>I have created a search function that returns product data in a <code>DataGridView</code> (data is coming from local database), it does work but I need to have control over returned data in my grid view.</p>
  <h3 id="current-behavior-junr">Current behavior</h3>
  <p>It returns all columns of database row in <code>DataGridView</code></p>
  <h3 id="what-i-want-ndo6">What I want</h3>
  <ol>
  <li>Returning only 2 or 3 columns of searched item instead of all columns</li>
  <li>Create new list of searched items so I can edit those returned data</li>
  </ol>
  <h3 id="logic-ja6r">Logic</h3>
  <ol>
  <li>Search for product</li>
  <li>Get product <em>name and price from database</em>, <em>add custom quantity field</em> make new list and add this item to show in <code>DataGridView</code></li>
  <li>Be able to change quantity field in <code>DataGridView</code></li>
  </ol>
  <h3 id="code-mzcp">Code</h3>
  <p>Here is my search code that returns all columns of product table</p>
  <pre><code>using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings[&quot;SampleDatabaseWalkthrough.Properties.Settings.SampleDatabaseConnectionString&quot;].ConnectionString))
  {
      if (cn.State == ConnectionState.Closed)
          cn.Open();
      using (DataTable dt = new DataTable(&quot;Products&quot;))
      {
          using (SqlCommand cmd = new SqlCommand(&quot;select * from Products where Id=@Id or Name like @Name&quot;, cn))
          {
              cmd.Parameters.AddWithValue(&quot;Id&quot;, searchBox.Text);
              cmd.Parameters.AddWithValue(&quot;Name&quot;, string.Format(&quot;%{0}%&quot;, searchBox.Text));
              SqlDataAdapter adapter = new SqlDataAdapter(cmd);
              adapter.Fill(dt);
  
  
              // Following line will show all columns of founded product and replace it with next search result
                                                                             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
              selectedItems.DataSource = dt;
          }
      }
  }
  </code></pre>
  <blockquote>
  <p>PS: Code above finds products based on <code>id</code> or <code>name</code> entered in search field and return all columns of product row.</p>
  </blockquote>
  <h3 id="idea-kahh">Idea:</h3>
  <p>I think I can be able to create my list after finding product but the issue I'm facing is that I'm not sure how to get those specific columns from my <code>dt</code> (table row),</p>
  <p>Here is what I've tried and failed (commented)</p>
  <pre><code>using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings[&quot;SampleDatabaseWalkthrough.Properties.Settings.SampleDatabaseConnectionString&quot;].ConnectionString))
  {
      if (cn.State == ConnectionState.Closed)
          cn.Open();
      using (DataTable dt = new DataTable(&quot;Products&quot;))
      {
          using (SqlCommand cmd = new SqlCommand(&quot;select * from Products where Id=@Id or Name like @Name&quot;, cn))
          {
              cmd.Parameters.AddWithValue(&quot;Id&quot;, searchBox.Text);
              cmd.Parameters.AddWithValue(&quot;Name&quot;, string.Format(&quot;%{0}%&quot;, searchBox.Text));
              SqlDataAdapter adapter = new SqlDataAdapter(cmd);
              adapter.Fill(dt);
              //selectedItems.DataSource = dt; //&lt;-- changed with lines below
  
  
              List&lt;string&gt; items = new List&lt;string&gt;();
              items.Add(dt); // &lt;-- here is the issue (it expect to get string of my table row let say: Price column, but I don't know how to get Price column value from dt)
              selectedItems.DataSource = items;
          }
      }
  }
  </code></pre>
  <blockquote>
  <p>PS: code above is just idea and obviously I am not sure if it is best way to do it or not that's why I'm asking here :)</p>
  </blockquote>
  <p><strong>Any suggestions?</strong></p>
  <h3 id="update-y5bk">Update</h3>
  <p>I've created new class and added my data to that class as following</p>
  <pre><code>public class Item
  {
    public int Id { get; set; }
    public string Name { get; set; }
    public string Price { get; set; }
    public string Qty { get; set; }
  }
  </code></pre>
  <p>Then in my query I added</p>
  <pre><code>List&lt;Item&gt; itemList = new List&lt;Item&gt;();
  for (int i = 0; i &lt; dt.Rows.Count; i++)
  {
    Item item = new Item();
    item.Id = Convert.ToInt32(dt.Rows[i][&quot;Id&quot;]);
    item.Name = dt.Rows[i][&quot;Name&quot;].ToString();
    item.Price = dt.Rows[i][&quot;SellPrice&quot;].ToString();
    item.Qty = dt.Rows[i][&quot;Qty&quot;].ToString();
    itemList.Add(item);
  }
  selectedItems.DataSource = itemList;
  </code></pre>
  <p>Now it does return data in my <code>selectedItems</code> but when I search for next product instead of adding it to the list it replace it with first product.</p>