Partager via

How use Convert in a DataTable select?

  • Article

How to use Convert() when doing DataTable.Select ?

Sometimes you may need or want to select out a number of rows from a DataTable using the LIKE operator, this is no problem on a column with a string type.

But what if you wish to do it on a column with, for example, an Integer type?

In short, you need to convert it to the appropriate .Net Framework type and not the SQL type.

Or you can use LINQ.

For example, create a new C# application; replace the main method code with this:

            DataTable dt = new DataTable();

           

            dt.Columns.Add("Age", typeof(int));

            dt.Columns.Add("Name", typeof(String));

            dt.Rows.Add(new object[] { 25, "Mike" });

            dt.Rows.Add(new object[] { 26, "Paul"});

            dt.Rows.Add(new object[] { 41, "John" });

            dt.Rows.Add(new object[] { 37, "Anna" });

            dt.Rows.Add(new object[] { 19, "Mary" });

            dt.Rows.Add(new object[] { 31, "Bret" });

            // Using the SQL Server type of nvarchar(10)

            // String sql = "Convert(Age, nvarchar(10)) like '2%'";

            // Fails with:

            // Unhandled Exception: System.Data.EvaluateException: The expression contains undefined function call nvarchar().

            // Using just String

            // String sql = "Convert(Age, String) like '2%'";

     // fails with:

            // Unhandled Exception: System.Data.EvaluateException: Invalid type name 'String'.

            // Using the .Net Framwork type: System.String or 'System.String' works

            String sql = "Convert(Age, 'System.String') like '2%'";

           

            DataRow[] rows = dt.Select(sql);

            foreach (DataRow row in rows)

            {

                Console.WriteLine("Name: {1} is {0}", row[0].ToString(), row[1].ToString());

            }

            // or if you wish, simply use LINQ

            Console.WriteLine("\nUsing LINQ:");

            var v = from x in dt.AsEnumerable() where x["Age"].ToString().StartsWith("2") select x;

            foreach (var row in v)

            {

           Console.WriteLine("Name: {1} is {0}", row[0].ToString(), row[1].ToString());

            }

".NET Framework Class Library - DataColumn.Expression Property"

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

Comments