How to Get a Specific Column Value from a DataTable?

Question

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.

Answer 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");
}

Answer 2

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.

Answer 3

        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();

Answer 4

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).

Answer 5

I'm hesitant which solution is more efficient - Dal's or Seattle Leonard?