45

I have a datatable. I need to fetch a certain column value based on the user input. For example, lets say the datatable has two columns CountryID and CountryName.

I need to find CountryID in the datatable based on the user input country name. I could just open a connection with DB and run the query select countryID from Country where countryName = @userinput. Is there anyway i could do this on the datatable.

John Saunders
  • 159,224
  • 26
  • 237
  • 393
peace
  • 797
  • 2
  • 11
  • 18

6 Answers6

75
string countryName = "USA";
DataTable dt = new DataTable();
int id = (from DataRow dr in dt.Rows
              where (string)dr["CountryName"] == countryName
              select (int)dr["id"]).FirstOrDefault();
demonplus
  • 5,320
  • 11
  • 48
  • 64
Seattle Leonard
  • 6,238
  • 3
  • 26
  • 37
21
foreach (DataRow row in Datatable.Rows) 
{
    if (row["CountryName"].ToString() == userInput) 
    {
        return row["CountryID"];
    }
}

While this may not compile directly you should get the idea, also I'm sure it would be vastly superior to do the query through SQL as a huge datatable will take a long time to run through all the rows.

İlker Elçora
  • 580
  • 4
  • 13
Jimmy
  • 9,546
  • 13
  • 56
  • 77
  • Simple and straight to the point but as you said, this can cost alot in the later future. – peace May 26 '10 at 22:02
  • All in all nothing is going to be more efficient than just using SQL, LINQ to Datatables and Datatables / Dataviews will all have to load all of the records into memory and then query them – Jimmy May 27 '10 at 02:50
  • Another option is simply just using linq to sql – Jimmy May 27 '10 at 02:50
  • this is the fastest and direct way to work with a fair amount of data. Best answer IMHO. – Liquid Core Oct 08 '13 at 11:10
15

I suggest such way based on extension methods:

IEnumerable<Int32> countryIDs =
    dataTable
    .AsEnumerable()
    .Where(row => row.Field<String>("CountryName") == countryName)
    .Select(row => row.Field<Int32>("CountryID"));

System.Data.DataSetExtensions.dll needs to be referenced.

Zar Shardan
  • 5,297
  • 2
  • 35
  • 35
Deilan
  • 4,410
  • 3
  • 36
  • 50
  • 2
    This is exactly what I was looking for, thank you! I almost missed your final comment about the reference--thank you for that as well. – kwill Aug 10 '16 at 20:50
2

As per the title of the post I just needed to get all values from a specific column. Here is the code I used to achieve that. 

    public static IEnumerable<T> ColumnValues<T>(this DataColumn self)
    {
        return self.Table.Select().Select(dr => (T)Convert.ChangeType(dr[self], typeof(T)));
    }
cdiggins
  • 16,566
  • 6
  • 99
  • 96
1

I suppose you could use a DataView object instead, this would then allow you to take advantage of the RowFilter property as explained here:

http://msdn.microsoft.com/en-us/library/system.data.dataview.rowfilter.aspx

private void MakeDataView() 
{
    DataView view = new DataView();

    view.Table = DataSet1.Tables["Countries"];
    view.RowFilter = "CountryName = 'France'";
    view.RowStateFilter = DataViewRowState.ModifiedCurrent;

    // Simple-bind to a TextBox control
    Text1.DataBindings.Add("Text", view, "CountryID");
}
Dalbir Singh
  • 2,619
  • 3
  • 25
  • 29
1

Datatables have a .Select method, which returns a rows array according to the criteria you specify. Something like this:

Dim oRows() As DataRow

oRows = dtCountries.Select("CountryName = '" & userinput & "'")

If oRows.Count = 0 Then
   ' No rows found
Else
   ' At least one row found. Could be more than one
End If

Of course, if userinput contains ' character, it would raise an exception (like if you query the database). You should escape the ' characters (I use a function to do that).

ACB
  • 950
  • 6
  • 8