CODEWITHSUNDEEP
c#sqldatatableado.net
Kiran Malgi
Kiran Malgi

Reputation: 97

Compare string values with double in DataTable.Select()

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?

enter image description here

My current code is

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

Upvotes: 2

Views: 14877

Answers (2)

ASh
ASh

Reputation: 35680

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

Upvotes: 5

Tim Schmelter
Tim Schmelter

Reputation: 460108

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.

Upvotes: 3

Related Questions