Set a DataView (or DataTable.DefaultView) RowFilter for a custom type

Question

I am developing a datagridview filtering application. I am using the RowFilter property for dataview for filtering data. My database table contains fields of data types int and varchar. And I want to use the "LIKE" query in the RowFilter property to filter the dataview, but "LIKE" is used only for the string data type, and not for the int data type. Therefore, I want to convert a field of type int to a varchar data type, but I do not want to change the structure of the table. I just want the data type to be replaced by a temporary one only for my filter condition.

Can someone help me in solving this problem?

 string colname="ProductID"; string condition="111"; DataView dv = new DataView(); dv.Table = ds.Tables[0] ; dv.RowFilter ="CAST ("+colname+" AS TEXT) LIKE '"+ condition+"%'" ; 

Answer 1

RowFilter is run by .NET, not SQL Server. It supports a limited set of expressions, which are described in the Framework documentation.

http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

You should be able to use LIKE as well as CONVERT, but unlike the SQL server, it wants to be of type .NET.

 dv.RowFilter ="CONVERT("+colname+", System.String) LIKE '"+ condition+"%'" ; 

Answer 2

You can distinguish an integer ...

  SELECT * FROM table WHERE CAST(field AS TEXT) LIKE '%123%' 

Answer 3

you can use

 Convert(varchar(20),value) 

or

 cast(value is varchar)