Compare string values with double in DataTable.Select()

Question

My datatable salary column is string column and user gives input value in string format, like userInput="1000". But data stored in salary column is as shown in below image. How do I compare user input with data stored in data table?

My current code is

DataRows[] rows = dt.Select("Salary='"+userInput+"'");

Solution 1

to avoid formatting issues you should better compare numeric values. DataTable select filter supports Convert function:

var dt = new DataTable();
dt.Columns.Add("Salary", typeof(string));

// test values
dt.Rows.Add("0.10000");
dt.Rows.Add(".1");
dt.Rows.Add("-.1");
dt.Rows.Add("1.1");

decimal salary;
string userInput = "0.10";
if (decimal.TryParse(userInput, out salary))
{           
    DataRow[] matchedRows = dt.Select("Convert(Salary, 'System.Decimal') = " + salary.ToString());
    foreach(var r in matchedRows)
        Console.WriteLine(r["Salary"]);
}

output is:

.1
0.10000

Solution 2

I would use Linq-To-DataTable:

decimal value;
if(decimal.TryParse(userInput, out value))
{
   var filteredRows = from row in dt.AsEnumerable()
                      where row.Field<decimal>("Salary") == value
                      select row;
   // if you want an array:
   DataRow[] rows = filteredRows.ToArray();
}

If you insist on using DataTable.Select you have to remove the apostrophes:

DataRow[] rows = table.Select("Salary=" + userInput);

But this presumes that the input value is using the english format(so f.e. not using a different decimal separator like comma that even causes a SyntaxErrorException).

---

If the type of the column is actually string you need to parse it to double or decimal:

var filteredRows = from row in dt.AsEnumerable()
                   where decimal.Parse(row.Field<string>("Salary")) == value
                   select row;

But in this cae you should better fix the wrong datatype instead.

Comments

[René Vogt]

I understood OP that hisSalary column is of type string too. So does row.Field<decimal> really work?

[Tim Schmelter]

@RenéVogt: i think it's double or decimal, that's why OP's code doesn't work. If he insisted on using DataTable.Select he would need to remove the apostrophes like in: dt.Select("Salary=" +userInput). But then it depends on the current culture if the string conversion doesn't cause another issue.