CODEWITHSUNDEEP
c#.netwinforms
peace
peace

Reputation: 887

How to Get a Specific Column Value from a DataTable?

I have a datatable. I need to fetch a certain column value based on the user input. For example, let's 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.

Upvotes: 53

Views: 405670

Answers (8)

user23048929
user23048929

Reputation: 1

for (int dc =0; dc < ColumnCount; dc++)
                {

                    sw.Write(d_table.Columns[dc].ToString());



                }

Upvotes: -1

Abdul Khaliq
Abdul Khaliq

Reputation: 2285

You can also use The Field extension method receives a type parameter. It has several parameter list overloads.

// Get the first & third field's value by column index.

   int weight = dataTable.Rows[0].Field<int>(0);
   string code = dataTable.Rows[0].Field<string>(2);

// Get the second & Fourth field's value by column name.

string name = dataTable.Rows[0].Field<string>("Name");
DateTime date = dataTable.Rows[0].Field<DateTime>("Date");

https://social.msdn.microsoft.com/forums/en-US/339cf1e3-215c-46c8-8721-f608b16cf3ae/how-to-get-the-column-value-from-the-data-table?forum=aspgettingstarted&prof=required

Upvotes: 0

cdiggins
cdiggins

Reputation: 18243

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

Upvotes: 2

Deilan
Deilan

Reputation: 4886

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.

Upvotes: 18

Seattle Leonard
Seattle Leonard

Reputation: 6776

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

Upvotes: 79

Jimmy
Jimmy

Reputation: 9815

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.

Upvotes: 26

ACB
ACB

Reputation: 1000

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

Upvotes: 2

Dalbir Singh
Dalbir Singh

Reputation: 2638

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

Upvotes: 1

Related Questions